2

I have a postgresql database which there is the table have lat & long columns. The postgresql does install the postgis extension. My question is 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! Thanks.

4

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);
  • 1
    Great! It works! I can see the point in QGIS! – wing suet cheung Oct 14 '14 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. – wing suet cheung Oct 14 '14 at 4:22
  • How are you feeding your database ? If it is a SQL statement in a script you can update it to insert data in geom: – syldor Oct 14 '14 at 4:25
  • 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 '14 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.