1

I've been exploring PostGIS and have found it to be quite slow and I thought I would ask the community if they notice any blatant flaws in my queries. First, let me say that I need to query elevation points as fast as possible and I am very close at this point to switching to an in-memory data store like Redis to perform this operation if I cannot speed my queries up. My first naive query is the following:

SELECT ST_Value(rast, 'SRID=4326;POINT(-111.5000000 38.5000000)'::geometry) 
FROM srtm 
WHERE ST_Intersects(rast, 'SRID=4326;POINT(-111.5000000 38.5000000)'::geometry);

Initially, I thought that this would be a bad query because I need to get 10s of thousands of points in a single query, this means that I would have call this query 10s of thousands of times in my software. The first test that I ran showed that I could get a single elevation in just under 100ms (I'm running postgis in a Docker container too so that might slow things down a bit, but I'm looking at relative benchmarks at the moment). So I then posted Querying Thousands of Points with ST_Value()? asking the community if there was a better way to do a batch call to postgis (in hopes of speedings things up since I would only have to make a single query). The result is the following:

SELECT gv.val
FROM srtm , LATERAL ST_Intersection(rast, 'SRID=4326;MULTIPOINT(-111.5000000 38.5000000)'::geometry)  AS gv
WHERE ST_Intersects(rast, 'SRID=4326;MULTIPOINT(-111.5000000 38.5000000)'::geometry);

This query took nearly a second in my current configuration, YIKES! So then I thought, well maybe if I have more points it will scale better, say like 10,000 multipoints. So right off the bat I tried 10,000 points. My system sat there for a very long time and never finished the query before I gave up and ctrl-c'd the process. This was very bad news. So then, I created a multipoint with only 4 entries as shown in the following query:

SELECT (ST_Dump(gv.geom)).geom, gv.val
FROM srtm , LATERAL ST_Intersection(rast, 'SRID=4326;MULTIPOINT(-111.305048568 38.0601633931,-111.822991286 38.6025320796,-111.136977796 38.3631992596,-111.206470006 38.971228396)'::geometry)  AS gv
WHERE ST_Intersects(rast, 'SRID=4326;MULTIPOINT(-111.305048568 38.0601633931,-111.822991286 38.6025320796,-111.136977796 38.3631992596,-111.206470006 38.971228396)'::geometry);

This query took nearly 3 seconds to complete. As a result, I found that my initial query was the fastest possible way to get the data, which is still rather slow.

Can someone explain to me why the multipoint query did not speed things up?

Is there a better batch type call I can make that could get me closer to thousands of queries per second?

If not, I think I may have to switch to using something like Redis and come up with some kind of clever scheme to load billions of elevation points into memory.

  • 1
    When you have to query raster values for that many points at the same time I would rather try to transform those points into a raster that I would then intersect with the srtm raster. – Pierre Racine Dec 5 '16 at 16:02
  • I'm really curious if Pierre's suggestion will help. PostGIS has a reputation for being very fast. – Alex G Rice Dec 9 '16 at 23:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.