Share this:
Most GeoServer users know how to publish tables from a database such as PostgreSQL or Oracle as vector layers. More seasoned GeoServer users, however, also take advantage of SQL Views to publish those same tables with even greater control.
So what is an SQL View? A SQL View is a type of layer that is based on a database query that you write inside GeoServer itself. To the end user, it looks like a regular layer, but behind the curtain, you have all the power of spatial SQL at your fingertips to enrich the data that your users receive.
Uses of SQL Views
There are a number of different reasons to incorporate an SQL View into your application. For example, it’s possible to:
… only expose certain attributes to users:
SELECT geom, id, name FROM banks
… run spatial queries to do spatial computation or analysis:
SELECT *, ST_Area(geom) AS area FROM countries
… join two tables together:
SELECT airport.geom, airport.name, city.population
FROM airports AS airport, cities AS city
WHERE airport.city = city.id
… convert data types that GeoServer doesn’t support:
SELECT id, name, geom, iwxxm::text FROM weather_stations
IWXXM data is stored in XML, which can be stored and validated natively by PostgreSQL (as can JSON, arrays and other types), but is not available in GeoServer. But by adding ::text, we convert it to text and expose it as a regular attribute in our layers.
Using view parameters
We can take these static SQL Views one step further by adding parameters to our SQL queries to make dynamic OGC requests based on user input. Boundless has some examples of using parameterized SQL Views in our various tutorials, including GeoNames Heat Map, Building a Census Map, and Building a Routing Application.
The trick is to add parameters to the SQL View that can be specified during a WMS or a WFS request:
SELECT * FROM buildings WHERE type = '%type%'
When we make a WMS GetMap request, for example, we can add the following to the URL:
…&VIEWPARAMS=type:hospital
The result will be the execution of the following query on the database:
SELECT * FROM buildings WHERE type = 'hospital'
If you go to our Geonames Word Map demo and type “canyon” as the search term, you can see the following WMS request being sent to our server:
Buried in there is VIEWPARAMS=word:canyon, and if you open this URL in your browser, you’ll see the results of the query are taken by GeoServer to generate a heatmap.
Our routing application uses multiple parameters (source, target and cost) to generate the path between two points.
Once you’ve added them to your GeoServer toolkit, you’ll wonder how you ever did without SQL Views!
Security
Allowing a GeoServer client to influence the SQL that will be executed on your database opens the door to an SQL injection attack. To prevent the client from running arbitrary SQL code, GeoServer comes with parameter validation using regular expressions. We know that regular expressions can have a steep learning curve, but let’s go over some easy examples.
We must consider what values we want to allow for each parameter as the first step to crafting a validation expression.
Take the following SQL query:
SELECT * FROM roads
WHERE city = '%city%' AND population > %rank% AND type = '%type%'
In this example, we will accept any city name with alphabetic characters (A-z) and spaces (\s) giving a regular expression of ^[A-z\s]+$. Population is always a number (\d) so we can use ^\d+$. Finally, for the road type, we only want to accept one of the following: highway, primary, secondary and residential. This gives the following expression: ^(highway|primary|secondary|residential)$.
By default, GeoServer uses a slightly more permissive (but usually safe!) ^[\w\d\s]+$, which allows letters, numbers and spaces for all parameters.
With these controls, we have prevented a malicious client from crafting a SQL injection that could potentially destroy our data. If a client does attempt to use a parameter that fails the regular expression check (for example: VIEWPARAMS=city:London,rank:1,type:tertiary), GeoServer will return an error:
Invalid value for parameter name
More importantly the SQL will not be executed!
Caveats
So what’s the catch? There are a few things we have to take into consideration when using SQL Views. The main points of attention are:
First, be aware of potential performance implications. If you write complex SQL queries, remember that each time you make a request, the query will be executed. If it’s a slow query, your users will be waiting that much longer for a response.
Second, layers built from SQL Views are read-only. This means that you can’t use WFS-T to write back to the layer, unlike regular layers that have been published from regular database tables.
More reading
The Boundless Workshops page is a great place to read about the practical use of SQL Views in applications.
For a thorough discussion, see the OpenGeo Suite User manual’s section on SQL Views.
The post Support story: Getting more out of SQL Views appeared first on Boundless.
from Planet GS via John Jason Fallows in Inoreader http://ift.tt/19ClBrD
Benjamin Trigona-Harany
More Stories
‘My 401k Misses You’: Black Woman Pumped To Meet Donald Trump In Philadelphia – July 18, 2023 at 04:56PM
Energy Provider Warns of Impending ‘Crisis,’ ‘Blackout Conditions’ Driven By Biden Plans – July 18, 2023 at 04:20PM
Dog starts barking at cows crossing a bridge, so the cows stop to have a look. – July 17, 2023 at 02:27PM