You probably know how to create a SQL view. Using views lets you represent data in different ways, extracting and transforming the data and avoiding data duplication.
With RDBMS, you can store views inside the database. In this case, a view is just a feature type for GeoServer, just like for a table.
You can also use a different approach with GeoServer, storing the SQL code inside your GeoServer configuration. This way, SQL views allow the execution of a custom SQL query on each request to the layer. This avoids the need to create a database view for complex queries.
Note
We use PostGIS in this book. While it is one of the most powerful spatial databases available, not to mention that it is free to use, you may need to use other databases. GeoServer also supports Oracle, SQL Server, and MySQL with extension modules. You can use the recipes in this book with any of them; you only need to be careful with the SQL code. Code inserted in this book uses the ST_* functions that may have different syntax or be unavailable in other databases than PostGIS.
We will build a view that contains only European countries. Open your GeoServer web interface and switch to Layers:
Select Add a new resource, and from the dropdown list, select the data store pointing to your RDBMS, PostGIS in our case. Instead of selecting a table from the list, select the Configure new SQL view… link:
In the form, insert
EuropeanCountriesas View Name and the following code as the SQL statement:SELECT ADMIN, ST_UNION(COUNTRIES_EXP.GEOM) AS GEOM FROM (SELECT ADMIN, (ST_DUMP(GEOM)).geom as geom FROM COUNTRIES WHERE REGION_UN = 'Europe') COUNTRIES_EXP WHERE ST_Intersects(COUNTRIES_EXP.GEOM, ST_GeomFromText('POLYGON((-11 37.40, -11 73.83, 27.28 73.83, 27.28 37.40, -11 37.40))',4326)) = TRUE GROUP BY ADMINMove to the bottom of the screen and select the Guess Geometry type and srid checkbox and click on Refresh. The 4326 EPSG code is properly detected, but you have to manually select MultiPolygon to avoid detecting the value of the polygon instead:
Click on Save, and you will be brought to the publish layer form. Click on the button to calculate the native data extent and click on Publish. Move to Layer Preview and select the EuropeanCountries layer; your map should look like this one:
Creating a SQL view in GeoServer is not different from creating one in an RDBMS. You just have to build proper SQL code to filter and transform the feature.
It is not mandatory that source tables for your view are already published in GeoServer. You only need to have a data store properly configured to an RDBMS; you can't use a SQL view against a shapefile or other file-based stores.
As you can see in the SQL code for this recipe, you can use any combination of the standard SQL and vendor extension. GeoServer does not evaluate the code, but it demands parsing and evaluation to the RDBMS.
You can use the aggregation and transformation function as we did. You need to return at least a proper geometrical field so that GeoServer can evaluate it and use it to configure a layer.
The view created from GeoServer is not stored inside your RDBMS. This may sound odd if you're used to creating views in a database. Indeed, GeoServer views are a sort of virtual table. You can check this inside the data directory. Look for the workspace and find the featuretype definition, which is in the featuretype.xml file. You will find that your SQL query is just stored inside it:
<metadata>
<entry key="JDBC_VIRTUAL_TABLE">
<virtualTable>
<name>EuropeanCountries</name>
<sql>SELECT
ADMIN,
ST_UNION(COUNTRIES_EXP.GEOM) AS GEOM
FROM
(SELECT
ADMIN,
(ST_DUMP(GEOM)).geom as geom
FROM
COUNTRIES
WHERE
REGION_UN = 'Europe') COUNTRIES_EXP
WHERE
ST_Intersects(COUNTRIES_EXP.GEOM, ST_GeomFromText('POLYGON((-11 37.40, -11 73.83, 27.28 73.83, 27.28 37.40, -11 37.40))',4326)) = TRUE
GROUP BY ADMIN</sql>
<escapeSql>false</escapeSql>
<geometry>
<name>geom</name>
<type>MultiPolygon</type>
<srid>4326</srid>
</geometry>
</virtualTable>
</entry>
</metadata>