6. Feature stores

Feature stores are workspace resources that provide access to stored features. The two most common use cases for feature stores are:

The remainder of this chapter describes some relevant terms and the feature store configuration files in detail. You can access this configuration level by clicking feature stores in the service console. The corresponding resource configuration files are located in subdirectory datasources/feature/ of the active deegree workspace directory.

_images/workspace-overview-feature.png

Feature store resources provide access to geo objects

6.1. Features, feature types and application schemas

Features are abstractions of real-world objects, such as rivers, buildings, streets or state boundaries. They are the geo objects of a particular application domain.

A feature types defines the data model for a class of features. For example, a feature type River could define a class of river features that all have the same properties.

6.1.1. Simple vs. rich features and feature types

Some feature types have a more complex structure than others. Traditionally, GIS software copes with “simple” feature types:

  • Every property is either simple (string, number, date, etc.) or a geometry
  • Only a single property with one name is allowed

Basically, a simple feature type is everything that can be represented using a single database table or a single shape file. In contrast, “rich” feature types additionally allow the following:

  • Multiple properties with the same name
  • Properties that contain other features
  • Properties that reference other features or GML objects
  • Properties that contain GML core datatypes which are not geometries (e.g. code types or units of measure)
  • Properties that contain generic XML

Example of a rich feature instance encoded in GML

<ad:Address gml:id="AD_ADDRESS_b15cd863-1b47-4f3c-9cd5-d5283d674a2b">
  <ad:inspireId>
    <base:Identifier xmlns:base="urn:x-inspire:specification:gmlas:BaseTypes:3.2">
      <base:localId>0532200000000003</base:localId>
      <base:namespace>NL.KAD.BAG</base:namespace>
    </base:Identifier>
  </ad:inspireId>
  <ad:position>
    <ad:GeographicPosition>
      <ad:geometry>
        <gml:Point gml:id="POINT_64fae7bf-a836-44af-a63c-349bed1c6f55" srsName="urn:ogc:def:crs:EPSG::4258">
          <gml:pos>52.689618 5.246345</gml:pos>
        </gml:Point>
      </ad:geometry>
      <ad:specification>entrance</ad:specification>
      <ad:method>byOtherParty</ad:method>
      <ad:default>true</ad:default>
    </ad:GeographicPosition>
  </ad:position>
  <ad:locator>
    <ad:AddressLocator>
      <ad:designator>
        <ad:LocatorDesignator>
          <ad:designator>1</ad:designator>
          <ad:type>2</ad:type>
        </ad:LocatorDesignator>
      </ad:designator>
      <ad:level>unitLevel</ad:level>
    </ad:AddressLocator>
  </ad:locator>
  <ad:validFrom>2009-01-05T23:00:00.000</ad:validFrom>
  <ad:validTo>2299-12-30T23:00:00.000</ad:validTo>
  <ad:beginLifespanVersion xsi:nil="true" nilReason="UNKNOWN" />
  <ad:endLifespanVersion xsi:nil="true" nilReason="UNKNOWN" />
  <ad:component xlink:href="#FEATURE_d4a54e57-91cd-410d-9c3d-b0fafdaa080f" />
  <ad:component xlink:href="#FEATURE_240b3dd2-fc1c-448e-82a4-210cffe6dd34" />
  <ad:component xlink:href="#FEATURE_64f481f4-8a21-4474-8efd-28d01db5e2e3" />
</ad:Address>

Hint

All deegree feature stores support simple feature types, but only the SQL feature store and the memory feature store support rich feature types.

6.1.2. Application schemas

An application schema defines a number of feature types for a particular application domain. When referring to an application schema, one usually means a GML application schema that defines a hierarchy of rich feature types. Examples for GML application schemas are:

  • INSPIRE Data Themes (Annex I, II and III)
  • GeoSciML
  • CityGML
  • XPlanung
  • AAA

The following diagram shows a part of the INSPIRE Annex I application schema in UML form:

_images/address_schema.png

Hint

The SQL feature store or the memory feature store can be used with GML application schemas.

6.2. Shape feature store

The shape feature store serves a feature type from an ESRI shape file. It is currently not transaction capable and only supports simple feature types.

6.2.1. Minimal configuration example

The only mandatory element is File. A minimal valid configuration example looks like this:

Shape Feature Store config (minimal configuration example)

<ShapeFeatureStore configVersion="3.1.0"
  xmlns="http://www.deegree.org/datasource/feature/shape"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.deegree.org/datasource/feature/shape
  http://schemas.deegree.org/datasource/feature/shape/3.1.0/shape.xsd">

  <!-- Required: Path to shape file on file system (can be relative) -->
  <File>/tmp/rivers.shp</File>

</ShapeFeatureStore>

This configuration will set up a feature store based on the shape file /tmp/rivers.shp with the following settings:

  • The feature store offers the feature type app:rivers (app bound to http://www.deegree.org/app)
  • SRS information is taken from file /tmp/rivers.prj (if it does not exist, EPSG:4326 is assumed)
  • The geometry is added as property app:GEOMETRY
  • All data columns from file /tmp/rivers.dbf are used as properties in the feature type
  • Encoding of text columns in /tmp/rivers.dbf is guessed based on actual contents
  • An alphanumeric index is created for the dbf to speed up filtering based on non-geometric constraints

6.2.2. More complex configuration example

A more complex example that uses all available configuration options:

Shape Feature Store config (more complex configuration example)

<ShapeFeatureStore configVersion="3.1.0"
  xmlns="http://www.deegree.org/datasource/feature/shape"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.deegree.org/datasource/feature/shape
  http://schemas.deegree.org/datasource/feature/shape/3.1.0/shape.xsd">
  <StorageCRS>EPSG:4326</StorageCRS>
  <FeatureTypeName>River</FeatureTypeName>
  <FeatureTypeNamespace>http://www.deegree.org/app</FeatureTypeNamespace>
  <FeatureTypePrefix>app</FeatureTypePrefix>
  <File>/tmp/rivers.shp</File>
  <Encoding>ISO-8859-1</Encoding>
  <GenerateAlphanumericIndexes>false</GenerateAlphanumericIndexes>
  <Mapping>
    <SimpleProperty name="objectid" mapping="OBJECTID" />
    <GeometryProperty name="mygeom" />
  </Mapping>
</ShapeFeatureStore>

This configuration will set up a feature store based on the shape file /tmp/rivers.shp with the following settings:

  • SRS of stored geometries is EPSG:4326 (no auto-detection)
  • The feature store offers the shape file contents as feature type app:River (app bound to http://www.deegree.org/app)
  • Encoding of text columns in /tmp/rivers.dbf is ISO-8859-1 (no auto-detection)
  • No alphanumeric index is created for the dbf (filtering based on non-geometric constraints has to be performed in-memory)
  • The mapping between the shape file columns and the feature type properties is customized.
  • Property objectid corresponds to column OBJECTID of the shape file
  • Property geometry corresponds to the geometry of the shape file

6.2.3. Configuration options

The configuration format for the deegree shape feature store is defined by schema file http://schemas.deegree.org/datasource/feature/shape/3.1.0/shape.xsd. The following table lists all available configuration options. When specifiying them, their order must be respected.

Option Cardinality Value Description
StorageCRS 0..1 String CRS of stored geometries
FeatureTypeName 0..n String Local name of the feature type (defaults to base name of shape file)
FeatureTypeNamespace 0..1 String Namespace of the feature type (defaults to “http://www.deegree.org/app”)
FeatureTypePrefix 0..1 String Prefix of the feature type (defaults to “app”)
File 1..1 String Path to shape file (can be relative)
Encoding 0..1 String Encoding of text fields in dbf file
GenerateAlphanumericIndexes 0..1 Boolean Set to true, if an index for alphanumeric fields should be generated
Mapping 0..1 Complex Customized mapping between dbf column names and property names

6.3. Memory feature store

The memory feature store serves feature types that are defined by a GML application schema and are stored in memory. It is transaction capable and supports rich GML application schemas.

6.3.1. Minimal configuration example

The only mandatory element is GMLSchema. A minimal valid configuration example looks like this:

Memory Feature Store config (minimal configuration example)

<MemoryFeatureStore configVersion="3.0.0"
  xmlns="http://www.deegree.org/datasource/feature/memory"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.deegree.org/datasource/feature/memory
  http://schemas.deegree.org/datasource/feature/memory/3.0.0/memory.xsd">

  <!-- Required: GML application schema file / directory to read feature types from -->
  <GMLSchema version="GML_32">../../appschemas/inspire/annex1/addresses.xsd</GMLSchema>

</MemoryFeatureStore>

This configuration will set up a memory feature store with the following settings:

  • The GML 3.2 application schema from file ../../appschemas/inspire/annex1/addresses.xsd is used as application schema (i.e. scanned for feature type definitions)
  • No GML datasets are loaded on startup, so the feature store will be empty unless an insertion is performed (e.g. via WFS-T)

6.3.2. More complex configuration example

A more complex example that uses all available configuration options:

Memory Feature Store config (more complex configuration example)

<MemoryFeatureStore configVersion="3.0.0" xmlns="http://www.deegree.org/datasource/feature/memory"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.deegree.org/datasource/feature/memory
  http://schemas.deegree.org/datasource/feature/memory/3.0.0/memory.xsd">
  <StorageCRS>urn:ogc:def:crs:EPSG::4258</StorageCRS>
  <GMLSchema version="GML_32">../../appschemas/inspire/annex1/</GMLSchema>
  <GMLFeatureCollection version="GML_32">../../data/gml/address.gml</GMLFeatureCollection>
  <GMLFeatureCollection version="GML_32">../../data/gml/parcels.gml</GMLFeatureCollection>
</MemoryFeatureStore>

This configuration will set up a memory feature store with the following settings:

  • Directory ../../appschemas/inspire/annex1/ is scanned for *.xsd files. All found files are loaded as a GML 3.2 application schema (i.e. analyzed for feature type definitions).
  • Dataset file ../../data/gml/address.gml is loaded on startup. This must be a GML 3.2 file that contains a feature collection with features that validates against the application schema.
  • Dataset file ../../data/gml/parcels.gml is loaded on startup. This must be a GML 3.2 file that contains a feature collection with features that validates against the application schema.
  • The geometries of loaded features are converted to urn:ogc:def:crs:EPSG::4258.

6.3.3. Configuration options

The configuration format for the deegree memory feature store is defined by schema file http://schemas.deegree.org/datasource/feature/memory/3.0.0/memory.xsd. The following table lists all available configuration options (the complex ones contain nested options themselves). When specifiying them, their order must be respected.

Option Cardinality Value Description
StorageCRS 0..1 String CRS of stored geometries
GMLSchema 1..n String Path/URL to GML application schema files/dirs to read feature types from
GMLFeatureCollection 0..n Complex Path/URL to GML feature collections documents to read features from

6.4. Simple SQL feature store

The simple SQL feature store serves simple feature types that are stored in a spatially-enabled database, such as PostGIS. However, it’s not suited for mapping rich GML application schemas and does not support transactions. If you need these capabilities, use the SQL feature store instead.

Tip

If you want to use the simple SQL feature store with Oracle or Microsoft SQL Server, you will need to add additional modules first. This is described in Adding database modules.

6.4.1. Minimal configuration example

There are three mandatory elements: JDBCConnId, SQLStatement and BBoxStatement. A minimal configuration example looks like this:

Simple SQL feature store config (minimal configuration example)

<SimpleSQLFeatureStore configVersion="3.0.1"
  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.1/simplesql.xsd">

  <!-- Required: Database connection -->
  <JDBCConnId>connid</JDBCConnId>

  <!-- Required: Query statement -->
  <SQLStatement>
    SELECT name, title, asbinary(the_geom) FROM some_table
    WHERE the_geom &amp;&amp; st_geomfromtext(?, -1)
  </SQLStatement>

  <!-- Required: Bounding box statement -->
  <BBoxStatement>SELECT astext(ST_Estimated_Extent('some_table', 'the_geom')) as bbox</BBoxStatement>

</SimpleSQLFeatureStore>

6.4.2. More complex configuration example

Simple SQL feature store config (more complex configuration example)

<SimpleSQLFeatureStore configVersion="3.0.1"
  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.1/simplesql.xsd">

  <!-- Required: Database connection -->
  <JDBCConnId>connid</JDBCConnId>

  <!-- Required: Query statement -->
  <SQLStatement>
    SELECT name, title, asbinary(the_geom) FROM some_table
    WHERE the_geom &amp;&amp; st_geomfromtext(?, -1)
  </SQLStatement>

  <!-- Required: Bounding box statement -->
  <BBoxStatement>SELECT astext(ST_Estimated_Extent('some_table', 'the_geom')) as bbox</BBoxStatement>

</SimpleSQLFeatureStore>

6.4.3. Configuration options

The configuration format is defined by schema file http://schemas.deegree.org/datasource/feature/simplesql/3.0.1/simplesql.xsd. The following table lists all available configuration options (the complex ones contain nested options themselves). When specifiying them, their order must be respected.

Option Cardinality Value Description
StorageCRS 0..1 String CRS of stored geometries
FeatureTypeName 0..n String Local name of the feature type (defaults to table name)
FeatureTypeNamespace 0..1 String Namespace of the feature type (defaults to “http://www.deegree.org/app”)
FeatureTypePrefix 0..1 String Prefix of the feature type (defaults to “app”)
JDBCConnId 1..1 String Identifier of the database connection
SQLStatement 1..1 String SELECT statement that defines the feature type
BBoxStatement 1..1 String SELECT statement for the bounding box of the feature type
LODStatement 0..n Complex Statements for specific WMS scale ranges

6.5. SQL feature store

The SQL feature store allows to configure highly flexible mappings between feature types and database tables. It can be used for simple mapping tasks (mapping a single database table to a feature type) as well as sophisticated ones (mapping a complete INSPIRE Data Theme to dozens or hundreds of database tables). As an alternative to relational mapping, it additionally offers so-called BLOB mapping which stores any kind of rich feature using a fixed and very simple database schema. In contrast to the simple SQL feature store, the SQL feature store is transaction capable (even for complex mappings) and ideally suited for mapping rich GML application schemas. It currently supports the following databases:

  • PostgreSQL (8.3, 8.4, 9.0, 9.1, 9.2) with PostGIS extension (1.4, 1.5, 2.0)
  • Oracle Spatial (10g, 11g)
  • Microsoft SQL Server (2008, 2012)

Tip

If you want to use the SQL feature store with Oracle Spatial or Microsoft SQL Server, you will need to add additional modules first. This is described in Adding database modules.

6.5.1. Minimal configuration example

A very minimal valid configuration example looks like this:

SQL feature store: Minimal configuration

<SQLFeatureStore configVersion="3.2.0"
  xmlns="http://www.deegree.org/datasource/feature/sql"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.deegree.org/datasource/feature/sql
  http://schemas.deegree.org/datasource/feature/sql/3.2.0/sql.xsd">
  <JDBCConnId>postgis</JDBCConnId>
  <FeatureTypeMapping table="country"/>
</SQLFeatureStore>

This configuration defines a SQL feature store resource with the following properties:

  • JDBC connection resource with identifier postgis is used to connect to the database
  • A single table (country) is mapped
  • Feature type is named app:country (app=http://www.deegree.org/app)
  • Properties of the feature type are automatically derived from table columns
  • Every primitive column (number, string, date) is used as a primitive property
  • Every geometry column is used as a geometry property (storage CRS is determined automatically, inserted geometries are transformed by deegree, if necessary)
  • Feature id (gml:id) is based on primary key column, prefixed by COUNTRY_
  • For insert transactions, it is expected that the database generates new primary keys value automatically (primary key column must have a trigger or a suitable type such as SERIAL in PostgreSQL)

6.5.2. More complex configuration example

A more complex example:

SQL feature store: More complex configuration

<SQLFeatureStore xmlns="http://www.deegree.org/datasource/feature/sql" xmlns:xlink="http://www.w3.org/1999/xlink"
  xmlns:base="urn:x-inspire:specification:gmlas:BaseTypes:3.2" xmlns:ad="urn:x-inspire:specification:gmlas:Addresses:3.0"
  xmlns:gml="http://www.opengis.net/gml/3.2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" configVersion="3.2.0"
  xsi:schemaLocation="http://www.deegree.org/datasource/feature/sql http://schemas.deegree.org/datasource/feature/sql/3.2.0/sql.xsd">
  <JDBCConnId>inspire</JDBCConnId>
  <StorageCRS srid="-1" dim="2D">EPSG:4258</StorageCRS>
  <GMLSchema>../../appschemas/inspire/annex1/Addresses.xsd</GMLSchema>
  <GMLSchema>../../appschemas/inspire/annex1/AdministrativeUnits.xsd</GMLSchema>
  <GMLSchema>../../appschemas/inspire/annex1/CadastralParcels.xsd</GMLSchema>

  <FeatureTypeMapping name="ad:Address" table="ad_address">
    <FIDMapping prefix="AD_ADDRESS_">
      <Column name="attr_gml_id" type="string" />
      <UUIDGenerator />
    </FIDMapping>
    <Complex path="ad:inspireId">
      <Complex path="base:Identifier">
        <Primitive path="base:localId" mapping="localid" />
        <Primitive path="base:namespace" mapping="'NL.KAD.BAG'" />
      </Complex>
    </Complex>
    <Complex path="ad:position">
      <Join table="ad_address_ad_position" fromColumns="fid" toColumns="fk" />
      <Complex path="ad:GeographicPosition">
        <Complex path="ad:geometry">
          <Geometry path="." mapping="value" />
        </Complex>
        <Complex path="ad:specification">
          <Primitive path="text()" mapping="'entrance'" />
        </Complex>
        <Complex path="ad:method">
          <Primitive path="text()" mapping="'byOtherParty'" />
        </Complex>
        <Primitive path="ad:default" mapping="'true'" />
      </Complex>
    </Complex>
    <Complex path="ad:locator">
      <Join table="ad_address_ad_locator" fromColumns="attr_gml_id" toColumns="parentfk" orderColumns="num"
        numbered="true" />
      <Complex path="ad:AddressLocator">
        <Complex path="ad:designator">
          <Join table="ad_address_ad_locator_ad_addresslocator_ad_designator" fromColumns="id" toColumns="parentfk"
            orderColumns="num" numbered="true" />
          <Complex path="ad:LocatorDesignator">
            <Primitive path="ad:designator" mapping="ad_addresslocator_ad_locatordesignator_ad_designator" />
            <Complex path="ad:type">
              <Primitive path="text()" mapping="ad_addresslocator_ad_locatordesignator_ad_type" />
              <Primitive path="@codeSpace" mapping="ad_addresslocator_ad_locatordesignator_ad_type_attr_codespace" />
            </Complex>
          </Complex>
        </Complex>
        <Complex path="ad:level">
          <Primitive path="text()" mapping="ad_addresslocator_ad_level" />
          <Primitive path="@codeSpace" mapping="ad_addresslocator_ad_level_attr_codespace" />
        </Complex>
      </Complex>
    </Complex>
    <Complex path="ad:validFrom">
      <Primitive path="text()" mapping="ad_validfrom" />
      <Primitive path="@nilReason" mapping="ad_validfrom_attr_nilreason" />
      <Primitive path="@xsi:nil" mapping="ad_validfrom_attr_xsi_nil" />
    </Complex>
    <Complex path="ad:validTo">
      <Primitive path="text()" mapping="ad_validto" />
      <Primitive path="@nilReason" mapping="ad_validto_attr_nilreason" />
      <Primitive path="@xsi:nil" mapping="ad_validto_attr_xsi_nil" />
    </Complex>
    <Complex path="ad:beginLifespanVersion">
      <Primitive path="text()" mapping="ad_beginlifespanversion" />
      <Primitive path="@nilReason" mapping="ad_beginlifespanversion_attr_nilreason" />
      <Primitive path="@xsi:nil" mapping="ad_beginlifespanversion_attr_xsi_nil" />
    </Complex>
    <Complex path="ad:endLifespanVersion">
      <Primitive path="text()" mapping="ad_endlifespanversion" />
      <Primitive path="@nilReason" mapping="ad_endlifespanversion_attr_nilreason" />
      <Primitive path="@xsi:nil" mapping="ad_endlifespanversion_attr_xsi_nil" />
    </Complex>
    <Complex path="ad:component">
      <Join table="ad_address_ad_component" fromColumns="attr_gml_id" toColumns="parentfk" orderColumns="num"
        numbered="true" />
      <Feature path=".">
        <Href mapping="href" />
      </Feature>
    </Complex>
  </FeatureTypeMapping>
  
</SQLFeatureStore>

This configuration snippet defines a SQL feature store resource with the following properties:

  • JDBC connection resource with identifier inspire is used to connect to the database
  • Storage CRS is EPSG:4258, database srid is -1 (inserted geometries are transformed by deegree to the storage CRS, if necessary)
  • Feature types are read from three GML schema files
  • A single feature type ad:Address (ad=urn:x-inspire:specification:gmlas:Addresses:3.0) is mapped
  • The root table of the mapping is ad_address
  • Feature type is mapped to several tables
  • Feature id (gml:id) is based on column attr_gml_id, prefixed by AD_ADDRESS__
  • For insert transactions, new values for column attr_gml_id in the root table are created using the UUID generator. For the joined tables, the database has to create new primary keys value automatically (primary key columns must have a trigger or a suitable type such as SERIAL in PostgreSQL)

6.5.3. Overview of configuration options

The SQL feature store configuration format is defined by schema file http://schemas.deegree.org/datasource/feature/sql/3.2.0/sql.xsd. The following table lists all available configuration options (the complex ones contain nested options themselves). When specifying them, their order must be respected:

Option Cardinality Value Description
<JDBCConnId> 1 String Identifier of the database connection
<DisablePostFiltering> 0..1 Empty If present, queries that require in-memory filtering are rejected
<StorageCRS> 0..1 Complex CRS of stored geometries
<GMLSchema> 0..n String Path/URL to GML application schema files/dirs to read feature types from
<BLOBMapping> 0..1 Complex Activates a special mapping mode that uses BLOBs for storing features
<FeatureTypeMapping> 0..n Complex Mapping between a feature type and a database table

The usage of these options and their sub-options is explained in the remaining sections.

6.5.4. Mapping tables to simple feature types

This section describes how to define the mapping of database tables to simple feature types. Each <FeatureTypeMapping> defines the mapping between one table and one feature type:

SQL feature store: Mapping a single table

<SQLFeatureStore configVersion="3.2.0"
  xmlns="http://www.deegree.org/datasource/feature/sql"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.deegree.org/datasource/feature/sql
  http://schemas.deegree.org/datasource/feature/sql/3.2.0/sql.xsd">
  <JDBCConnId>postgis</JDBCConnId>
  <FeatureTypeMapping table="country"/>
</SQLFeatureStore>

This example assumes that the database contains a table named country within the default database schema (for PostgreSQL public). Alternatively, you can qualify the table name with the database schema, such as public.country. The feature store will try to automatically determine the columns of the table and derive a suitable feature type:

  • Feature type name: app:country (app=http://www.deegree.org/app)
  • Feature id (gml:id) based on primary key column of table country
  • Every primitive column (number, string, date) is used as a primitive property
  • Every geometry column is used as a geometry property

A single configuration file may map more than one table. The following example defines two feature types, based on tables country and cities.

SQL feature store: Mapping two tables

<SQLFeatureStore configVersion="3.2.0"
  xmlns="http://www.deegree.org/datasource/feature/sql"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.deegree.org/datasource/feature/sql
  http://schemas.deegree.org/datasource/feature/sql/3.2.0/sql.xsd">
  <JDBCConnId>postgis</JDBCConnId>
  <FeatureTypeMapping table="country"/>
  <FeatureTypeMapping table="city"/>
</SQLFeatureStore>

There are several options for <FeatureTypeMapping> that give you more control over the derived feature type definition. The following table lists all available options (the complex ones contain nested options themselves):

Option Cardinality Value Description
table 1 String Name of the table to be mapped (can be qualified with database schema)
name 0..1 QName Name of the feature type
<FIDMapping> 0..1 Complex Defines the mapping of the feature id
<Primitive> 0..n Complex Defines the mapping of a primitive-valued column
<Geometry> 0..n Complex Defines the mapping of a geometry-valued column

Hint

The order of child elements <Primitive> and <Geometry> is not restricted. They may appear in any order.

These options and their sub-options are explained in the following subsections.

6.5.4.1. Customizing the feature type name

By default, the name of a mapped feature type will be derived from the table name. If the table is named country, the feature type name will be app:country (app=http://www.deegree.org/app). The name attribute allows to set the feature type name explicity. In the following example, it will be app:Land (Land is German for country).

SQL feature store: Customizing the feature type name

...
  <FeatureTypeMapping table="country" name="Land"/>
...

The name of a feature type is always a qualified XML name. You can use standard XML namespace binding mechanisms to control the namespace and prefix of the feature type name:

SQL feature store: Customizing the feature type namespace and prefix

...
  <FeatureTypeMapping xmlns:myns="http://mydomain.org/myns" table="country" name="myns:Land"/>
...

6.5.4.2. Customizing the feature id

By default, values for the feature id (gml:id attribute in GML) will be based on the primary key column of the mapped table. Values from this column will be prepended with a prefix that is derived from the feature type name. For example, if the feature type name is app:Country, the prefix is APP_COUNTRY. The feature instance that is built from the table row with primary key 42 will have feature id APP_COUNTRY42.

If this is not what you want, or automatic detection of the primary key column fails, customize the feature id mapping using the <FIDMapping> option:

SQL feature store: Customizing the feature id mapping

...
<FeatureTypeMapping table="country">
  <FIDMapping prefix="C_">
    <Column name="fid" />
  </FIDMapping>
</FeatureTypeMapping>
...

Here are the options for <FIDMapping>:

Option Cardinality Value Description
prefix 0..1 String Feature id prefix, default: derived from feature type name
<Column> 1..n Complex Column that stores (a part of) the feature id

As <Column> may occur more than once, you can define that the feature id is constructed from multiple columns:

SQL feature store: Customizing the feature id mapping

...
<FeatureTypeMapping table="country">
  <FIDMapping prefix="C_">
    <Column name="key1" />
    <Column name="key2" />
  </FIDMapping>
</FeatureTypeMapping>
...

Here are the options for <Column>:

Option Cardinality Value Description
name 1 String Name of the database column
type 0..1 String Column type (string, boolean, decimal, double or integer), default: auto

Hint

Technically, the feature id prefix is important to determine the feature type when performing queries by feature id. Every <FeatureTypeMapping> must have a unique feature id prefix.

6.5.4.3. Customizing the mapping between columns and properties

By default, the SQL feature store will try to automatically determine the columns of the table and derive a suitable feature type:

  • Every primitive column (number, string, date) is used as a primitive property
  • Every geometry column is used as a geometry property

If this is not what you want, or automatic detection of the column types fails, use <Primitive> and <Geometry> to control the property definitions of the feature type and the column-to-property mapping:

SQL feature store: Customizing property definitions and the column-to-property mapping

...
  <FeatureTypeMapping table="country">
    <Primitive path="property1" mapping="prop1" type="string"/>
    <Geometry path="property2" mapping="the_geom" type="Point">
      <StorageCRS srid="-1">EPSG:4326</StorageCRS>
    </Geometry>
    <Primitive path="property3" mapping="prop2" type="integer"/>
  </FeatureTypeMapping>
...

This example defines a feature type with three properties:

  • property1, type: primitive (string), mapped to column prop1
  • property2, type: geometry (point), mapped to column the_geom, storage CRS is EPSG:4326, database srid is -1
  • property3, type: primitive (integer), mapped to column prop2

The following table lists all available configuration options for <Primitive> and <Geometry>:

Option Cardinality Value Description
path 1 QName Name of the property
mapping 1 String Name of the database column
type 1 String Property/column type
<Join> 0..1 Complex Defines a change in the table context
<CustomConverter> 0..1 Complex Plugs-in a specialized DB-to-ObjectModel converter implementation
<StorageCRS> 0..1 Complex CRS of stored geometries and database srid (only for <Geometry>)

Hint

If your configuration file is stored in UTF-8 encoding deegree allows special chars from this charset in the mapping (e.g. the property Straße can be stored in the column ‘strasse’ or ‘straße’). Required is that the database supports UTF-8 as well.

6.5.5. Mapping GML application schemas

The former section assumed a mapping configuration that didn’t use a given GML application schema. If a GML application schema is available and specified using <GMLSchema>, the mapping possibilities and available options are extended. We refer to these two modes as table-driven mode (without GML schema) and schema-driven mode (with GML schema).

Here’s a comparison of table-driven and schema-driven mode:

  Table-driven mode Schema-driven mode
GML application schema Derived from tables Must be provided
Data model (feature types) Derived from tables Derived from GML app schema
GML version Any (GML 2, 3.0, 3.1, 3.2) Fixed to version of app schema
Mapping principle Property to table column XPath-based or BLOB-based
Supported mapping complexity Low Very high

Hint

If you want to create a relational mapping for an existing GML application schema (e.g. INSPIRE Data Themes, GeoSciML, CityGML, XPlanung, AAA), always copy the schema files into the appschemas/ directory of your workspace and reference the schema in your configuration.

In schema-driven mode, the SQL feature store extracts detailed feature type definitions and property declarations from GML application schema files. A basic configuration for schema-driven mode defines the JDBC connection id, the general CRS of the stored geometries and one or more GML application schema files:

SQL FeatureStore (schema-driven mode): Skeleton config

<SQLFeatureStore configVersion="3.2.0"
  xmlns="http://www.deegree.org/datasource/feature/sql"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.deegree.org/datasource/feature/sql
  http://schemas.deegree.org/datasource/feature/sql/3.2.0/sql.xsd">

  <JDBCConnId>postgis</JDBCConnId>
  <StorageCRS dim="2D" srid="-1">EPSG:4258</StorageCRS>
  <GMLSchema>../../appschemas/inspire/annex1/ad_address.xsd</GMLSchema>

</SQLFeatureStore>

6.5.5.2. Mapping rich feature types

In schema-driven mode, the <FeatureTypeMapping> element basically works as in table-driven mode (see Mapping tables to simple feature types). It defines a mapping between a table in the database and a feature type. However, there are additional possibilities and it’s usually more suitable to focus on feature types and XML nodes instead of tables and table columns. Here’s an overview of the <FeatureTypeMapping> options and their meaning in schema-driven mode:

Option Cardinality Value Description
table 1 String Name of the table to be mapped (can be qualified with database schema)
name 0..1 QName Name of the feature type
<FIDMapping> 1 Complex Defines the mapping of the feature id
<Primitive> 0..n Complex Defines the mapping of a primitive-valued node
<Geometry> 0..n Complex Defines the mapping of a geometry-valued node
<Complex> 0..n Complex Defines the mapping of a complex-valued node
<Feature> 0..n Complex Defines the mapping of a feature-valued node

Hint

The order of child elements <Primitive>, <Geometry>, <Complex> and <Feature> is not restricted. They may appear in any order.

We’re going to explore the additional options by describing the necessary steps for mapping feature type ad:Address (from INSPIRE Annex I) to an example database. Start with a single <FeatureTypeMapping>. Provide the table name and the mapping for the feature identifier. The example uses a table named ad_address and a key column named fid:

SQL feature store (schema-driven mode): Start configuration

...
<FeatureTypeMapping name="ad:Address" table="ad_address" xmlns:ad="urn:x-inspire:specification:gmlas:Addresses:3.0">
  <FIDMapping>
    <Column name="fid" />
  </FIDMapping>
</FeatureTypeMapping>
...

Tip

In schema-driven mode, there is no automatic detection of columns, column types or primary keys. You always have to specify <FIDMapping>.

Tip

If this configuration matches your database and you have a working WFS resource, you should be able to query the feature type (although no properties will be returned): http://localhost:8080/services?service=WFS&version=2.0.0&request=GetFeature&typeName=ad:Address&count=1

Mapping rich feature types works by associating XML nodes of a feature instance with rows and columns in the database. The table context (the current row) is changed when necessary. In the beginning of a <FeatureTypeMapping>, the current context node is an ad:Address element and the current table context is a row of table ad_address. The first (required) property that we’re going to map is ad:inspireId. The schema defines that ad:inspireId has as child element named base:Identifier which in turn has two child elements named base:localId and base:namespace. Lets’s assume that we have a column localid in our table, that we want to map to base:localId, but for base:namespace, we don’t have a corresponding column. We want this element to have the fixed value NL.KAD.BAG for all instances of ad:Address. Here’s how to do it:

SQL feature store (schema-driven mode): Complex elements and constant mappings

<FeatureTypeMapping name="ad:Address" table="ad_address" xmlns:base="urn:x-inspire:specification:gmlas:BaseTypes:3.2" xmlns:ad="urn:x-inspire:specification:gmlas:Addresses:3.0">
  <FIDMapping>
    <Column name="fid" />
  </FIDMapping>

  <Complex path="ad:inspireId">
    <Complex path="base:Identifier">
      <Primitive path="base:localId" mapping="localid"/>
      <Primitive path="base:namespace" mapping="'NL.KAD.BAG'"/>
    </Complex>
  </Complex>

</FeatureTypeMapping>

There are several things to observe here. The Complex element occurs twice. In the path attribute of the first occurrence, we specified the qualified name of the (complex) property we want to map (ad:inspireId). The nested Complex targets child element base:Identifier of ad:inspireId. And finally, the Primitive elements specify that child element base:localId is mapped to column localid and element base:namespace is mapped to constant NL.KAD.BAG (note the single quotes around NL.KAD.BAG).

To summarize:

  • Complex is used to select a (complex) child element to be mapped. It is a container for child mapping elements (Primitive, Geometry, Complex or Feature)
  • In the mapping attribute of Primitive, you can also use constants, not only column names

The next property we want to map is ad:position. It contains the geometry of the address, but the actual GML geometry is nested on a deeper level and the property can occur multiple times. In our database, we have a table named ad_address_ad_position with columns fk (foreign key to ad_address) and value (geometry). Here’s the extended mapping:

SQL feature store (schema-driven mode): Join elements and XPath expressions

<FeatureTypeMapping name="ad:Address" table="ad_address" xmlns:base="urn:x-inspire:specification:gmlas:BaseTypes:3.2" xmlns:ad="urn:x-inspire:specification:gmlas:Addresses:3.0">
  <FIDMapping>
    <Column name="fid" />
  </FIDMapping>

  <Complex path="ad:inspireId">
    <Complex path="base:Identifier">
      <Primitive path="base:localId" mapping="localid" />
      <Primitive path="base:namespace" mapping="'NL.KAD.BAG'" />
    </Complex>
  </Complex>

  <Complex path="ad:position">
    <Join table="ad_address_ad_position" fromColumns="fid" toColumns="fk" />
    <Complex path="ad:GeographicPosition">
      <Complex path="ad:geometry">
        <Geometry path="." mapping="value" />
      </Complex>
      <Complex path="ad:specification">
        <Primitive path="text()" mapping="'entrance'" />
      </Complex>
      <Complex path="ad:method">
        <Primitive path="text()" mapping="'byOtherParty'" />
      </Complex>
      <Primitive path="ad:default" mapping="'true'" />
    </Complex>
  </Complex>

</FeatureTypeMapping>

Again, the Complex element is used to drill into the XML structure of the property and several elements are mapped to constant values. But there are also new things to observe:

  • The first child element of a <Complex> (or <Primitive>, <Geometry> or <Feature>) can be <Join>. <Join> performs a table change: table rows corresponding to ad:position are not stored in the root feature type table (ad_address), but in a joined table. All siblings of <Join> (or their children) refer to this joined table (ad_address_ad_position). The join condition that determines the related rows in the joined table is ad_address.fid=ad_address_ad_position.fk. <Join> is described in detail in the next section.
  • Valid expressions for path can also be . (current node) and text() (primitive value of the current node).

Let’s move on to the mapping of property ad:component. This property can occur multiple times and contains (a reference to) another feature.

SQL feature store (schema-driven mode): Feature elements

<FeatureTypeMapping name="ad:Address" table="ad_address" xmlns:base="urn:x-inspire:specification:gmlas:BaseTypes:3.2" xmlns:ad="urn:x-inspire:specification:gmlas:Addresses:3.0">    
    [...]
    <Complex path="ad:component">
      <Join table="ad_address_ad_component" fromColumns="fid" toColumns="fk"/>
      <Feature path=".">
        <Href mapping="href"/>
      </Feature>
    </Complex>
</FeatureTypeMapping>

As in the mapping of ad:position, a <Join> is used to change the table context. The table that stores the information for ad:component properties is ad_address_ad_component. The <Feature> declares that we want to map a feature-valued node and it’s <Href> sub-element defines that column href stores the value for the xlink:href.

Here is an overview on all options for <Complex> elements:

Option Cardinality Value Description
path 1 QName Name/XPath-expression that determines the element to be mapped
<Join> 0..1 Complex Defines a change in the table context
<CustomConverter> 0..1 Complex Plugs-in a specialized DB-to-ObjectModel converter implementation
<Primitive> 0..n Complex Defines the mapping of a primitive-valued node
<Geometry> 0..n Complex Defines the mapping of a geometry-valued node
<Complex> 0..n Complex Defines the mapping of a complex-valued node
<Feature> 0..n Complex Defines the mapping of a feature-valued node

Hint

The order of child elements <Primitive>, <Geometry>, <Complex> and <Feature> is not restricted. They may appear in any order.

Here is an overview on all options for <Feature> elements:

Option Cardinality Value Description
path 1 QName Name/XPath-expression that determines the element to be mapped
<CustomConverter> 0..1 Complex Plugs-in a specialized DB-to-ObjectModel converter implementation
<Href> 0..1 Complex Defines the column that stores the value for xlink:href

6.5.5.3. Changing the table context

At the beginning of a <FeatureTypeMapping>, the current table context is the one specified by the table attribute. In the following example snippet, this would be table ad_address.

SQL feature store: Initial table context

<FeatureTypeMapping name="ad:Address" table="ad_address">
 [...]
  <Complex path="gml:identifier">
    <Primitive path="text()" mapping="gml_identifier"/>
    <Primitive path="@codeSpace" mapping="gml_identifier_attr_codespace"/>
  </Complex>
 [...]
</FeatureTypeMapping>

Note that all mapped columns stem from table ad_address. This is fine, as each feature can only have a single gml:identifier property. However, when mapping a property that may occur any number of times, we will have to access the values for this property in a separate table.

SQL feature store: Changing the table context

<FeatureTypeMapping name="ad:Address" table="ad_address">
 [...]
  <Complex path="gml:identifier">
    <Primitive path="text()" mapping="gml_identifier"/>
    <Primitive path="@codeSpace" mapping="gml_identifier_attr_codespace"/>
  </Complex>
 [...]
  <Complex path="ad:position">
    <Join table="ad_address_ad_position" fromColumns="attr_gml_id" toColumns="parentfk" orderColumns="num" numbered="true"/>
    <Complex path="ad:GeographicPosition">
     <Complex path="ad:geometry">
       <Primitive path="@nilReason" mapping="ad_geographicposition_ad_geometry_attr_nilreason"/>
       <Primitive path="@gml:remoteSchema" mapping="ad_geographicposition_ad_geometry_attr_gml_remoteschema"/>
       <Primitive path="@owns" mapping="ad_geographicposition_ad_geometry_attr_owns"/>
       <Geometry path="." mapping="ad_geographicposition_ad_geometry_value"/>
     </Complex>
     [...]
     <Primitive path="ad:default" mapping="ad_geographicposition_ad_default"/>
   </Complex>
 </Complex>
 [...]
</FeatureTypeMapping>

In this example, property gml:identifier is mapped as before (the data values stem from table ad_address). In contrast to that, property ad:position can occur any number of times for a single ad_address feature instance. In order to reflect that in the relational model, the values for this property have to be taken from/stored in a separate table. The feature type table (ad_address) must have a 1:n relation to this table.

The <Join> element is used to define such a change in the table context (in other words: a relation/join between two tables). A <Join> element may only occur as first child element of any of the mapping elements (<Primitive>, <Geometry>, <Feature> or <Complex>). It changes from the current table context to another one. In the example, the table context in the mapping of property ad:position is changed from ad_address to ad_address_ad_position. All mapping instructions that follow the <Join> element refer to the new table context. For example, the geometry value is taken from ad_address_ad_position.ad_geographicposition_ad_geometry_value.

The following table lists all available options for <Join> elements:

Option Cardinality Value Description
table 1..1 String Name of the target table to change to.
fromColumns 1..1 String One or more columns that define the join key in the source table.
toColumns 1..1 String One or more columns that define the join key in the target table.
orderColumns 0..1 String One or more columns hat define the order of the joined rows.
numbered 0..1 Boolean Set to true, if orderColumns refers to a single column that contains natural numbers [1,2,3,...].
<AutoKeyColumn> 0..n Complex Columns in the target table that store autogenerated keys (only required for transactions).

Attributes fromColumns, toColumns and orderColumns may each contain one or more columns. When specifying multiple columns, they must be given as a whitespace-separated list. orderColumns is used to force a specific ordering on the joined table rows. If this attribute is omitted, the order of joined rows is not defined and reconstructed feature instances may vary each time they are fetched from the database. In the above example, this would mean that the multiple ad:position properties of an ad:Address feature may change their order.

In case that the order column stores the child index of the XML element, the numbered attribute should be set to true. In this special case, filtering on property names with child indexes will be correctly mapped to SQL WHERE clauses as in the following WFS example request.

SQL feature store: WFS query with child index

<GetFeature version="2.0.0" service="WFS">
  <Query typeNames="ad:Address">
    <fes:Filter>
      <fes:BBOX>
        <fes:ValueReference>ad:position[3]/ad:GeographicPosition/ad:geometry</fes:ValueReference>
        <gml:Envelope srsName="urn:ogc:def:crs:EPSG::4258">
          <gml:lowerCorner>52.691 5.244</gml:lowerCorner>
          <gml:upperCorner>52.711 5.245</gml:upperCorner>
        </gml:Envelope>
      </fes:BBOX>
    </fes:Filter>
  </Query>
</GetFeature>

In the above example, only those ad:Address features will be returned where the geometry in the third ad:position property has an intersection with the specified bounding box. If only other ad:position properties (e.g. the first one) matches this constraint, they will not be included in the output.

The <AutoKeyColumn> configuration option is only required when you want to use transactions on your feature store and your relational model is non-canonical. Ideally, the mapping will only change the table context in case the feature type model allows for multiple child elements at that point. In other words: if the XML schema has maxOccurs set to unbounded for an element, the relational model should have a corresponding 1:n relation. For a 1:n relation, the target table of the context change should have a foreign key column that points to the primary key column of the source table of the context change. This is important, as the SQL feature store has to propagate keys from the source table to the target table and store them there as well.

If the joined table is the origin of other joins, than it is important that the SQL feature store can generate primary keys for the join table. If not configured otherwise, it is assumed that column id stores the primary key and that the database will auto-generate values on insert using database mechanisms such as sequences or triggers.

If this is not the case, use the AutoKeyColumn options to define the columns that make up the primary key in the join table and how the values for these columns should be generated on insert. Here’s an example:

SQL feature store: Key propagation for transactions

[...]
<Join table="B" fromColumns="id" toColumns="parentfk" orderColumns="num" numbered="true">
  <AutoKeyColumn name="pk1">
    <UUIDGenerator />
  </AutoKeyColumn>
  [...]
  <Join table="C" fromColumns="pk1" toColumns="parentfk" />
  [...]  
</Join>
[...]

In this example snippet, the primary key for table B is stored in column pk1 and values for this column are generated using the UUID generator. There’s another change in the table context from B to C. Rows in table C have a key stored in column parentfk that corresponds to the B.pk1. On insert, values generated for B.pk1 will be propagated and stored for new rows in this table as well. The following table lists the options for <AutoKeyColumn> elements.

Inside a <AutoKeyColumn>, you may use the same key generators that are available for feature id generation (see above).

6.5.5.4. BLOB mapping

An alternative approach to mapping each feature type from an application schema using <FeatureTypeMapping> is to specify a single <BLOBMapping> element. This activates a different storage strategy based on a fixed database schema. Central to this schema is a table that stores every feature instance (and all of it’s properties) as a BLOB (binary large object).

Here is an overview on all options for <BLOBMapping> elements:

Option Cardinality Value Description
<BlobTable> 0..1 String Database table that stores features, default: gml_objects
<FeatureTypeTable> 0..1 String Database table that stores feature types, default: feature_types

The central table (controlled by <BlobTable>) uses the following columns:

Column PostGIS type Used for
id serial Primary key
gml_id text Feature identifier (used for id queries and resolving xlink references)
gml_bounded_by geometry Bounding box (used for spatial queries)
ft_type smallint Feature type identifier (used to narrow the result set)
binary_object bytea Encoded feature instance

The other table (controlled by <FeatureTypeTable>) stores a mapping of feature type names to feature type identifiers:

Column PostGIS type Used for
id smallint Primary key
qname text Name of the feature type
bbox geometry Aggregated bounding box for all features of this type

Hint

In order for <BLOBMapping> to work, you need to have the correct tables in your database and initialize the feature type table with the names of all feature types you want to use. We recommend not to do this manually, see Auto-generating a mapping configuration and tables. The wizard will also create suitable indexes to speed up queries.

Hint

You may wonder how to get data into the database in BLOB mode. As for standard mapping, you can do this by executing WFS-T requests or by using the feature store loader. Its usage is described in the last steps of Auto-generating a mapping configuration and tables.

Hint

In BLOB mode, only spatial and feature id queries can be mapped to SQL WHERE-constraints. All other kinds of filter conditions are performed in memory. See Evaluation of query filters for more information.

6.5.6. Transactions and feature id generation

The mapping defined by a <FeatureTypeMapping> element generally works in both directions:

  • Table-to-feature-type (query): Feature instances are created from table rows
  • Feature-type-to-table (insert): New table rows are created for inserted feature instances

However, there’s a caveat for inserts: The SQL feature store has to know how to obtain new and unique feature ids.

When features are inserted into a SQL feature store (for example via a WFS transaction), the client can choose between different id generation modes. These modes control whether feature ids (the values in the gml:id attribute) have to be re-generated. There are three id generation modes available, which directly relate to the WFS 1.1.0 specification:

  • UseExisting: The feature store will use the original gml:id values that have been provided in the input. This may lead to errors if the provided ids are already in use or if the format of the id does not match the configuration.
  • GenerateNew: The feature store will discard the original gml:id values and use the configured generator to produce new and unique identifiers. References in the input (xlink:href) that point to a feature with an reassigned id are fixed as well, so reference consistency is ensured.
  • ReplaceDuplicate: The feature store will try to use the original gml:id values that have been provided in the input. If a certain identifier already exists in the database, the configured generator is used to produce a new and unique identifier. NOTE: Support for this mode is not implemented yet.

Hint

In a WFS 1.1.0 insert request, the id generation mode is controlled by attribute idGenMode. WFS 1.0.0 and WFS 2.0.0 don’t support to specify it on a request basis. However, in the deegree WFS configuration you can control it in the option EnableTransactions.

In order to generate the required ids for GenerateNew, you can choose between different generators. These are configured in the <FIDMapping> child element of <FeatureTypeMapping>:

6.5.6.1. Auto id generator

The auto id generator depends on the database to provide new values for the feature id column(s) on insert. This requires that the used feature id columns are configured appropriately in the database (e.g. that they have a trigger or a suitable column type such as SERIAL in PostgreSQL).

SQL feature store: Auto id generator example

[...]
<FIDMapping prefix="AD_ADDRESS_">
  <Column name="attr_gml_id" />
  <AutoIDGenerator />
</FIDMapping>
[...]

This snippet defines the feature id mapping and the id generation behaviour for a feature type called ad:Address

  • When querying, the prefix AD_ADDRESS_ is prepended to column attr_gml_id to create the exported feature id. If attr_gml_id contains the value 42 in the database, the feature instance that is created from this row will have the value AD_ADDRESS_42.
  • On insert (mode=UseExisting), provided gml:id values must have the format AD_ADDRESS_$. The prefix AD_ADDRESS_ is removed and the remaining part of the identifier is stored in column attr_gml_id.
  • On insert (mode=GenerateNew), the database must automatically create a new value for column attr_gml_id which will be the postfix of the newly assigned feature id.

6.5.6.2. UUID generator

The UUID generator generator uses Java’s UUID implementation to generate new and unique identifiers. This requires that the database column for the id is a character column that can store strings with a length of 36 characters and that the database does not perform any kind of insertion value generation for this column (e.g triggers).

SQL feature store: UUID generator example

[...]
<FIDMapping prefix="AD_ADDRESS_">
  <Column name="attr_gml_id" />
  <UUIDGenerator />
</FIDMapping>
[...]

This snippet defines the feature id mapping and the id generation behaviour for a feature type called ad:Address

  • When querying, the prefix AD_ADDRESS_ is prepended to column attr_gml_id to create the exported feature id. If attr_gml_id contains the value 550e8400-e29b-11d4-a716-446655440000 in the database, the feature instance that is created from this row will have the value AD_ADDRESS_550e8400-e29b-11d4-a716-446655440000.
  • On insert (mode=UseExisting), provided gml:id values must have the format AD_ADDRESS_$. The prefix AD_ADDRESS_ is removed and the remaining part of the identifier is stored in column attr_gml_id.
  • On insert (mode=GenerateNew), a new UUID is generated and stored in column attr_gml_id.

6.5.6.3. Sequence id generator

The sequence id generator queries a database sequence to generate new and unique identifiers. This requires that the database column for the id is compatible with the values generated by the sequence and that the database does not perform any kind of automatical value insertion for this column (e.g triggers).

SQL feature store: Database sequence generator example

[...]
<FIDMapping prefix="AD_ADDRESS_">
  <Column name="attr_gml_id" />
  <SequenceIDGenerator sequence="SEQ_FID">
</FIDMapping>
[...]

This snippet defines the feature id mapping and the id generation behaviour for a feature type called ad:Address

  • When querying, the prefix AD_ADDRESS_ is prepended to column attr_gml_id to create the exported feature id. If attr_gml_id contains the value 42 in the database, the feature instance that is created from this row will have the value AD_ADDRESS_42.
  • On insert (mode=UseExisting), provided gml:id values must have the format AD_ADDRESS_$. The prefix AD_ADDRESS_ is removed and the remaining part of the identifier is stored in column attr_gml_id.
  • On insert (mode=GenerateNew), the database sequence SEQ_FID is queried for new values to be stored in column attr_gml_id.

6.5.7. Evaluation of query filters

The SQL feature store always tries to map filter conditions (e.g. from WFS GetFeature requests or when accessed by the WMS) to SQL-WHERE conditions. However, this is not always possible. Sometimes a filter uses an expression that just can not be mapped to an equivalent SQL-WHERE clause. For example when using BLOB mapping and the filter is not based on a feature id or a spatial constraint.

In such cases, the SQL feature store falls back to in-memory filtering. It will reconstruct feature by feature from the database and evaluate the filter in memory. If the filter matches, it will be included in the result feature stream. If not, it is skipped.

The downside of this strategy is that it can put a serious load on your server. If you want to turn off in-memory filtering completely, use <DisablePostFiltering>. If this option is specified and a filter requires in-memory filtering, the query will be rejected.

6.5.8. Auto-generating a mapping configuration and tables

Although this functionality is still in beta stage, the services console can be used to automatically derive an SQL feature store configuration and set up tables from an existing GML application schema. If you don’t have an existing database structure that you want to use, you can use this option to create a working database set up very quickly. And even if you have an existing database you need to map manually, this functionality can be prove very helpful to generate a valid mapping configuration to start with.

Hint

As every (optional) attribute and element will be considered in the mapping, you may easily end up with hundreds of tables or columns.

This walkthrough is based on the INSPIRE Annex I schemas, but you should be able to use these instructions with other GML application schemas as well. Make sure that the INSPIRE workspace has been downloaded and activated as described in Example workspace 1: INSPIRE Network Services. As another prerequisite, you will have to create an empty, spatially-enabled PostGIS database that you can connect to from your deegree installation.

Tip

Instead of PostGIS, you can also use an Oracle Spatial or an Microsoft SQL Server database. In order to enable support for these databases, see Adding database modules.

Hint

If the application schema contains UTF-8 characters which are not part of the 7-bit ASCII subset they are normalised during the generation of the feature store configuration for the database mapping (but kept for the feature type names). So the mapping to table and column names contains only 7-bit ASCII character and it is no requirement to the database to use UTF-8.

As a first step, create a JDBC connection to your database. Click server connections -> jdbc and enter inspire (or an other identifier) as connection id:

_images/console_featurestore_mapping1.jpg

Creating a JDBC connection

Afterwards, click Create new and enter the connection details to your database:

_images/console_featurestore_mapping2.jpg

Creating a JDBC connection

By clicking Test connection, you can ensure that deegree can connect to your database:

_images/console_featurestore_mapping3.jpg

Testing the JDBC connection

If everything works, click Create to finish the creation of your JDBC resource:

_images/console_featurestore_mapping4.jpg

Testing the JDBC connection

Now, change to data stores -> feature. We will have to delete the existing (memory-based) feature store first. Click Delete:

_images/console_featurestore_mapping5.jpg

Deleting the memory-based feature store

Enter “inspire” as name for the new feature store, select “SQL” from the drop-down box and click Create new:

_images/console_featurestore_mapping6.jpg

Creating a new SQL feature store resource

Select “Create tables from GML application schema” and click Next:

_images/console_featurestore_mapping7.jpg

Mapping a new SQL feature store configuration

You can now select the GML application schema files to be used. For this walkthrough, tick Addresses.xsd, AdministrativeUnits.xsd and CadastralParcels.xsd (if you select all schema files, hundreds of feature types from INPIRE Annex I will be mapped):

_images/console_featurestore_mapping8.jpg

Selecting the GML schema files to be considered

Hint

This view presents any .xsd files that are located below the appschemas/ directory of your deegree workspace. If you want to map any other GML application schema (such as GeoSciML or CityGML), place a copy of the application schema files into the appschemas/ directory (using your favorite method, e.g. a file browser) and click Rescan. You should now have the option to select the files of this application schema in the services console view.

_images/console_featurestore_mapping9.jpg

Selecting the GML schema files to be considered

Scroll down and click Next.

_images/console_featurestore_mapping10.jpg

Selecting mapping type and storage CRS

You will be presented with a rough analysis of the feature types contained in the selected GML application schema files. Select “Relational” (you may also select BLOB if your prefer this kind of storage) and enter “EPSG:4258” as storage CRS (this is the code for ETRS89, the recommmended CRS for harmonized INSPIRE datasets). After clicking Next, an SQL feature store configuration will be automatically derived from the application schema:

_images/console_featurestore_mapping11.jpg

The auto-generated SQL feature store configuration

Click Save to store this configuration:

_images/console_featurestore_mapping12.jpg

Auto-generated SQL statements for creating tables

Now, click Create DB tables. You will be presented with an auto-generated SQL script for creating the required tables in the database:

_images/console_featurestore_mapping13.jpg

Auto-generated SQL statements for creating tables

Click Execute. The SQL statements will now be executed against your database and the tables will be created:

_images/console_featurestore_mapping15.jpg

Mapping finished

Click Start feature store:

_images/console_featurestore_mapping17.jpg

Finished

Click Reload to force a reinitialization of the other workspace resources. We’re finished. Features access of the WFS and WMS uses your database now. However, as your database is empty, the WMS will not render anything and the WFS will not return any features when queried. In order to insert some harmonized INSPIRE features, click send requests and select one of the insert requests:

Use the third drop-down menu to select an example request. Entries “Insert_200.xml” or “Insert_110.xml” can be used to insert a small number of INSPIRE Address features using WFS-T insert requests:

_images/console_workspace_inspire3.jpg

WFS-T example requests

Click Send to execute the request. After successful insertion, the database contains a few addresses, and you may want to move back to the layer overview (see layers). If you activate the AD.Address layer, the newly inserted features will be rendered by the deegree WMS (look for them in the area of Enkhuizen):

_images/console_workspace_inspire4.jpg

Ad.Address layer after insertion of example Address features

Of course, you can also perform WFS queries against the database backend, such as requesting of INSPIRE Addresses by street name:

_images/console_workspace_inspire5.jpg

More WFS examples

Besides WFS-T requests, there’s another handy option for inserting GML-encoded features. Click data stores -> feature to access the feature store view again:

_images/console_featurestore_mapping18.jpg

Accessing the feature store loader

After clicking Loader, you will be presented with a simple view where you can insert a URL of a valid GML dataset:

_images/console_featurestore_mapping19.jpg

The feature store loader

Basically, you can use this view to insert any valid, GML-encoded dataset, as long as it conforms to the application schema. The INSPIRE workspace contains some suitable example datasets, so you may use a file-URL like:

Tip

The above URLs are for a UNIX system with a user named “kelvin”. You will need to adapt the URLs to match the location of your workspace directory.

After entering the URL, click Import:

_images/console_featurestore_mapping20.jpg

Imported INSPIRE datasets via the Loader