Search code examples
postgresqljdbcpostgisprepared-statement

Parameterizing postgis geometry query

I'm trying to query whether any of a set of polygons (passed in at runtime) intersects with a set of polygons stored in the database in the "enclosing_polygons" field, which is a MultiPolygonField.

Here is an example of the query:

select * from my_table where field1 = any (?) and field2 = any (?) and (
ST_Intersects(ST_GeometryFromText('POLYGON((? ?, ? ?, ? ?, ? ?, ? ?))'), enclosing_polygons) or
ST_Intersects(ST_GeometryFromText('POLYGON((? ?, ? ?, ? ?, ? ?, ? ?))'), enclosing_polygons)) 
and detection_type = 0 order by confidence desc limit 2000

The query works fine with hardcoded values, but when I try to parameterize it, Postgres does not seem to recognize the ? placehoders for the polygon points as parameters when I try to populate them.

When I set the first two parameters (for field1 and field2), these JDBC statements succeed:

statement.setArray(1, array1)
statement.setArray(2, array2)

However, if I try to set any parameters beyond these first two, they fail. This statement:

statement.setDouble(3, point1x)

fails with the following error:

The column index is out of range: 3, number of columns: 2.

Why does Postgres not recognize these ?s in the POLYGON constructor as query parameters?

How can I make this work?

Solution

  • It is up to your driver to implement the ? placeholders, PostgreSQL never sees them. In your driver, like in almost all drivers, the question marks occurring inside the single quotes are just literal question marks, not place holders.

    You probably need to construct the POLYGON((...)) string yourself, then pass that whole string into the query as a single placeholder. So that part of the query would look like ST_Intersects(ST_GeometryFromText(?), enclosing_polygons)

    There are alternatives but they mostly just move the problem around without directly solving it. If you really want to just use plain question marks with each bound to one number, you could replace the ST_GeometryFromText function with something like:

    st_makepolygon(st_makeline(array[st_makepoint(?,?),st_makepoint(?,?),st_makepoint(?,?),st_makepoint(?,?),st_makepoint(?,?)]))