Changing PostgreSQL table to support PostGIS

Question

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!

Answer 1

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);

• wing suet cheung: Great! It works! I can see the point in QGIS!
• wing suet cheung: How can it update the geometry column e.g. the_geom automatically? The table is the dynamic table to store the GPS data.
• syldor: 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));