2

I have a PostgreSQL 10 Server (x64) running on my Windows 8 (x64 based) machine. On the top of my Postgres database, there is PostgREST API running that receives GPS data (5 seconds interval) from a GPS sensor, and stores it into database. The sample data looks like this:

id  datetime             lon        lat
1   2015-06-17 15:31:02  -1.123456  36.123456
2   2015-06-17 15:36:05  -1.123654  36.123533
3   2016-06-17 15:41:08  -1.123788  36.123623
...

Now, I would like to add points geometry to the above data based on latitude and longitude. I have created a new column geom in the above table my_tbl.

Alter my_tbl add column geom geometry;

Instead of doing:

Update my_tbl SET geom = ST_SetSRID(ST_MakePoint(lon, lat), 4326);

I would like to automate this task. That is, I would like to automatically add point geometry to my_tble each time GPS record is inserted. I came across these (e.g. Adding additional geometry column in PostGIS? and Automatically calculating geometry area when adding polygon in ArcMap?) relevant questions already, which point out towards PostgreSQL triggers. However, my scenario (continuous GPS logging at 5 seconds interval) is a bit different, and I would still like to receive some suggestions.

Would someone like to help/explain a bit on my scenario, and the task I want to achieve?

  • 1
    there are plenty of options, form different parts of your stack; e.g. it's perfectly fine to INSERT INTO my_table (datetime, lon, lat, geom) VALUES (<timestamp>, <lon>, <lat>, ST_SetSRID( ST_MakePoint( <lon>, <lat> ), 4326 ); from your ORM/app logic, which avoids the overhead of a trigger in a simple case like yours. – ThingumaBob Jul 8 at 10:20
  • Thanks a lot for your response! I would like to know more about "which avoids the overhead of a trigger". Does adding a trigger come at the expense of "heavier" database and consequently, slower performance? Secondly, why would I need to do INSERT my_tbl(datetime, lon, lat) since API is doing it for me? – khajlk Jul 8 at 10:39
  • 1
    Well, it will add the trigger invocation and a call to the trigger function for every record that is inserted. It's not exactly 'heavy', especially for a scenario like yours (the logic in the function will determine the performance penalty, really, and in your case you'd barely notice at all). But it's there, plus you will need at least a fundamental knowledge of how to manage and maintain triggers. The INSERT is an example; your API will have to issue INSERT commands at some point to get data into the DB, and it's trivial to add the geometry generation to those statements. – ThingumaBob Jul 8 at 10:57
5

my answer is based on: http://www.postgresqltutorial.com/creating-first-trigger-postgresql/ and https://stackoverflow.com/questions/39607334/postgres-trigger-creation-error-no-language-specified-sql-state-42p13

EDIT1: adjusting for ThingumaBob's comment

first you need to create a function to be used by your trigger action :

CREATE OR REPLACE FUNCTION update_geom()
  RETURNS trigger AS
$BODY$
BEGIN
   NEW.geom = ST_SetSRID(ST_MakePoint(NEW.lon, NEW.lat), 4326);

   RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE -- Says the function is implemented in the plpgsql language; VOLATILE says the function has side effects.
COST 100; -- Estimated execution cost of the function.

then you need to create your trigger:

CREATE TRIGGER update_geom_after_insert
  BEFORE INSERT
  ON my_tbl
  FOR EACH ROW
  EXECUTE PROCEDURE update_geom();
  • 2
    this works, but note that it adds significant overhead; this triggers a full table UPDATE every 5 seconds, which is heavy even if you had a NULLS FIRST index on that column. better use a BEFORE INSERT trigger FOR EACH ROW and set the NEW.geom record field accordingly. – ThingumaBob Jul 8 at 10:24
  • i had that in mind, but i didn't know that you could update before the insertion, i will revise the answer shortly. thank you! – vagvaf Jul 8 at 10:39
  • 1
    you don't want to UPDATE (I corrected my comment to avoid misunderstandings ,) ); in a BEFORE trigger, the trigger function receives a NEW record that stores the row values that are due to get inserted. with no geom provided, NEW.geom will be NULL. you can then simply say NEW.geom = ST_SetSRID(ST_MakePoint(NEW.lon, NEW.lat), 4326); and RETURN NEW – ThingumaBob Jul 8 at 10:50
  • thanks for the clarification. could you confirm that I updated the answer in the spirit of your comment? :) – vagvaf Jul 8 at 11:10
  • ,) perfect. AFAIK that's about the most performant trigger for this operation you can get, and most likely exactly what OP wants. – ThingumaBob Jul 8 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.