参数化gIS几何图查询
我正在尝试查询是否有任何一组多边形（在运行时传递）与“ enclosing_polygons”字段中的数据库中存储的一组多边形相交，这是一个多物菲尔德。
这是查询的一个示例：
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
查询与硬编码值一起工作正常，但是当我尝试将其参数化时，Postgres似乎无法识别？当我尝试填充它们时，多边形的地理音符是参数。
当我设置前两个参数（对于field1和field2）时，这些JDBC语句成功：
statement.setArray(1, array1)
statement.setArray(2, array2)
但是，如果我尝试设置前两个以外的任何参数，它们会失败。此语句：
statement.setdouble（3，point1x）
失败了以下错误：
The column index is out of range: 3, number of columns: 2.
为什么Postgres在Polygon构造函数中不识别它们是查询参数？
我该如何完成这项工作？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
由您的驱动程序来实现
？占位符，PostgreSQL永远不会看到它们。在您的驾驶员中，几乎在所有驱动程序中，单语引号中出现的问号只是字面的问号，而不是位置持有人。您可能需要自己构造
polygon（（...））字符串，然后将整个字符串作为单个占位符传递到查询中。因此，查询的一部分看起来像st_intersects（st_geometryfromtext（？），enclosing_polygons）有其他选择，但它们大多只是在不直接求解的情况下移动问题。如果您真的想只使用一个绑定到一个数字的普通问号，则可以用以下内容替换st_ geometryfromtext函数：
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 likeST_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: