0

I've got in PostgreSQL/PostGIS the table "points" with latitude and longitude fields. I've done

update  points
set geom = st_point (longitude, latitude);

and also

select updategeometrysrid ('points', 'geom', 3395);

In QGIS I've added that table with the Data Source Manager to a project in EPSG::3395 with a shapefile layer already loaded. However, points from PostGIS table do not appear correctly projected. Any suggestions to fix it?

  • 1
    EPSG SRID 3395 is not latitude/longitude degrees -- It's Web Mercator meters. Use ST_SetSRID(ST_MakePoint(longitude,latitude),4326) to UPDATE your geometry, and don't forget to build a spatial index. – Vince Jun 20 at 10:37
  • Why 4326 instead of 3395? – Jesús Pinilla Jun 20 at 11:23
  • 1
    Because 4326 is WGS84 decimal degrees (what you have). If you want to store 3395, you'd need to nest in an ST_Transform( ddgeom , 3395). – Vince Jun 20 at 11:27
  • Agh, Vince! 3395 is not Web Mercator, but World Mercator. – mkennedy Jun 20 at 23:18
0

All updategeometrysrid does is set metadata for a program to correctly read the geometry type. Therefore, if the geometry is not actually recorded using 3395, any features position will be incorrect.

I suspect you need st_transform to get the geometry from it's original type to 3395

  • 1
    A ST_Transform would be necessary to change the geometry in the table, but QGIS should be smart enough to project on the fly when the metadata is correct. To make this a complete answer, you should not only state what's wrong, but how to fix it (in this case, including the option to either change the metadata to match the data or change the data to match the metadata). – Vince Jun 20 at 11:00
0

try as below which stores the coordinates in geometry in Geographic coordinates, then QGIS should be able to display the points appropriately on the existing layer which is in 3395

update  points
set geom = ST_SetSRID(ST_Point(longitude, latitude),4326)

or try as below which stores the coordinates in 3395 coordinate system

update  points
set geom = ST_Transform(ST_SetSRID(ST_Point(longitude, latitude),4326),3395)

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.