0

I want to know that what is the type of projection (900913, 4326 or so on..) of long/lat co-ordinates. I happen to come across a slight confusion while querying planet_osm_lines when I dump (St_DumpPoints) LineString into point and trying to get long/lat from that point. In the following code, I tried to transform into longitude using 900913 and 4326 projection. I realized that 4326 give me correct result while 900913 give the same as point value.

So, my question is, is 4326 default projection value for long/lat? i.e. any long/lat data that I get from, such as smartphone, or other device are in form of 4326?

In addition, while computing (st_distance, st_closestPoint etc.) the lat/long co-ordinates with the database 900913 based co-ordinates do I have to change it into 900913 projection? such as:

ST_Transform(ST_SetSRID(ST_makePoint(long, lat),4326), 900913);

COde:

osm=# select d.osm_id, d.name, st_x(st_transform(st_setsrid(d.a,900913),900913)), st_y(d.a) from (SELECT osm_id, name, (ST_DumpPoints(st_astext(way))).geom as a FROM planet_osm_line limit 1000) as d;
      osm_id   |       name        |    st_x    |    st_y
    -----------+-------------------+------------+------------

 328454927 |                   | 9310610.35 | 3223602.19
 328454927 |                   |  9310606.6 | 3223579.35
 328454927 |                   | 9310597.74 | 3223554.21
 328454927 |                   | 9310590.81 | 3223541.52
 328454927 |                   | 9310591.28 | 3223530.75
 328454927 |                   | 9310593.43 | 3223519.49
 328454927 |                   | 9310593.43 | 3223495.79

osm=# select d.osm_id, d.name, st_x(st_transform(st_setsrid(d.a,900913),4326)), st_y(d.a) from (SELECT osm_id, name, (ST_DumpPoints(st_astext(way))).geom as a FROM planet_osm_line limit 1000) as d;
  osm_id   |       name        |       st_x       |    st_y
-----------+-------------------+------------------+------------
 328454927 |                   | 83.6386358188641 | 3223602.19
 328454927 |                   | 83.6386021320409 | 3223579.35
 328454927 |                   | 83.6385225413067 | 3223554.21
 328454927 |                   | 83.6384602880575 | 3223541.52
 328454927 |                   | 83.6384645101394 | 3223530.75
 328454927 |                   |  83.638483823918 | 3223519.49
 328454927 |                   |  83.638483823918 | 3223495.79

I would be very helpful to have a clear understanding on these confusion from you guys.

  • You have two very different questions in this Question. GIS SE policy is to have one question per Question. Please edit this question. – Vince Sep 14 '15 at 13:35
1

There are hundreds of pre-defined geographic coordinate system (GCS) EPSG codes, and an infinite number of potential GCS strings. The best projection code is the appropriate one, not any "default" value. Using an incorrect GCS code could shift the location by as much as a kilometer (using a projected CS could put you tens of thousands of kilometers away).

0

1) The coordinates in (EPSG) 4326 are long/lat, and they are in decimal degrees. The coordinates in (SRID) 900913 are easting/northing, and they are in meters. The confusion arise because Google Map shows (SRID) 900913 coordinates as long/lat degrees when they are actually easting/northing meters.

2) You should avoid using ST_Distance or any functions that return distances/lengths on (SRID) 900913 coordinates for many reasons. Firstly, (SRID) 900913 is not conformal. Even for (conformal) Mercator and Transverse Mercator, distances and areas are only preserved near the chosen latitude and longitude of true scale. A general guide is that you should not use ST_Distance on any whole-world map projections.

So, when in doubt, just stick to ST_Distance on (EPSG) 4326 coordinates which will return the geodesic distances.

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.