1

After import some data from OSM file into Postgre, the coordinates numbers are too big and having trouble to cast to Geography:

Coordinate values are out of range [-180 -90, 180 90] for GEOGRAPHY type

In my table:

select ST_AsText( the_geom )  from routes limit 4

"LINESTRING(-4902130.86 -2265840.91,-4902083.04 -2265907.16)"
"LINESTRING(-4903296.22 -2266295.18,-4903363.69 -2266333.39)"
"LINESTRING(-5705594.67 -3518944.1,-5705523.22 -3518937.37)"
"LINESTRING(-5484056.84 -1884629.16,-5484093.47 -1884510.24)"

I think the numbers should be between (-90 / 90, -180 / 180) ...

Info:

1: select ST_SRID( the_geom ) returns 900913 for all lines.

2: select UpdateGeometrySRID('public', 'routes', 'the_geom', 4326) does not converts the numbers, but SRID now shows 4326.

  • 900913 is google web Mercator aux.. 900913 is outdated epsg code should be now EPSG:3857 -- WGS84 Web Mercator (Auxiliary Sphere) But that is in meters. and won't work for geography type. simply changing the srid won't do either. If someone doesn't post something soon I will try to help. this is the limit of my capability now. – Brad Nesom Oct 31 '13 at 16:28
  • Ok, thanks. But .... why do UpdateGeometrySRID not changes the coordinates? By the way, in other table the SRID shows 0 (zero) and it's working perfectly (for points)... – Magno C Oct 31 '13 at 16:38
  • 1
    Because it only changes the information about SRID use ST_Transform. – Jakub Kania Oct 31 '13 at 16:53
  • YOU SAVE THE DAY ! ALTER TABLE routes ADD temp geometry; select UpdateGeometrySRID('public', 'routes', 'temp', 4326) ; update routes set temp = ST_Transform(the_geom,4326); select UpdateGeometrySRID('public', 'routes', 'the_geom', 4326) ; update routes set the_geom = temp; alter table routes drop column temp; – Magno C Oct 31 '13 at 17:17
1

From @Jakub Kania tip :

ALTER TABLE routes ADD temp geometry;
select UpdateGeometrySRID('public', 'routes', 'temp', 4326) ;
update routes set temp = ST_Transform(the_geom,4326);
select UpdateGeometrySRID('public', 'routes', 'the_geom', 4326) ;
update routes set the_geom = temp;
alter table routes drop column temp;
  • I can't change the_geom to 4326 before transform. ST_transform needs this information to make the magic, so I need the temp column. – Magno C Oct 31 '13 at 17:23

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.