CODEWITHSUNDEEP
sqlgispostgis
nicolasst22
nicolasst22

Reputation: 99

Get distance in km in Postgis

I have 2 tables: City and River.

City: ID, city_name, the_geom (poin)
River: id, name, the_geom (multiline)

For each river I want to know the city farthest away and its distance in km.

For example... I have the query

Select city1.city_name, st_distance(city1.the_geom, river1.the_geom)
from city city1, river river1
where 
river1.name = 'Albany' 
order by st_distance(city1.the_geom, river1.the_geom) desc
limit 1

In this case, I get the city farthest away from the Albany River. Is this the best way to query?

I get this result:

City_name; distance
"Topeka";  13.2534798131185

But I don't know if the result is in km... If it isn't... How can I get the result in km??

Upvotes: 0

Views: 1090

Answers (1)

mlinth
mlinth

Reputation: 3118

The units returned by st_distance are in spatial ref units - assuming you are using lat/lon (EPSG 4326) those units will be decimal degrees.

You have two choices - project to a suitable coordinate system in metres, or use the geography type.

Here is your query using geography:

SELECT city1.city_name, 

 st_distance(city1.the_geom::geography, river1.the_geom::geography) /1000 -- convert m to km

  FROM city city1, river river1
  WHERE
         river1.name = 'Albany' 
  ORDER by st_distance(city1.the_geom::geography, river1.the_geom::geography) desc 
  LIMIT 1

(Note: I am not sure of the value of casting to geography in the order by statement).

Upvotes: 1

Related Questions