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?
Geoserver version 2.10 (just in case the current version might handle this better..)
Trying to limit the game of cat and mouse with escape quotations, I tried a CTE method, but unfortunately throws a different error:
with cte_bounds as ( select elem_name , mid_name , high_name , districtid , district , geom from dpsdata."SchoolBoundaries_All_Projected" b ) , cte_coords as ( select '%lon%'::double precision as lon , '%lat%'::double precision as lat ) select * from cte_bounds as b where ST_Intersects( ( select st_setsrid(ST_MakePoint(lon, lat), 4326) from cte_coords) , b.geom)
The error this time being:
ERROR: invalid input syntax for type double precision: ""
Again, this query works in PostGIS when the %lon% and %lat% values use real coordinates.