1

I have calculate distance from one point to a linestring. I have used the following query

WITH data AS (
SELECT geom::geometry AS road_network from linestring)

SELECT  ST_Distance(ST_Line_Interpolate_Point(road_network, ST_Line_Locate_Point(road_network,ST_GeomFromText('POINT(90.402 23.753)'))), ST_GeomFromText('POINT(90.402 23.753)'))  as Dist 


FROM data

ORDER BY Dist
Limit 1

Its returning the following result

0.000501079237817185

I want to get the result in Meter. How can I convert it? I am using WGS84 projection system. I know the ST_Transform function but I need a perfect query where I use this function.

Thanks

Devils Dream
  • 3,179
  • 9
  • 44
  • 78
  • For length in meters use "ST_Length_Spheroid" function. http://gis.stackexchange.com/questions/32874/how-to-calculate-length-of-polyline-geometry-for-several-tables-in-postgis – spatialhast Mar 14 '13 at 10:11
  • Thanks but I am newbie. If you can answer with codes it will help me too much. – Devils Dream Mar 14 '13 at 10:28
  • Doing is learning. replace ST_DISTANCE with ST_Length_Spheroid and add 'SPHEROID["WGS 84",6378137,298.257223563]' to second argument – simpleuser001 Mar 14 '13 at 10:45

1 Answers1

3

Why are you locating and interpolating those points to get the distance?

About your question: Easiest is to convert to geogrphy type on the fly:

SELECT  ST_Distance(geom::geography,ST_GeographyFromText('POINT(90.402 23.753)')) as Dist 
FROM linestring
ORDER BY Dist
Limit 1;
Nicklas Avén
  • 13,241
  • 1
  • 39
  • 48
  • 1
    I am trying to query closest roads from a point. This is why I used locate and interpolate. Could you please make my code to convert into Meter? It will be a great help. Thanks for reply – Devils Dream Mar 14 '13 at 10:33
  • 1
    @DevilsDream Have you tried the above. It will give you the distance to the closest road in the table linestring (which from your query seems to be the name of your linestring table). You can yourself cast geometry to geograpy like I did (::geography) if you absolutely want to make it complicated. distance calculations on geography type returns in meter. – Nicklas Avén Mar 14 '13 at 11:13
  • Thats like a boss. Thanks a lot Nicklas Aven. Could you please check this issue http://gis.stackexchange.com/questions/54427/how-to-findout-direction-postgis – Devils Dream Mar 14 '13 at 11:41