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?

Improve this question
4
  • 2
    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, 2019 at 10:37
  • Why 4326 instead of 3395?
    – jpinilla
    Jun 20, 2019 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, 2019 at 11:27
  • 1
    Agh, Vince! 3395 is not Web Mercator, but World Mercator.
    – mkennedy
    Jun 20, 2019 at 23:18

2 Answers 2

Reset to default
1

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)
Improve this answer
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

Improve this answer
1
  • 2
    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, 2019 at 11:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.