[GIS] Generate a geometry column from latitude and longitude columns with SQL

dbmanagerpostgispostgresqlqgissql

My table has the columns latitude and longitude in float8 type.
I want to generate a geometry column from these two columns.
I make the following query in the SQL Window of the DB Manager in QGIS.

ALTER TABLE populated_places
ADD geographyColumn AS geography::STGeomFromText('POINT('+convert(varchar(20),latitude)+' 'convert(varchar(20),longitude)+')',4326)

I get the following error: syntax error at or near "AS"

How can I solve this problem?

Best Answer

Your statement looks like a random chain of SQL related commands, or a MySQL/MS SQL Server command (see @ConcreteGannet's answer), so in accordance with your questions tags, the actual solution to this issue is likely to properly introduce yourself to the PostgreSQL dialect, and the PostGIS extension.

The correct set of commands would be to

  1. add the geom column of type GEOMETRY, and register the typemod constraints (POINT, 4326) to the system catalogues: 
    ALTER TABLE populated_places
  ADD COLUMN geom GEOMETRY(POINT, 4326)
;
  2. fill the new column with actual geometries 
     UPDATE populated_places
   SET  geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)
 ;
  3. create a spatial index covering your new geometries [optional, but highly recommended] 
    CREATE INDEX ON populated_places
  USING GIST (geom)
;
  4. update table statistics for the query planner [optional, but highly recommended] 
    VACUUM ANALYZE populated_places;
Related Question