JTS几何在Hibernate空间中生成"错误:函数within(geometry, bytea)不存在"

huangapple go评论81阅读模式
英文:

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 = &quot;Select s from #{#entityName} s where within(s.shape, :bounds )= true&quot;)
public List&lt;SiteModel&gt; 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 = &quot;Select s from #{#entityName} s where within(s.shape, :bounds )= true&quot;)
public List&lt;SiteModel&gt; findWithinBounds(Geometry bounds);

@Query(value = &quot;Select s from #{#entityName} s where dwithin(s.shape, :point, :distance)= true&quot;)
public List&lt;SiteModel&gt; 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().

huangapple
  • 本文由 发表于 2020年10月7日 12:11:53
  • 转载请务必保留本文链接:https://go.coder-hub.com/64237081.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定