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