I'm using postGIS, and I'm not very familiar with SQL.
I can successfully insert into my markers
table as long as I do something like this (pseudocode!):
'INSERT INTO markers(created_by, title, description, lat, lng, geography)\
values($1, $2, $3, $4::decimal, $5::decimal, ST_SetSRID(ST_MakePoint($5::decimal, $4::decimal), $6))',
[
data.created_by,
data.title,
data.description,
data.lat,
data.lng,
4326
]'
As you can see, I am doing ST_SetSRID(ST_MakePoint($5::decimal, $4::decimal), $6)
from my application code.
I want to avoid that, and instead put this on the database side. Since I can do things like auto time stamps on the db side, I'm wondering if I can also do ST_SetSRID(ST_MakePoint(lng, lat), 4326)
on the table itself.
This way, the application only needs to provide lat and lng, and the database will do the ST_SetSRID(ST_MakePoint())
operation.
Thanks
Edit: I'm not sure whose answer to mark as +1 since both offered tremendously good help and advice. I hope future readers will read both and hopefully learn something!
You can do this by using a trigger. Your insert call would not deal with the geometry, only with lat-long and other non-spatial fields, and the trigger function will create the geometry. Let's not forget to do the same when updating the row.
Note that I have hard-coded the SRID as it is not possible to pass extra dynamic parameters to the trigger function. If need be, add a field to your table to hold this value and you can refer to it as new.srid
CREATE OR REPLACE FUNCTION markers_geog_tg_fn() RETURNS trigger AS
$BODY$BEGIN
IF TG_OP = 'INSERT' AND (NEW.lat ISNULL or NEW.lng ISNULL ) THEN
RETURN NEW; -- no geometry
ELSIF TG_OP = 'UPDATE' THEN
--Lat Long updated to null, erase geometry
IF NEW.lat ISNULL or NEW.lng ISNULL THEN
NEW.geography = NULL;
END IF;
IF NEW.lat IS NOT DISTINCT FROM OLD.lat and NEW.lng IS NOT DISTINCT FROM OLD.lng THEN
RETURN NEW; -- same old geometry
END IF;
END IF;
-- Attempt to transform a geometry
BEGIN
NEW.geography := ST_SetSRID(ST_MakePoint(NEW.lng::decimal, NEW.lat::decimal), 4326))
EXCEPTION WHEN SQLSTATE 'XX000' THEN
RAISE WARNING 'geography not updated: %', SQLERRM;
END;
RETURN NEW;
END;$BODY$ LANGUAGE plpgsql;
CREATE TRIGGER markers_geog_tg BEFORE INSERT OR UPDATE
ON markers FOR EACH ROW
EXECUTE PROCEDURE markers_geog_tg_fn();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With