Points in Polygon Optimization

[RealCoChenchao]

Working on a problem to find which points falling into the driving time isochrone differences. A relative decent example is there is 738,014 points within the bbox that built around the isochrones and there are 12,174 polygon difference (10 vs 5) and I join these two using ST_Intersects(i.geometry, gfp.geometry) Both isochrones and points are RTree indexed. Can anyone give me a good estimate on how long it will take to get the results and what some additional filtering should I apply? For example, should I build a bbox for each isochrones first and filtering the points to compare?

[Maxxen]

Rtree indexes doesnt apply for joins yet, so that wont have any effect. However, we still do some join optimizations on a normal st_intersects join. Its hard to guess what kind of performance this will get you, the best way to figure out what performance you will get is to simply test running the query.

[RealCoChenchao]

Thanks for the quick response. I agree with the testing! I guess if I follow my current method, I need to do the same join for roughly 85 GEOIDs , although not every GEOID has many isochrone differences as above example, there could be more points need to be checked. Therefore, I am look at 1448*85 ~ 34 hours of running time?

[RealCoChenchao]

Ha, when I just wrapped up this post, I got the results of following and it takes 1,458s. In this snippet, the number of isochrones is 4*12174.

WITH isochrone_bounds AS (
    SELECT
        GEOID, 
        cell_id,
        iso_min_inner, -- Maintain the exact isochrone interval
        ST_Envelope(geometry) AS bbox_geom, -- Create the bounding box for each isochrone
        geometry -- Keep the exact geometry for precise intersection
    FROM isochrone_boundary
),

candidate_points_filtered AS (
    SELECT 
        m.*,
        i.GEOID,
        i.cell_id,
        i.iso_min_inner
    FROM candidate_points m
    JOIN isochrone_bounds i 
      ON ST_Intersects(i.bbox_geom, m.geometry) -- First filter by bounding box
)

SELECT 
    *
FROM candidate_points_filtered;

[RealCoChenchao]

The final query should be following. I guess it should not take too long from candidate_points_filtered

SELECT 
    i.cell_id,
    i.GEOID,
    i.iso_min_inner AS iso_min,
    gfp.rc_400m_cell_id
FROM isochrone_boundary i
JOIN candidate_points_filtered gfp 
    ON ST_Intersects(i.geometry, gfp.geometry)