Generate a geometry column from latitude and longitude columns with SQL

Question

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?

Answer 1

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;
   

Answer 2

I think you are missing a plus character between the literal space and the second call to the convert function. I added spaces either side of the plus operators, which I think helps one spot the problem.

My suggested complete statement:

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

My change in bold below: ... ' ' + convert(...