Querying contour lines for elevation at point using PostGIS?

Querying contour lines for elevation at point using PostGIS?

Is there a way to interpolate between these contour lines for a given point using postgis?

I've come up with the following query to gather the potential contour lines of interest:

$sql = "SELECT osm_id, ele, ST_AsGeoJSON(way) AS geojson, ST_distance(way, ST_Transform(GeomFromText('POINT(".$lon." ".$lat.")',4326),900913)) AS dist, ST_ClosestPoint(way,ST_Transform(GeomFromText('POINT(".$lon." ".$lat.")',4326),900913)) as closest, ST_Azimuth(ST_Transform(GeomFromText('POINT(".$lon." ".$lat.")',4326),900913), ST_ClosestPoint(way,ST_Transform(GeomFromText('POINT(".$lon." ".$lat.")',4326),900913)))/(2*pi())*360 as angle FROM planet_osm_line WHERE way && ST_Transform(GeomFromText('POINT(".$lon." ".$lat.")',4326),900913) ORDER BY dist LIMIT 10";

The post processing of these results I will just describe rather than posting my convoluted code. It seems the real crux of the problem is finding the two contours of interest. Using the two closest contours isn't always the best or even accurate solution as they may both be on one side of the point of interest.

What I did was accept the closest contour as a keeper. Then check the azimuth between the point of interest and the closest point and compare this azimuth to the similiar azimuth calculated from the first accepted contour. The closer the delta between these two was to 180 the higher the probability that this was my second contour.

The function ST_Line_Interpolate_Point will interpolate the x, y and z values, given a line as input and a fraction along the line to interpolate to, eg,

SELECT ST_AsText(ST_Line_Interpolate_Point(ST_MakeLine(ST_MakePoint(0,0,0), ST_MakePoint(10, 10, 2)),.5));

returnsPOINT Z (5 5 1)as you would expect.

To create the line, you can use ST_ClosestPoint, which will return the nearest point on a line, to the point you are trying to interpolate to. If you do this for the nearest two contours, you can substitute the values into ST_MakeLine in place of the two ST_MakePoints above.

You may also find ST_Line_Locate_Point useful, for finding points on a line. You said you wanted pointers, rather than a full query, so I hope this helps. '

Watch the video: Tutorial - Excel 2010 - 10 Things you must know