[postgis-users] PostGIS - Simple Raster Point Query
Pierre Racine
Pierre.Racine at sbf.ulaval.ca
Tue Dec 18 13:56:30 PST 2012
Try this (adapting the linestring to match your raster extent):
SELECT p, geom, ST_Value(rast,geom) elev
FROM (SELECT p, ST_line_interpolate_point(
ST_GeometryFromText('LINESTRING(-69 46.5, -68 46, -67 46.5, -67 45)', 4326),
CAST(p AS numeric)/30) geom
FROM generate_series(1,30) p
) foo,
srtm_tiled_100x100
WHERE ST_Intersects(rast, geom)
> -----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: Tuesday, December 18, 2012 3:09 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] PostGIS - Simple Raster Point Query
>
> 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 <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
>
>
More information about the postgis-users
mailing list