Hi Pierre,<div><br></div><div>Sorry, I might not have been 100% clear. What you say is exactly what I want to achieve. That's the reason I'm posting the question here, because I do not want to run 40 odd queries if I can do it with one. Since I'm new to PostGIS though, I don't know how to, hence why I'm asking.</div>
<div><br></div><div>Thanks,</div><div>Tim</div><div class="gmail_extra"><br clear="all"><div>Tim-Hinnerk Heuer<br><br>Twitter: @geekdenz<br><div>Blog: <a href="http://www.thheuer.com/" target="_blank">http://www.thheuer.com</a></div>
</div><br>
<br><br><div class="gmail_quote">On 18 December 2012 08:53, Pierre Racine <span dir="ltr"><<a href="mailto:Pierre.Racine@sbf.ulaval.ca" target="_blank">Pierre.Racine@sbf.ulaval.ca</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
When you say: "one query is run for each point on the line", you mean your application does.<br>
<br>
Why don't you just create a complex query that will do everything in SQL so PostgreSQL will take charge of everything and optimize the management of the row reading for this single query? This is the advantage of having a complete GIS SQL API in the database.<br>
<div class="im"><br>
Pierre<br>
<br>
> -----Original Message-----<br>
> From: <a href="mailto:postgis-users-bounces@lists.osgeo.org">postgis-users-bounces@lists.osgeo.org</a> [mailto:<a href="mailto:postgis-users-">postgis-users-</a><br>
> <a href="mailto:bounces@lists.osgeo.org">bounces@lists.osgeo.org</a>] On Behalf Of Tim-Hinnerk Heuer<br>
</div><div class="im">> Sent: Friday, December 14, 2012 7:32 PM<br>
> To: PostGIS Users Discussion<br>
> Subject: Re: [postgis-users] PostGIS - Simple Raster Point Query<br>
><br>
</div><div class="im">> Good point and apologies I haven't been more clear.<br>
><br>
> We want to achieve this: <a href="http://npm.landcareresearch.co.nz/elevation/" target="_blank">http://npm.landcareresearch.co.nz/elevation/</a><br>
> with Open Source technology and our own data. Also, instead of doing a single<br>
> line, we want to implement a LineString which can have multiple control points<br>
> in it.<br>
><br>
> So far we've got it working by querying for each point on the line, say n<br>
> segments would give us n+1 points on the line and therefore n+1 queries which<br>
> could potentially be a lot. At the moment one query is run for each point on the<br>
> line, which is fine, but I believe there must be a much better way with PostGIS to<br>
> get all the points' st_values at once, preferably, like in the example above, also<br>
> the distance of each point from the first point on the line.<br>
><br>
> Thought an example application, similar to the one we are developing would be<br>
> better than a thousand words.<br>
><br>
> Thanks for all your help.<br>
><br>
> Regards,<br>
> Tim<br>
><br>
> Tim-Hinnerk Heuer<br>
><br>
> Twitter: @geekdenz<br>
><br>
</div>> Blog: <a href="http://www.thheuer.com" target="_blank">http://www.thheuer.com</a> <<a href="http://www.thheuer.com/" target="_blank">http://www.thheuer.com/</a>><br>
<div class="HOEnZb"><div class="h5">><br>
><br>
><br>
> On 15 December 2012 13:18, Pierre Racine <<a href="mailto:Pierre.Racine@sbf.ulaval.ca">Pierre.Racine@sbf.ulaval.ca</a>><br>
> wrote:<br>
><br>
><br>
> Maybe it would be better to describe more generally what you are<br>
> trying to achieve. Most complex processing can normally be achieved in one<br>
> complex query.<br>
><br>
> PostgreSQL should cache what was loaded in previous queries so that it<br>
> does not necessarily reload tiles at each query.<br>
><br>
> Pierre<br>
><br>
><br>
> > We query the points on the line with one query for each point. This<br>
> could be<br>
> > optimised like this:<br>
> > SELECT ST_AsText(<br>
> > ST_line_interpolate_point(<br>
> > ST_GeometryFromText('LINESTRING(0 0,0 1,1 1)'), CAST(p AS<br>
> numeric)/10<br>
> > )<br>
> > )<br>
> > FROM generate_series(1,10) p;<br>
> > to get all points. But this is still requiring one query for each point on<br>
> the line. I<br>
> > suspect, if we were to query all points at once, there will have to be<br>
> less reading<br>
> > of the raster, because the points are likely to be on the same tile.<br>
> However, I do<br>
> > not know how to achieve that. Can I somehow intersect the line with<br>
> the raster<br>
> > and query all the points above?<br>
> ><br>
> > Any hints would be greatly appreciated.<br>
> ><br>
> > Thanks,<br>
> > Tim<br>
> ><br>
> > Tim-Hinnerk Heuer<br>
> ><br>
> > Twitter: @geekdenz<br>
> ><br>
><br>
> > Blog: <a href="http://www.thheuer.com" target="_blank">http://www.thheuer.com</a> <<a href="http://www.thheuer.com/" target="_blank">http://www.thheuer.com/</a>><br>
><br>
> ><br>
> ><br>
> ><br>
> > On 9 December 2012 20:36, Tim-Hinnerk Heuer<br>
> <<a href="mailto:th.heuer@gmail.com">th.heuer@gmail.com</a>> wrote:<br>
> ><br>
> ><br>
> > Hi Pierre,<br>
> ><br>
> > Great! Thank you so much, that worked!<br>
> ><br>
> > You're a genius!<br>
> ><br>
> > In case you are wondering. That point is Mount Ruhapehu and it<br>
> gave<br>
> > me the answer: 2611.7314453125 (m) which is indeed correct.<br>
> ><br>
> > Kind regards,<br>
> > Tim<br>
> ><br>
> > Tim-Hinnerk Heuer<br>
> ><br>
> > Twitter: @geekdenz<br>
> ><br>
><br>
> > Blog: <a href="http://www.thheuer.com" target="_blank">http://www.thheuer.com</a> <<a href="http://www.thheuer.com/" target="_blank">http://www.thheuer.com/</a>><br>
><br>
> ><br>
> ><br>
> ><br>
> > On 9 December 2012 20:24, Pierre Racine<br>
> > <<a href="mailto:Pierre.Racine@sbf.ulaval.ca">Pierre.Racine@sbf.ulaval.ca</a>> wrote:<br>
> ><br>
> ><br>
> > SELECT ST_Value(rast,ST_SetSRID(ST_Point(1821416,<br>
> 5649720),<br>
> > 2193))<br>
> > FROM nzdem3<br>
> > WHERE ST_Intersects(rast,ST_SetSRID(ST_Point(1821416,<br>
> > 5649720), 2193))<br>
> ><br>
> ><br>
><br>
><br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
> <a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
><br>
><br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br></div>