0

I have created a point geometry using ST_GeomFromText function in PostGIS and simultaneous set the SRID to 3857.

Alter table data.trips_42779
add column geom geometry;

UPDATE data.trips_42779
SET geom_start = ST_GeomFromText('POINT(' || start_lon || ' ' || start_lat || ')', 3857);

However, when I check the SRID using the below query it shows SRID as 0 (see first row in the table in the image below).

select * from geometry_columns;

enter image description here

Also spatial join with any layer that has SRID as 3857 returns NULL.
How can I set the SRID of point layer created from Lon/Lat correctly to EPSG:3857 ?

2
  • 3
    You have an extra problem here: You can't use longitude and latitude as values into a point in Web Mercator without mapping within ~200 meters of the origin of Null Island. You need to reproject the WGS84 GCS coords to 3857, as in ST_Transform(ST_SetSRID(ST_MakePoint(start_lon,start_lat),4326),3857)
    – Vince
    Commented Oct 17, 2020 at 4:00
  • Thanks . This was helpful. Perhaps you can add this comment to the answer
    – Jio
    Commented Oct 18, 2020 at 22:36

1 Answer 1

4

Right now you're just querying the metadata of the tables, so because you didn't specify a CRS when creating the table, you'll get 0 for the SRID.

If you create the table this way:

Alter table data.trips_42779
add column geom geometry(Point, 3857);

Then you'll get an SRID value.

However, what does the following return?

select ST_SRID(geom_start) from trips_42799

Your Answer

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

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