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..)
UPDATE:
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.
