I have the following datasets stored in PostGIS 9.5 with all the relevent indexes built.
- Collected Points - over 4 million points
- Polygons - almost 5000 multi polygons (and they are multi polygons)
- Road Lines - over 5 millions linestrings
- Access Points - 68k point features
For each Collected Point I need to test whether it is in a Polygon, if it is then select the nearest Access Point, if it is not in the Polygon, then select the closest point from the lines.
I have tried a number of methods and the closest I can get to the right SQL was this:
CREATE TABLE test AS WITH polygon AS ( SELECT ST_Union(geom) as geom FROM polygons ) SELECT cp.*, CASE WHEN ST_Intersects(cp.geom, polygon.geom) THEN (Select ap.geom FROM access_points ap ORDER BY ap.geom <-> cp.geom LIMIT 1) ELSE ST_CLosestPoint(l.geom, cp.geom) END AS closest_geom FROM polygon, collected_points cp, lines l
this code is running but even on a sample of 150 Collected Points the query has been running for a long time.
Maybe I am trying to do too much in a query or need to use more subqueries or CTS statements.
Can someone please suggest a better solution