3

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).

5

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

  • Very good! Running time is only about 115 ms.Thank you very much for your perfect quick answer! – zsoooc Jan 22 '18 at 12:46

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.