1

I have a PostgreSQL database which there is the table have lat & long columns. The PostgreSQL does install the PostGIS extension.

How can I change the table with lat & long columns to support PostGIS?

How can I add the the_geom column?

I would like to access this table by using QGIS finally!

1 Answer 1

5

So you're saying that you installed the PostGIS extension ? (CREATE EXTENSION postgis;)

If so, you have to add a column geometry whith the following function:

AddGeometryColumn(varchar table_name, varchar column_name, integer srid, varchar type, integer dimension, boolean use_typmod=true);

So if your table is named myTable:

SELECT AddGeometryColumn('myTable', 'geom', 4326, 'Point', 2);

(You can choose the SRID of your choice) Once your column is created, you have to set all the values:

UPDATE myTable SET geom=ST_MakePoint(longitude, latitude);

Or to be more precise, you can create a point marked as WGS84:

 UPDATE myTable SET geom=ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
3
  • 1
    Great! It works! I can see the point in QGIS! Oct 14, 2014 at 4:20
  • How can it update the geometry column e.g. the_geom automatically? The table is the dynamic table to store the GPS data. Oct 14, 2014 at 4:22
  • How do you feed your database ? If it is a SQL statement in a script you can update it so it inserts the geom data: INSERT into myTable VALUES (longitude, latitude, ST_MakePoint(longitude, latitude));
    – syldor
    Oct 14, 2014 at 4:26

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.