2

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?

2 Answers 2

8

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;
    
3
  • I think the original question is fine in Microsoft SQL Server, and CONVERT is not an ISO standard SQL function, so if I had to bet I'd say it is SQL Server and not Postgres. Apr 7, 2021 at 7:08
  • @ConcreteGannet and indeed I thought as much...but I have to work with whatever OP cares to share in terms of information right. I added a note about this, though.
    – geozelot
    Apr 7, 2021 at 7:23
  • I take it back! float8 isn't an MSSQL data type. Thanks for your thoughts. Apr 8, 2021 at 4:02
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(...

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.