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?