4

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.

1
  • You can convert an array of strings to row in a virtual table and join on that. This is probably more a Database Administrators issue, though.
    – Vince
    Jul 29 at 3:58
4

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.

1
  • Thank you! Both worked perfectly and I learned a few new tricks in the process! Jul 30 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.