报错ll_to_earth函数不存在，求解决Spring Data JPA位置半径查询问题

Hey there! That error is a common gotcha when working with PostgreSQL geospatial functions—let's break down how to fix it step by step.

Why This Happens #

The ll_to_earth and earth_distance functions you’re using aren’t part of standard PostgreSQL. They belong to the PostGIS extension, which adds robust geospatial capabilities to PostgreSQL. If this extension isn’t enabled in your database, PostgreSQL won’t recognize these functions at all.

Step 1: Check if PostGIS is Installed #

First, verify PostGIS is installed on your PostgreSQL server. Log into your PostgreSQL command line (using psql) and run this query:

SELECT postgis_version();

• If you get a version string back (like 3.3.2 r20026), PostGIS is already installed.
• If you get an error, install PostGIS first:
  • On Ubuntu/Debian: sudo apt-get install postgresql-<your-postgres-version>-postgis-3
  • On RHEL/CentOS: sudo yum install postgis30_<your-postgres-version>
  • On Windows: Use the StackBuilder tool included with PostgreSQL to install PostGIS.

Step 2: Enable PostGIS in Your Application Database #

Once PostGIS is installed, you need to enable it for the specific database your app uses. From the psql prompt:

1. Switch to your database: \c your_database_name
2. Run the extension enable command:

CREATE EXTENSION IF NOT EXISTS postgis;

3. Confirm it’s enabled with:

SELECT * FROM pg_extension WHERE extname = 'postgis';

You should see a row for PostGIS in the results if it worked.

Step 3: Fix Your JPA Query #

You had a partial query—here’s the complete, working version. You can choose between native SQL (more straightforward for PostGIS functions) or JPQL:

Option 1: Native SQL (Recommended for PostGIS)

@RepositoryRestResource(collectionResourceRel = "places", path = "places")
public interface PlaceRepository extends JpaRepository<PlaceEntity, Long> {
    @Query(value = "" +
            "SELECT * FROM place p " +
            "WHERE earth_distance(ll_to_earth(p.latitude, p.longitude), ll_to_earth(?1, ?2)) <= ?3",
            nativeQuery = true)
    List<PlaceEntity> findPlacesWithinRadius(double lat, double lng, double radius);
}

(Adjust the table name place if your entity uses a different name via @Table.)

Option 2: JPQL (With Named Parameters)

If you prefer JPQL, use named parameters to make the query clearer:

@RepositoryRestResource(collectionResourceRel = "places", path = "places")
public interface PlaceRepository extends JpaRepository<PlaceEntity, Long> {
    @Query(value = "" +
            "SELECT p FROM PlaceEntity p " +
            "WHERE earth_distance(ll_to_earth(p.latitude, p.longitude), ll_to_earth(:lat, :lng)) <= :radius")
    List<PlaceEntity> findPlacesWithinRadius(@Param("lat") double lat, @Param("lng") double lng, @Param("radius") double radius);
}

Performance Tip #

For faster radius queries (especially with large datasets), add a spatial index to your latitude/longitude columns:

CREATE INDEX idx_place_geo ON place USING gist(ll_to_earth(latitude, longitude));

内容的提问来源于stack exchange，提问作者Stefan Falk