QGIS Add geometry to attribute table

Question

I am importing my data into QGIS from a local postgreSQL server. The dataset loaded into the database contains 11 columns including a X and Y column which are in the CRS (Coodinate Reference System): EPSG:21781, CH1903 / LV03. I am trying to plot these points as a layer in QGIS but when I import it using the “Add PostGIS layer” I have to click “Also list tables with no geometry” to find it. Once added it appears as an attribute table which I can go into the layer properties and select the correct CRS but it still doesn't appear correctly.

I am still new to QGIS and PostgreSQL, am I doing something wrong or do I need to define the coordinates in the database before I import them to QGIS?

Answer 1

You need to create the geometry on the table. As of now, you are just displaying a table that does not contain any point so the coordinate system does not even apply.

So, first you would create the geometry column via PostGIS AddGeometryColumn

SELECT AddGeometryColumn ('myschema','mytable','geom',21781,'POINT',2);

Then you would update this new column with existing values.

UPDATE mytable SET geom = ST_SETSRID(ST_MakePoint(X, Y), 21781);