PostGIS: Using nearest (<->), and ST_ClosestPoint in a create table

Question

I have the following datasets stored in PostGIS 9.5 with all the relevent indexes built.

1. Collected Points - over 4 million points
2. Polygons - almost 5000 multi polygons (and they are multi polygons)
3. Road Lines - over 5 millions linestrings
4. 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

Answer 1

First, to get decent performance, decompose your linestring to points.

CREATE TABLE pts AS
  SELECT (dp).geom AS lnpt, l.geom
  FROM lines AS l
  CROSS JOIN LATERAL ST_DumpPoints(geom) AS dp;

CREATE INDEX ON pts USING gist (lnpt);

Then run something like this,

SELECT cp.geom, COALESCE(ap.geom, l.geom) AS wanted
FROM collected_points AS cp
LEFT OUTER JOIN LATERAL (
  SELECT ap.geom
  FROM access_points
  WHERE EXISTS (
    SELECT 1
    FROM polygon AS p
    WHERE ST_Intersects( cp.geom, p.geom )
  )
  ORDER BY ap.geom <=> cp.geom
  OFFSET 0 ROWS
  FETCH FIRST 1 ROW ONLY
) AS ap
LEFT OUTER JOIN (
  SELECT l.geom
  FROM pts
  ORDER BY ap.geom <=> pts.lnpt
  OFFSET 0 ROWS
  FETCH FIRST 1 ROW ONLY
) AS l
  ON ap IS NULL;