英文:
JTS geometry in Hibernate spatial generating " ERROR: function within(geometry, bytea) does not exist"
问题
Hibernate-spatial 5.4.22,hibernate.dialect = org.hibernate.spatial.dialect.postgis.PostgisDialect
一个非常直接的查询:
import org.locationtech.jts.geom.Geometry;
...
@Query(value = "Select s from #{#entityName} s where within(s.shape, :bounds )= true")
public List<SiteModel> findWithinBounds(Geometry bounds);
边界几何图形是通过以下方式生成的:
GeometryFactory gf = new GeometryFactory();
Polygon bounds = gf.createPolygon(sc);
bounds.setSRID(4326);
return newSiteService.findWithinBounds(bounds);
但是会产生错误:
select
sitemodel0_.site_id as site_id1_1_,
sitemodel0_.accuracy as accuracy2_1_,
sitemodel0_.comment as comment3_1_,
sitemodel0_.country_code as country_4_1_,
sitemodel0_.directions as directio5_1_,
sitemodel0_.flag as flag6_1_,
sitemodel0_.height as height7_1_,
sitemodel0_.h_accuracy as h_accura8_1_,
sitemodel0_.h_method_id as h_method9_1_,
sitemodel0_.latitude as latitud10_1_,
sitemodel0_.longitude as longitu11_1_,
sitemodel0_.method_id as method_12_1_,
sitemodel0_.orig_coord as orig_co13_1_,
sitemodel0_.orig_system_id as orig_sy14_1_,
sitemodel0_.owner_id as owner_i15_1_,
sitemodel0_.shape as shape16_1_,
sitemodel0_.site_name as site_na17_1_
from
sc.site_proposed sitemodel0_
where
within(sitemodel0_.shape, ?)=true
WARN : SQL Error: 0, SQLState: 42883
ERROR: ERROR: function within(geometry, bytea) does not exist
因此,它似乎已经识别出了 postgis 的 shape 字段是几何类型,但无法理解 JTS 几何对象。我看到有很多关于相反情况的问题,但没有这个错误情况。
英文:
Hibernate-spatial 5.4.22, hibernate.dialect = org.hibernate.spatial.dialect.postgis.PostgisDialect
A very straightforward query:
import org.locationtech.jts.geom.Geometry;
...
@Query(value = "Select s from #{#entityName} s where within(s.shape, :bounds )= true")
public List<SiteModel> findWithinBounds(Geometry bounds);
The bounds geometry is generated by:
GeometryFactory gf = new GeometryFactory();
Polygon bounds = gf.createPolygon(sc);
bounds.setSRID(4326);
return newSiteService.findWithinBounds(bounds);
But it generates error
select
sitemodel0_.site_id as site_id1_1_,
sitemodel0_.accuracy as accuracy2_1_,
sitemodel0_.comment as comment3_1_,
sitemodel0_.country_code as country_4_1_,
sitemodel0_.directions as directio5_1_,
sitemodel0_.flag as flag6_1_,
sitemodel0_.height as height7_1_,
sitemodel0_.h_accuracy as h_accura8_1_,
sitemodel0_.h_method_id as h_method9_1_,
sitemodel0_.latitude as latitud10_1_,
sitemodel0_.longitude as longitu11_1_,
sitemodel0_.method_id as method_12_1_,
sitemodel0_.orig_coord as orig_co13_1_,
sitemodel0_.orig_system_id as orig_sy14_1_,
sitemodel0_.owner_id as owner_i15_1_,
sitemodel0_.shape as shape16_1_,
sitemodel0_.site_name as site_na17_1_
from
sc.site_proposed sitemodel0_
where
within(sitemodel0_.shape, ?)=true
WARN : SQL Error: 0, SQLState: 42883
ERROR: ERROR: function within(geometry, bytea) does not exist
So it seems to picked up that postgis shape field is geometry ok. (it is postgis geometry type), but is failing to understand the JTS geometry object. I have seen many questions about the reverse, but not this error.
答案1
得分: 1
感谢 @Karel Maesen 的提示,我成功地使它工作起来了。我需要将以下内容放入属性中:
hibernate.dialect = org.hibernate.spatial.dialect.postgis.PostgisDialect
spring.jpa.properties.hibernate.dialect = org.hibernate.spatial.dialect.postgis.PostgisPG95Dialect
完成这些操作后,空间查询,包括 within 和 dwithin,都能正常工作。
@Query(value = "Select s from #{#entityName} s where within(s.shape, :bounds )= true")
public List<SiteModel> findWithinBounds(Geometry bounds);
@Query(value = "Select s from #{#entityName} s where dwithin(s.shape, :point, :distance)= true")
public List<SiteModel> findCloseTo(Geometry point, double distance);
英文:
Thanks to the hint from @Karel Maesen, I did get it working. I need to put
hibernate.dialect = org.hibernate.spatial.dialect.postgis.PostgisDialect
spring.jpa.properties.hibernate.dialect = org.hibernate.spatial.dialect.postgis.PostgisPG95Dialect
into properties. With that done, the spatial queries, with and dwithin are both work.
@Query(value = "Select s from #{#entityName} s where within(s.shape, :bounds )= true")
public List<SiteModel> findWithinBounds(Geometry bounds);
@Query(value = "Select s from #{#entityName} s where dwithin(s.shape, :point, :distance)= true")
public List<SiteModel> findCloseTo(Geometry point, double distance);
答案2
得分: 0
PostgisDialect
已经被弃用了相当长的时间。您应该使用更新的Postgis方言之一,比如PostgisPG95Dialect
。然后在SQL中,您应该看到一个st_within()
函数,而不是within()
。
英文:
PostgisDialect
has been deprecated for quite some time. You should use one of more recent dialects for Postgis such as PostgisPG95Dialect
. You should see then in the SQL a st_within()
function rather than within()
.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论