3

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.

  • 2
    I think you will need to work out the proper string concatenation for the WKT string, e.g. 'POINT (' || %lon% || ' ' || %lat% || ')' (or similar) – ThingumaBob Jun 18 '18 at 20:39
  • OK cool - I can live with that if that's the correct workflow. Thanks!!! – DPSSpatial Jun 18 '18 at 20:48
  • ...well, that's at least what I think that error is about ,) if a clientside search doesn't work for you, it's either directly call the DB or let middleware do that for you, and a SQL View via GeoServer sounds just right on a first read. – ThingumaBob Jun 19 '18 at 7:15
  • next to that, you could call the WFS directly with a spatial filter to find the intersecting polygon. might be the less demanding option if you expect heavy usage. – ThingumaBob Jun 19 '18 at 7:27
  • 3
    what @ThingumaBob says, though, I would use the concat operator to avoid those hideous || . If it is parameterized SQL, remember that you have to escape an apostrophe with an apostrohpe, so, you can end up with 2-5 apostrophes depending on the situation. – John Powell Jun 19 '18 at 10:58
2

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)
  • As others including you have suggested, this is a frustrating game of cat and mouse with escape quotations... no luck yet... – DPSSpatial Jun 19 '18 at 16:42
1

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.