Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Unit of return value of ST_Distance

I need to calculate the distance between

  1. all buildings and
  2. all hospitals

in a map imported from OSM.

I use following query:

SELECT building_id, hospital_id, ST_Distance(building_centroid, hospital_location)
FROM 
(
select planet_osm_polygon.osm_id building_id, ST_Centroid(planet_osm_polygon.way) building_centroid
from planet_osm_polygon
where building = 'yes'
) buildings,
(
select planet_osm_point.osm_id hospital_id, planet_osm_point.way hospital_location
from planet_osm_point  
where amenity = 'hospital') hospitals

I get strange results - the distance is always smaller than 1.

How can I get the to know the unit, in which these values are reported?

Update 1: Sample result:

Sample result of the query above

Update 2: This query seems to work

SELECT building_id, hospital_id, ST_Distance_sphere(building_centroid, hospital_location) distance
FROM 
(
select planet_osm_polygon.osm_id building_id, ST_Centroid(planet_osm_polygon.way)  building_centroid
from planet_osm_polygon
where building = 'yes'
) buildings,
(
select planet_osm_point.osm_id hospital_id, planet_osm_point.way hospital_location
from planet_osm_point  
where amenity = 'hospital') hospitals
ORDER BY distance
like image 718
Dmitrii Pisarenko Avatar asked Nov 04 '12 19:11

Dmitrii Pisarenko


1 Answers

The general rule for units is that the output length units are the same as the input length units.

The OSM way geometry data has length units of degrees of latitude and longitude (SRID=4326). Therefore, the output units from ST_Distance will also have lenth units of degrees, which are not really useful.

There are several things you can do:

  • Use ST_Distance_Sphere for fast/approximate distances in metres
  • Use ST_Distance_Spheroid for accurace distances in metres
  • Convert the lat/long geometry data types to geography, which automagically makes ST_Distance and other functions to use linear units of metres
like image 129
Mike T Avatar answered Sep 30 '22 04:09

Mike T