[postgis-users] Querying PostGIS Raster with MultiPoint

[Pierre Racine]

Check my answer in StackExchange...

> -----Original Message-----
> From: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-
> bounces at lists.osgeo.org] On Behalf Of Tobias Bieniek
> Sent: Thursday, November 15, 2012 5:55 PM
> To: postgis-users at lists.osgeo.org
> Subject: [postgis-users] Querying PostGIS Raster with MultiPoint
> 
> Hi Guys
> 
> I've posted the following question on StackExchange already, but it
> seems that nobody was able to help me.
> (http://gis.stackexchange.com/questions/40371/querying-postgis-raster-with-
> multipoint)
> 
> For one of my projects using PostGIS 2 I have a table with GPS tracks
> (igc_files). It holds records including a MULTIPOINT() column that has
> about 1,000 points for each record and a column that has an array of
> the corresponding timestamps. I also have a raster table (elevations)
> that should at some point hold all of the SRTM elevation data. For now
> I have only imported a few relevant areas though.
> 
> What I want to do is get the elevation for each point of one certain
> record and the corresponding timestamps. I have implemented an naive
> approach and it does work, but I'm wondering about its performance
> implications. This is how it currently looks:
> 
> SELECT timestamps[location_id] AS timestamp,
>        ST_VALUE(elevations.rast, subq.location) AS elevation
> FROM elevations, (
>     SELECT (ST_DUMP(igc_files.locations)).path[1] AS location_id,
>            (ST_DUMP(igc_files.locations)).geom AS location
>     FROM igc_files
>     WHERE igc_files.id = 1
> ) AS subq, igc_files
> WHERE igc_files.id = 1 AND
>       ST_INTERSECTS(subq.location, elevations.rast) AND
>       ST_VALUE(elevations.rast, subq.location) IS NOT NULL
> 
> Since this is a GPS track and the points are not that far apart,
> PostGIS could assume that the next point is in the same raster tile
> and check that tile first before checking the index again from the
> top. Is that actually done by PostGIS or is it just starting to search
> the index from the top for each of the points?
> 
> I have tried to use ST_INTERSECTION() instead of ST_VALUE() and
> feeding in the MULTIPOINTdirectly, but that was actually even slower
> by a few orders of magnitude. Is there a feature/function of PostGIS
> that I am missing to do this more efficiently?
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users