[postgis-users] Querying PostGIS Raster with MultiPoint

[Basques, Bob (CI-StPaul)]

I'm working on something similar, and wondering about what your current performance values are?  We (I) went with a more abstracted method of storing the data and went with a point per record strategy.

We gain about 1 million records per month from a GPS fleet of 300 (or so) assets and have yet to get into any archiving strategy for the data.  We're running against a 6.3 million record database currently, and responses times are good enough to update multiple web based user displays (10-15 screens active at any one time) with all active locations without taxing the server (yet).

We're still measuring things and have plans to add in an archiving strategy when the DB is unable to handle the lookup any longer.

Bobb


>>  -----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 4: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