[postgis-users] PostGIS - Simple Raster Point Query

[Tim-Hinnerk Heuer]

Hi Pierre,

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.

Thanks,
Tim

Tim-Hinnerk Heuer

Twitter: @geekdenz
Blog: http://www.thheuer.com



On 18 December 2012 08:53, Pierre Racine <Pierre.Racine at sbf.ulaval.ca>wrote:

> 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
> >
> >
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121218/c3ab8d93/attachment.html>