PostGIS SQL nested query

Question

I have a layer of AIS points that I want to make into lines using st_makeline and then use st_intersects to determine if any of the lines intersect a polygon.

So I think I want to nest

st_intersects(st_makeline, polgon.geom)

The SQL I am using for the makeline is:

SELECT mmsi, ST_MakeLine(geom ORDER BY date_time_stamp) As newgeom
 FROM ais.cargo As ais
 GROUP BY mmsi;

How do I wrap the st_intersects around this?

Answer 1

If you have a single and simple polygon geometry:

WITH lines AS (
 SELECT mmsi, ST_MakeLine(geom ORDER BY date_time_stamp) As newgeom
 FROM ais.cargo As ais
 GROUP BY mmsi
)
SELECT mmsi FROM lines
 WHERE ST_Intersects(ST_GeomFromText('POLYGON((x1 y1, x2 y2, ...))', EPSG), newgeom);

Substitute the coordinates of your polygon into x1 y1, x2 y2, ..., the EPSG is the EPSG code of the used projection.

If your polygon(s) are stored in a table called po and geom column contains the geometry of the polygons:

WITH lines AS (
 SELECT mmsi, ST_MakeLine(geom ORDER BY date_time_stamp) As newgeom
 FROM ais.cargo As ais
 GROUP BY mmsi
)
SELECT mmsi FROM lines INNER JOIN po ON ST_Intersects(lines.newgeom, po.geom);

Note 1: I supposed the two geometries are in the same spatial reference system (projection).

Note 2: I used CTE (Common Table Expression) to make query more readable