1

I have a MultiLinestring, example coordinates, (698745.50201423 4210011.04002000) and I would like to convert them to something like (24.362 42.458) to use in Google Maps. When I try to get the SRID from the shapefile, it tells me it is an unknown (0) SRID.

I use SELECT UpdateGeometrySRID('roads','geom',4326); to set SRID in 4326 but again the format of coordinates is not changed. After I tried: 

ALTER TABLE roads
    ALTER COLUMN geom TYPE geometry(MultiLineString,4326) USING ST_Transform(geom,4326);

and 

  ALTER TABLE roads 
  ALTER COLUMN geom TYPE geometry(MULTILINESTRING, 4326) 
    USING ST_SetSRID(geom,4326);

but again the format of the coordinates is still like (698745.50201423 4210011.04002000).

nmtoken
  • 13,355
  • 5
  • 38
  • 87
vagelis
  • 599
  • 2
  • 13
  • 19

2 Answers2

6

You can't transform from one SRID to another without knowing what the SRID you are transforming from is. It looks like in your case that the coordinates are Spherical Mercator, which is SRID 3857. So, if this is true, then you can use ST_Transform in conjunction with ST_SetSRID:

UPDATE roads SET geom = ST_Transform(ST_SetSRID(geom, 3857), 4326);

and then run,

ALTER TABLE roads ALTER COLUMN geom TYPE geometry(MULTILINESTRING, 4326);

If this appears in the right place in Google Maps, then you are done, if not, then you might be looking at a UTM zone. Presumably you have information on what part of the world this data are from?

EDIT: on reflection if you don't know the SRID of the input geometry, it might be safer to create a new column and update that.

ALTER table roads add column geom_4326 geometry(MULTILINESTRING, 4326);
UPDATE roads set column geom_4326 = ST_Transform(ST_SetSRID(geom, 3857), 4326);

which will avoid potentially destroying your input data. You can, of course, drop and rename columns once you are sure it is in the right place.

Mike T
  • 42,095
  • 10
  • 126
  • 187
John Powell
  • 13,649
  • 5
  • 46
  • 62
0

Thats because effectively you say "the coordinate 698745.50201423 4210011.04002000 is in SRID 4326", which is not what you want.

You want to transform the coordinate from your source reference system to a destination reference system.

So you need to:

  1. Know the source coordinate system
  2. Use ST_Transform ( see https://postgis.net/docs/ST_Transform.html )
Mike T
  • 42,095
  • 10
  • 126
  • 187
til_b
  • 5,074
  • 1
  • 19
  • 36