Query with multiple fields of a derived table

Question

I have the following tables:

CREATE TABLE IF NOT EXISTS "walks"
(
    id    varchar(36),
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS "points"
(
    id          varchar(36),
    time        timestamp(6) with time zone,
    geometry    geometry(Point, 4326),
    walk_id     varchar(36),
    FOREIGN KEY (walk_id)
        REFERENCES walks (id)
)

You can think of a walk as representing an ordered set of points strung together: ---O------O---O-O-----O---

I'm trying to select all the points in order of a walk, but only for the walks that intersect a given Polygon (i.e. walks that have points inside the Polygon). Additionally, I want to select a boolean representing whether each returned point is inside that area or not.

I've come up with the following query, which works correctly:

SELECT p.id,
       w.id       AS walk_id,
       p.geometry,
       p.id IN (SELECT id
                FROM   points
                WHERE  ST_INTERSECTS(p.geometry,
'SRID=4326;POLYGON((-81.8 -0.5,-81.1 -0.5,-81.1 -1.5,-81.8 -1.5,-81.8 -0.5))'
        )) AS in_area
FROM   points p
       JOIN walks w
         ON w.id = p.walk_id
WHERE  w.id IN (SELECT w.id
                FROM   walks w
                       JOIN points p
                         ON p.walk_id = w.id
                WHERE  ST_INTERSECTS(p.geometry,
'SRID=4326;POLYGON((-81.8 -0.5,-81.1 -0.5,-81.1 -1.5,-81.8 -1.5,-81.8 -0.5))'
))
ORDER  BY p.time ASC;

This seems inefficient though, since the PostGIS Intersects function has to be called twice on everything.

Is it possible to select both the intersecting point IDs and walk IDs in a derived table or something, and then reference them both in the SELECT (for the boolean) and the WHERE (to filter the correct walks)?

Hi! Could you provide some sample data and the expected result? cheers — Jim Jones, Feb 09 '21 at 12:04

Jim Jones · Answer 1 · 2021-02-09T12:27:02.107

Without sample data it is quite hard to fully understand your use case, but maybe a CTE would simplify things a bit. Something like:

WITH p AS (
  SELECT id,geometry,walk_id,time,
   ST_Intersects(geometry,
    'SRID=4326;POLYGON((-81.8 -0.5,-81.1 -0.5,-81.1 -1.5,-81.8 -1.5,-81.8 -0.5))') AS in_area   
  FROM points )
SELECT p.id,w.id AS walk_id,p.geometry,p.in_area FROM p
JOIN walks w ON w.id = p.walk_id
WHERE p.in_area IS TRUE ORDER BY p.time ASC;

Query with multiple fields of a derived table

1 Answers1