Search code examples
spring-data-jpapostgis

Spring Boot 3 JPA Postgis - can't select rows based on geometry

We started to evaluate a migration to Postgis. Being curious, I read this nice article. When querying the data on a geometry and a distance, I didn't get any data. I am sure the data is in the database.

The entity is with the Point from locationtech:

@Data
@Entity(name = "us_cities")
public class City {

    @Id
    @Column(name="id")
    private long id;
    
    @Column(name="pop_2010")
    private long population2010;
    
    @Column(name="elev_in_ft")
    private long altitude;

    @Column(name="state")
    private String state;

    @Column(columnDefinition = "geometry(Point,4326)")
    private Point geom;

}

The repository is:

@Repository
public interface CityRepository extends JpaRepository<City, Long>{

    @Query(value="SELECT * from us_cities where ST_DistanceSphere(geom, :p) < :distanceM", nativeQuery = true)
    List<City> findNearWithinDistance(Point p, double distanceM);
}

The service is:

public List<City> findAround(double lat, double lon, double distanceM){
    log.info("Looking for city around ({},{}) withing {} meters", lat, lon, distanceM);
    Point p = factory.createPoint(new Coordinate(lon, lat));
    List<City> cities = repo.findNearWithinDistance(p, distanceM);
    return cities;
}

The dependencies are:

enter image description here

The column definitions in Postgres/Postgis look like:

enter image description here

The table in Postgis contains data, the first few rows being:

enter image description here

When selecting the data in a Spring Boot JPA app, I couldn't select any rows by geometry. Doing a simple 'select all' does find all rows in the table.

How to select data correctly in Spring JPA based on a Postgis query? Can you help me making these first steps towards Postgis?

The data was created with: a JSON file:

{
"type": "FeatureCollection",
"name": "us_cities",
"crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:OGC:1.3:CRS84" } },
"features": [
{ "type": "Feature", "properties": { "id": "53", "POP_2010": 40888.0, "ELEV_IN_FT": 1611.0, "STATE": "ND" }, "geometry": { "type": "Point", "coordinates": [ -101.296273199999973, 48.232509500000106 ] } },
{ "type": "Feature", "properties": { "id": "101", "POP_2010": 52838.0, "ELEV_IN_FT": 830.0, "STATE": "ND" }, "geometry": { "type": "Point", "coordinates": [ -97.032854699999973, 47.925256800000057 ] } } ...} ... ]

And a ogr2ogr command:

ogr2ogr -f "PostgreSQL" PG:"dbname=postgis user=postgis host=localhost port=5433 password=postgis" "src/main/resources/us_cities.geojson" -sql "select cast(ID as INTEGER), ELEV_IN_FT, POP_2010, STATE from us_cities" -nln us_cities
Solution

  • The query was not correct.

    After I replaced the native query from the article ...

    @Query(value="SELECT * from us_cities where ST_DistanceSphere(geom, :p) < :distanceM", nativeQuery = true)
    List<City> findNearWithinDistance(Point p, double distanceM);

    ... with ...

    @Query(value="SELECT s from com.hin.spatial.postgis.model.City s where ST_DistanceSphere(s.geom, :p) < :distanceM")
    List<City> findNearWithinDistance2(Point p, double distanceM);

    ... the query was a lot faster and also correct!