Skip to main content
2 of 6
Change title to describe the actual problem
IvanSanchez
  • 10.3k
  • 2
  • 20
  • 35

Why does my SQL query containing a WKT string throw a "invalid geometry" error?

We're building a simple application that takes the result of a geococde and determines which school boundary the point resides.

I'm trying to figure out if I should be doing this using a paramaterized SQL view in GeoServer which takes in the LON/LAT, or if there is another easier way I have overlooked that can pass the lat/lon to a GeoJSON Service and return the result.

The following SQL works in PostGIS:

select
   elem_name
  , mid_name
  , high_name
 , districtid
  , district

from dpsdata."SchoolBoundaries_All_Projected" as b

WHERE

  ST_Intersects(
      'POINT(-104.81879 39.77850)'
      , b.geom)

But when I use this as the base for SQL View in GeoServer, I get the error:

Failed to create SQL view: ERROR: parse error - invalid geometry Hint: "POINT(' " <-- parse error at position 18 within geometry Position: 476

In the view I have switched the coordinates to:

  ST_Intersects(
      'POINT('%lon%' '%lat%')'
      , b.geom)

...and have tried to declare the SRID using SRID=4326;POINT...

The Leaflet equivalent our developer has found uses the Mapbox/Leaflet-pip (point in polygon) plugin, but that requires the GeoJSON to exist as a flat file in the project folder, which I want to avoid as we update the boundaries on a regular basis, which the GeoServer service would always reflect.

Is there a better way, or again something I have overlooked that is easier than anything above?

Inactivated Account
  • 18.9k
  • 5
  • 70
  • 114