[postgis-users] PostGIS - Simple Raster Point Query

Pierre Racine Pierre.Racine at sbf.ulaval.ca
Mon Dec 17 11:53:10 PST 2012


When you say: "one query is run for each point on the line", you mean your application does.

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.

Pierre

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



More information about the postgis-users mailing list