Select multiple rows from PostGIS table where ST_Contains has multiple manual inputs

postgispostgresqlsql

Under normal circumstances I could get the spatial intersection between a table with multipolygons and another table with point geometry using some variant of

SELECT * 
FROM ( SELECT * FROM point_geometries) s 
JOIN polygon_geometries sg ON ST_Contains(sg.geom, s.point_geom)

But I have a situation where instead of a table, I have a list of point geometries to input. If I only have one geometry then I can do

SELECT * 
FROM polygon_geometries 
WHERE ST_Contains(geom, '0101000020E6100000E73B8A05101056C01F1166ED49594440')

which returns a single row from polygon_geometries giving me exactly what I want.

Now imagine I have 100+ geometries and I want to get all the rows from polygon_geometries where ST_Contains any of the points (in theory returning up to 100 rows from polygon_geometries). Is there a way to do this that doesn't involve constructing WHERE ST_Contains(geom, string1) OR ST_Contains(geom, string2)… etc?

I'd love to be able to feed a list of the strings in, if possible. I simply can not find any examples of this.

Best Answer

Two common options:

  • a set of materialized VALUES:

    WITH
  inputs(geom) AS [MATERIALIZED] (
    VALUES
      ('<WKB>'),
      ('<WKB>'),
      ...
  )
SELECT ply.*
FROM   polygon_geometries AS ply
JOIN   inputs AS inp
  ON   ST_Contains(ply.geom, ST_SetSRID(inp.geom::GEOMETRY, <SRID>)
;

  • a table from UNNEST:

    WITH
  inputs AS [MATERIALIZED] (
    SELECT ST_SetSRID(wkb::GEOMETRY, <SRID>) AS geom
    FROM   UNNEST(ARRAY[
             '<WKB>',
             '<WKB>',
             ...
           ]) AS wkb
  )
SELECT ply.*
FROM   polygon_geometries AS ply
JOIN   inputs AS inp
  ON   ST_Contains(ply.geom, inp.geom)
;

with the former being slightly faster in terms of creating the set, and the latter being more comfortable for copy/paste.

MATERIALIZED only applies to PostgreSQL >= 12.

Related Question