[postgis-users] Querying PostGIS Raster with MultiPoint

[Tobias Bieniek]

The project I am talking about is located at
http://skylines.xcsoar.org with the source code at
https://github.com/Turbo87/Skylines

It is a platform for glider and paraglider pilots, where they can
share their flights (in form of GPS track files) and use live tracking
by sending periodic location updates to the server via smartphone. For
each flights (and live track) a map is displayed and below it is a
chart with the time on the x-axis and the altitude above sea level on
the y-axis. Now we would like to display the elevation at these GPS
track locations in the chart too and that is where PostGIS Raster gets
relevant.

Since this web application is already in production but without the
raster part as of now, I can only comment on the performance on my
development machine. I don't have the exact query times here right
now, but it seemed to be rather fast. At the moment I only have a
small subset of the SRTM data loaded though and I'm the only user on
this machine, which is why I'm worried that it might not scale as good
on the production server.

As mentioned in my comment on StackExchange, we are also maintaining
an Android App where we do the terrain lookups from a GeoJP2 file. We
have tweaked our libary for this app so that it keeps the last tile(s)
in a cache and checks whether the next coordinate(s) are part of the
cached tiles first before using the index to load a new tile. I was
hoping that PostGIS Raster might do something similar or that it would
be possible to implement something like that somehow.

2012/11/16 Basques, Bob (CI-StPaul) <bob.basques at ci.stpaul.mn.us>:
> 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users