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
longitude. I have created a new column
geom in the above table
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?