Using LAT/LON in Leaflet with Geoserver GeoJSON?

Question

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.

Answer 1

So the SQL query works fine in vanilla PostGIS, but fails when using it in a Geoserver SQL View, specifically when switching numeric constants in the SQL query with %lat% and %lon% in the Geoserver SQL View query.

Let me quote from the Geoserver documentation:

Within the SQL View query, parameter names are delimited by leading and trailing % signs. The parameters can occur anywhere within the query text, including such uses as within SQL string constants, in place of SQL keywords, or representing entire SQL clauses.

This leads me to think that Geoserver is doing a very naïve substitution of the parameters (instead of prepared statements with parameter binding, which are less prone to certain kinds of problems). One can write different things like

select * from tablename where columnname = %value%;
select * from tablename where columnname = '%value%';

In fact, the geoserver documentation (for version 2.13, current at the time of this writing) includes examples of values surrounded by single quotes and not surrounded by single quotes.

When that parameter is substituted by its actual value, the SQL query will look like:

select * from tablename where columnname = 5;
select * from tablename where columnname = '5';

This can be a problem when the values contain single quotes, leading to SQL injections:

select * from people where name = 'John O'Hare';

(Note how the section on "parameters and validation" from the Geoserver documentation explicitly mentions this, and prompts the user to run regular expressions on the substituted values to avoid things like single quotes).

Now let's have another look at your SQL view query:

select foo from table where ST_Intersects(
  'POINT('%lon%' '%lat%')'
  , table.geom)

Now, keeping in mind that Geoserver just performs a naïve string substitution in the SQL query (without checking the SQL syntax, without binding parameters, without doing any magic with single quote characters), that will be substituted into something like:

select foo from table where ST_Intersects(
  'POINT('180' '90')'
  , table.geom)

That is most probably the reason why your error message displays a single quote right after POINT(:

Hint: "POINT(' " <-- parse error at position 18

Therefore, I suggest you change your syntax to something like

select foo from table where ST_Intersects(
  'POINT(%lon% %lat%)'
  , table.geom)

Answer 2

Thanks to Andrea Aime, the solution lies in using a CQL filter on the GeoJSON - I wasn't aware that this could work on GeoJSON, rather than just WMS, etc.

You can simply adding the following to the GeoJSON request:

&CQL_FILTER=intersects(geom, POINT(-104.81879 39.77850))

(curious that the ordering of the geom is reversed, but it works!)

The final URL to the GeoJSON then looks like this:

http://servername:8080/geoserver/dps-arcgisdev01/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=dps-arcgisdev01:dps_boundaries_projected&outputFormat=application%2Fjson&CQL_FILTER=intersects(geom, POINT(-104.81879 39.77850))

Our developer can then take the LON/LAT from the Geocode result and assemble the request to the GeoServer GeoJSON.

Very easy, and although CQL is documented, I completely missed that this could be used on GeoJSON. Very handy!