HowTo: add a SimpleSQL feature store

1. Introduction

This HowTo page describes how to use the deegree 3 services console to add a so-called "SimpleSQL feature store" configuration to the deegree 3 web services. The description is based on the deegree3 utahDemo.

The tutorial shows how to add data from a shape file into a new SimpleSQL feature store, in order to make the data available via deegree featureService (WFS) and deegree mapServices (WMS). The logical follow-up will be handled in another tutorial on how to add a layer to a mapService.

Generally, the SimpleSQLFeatureStore can be used with any spatial SQL database that supports WKT/WKB geometry representations and BBOX queries. Currently, it has been successfully tested with the following products:

• PostgreSQL 8.3 / PostGIS 1.2
• Oracle Spatial 10i
• MySQL 5.1 (requires deegree 3.1-pre2, or higher)

As the SimpleSQLFeatureStore relies on a very generic way to access features stored in a spatial database, it has some limitations/requirements:

• You need to have basic knowledge of spatial SQL and WKT/WKB functions of your database.
• Only read operations are supported (no transactions).
• Only simple features can be fetched from the db (no multi properties, nested properties, etc).

• Only BBOX-queries are actually filtered by the SQL database. Filter constraints that use other expressions, such as PropertyIsEqualTo will also work, but are evaluated in memory by deegree. For many use-cases (e.g. for WMS or in most WFS clients) this is not a real limitation, because they usually involve a BBOX constraint. However, if you have really large amounts of features in your database and want to filter using non-spatial constraints only (without a BBOX), responses will become slow.

If you require more functionality (like support for transactions, complex features, ...) you have to use a specialized SQL feature store. Currently, only the PostGISFeatureStore is available, but variants for other spatial databases (Oracle Spatial, MySQL, MSSQLServer,...) can be easily derived from that. Please let us know if you are interested in providing code or if you want to sponsor this.

2. Preparations

1. You need to create a spatial table in your database and prepare that table with spatial data based on a shapefile.
   • Create a spatially enabled database. This depends on your DBMS, so please refer to the respective DB documentation for details.

   • Download the example SHP-File (ZIP), which depicts the LandslideAreas of the Utah County. The shape file is provided by the Utah GIS Portal, hosted by the AGRC.

   • Unpack the zip file to a location of your choice.

   • Import the shapefile into your database. A common tool for doing this is GDAL: Read the ogr2ogr documentation or check the comprehensive ogr cheatsheet for example command lines.

     
     PostgreSQL/PostGIS

     ### INSERT DATA INTO DB ###
     ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 user=postgres password=postgres dbname=deegree" -nln "landslides" -a_srs "EPSG:26912" path/to/SGID93_GEOSCIENCE_LandslideAreas/SGID93_GEOSCIENCE_LandslideAreas.shp -skipfailures
     
     ### INSERT DATA INTO DB - ALTERNATIVE (geometry type for the created layer is defined as "GEOMETRY" & geometry column is renamed to from "wkb_geometry" to "the_geom") ###
     ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 user=postgres password=postgres dbname=deegree" -nln "landslides" -nlt "GEOMETRY" -lco GEOMETRY_NAME=the_geom -a_srs "EPSG:26912" path/to/SGID93_GEOSCIENCE_LandslideAreas/SGID93_GEOSCIENCE_LandslideAreas.shp -skipfailures
     
     ### TEST SUCCESS ###
     ogrinfo PG:"host=localhost port=5432 user=postgres password=postgres dbname=deegree" landslides | less

     MySQL

     ### INSERT DATA INTO DB ###
     ogr2ogr -f MySQL MYSQL:"mydb,host=myhost,user=mylogin,password='mypassword',port=3306" -nln "landslides" -a_srs "EPSG:26912" path/to/SGID93_GEOSCIENCE_LandslideAreas/SGID93_GEOSCIENCE_LandslideAreas.shp
     
     ### TEST SUCCESS ###
     ogrinfo MySQL:mydb,user=mylogin,password='mypassword',host=localhost,port=3306 landslides | less

     Oracle Spatial
     The shape to sdo converter for Oracle9i Spatial and higher versions may be downloaded from the oracle servers as shp2sdo.zip. There are "exe" files for both Windows and Linux (yes indeed, an exe file for Linux). Read using_shp2sdo.txt from the zip file for details.

     ### CREATE SQL SCRIPTS ###
     oracle@myserver:~/tools/shp2sdo_linux$ ./shp2sdo.exe -o ../../tmp/SGID93_GEOSCIENCE_LandslideAreas landslides -i -s 26912
     
     ### CREATE TABLE ###
     oracle@myserver:~/tools/shp2sdo_linux$ sqlplus myUser/myPassword @landslides.sql
     
     ### INSERT DATA ###
     oracle@myserver:~/tools/shp2sdo_linux$ sqlldr myUser/myPassword control=landslides.ctl

2. You need to create a working JDBC connection from deegree to your database. This step can be achieved with the deegree 3 services console. Read more about this step in the tutorial on how to add a JDBC connection.

3. Add SimpleSQL feature store

Services console login: "deegree" (without the quote signs) is the default password.

1. Click on the link feature stores.

2. At the bottom of the listed features there is an input field for new feature stores. Enter a name (e.g. utah_landslides) for the new feature store. From the select box choose the entry SimpleSQL. Click on Create new.
   

3. In the following screen you will be able to edit the XML file of the new feature store.
   The location of this file is displayed at the top. The name of the file matches the name that you entered in the previous step:

   Editing:
   /home/mays/deegree3/deegree-utah-demo/webapps/ROOT/WEB-INF/workspace/datasources/feature/utah_landslides.xml

   Below that you get an xml editor with example entries to help you along.
   
   

   There are four mandatory elements: <StorageCRS>, <ConnectionPoolId>, <SQLStatement> and <BBoxStatement>.

   For a full explanation on all elements please refer to the SimpleSQL feature store documentation. The configuration depends on your local machine and the type of database you are using.

   
   Example configuration for MySQL:

   <SimpleSQLFeatureStore
    configVersion="3.0.0"
    xmlns="http://www.deegree.org/datasource/feature/simplesql"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.deegree.org/datasource/feature/simplesql http://schemas.deegree.org/datasource/feature/simplesql/3.0.0/simplesql.xsd">
   
     <!-- [1] CRS for storing geometries -->
     <StorageCRS>EPSG:26912</StorageCRS>
   
     <!-- [0...1] Local name of the feature type (defaults to 'Feature') -->
     <FeatureTypeName>LandslideAreas</FeatureTypeName>
   
     <!-- [0...1] Namespace of the feature type (defaults to NULL namespace) -->
     <FeatureTypeNamespace>http://www.deegree.org/app</FeatureTypeNamespace>
   
     <!-- [0...1] Prefix of the feature type (defaults to auto-generated prefix) -->
     <FeatureTypePrefix>app</FeatureTypePrefix>
   
     <!-- [1] database connection -->
     <ConnectionPoolId>mysql</ConnectionPoolId>
   
     <!-- [1] query statement -->
     <SQLStatement>
       SELECT code, type, status, shape_leng, shape_area, asbinary(SHAPE) AS geom
       FROM landslides
       WHERE MBRIntersects(GeomFromText(?),SHAPE)
     </SQLStatement>
   
     <!-- [1] bounding box statement -->
     <BBoxStatement>
       SELECT 'POLYGON((199709.85678244993 4088920.4794775285,199709.85678244993 4644250.101781303,736495.959185044 4644250.101781303,736495.959185044 4088920.4794775285,199709.85678244993 4088920.4794775285))' AS bbox
     </BBoxStatement>
   
   </SimpleSQLFeatureStore>
   

   
   Of course, the above BBoxStatement should be improved to automatically calculate the extent of all features from their individual bounding boxes. An example snippet on how to do this in PostGIS or Oracle is given below. If you know how this works with MySQL, please let us know!

   
   Example configuration for PostGIS:

   <SimpleSQLFeatureStore
    configVersion="3.0.0"
    xmlns="http://www.deegree.org/datasource/feature/simplesql"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.deegree.org/datasource/feature/simplesql http://schemas.deegree.org/datasource/feature/simplesql/3.0.0/simplesql.xsd">
   
     <!-- [1] CRS for storing geometries -->
     <StorageCRS>EPSG:26912</StorageCRS>
   
     <!-- [0...1] Local name of the feature type (defaults to 'Feature') -->
     <FeatureTypeName>LandslideAreas</FeatureTypeName>
   
     <!-- [0...1] Namespace of the feature type (defaults to NULL namespace) -->
     <FeatureTypeNamespace>http://www.deegree.org/app</FeatureTypeNamespace>
   
     <!-- [0...1] Prefix of the feature type (defaults to auto-generated prefix) -->
     <FeatureTypePrefix>app</FeatureTypePrefix>
   
     <!-- [1] database connection -->
     <ConnectionPoolId>postgis</ConnectionPoolId>
   
     <!-- [1] query statement -->
     <SQLStatement>
       SELECT code, type, status, shape_leng, shape_area, asbinary(wkb_geometry) AS geom
       FROM landslides
       WHERE wkb_geometry &amp;&amp; st_geomfromtext(?, 26912)
     </SQLStatement>
   
     <!-- [1] bounding box statement -->
     <BBoxStatement>
       SELECT astext(ST_Estimated_Extent('landslides', 'wkb_geometry')) as bbox
     </BBoxStatement>
   </SimpleSQLFeatureStore>
   

   
   Example for Oracle Spatial: The SimpleSQL feature store is currently (3.0.1) not working with Oracle. Support is planned for one of the upcoming versions.

1. To apply the changes, you need to click the Save button at the top of the editor. The editor window will close and the Reload link in the top left corner will turn red to inform you about changes that have not been forwarded to the server side yet.

2. Click the Reload button to permanently store the changes in your configuration. (Follow-up message: Applying changes. Please wait).
   

4. Check success

1. The new SimpleSQL feature store utah_landslides will be listed togehter with all other available feature stores.
   

2. Check the capabilities of your WFS:

   Click web services and then click on the Capabilities button next to the wfs.
   

   The <WFS_Capabilities> document will contain the following section:

   <wfs:FeatureType>
     <wfs:Name xmlns:app="http://www.deegree.org/app">app:LandslideAreas</wfs:Name>
     <wfs:Title>app:LandslideAreas</wfs:Title>
     <wfs:DefaultSRS>EPSG:26912</wfs:DefaultSRS>
     <wfs:OtherSRS>EPSG:4326</wfs:OtherSRS>
     <wfs:OutputFormats>
       <wfs:Format>text/xml; subtype=gml/2.1.2</wfs:Format>
       <wfs:Format>text/xml; subtype=gml/3.0.1</wfs:Format>
       <wfs:Format>text/xml; subtype=gml/3.1.1</wfs:Format>
       <wfs:Format>text/xml; subtype=gml/3.2.1</wfs:Format>
     </wfs:OutputFormats>
     <ows:WGS84BoundingBox>
       <ows:LowerCorner>-114.619607 36.898519</ows:LowerCorner>
       <ows:UpperCorner>-108.148306 41.949927</ows:UpperCorner>
     </ows:WGS84BoundingBox>
   </wfs:FeatureType>
   

   
   Depending on your database the section for <WGS84BoundingBox> might be different. For PostGIS the combining BBOX gets calculated automatically from all geometries:

     <ows:WGS84BoundingBox>
       <ows:LowerCorner>-111.730497 40.127923</ows:LowerCorner>
       <ows:UpperCorner>-111.499315 40.500561</ows:UpperCorner>
     </ows:WGS84BoundingBox>
   

5. What next ?

The data is there already, but now we want a map! The logical follow-up will be handled in another tutorial and teach you how to add a layer to a mapService, based on existing data.

6. Further reading

For details on all the elements of a <SimpleSQLFeatureStore>, please refer to the feature store documentation.

Pages focusing on the deegree 3 services console:

Detailed description of the deegree 3 workspace and associated configuration pages:

---

CategoryDeegree3 CategoryHowTo CategoryServicesConsole