0

I have downloaded Malaysia map from opensteetmap website and I have started to work on it in PostGIS. I need to find out the distance of some points to the nearest street over the map, so I used this query to find the distance in kilometer:

select name,ST_Distance(ST_Transform(ST_SetSRID(ST_Point(101.6638513,3.114944),4326),3857),way)*60*1.6 as d from planet_osm_line order by d asc limit 5;

name | distance
"Lebuhraya Persekutuan";453.941853570964
"Lebuhraya Persekutuan";453.941853570964
"";1291.97891210748
"Lebuhraya Persekutuan";1741.0179885363
"Kerinchi Link";1990.39830038167

The results are strange and biased by the way. The street name is correct and they are the nearest street to the targeted point, however the calculated distance is wrong. I noticed that SRID in openstreetmap is 3857, so I converted my target point to that SRID. If you check these streets and this point over googlemap, you will notice that the distance between them is almost nothing. Do you have any idea why the calculation is so distorted? (I suspected that conversion between SRIDs has not done correctly)

3

You shouldn't calculate distances in Pseudo Mercator (EPSG:3857). It will give you strange results. The most versatile way would be to do it in Geography, which calculates it on a sphere in meters.

So:

select name,
ST_Distance(
ST_SetSRID(ST_Point(101.6638513,3.114944),4326)::geography,
ST_Transform(way, 4326)::geography) as d from planet_osm_line order by d asc limit 5
  • Thanks for your replay. To my understanding geography is for considering this fact that the earth surface is in curvy shape and gives more accurate result. But in my case the 5 meter distance is calculate 250 km which is very far. Secondly, I got this error "Only lon/lat coordinate systems are supported in geography". I think it cannot convert it. – Moohebat Jul 19 '18 at 11:06
  • Edited my query. – HeikkiVesanto Jul 19 '18 at 11:17
  • Re-edited my query, should work now. – HeikkiVesanto Jul 19 '18 at 13:41
  • Cool! The result is more realistic now! Thanks @HeikkiVesanto – Moohebat Jul 20 '18 at 2:05
  • Ops, sorry I did not notice that is it in degree, by converting to KM still calculated distance is unrealistically far! Can it be because of the map?! – Moohebat Jul 20 '18 at 2:15
1

As I suspected the problem was happened during converting osm into Postgres. I re-run the osm2postgres and kept the geographic projection to lat,long (SRID 4326) and now the ST_Distance gives me the right answer.

osm2pgsql -c -l -d db_name -H localhost -U postgres -W osmfile_path.osm

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.