QGIS - PostGIS Dynamic Point Layer

Question

I currently have a shp file with ~300 points. This shp file stores a bore name, x, y, z, and a few other attributes.

In a test PostGIS database, via QGIS, I have imported that shp file, called 'collars'. I have then gone and added holes to the 'collars' table, adding 3 new rows with all the required data, boreid, x, y, z etc.

Unfortunately I have realised upon re-opening QGIS that the layer does not automatically display these new holes. I understand that as I didn't add anything into the 'geometry' column these news rows of data have no spatial locations in the PostGIS database.

What I would like to know is can I cause the layers spatial data to recalculate, based off the x and y values, every time I open QGIS or whenever new data is added so that this 'collars' layer becomes a 'live' linked layer displaying new data as it is added in?

Answer 1

You can use something like

UPDATE your_points SET geom=ST_SetSRID(ST_MakePoint(x,y),31468)

(EPSG:31468 is my favorite srid, yours may differ)

Note that ST_MakePoint gives only raw coordinated, which doesn't match your SRID, therefore you need to assign an SRID to the gemeotry with ST_SetSRID.

http://postgis.net/docs/ST_MakePoint.html

http://postgis.net/docs/ST_SetSRID.html

If you are not so familiar with trigger functions, you can manually run the above query when inserted all your data.

Hint:

A better approach would be perhaps to directly collect your data in QGIS, open your table in QGIS, set your points in the map and gerate the coordinates if needed via ST_X and ST_Y.

Or if you allready have the coordinates in a table, open it as a comma-separated textfile (or spreadsheet, with the spreadsheet layer extension) in QGIS and let QGIS generate the geometries for you.

http://postgis.net/docs/manual-2.2/ST_X.html

http://postgis.net/docs/manual-2.2/ST_Y.html