[GIS] Optimizing PostGIS ST_Buffer to run in near real time

postgispostgresql

I have two tables in PostgreSQL 10.1 (with PostGIS 2.3).
The first table contains 7.5 million points (WGS84 coordinates). The other one contains linestrings (protected areas near the roads, 27300 linestrings, wgs84).
I would like to write a query which returns if the selected point (for example where gid=123) is near an area or not (boolean type). It is a very important criteria that the query should run very quickly (1 second or better).

I wrote this query, it use ST_Buffer with 2 meters. It works good, but slowly (3 sec):

SELECT p.gid, 
       CASE 
          WHEN ST_Intersects(ST_Buffer((p.geom::geography),2),pr.geom::geography)='true' THEN TRUE 
          ELSE FALSE 
       END  
FROM points p
JOIN protected_lines pr
ON ST_Intersects(ST_Buffer((p.geom::geography),2),pr.geom::geography)
WHERE p.gid=3905591;

I set GIST index (and cluster) to both tables.

How can I speed up my query to run in near real-time?

I tried this with polygons (about 1500 pieces) without buffer and ran quickly (better than 100 ms).

Best Answer

Instead of

ST_Intersects(ST_Buffer((p.geom::geography),2),pr.geom::geography)

you should use

ST_DWithin(p.geom::geography,pr.geom::geography,2)

ST_Buffer buffers EVERY geometry (slow) before intersecting (fast). ST_DWithin makes of use of indexes and should be much faster.

ST_DWithin — Returns true if the geometries are within the specified distance of one another

Related Solutions

[GIS] PostGIS ST_Buffer Radius

Because your data isn't projected - it is points on a spheroid - linear distances sort of don't make sense. Five miles at the equator is a much smaller angle than 5 miles on the arctic circle. But luckily PostGIS (>= 1.5) has the answer you're looking for:

SELECT *
FROM table
WHERE ST_DWithin(ST_GeogFromText('SRID=4326;POINT(0,0)'),
                 geography(latlon), 12070);

It has a geography type that is designed for just this sort of thing. It's similar to geometry, but it only ever uses EPSG:4326, and there are far fewer functions that work with it.

In the sample above, I have called ST_GeogFromText() (There is also a ST_GeographyFromText(), and I'm not sure if there is a difference) on the point of interest (it might work with regular WKT, because the SRID parameter is redundant), and cast the latlon column to the geography type. If you're doing lots of these, it can be more efficient to create a geography column in your table and skip the cast entirely. Finally, ST_DWithin() can take geography parameters, and it does the right thing with linear distances.

PostGIS – Achieving ArcGIS-like Speed with Spatial Index

Different approach. Knowing that the pain is in ST_Intersection, and that true/false tests are fast, trying to minimize the amount of geometry passing through the intersection might speed things up. For example, parcels that are totally contained in a jurisdiction don't need to be clipped, but ST_Intersection will still probably go to the trouble of building part of the intersection overlay before realizing it doesn't have to generate any new geometry. So this

INSERT INTO parcel_jurisdictions(parcel_gid,jurisdiction_gid,isect_geom)
SELECT
  a.orig_gid AS parcel_gid,
  b.orig_gid AS jurisdiction_gid,

  st_multi(st_intersection(a.geom,b.geom)) AS geom
FROM
  valid_parcels a, valid_jurisdictions b
WHERE
  st_intersects(a.geom, b.geom) and not st_within(a.geom, b.geom)
UNION ALL
SELECT
  a.orig_gid AS parcel_gid,
  b.orig_gid AS jurisdiction_gid,
  a.geom AS geom
FROM
  valid_parcels a, valid_jurisdictions b
WHERE
  st_within(a.geom, b.geom);

Or even terser

INSERT INTO parcel_jurisdictions(parcel_gid,jurisdiction_gid,isect_geom)
SELECT
  a.orig_gid AS parcel_gid,
  b.orig_gid AS jurisdiction_gid,
  CASE 
     WHEN ST_Within(a.geom,b.geom) 
     THEN a.geom
     ELSE ST_Multi(ST_Intersection(a.geom,b.geom)) 
  END AS geom
FROM valid_parcels a
JOIN valid_jurisdictions b
ON ST_Intersects(a.geom, b.geom)

Might even be faster w/o the UNION.

Best Answer

Related Solutions

[GIS] PostGIS ST_Buffer Radius

PostGIS – Achieving ArcGIS-like Speed with Spatial Index

Related Question