Search code examples
javaspring-bootpostgis

How to query points within an area via Spring boot?

@Query("SELECT w FROM ${WalkEntity.TABLE_NAME} AS w WHERE ST_Within(w.start_point, ST_MakePolygon('LINESTRING(41.15294130710586 113.20090225705104, 41.15294130710586 115.97018471050012, 37.53742712587926 115.97018471050012, 37.53742712587926 113.20090225705104, 41.15294130710586 113.20090225705104)')) AND w.visibility=0 AND w.state=0 ORDER BY w.start_time DESC")

If I run this query command in pgAdmin4, it works fine. But when i run the spring boot project, an error happened:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: ( near line 1, column 81 [SELECT w FROM tech.return0er.donkeygo.dao.entity.WalkEntity AS w WHERE ST_Within(w.start_point, ST_MakePolygon('LINESTRING(41.15294130710586 113.20090225705104, 41.15294130710586 115.97018471050012, 37.53742712587926 115.97018471050012, 37.53742712587926 113.20090225705104, 41.15294130710586 113.20090225705104)')) AND w.visibility=0 AND w.state=0 ORDER BY w.start_time DESC]
    at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) ~[hibernate-core-5.4.6.Final.jar:5.4.6.Final]
    at org.hibernate.hql.internal.ast.ErrorTracker.throwQueryException(ErrorTracker.java:93) ~[hibernate-core-5.4.6.Final.jar:5.4.6.Final]

@Query("SELECT w FROM ${WalkEntity.TABLE_NAME} w WHERE ST_DistanceSphere(w.start_point, ST_MakePoint(?1, ?2)) <= ?3*1000" +
            " AND w.visibility=${WalkEntity.VISIBLE_TO_ALL} AND w.state=${WalkEntity.STATE_VISIBLE}" +
            " ORDER BY w.start_time DESC")
    fun findNearby(latitude: Double, longitude: Double, km: Int, pageable: Pageable = PageRequest.of(0, 18)): List<WalkEntity>

This works fine. The 2 query commands are almost the same.

Solution

  • I assume that this is SQL query. So you have to set nativeQuery to true:

    @Query("SELECT w FROM ${WalkEntity.TABLE_NAME} AS w WHERE ST_Within(w.start_point,  ST_MakePolygon('LINESTRING(41.15294130710586 113.20090225705104, 41.15294130710586 115.97018471050012, 37.53742712587926 115.97018471050012, 37.53742712587926 113.20090225705104, 41.15294130710586 113.20090225705104)')) AND w.visibility=0 AND w.state=0 ORDER BY w.start_time DESC"
, nativeQuery=true)