Spring Boot中正确使用PostGIS Polygon执行查询的方法及SQL语法异常解决

Hey there, let's tackle that SQLGrammarException you're hitting when running your spatial query with PostGIS in Spring Boot. The error is likely coming from syntax issues in your query, plus a few optimizations we can make to get this working smoothly.

1. Fix the Syntax Escape Issue #

First off, the biggest culprit here is the incorrect escape for the type cast operator :: in your Java string. In your original query, you wrote \\:\\: — this gets translated to \: in the actual SQL sent to PostgreSQL, which is invalid syntax. You don't need to escape :: in Java strings, so we can replace those with plain ::.

2. Use Explicit Geometry Construction Functions #

Instead of directly writing the POLYGON(...) literal, using PostGIS functions like ST_GeomFromText() (with a defined SRID, e.g., 4326 for WGS84 coordinates) makes your query more robust and avoids potential parsing errors. Also, always set the SRID for your points using ST_SetSRID() to ensure spatial operations work correctly.

3. Verify Your ST_DWithin Logic #

Double-check that ST_DWithin is the right function for your use case:

• ST_DWithin(polygon, point, distance) checks if the distance from the point to the polygon is less than or equal to the given range value.
• If you actually want to check if the point lies inside the polygon, use ST_Contains(polygon, point) or ST_Intersects(polygon, point) instead.

If you do need ST_DWithin, note that using SRID 4326 (latitude/longitude) means the distance unit is degrees, not meters. If you need meter-based distances, transform your geometries to a projected SRID like 3857 (Web Mercator) using ST_Transform().

Fixed Query Example #

Here's the revised version of your query with all these fixes:

@Query(name = "getCellIdsForRectangle", value = "SELECT lk.* FROM lk_location as lk " +
        "LEFT JOIN lk_slocation as s ON ST_DWithin(ST_GeomFromText('POLYGON((-4.43 54.31, -4.39 54.31, -4.39 54.29, -4.43 54.29, -4.43 54.31))', 4326), " +
        "ST_SetSRID(ST_MakePoint(s.longitude, s.latitude), 4326), s.range) " +
        "WHERE ST_DWithin(ST_GeomFromText('POLYGON((-4.43 54.31, -4.39 54.31, -4.39 54.29, -4.43 54.29, -4.43 54.31))', 4326), " +
        "ST_SetSRID(ST_MakePoint(lk.longitude, lk.latitude), 4326), lk.range) " +
        "AND s.location_id IS NULL;", nativeQuery = true)
List<Location> getCellIdsForRectangle();

Additional Tips #

• Spatial Indexes: Speed up your queries by creating spatial indexes on the generated point geometries:

  CREATE INDEX idx_lk_location_geom ON lk_location USING GIST (ST_SetSRID(ST_MakePoint(longitude, latitude), 4326));
  CREATE INDEX idx_lk_slocation_geom ON lk_slocation USING GIST (ST_SetSRID(ST_MakePoint(longitude, latitude), 4326));
  

• Validate PostGIS Installation: Ensure PostGIS is properly enabled in your database by running:

  SELECT postgis_version();
  

• Entity Mapping: Double-check that your Location entity class correctly maps to the lk_location table columns (no typos, correct data types).

内容的提问来源于stack exchange，提问作者Islam Emam