Tuesday, 4 September 2018

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 MULTIPOINT directly, 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?




No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...