SpatiaLite: SLD/SE Styling

back to XmlBlob main page

SLD/SE Styling - Intro

Starting since version 4.1.0 SpatiaLite will support SLD/SE Styling; all styling documents exactly are XML Documents, so the whole SLD/SE implementation simply is a further specialized extension based on XmlBLOB.
If you are eventually interested to get a deeper comprehension of what really are the OGC Styled Layer Descriptor and Symbology Encoding standards, you can consult the following official documentation:

DB Tables implementing SLD/SE Styling

SELECT CreateStylingTables();

The CreateStylingTables() SQL function will attempt to create all DB Tables required in order to enable SLD/SE Styling support.
Any attempt to directly create or otherwise manipulate these Tables is strongly discouraged; you are always expected to properly invoke CreateStylingTables(), which will correctly define all required Primary / Foreign Key constraints, as will create all validating Triggers expected to support these Tables.

SELECT CreateStylingTables( 1 );

Please note: CreateStylingTables() will optionally accept a single (boolean) argument intended to choose between strict and relaxed Triggers alternative implementations:

when strict mode is specified (default setting) all SLD/SE documents are expected to successfully pass a formal XML Schema validation.
If for any reason such Schema validation fails then the Triggers will reject any attempt to insert the offending Styling document.
on the other side, when relaxed mode is specified a basic check will be performed so to ensure that the Styling document is well-formed, but no XML Schema validation will never be evaluated.

Important notice: oldest versions of libxml2 aren't able to support a successful Schema validation for SE 1.1.0 documents. Using the most recent 2.9.0 version of this library seems to be an absolute pre-requisite.

all pre-built binaries for Windows released by SpatiaLite itself surely support libxml2 2.9.0, and can be thus safely used in strict mode.
while building on Linux it's very probable that you'll encounter a system pre-packaged libxml2 corresponding to some obsolete version: in this case duly switching to relaxed mode is strongly suggested.

the SE_vector_styled_layers table

both f_table_name and f_geometry_column are intended to establish a relational correspondence with some vector layer already defined in the geometry_columns table (Foreign Key).
style_id simply is a progressive number allowing to define more alternative styles supporting the same identical layer.
In other words: f_table_name, f_geometry_column and style_id all together form the Primary Key for this table.
style is the corresponding SLD/SE style document (expected to be a valid XmlBLOB of the SLD/SE vector type).

the SE_raster_styled_layers table

coverage_name is intended to establish a relational correspondence with some already defined raster layer (Foreign Key).
style_id simply is a progressive number allowing to define more alternative styles supporting the same identical layer/coverage.
In other words: coverage_name and style_id all together form the Primary Key for this table.
style is the corresponding SLD/SE style document (expected to be a valid XmlBLOB of the SLD/SE raster type).
Caveat: the actual implementation of this table will surely change in future versions accordingly to RasterLite-2 development.
(current implementation mainly is kind of interim placeholder).

the SE_styled_groups and SE_styled_group_defs tables

These two strictly related tables are intended to support Styled Groups, i.e. complex rendering aggregates possibly based on more that a single layer, and may be of mixed types, i.e. including both Vector and Raster layers at the same time.
A basic key concept in Layer Groups is the paint priority order; each layer could eventually cover (i.e. making absolutely invisible) some previously rendered layer, thus producing unwanted and unexpected results. So explicitly defining a well known internal order is an absolute requirement for Styled Groups.

the SE_styled_groups table:
- group_name (Primary Key) is the unique name identifying the Group.
- both title and abstract are optional elements; anyway they are surely useful in order to keep your definitions in a clear and well ordered state.
the SE_styled_group_defs table:
- id (Primary Key) simply is a unique key; no special meaning is intended.
- group_name (Foreign Key) references to Group to whom this Layer belongs.
- paint_order is intended to specify the relative paint priority; lower values will be rendered first, upper values latest.
  In other world: the whole Group will be rendered respecting the increasing order of paint_order values.
- f_table_name, f_geometry_column and vector_style_id (Foreign Key) are intended to support a layer of the Vector type.
  A corresponding SLD/SE style must be defined in the SE_vector_styled_layers table.
- coverage_name and raster_style_id (Foreign Key) are intended to support a layer of the Raster type.
  A corresponding SLD/SE style must be defined in the SE_raster_styled_layers table.
- Please note: a group item must always correspond to a single layer; so defining a Vector or Raster layer item are mutually exclusive options.

the SE_external_graphics table

Please see the corresponding Wiki page

A practical tutorial by real examples

In this example we'll use the samples contained into the xml-samples/sld-se-styles folder.

export "SPATIALITE_SECURITY=relaxed"

For didactic simplicity we'll use the unsafe SQL functions supporting direct access to external files stored into the file-system.
Remember: you have to explicitly set the SPATIALITE_SECURITY=relaxed environment variable in order to enable all unsafe SQL Import/Export functions.

SELECT CreateStylingTables();
-----
1

And we'll suppose that the SLD/SE supporting tables have been created in strict mode, i.e. always requiring a successful XML Schema validation.

SELECT RegisterVectorStyledLayer( 'my_points', 'geom', 
  XB_Create( XB_LoadXML( 'C:/xml-samples/sld-se-styles/SLD-Stations.xml' ) ) );
-----
0

The RegisterVectorStyledLayer() SQL function inserts a registration into the SE_vector_style_layers table. Any attempt aimed to directly perform any INSERT or UPDATE SQL statement on behalf of SE_vector_styled_layers is strongly discouraged; always using the abstract function RegisterVectorStyledLayer() is warmly recommended, and will preserve long-term compatibility against subsequent releases.

the RegisterVectorStyledLayer() SQL function in its simpler form simply requires passing three arguments:
- the first and second arguments correspond to the Vector Layer, i.e. respectively to f_table_name and f_geometry_column.
  Please note: the corresponding layer is expected to be already defined into the geometry_columns table.
- the third argument is expected to be an appropriate XmlBLOB containing the Style definition.
  So we've used the XB_Create() SQL function in order to create a valid XmlBLOB; and then in turn we've invoked XB_LoadXML() in order to properly load the XML Document from the external file.

Anyway this first attempt was unsuccessful: we've get a 0 result, this meaning failure.
There is very simple reason accounting for this failure: we've missed to perform any XML Schema validation; and accordingly to this the safety Trigger has then rejected this first attempt.

SELECT RegisterVectorStyledLayer( 'my_points', 'geom', 
  XB_Create( XB_LoadXML( 'C:/xml-samples/sld-se-styles/SLD-Stations.xml' ),
    1, 1 ) );
-----
1

This second attempt is finally successful, because this time we've correctly invoked XB_Create() asking for full XML Schema validation.

SELECT RegisterVectorStyledLayer( 'my_lines', 'geom', 
  XB_Create( XB_LoadXML( 'C:/xml-samples/sld-se-styles/SLD-Railways1.xml' ),
    1, 1 ) );
-----
1

SELECT RegisterVectorStyledLayer( 'my_lines', 'geom', 
  XB_Create( XB_LoadXML( 'C:/xml-samples/sld-se-styles/SLD-Railways2.xml' ),
    1, 1 ) );
-----
1

Please note: invoking more than a single time the RegisterVectorStyledLayer() function on behalf of the same layer simply causes registering yet another alternative style; in this case the style_id value will be automatically increased as required so to ensure uniqueness.

SELECT RegisterVectorStyledLayer( 'my_polygs', 'geom', 
  XB_Create( XB_LoadXML( 'C:/xml-samples/sld-se-styles/SE-LocalCouncils1.xml' ),
    1, 1 ) );
-----
1

SELECT RegisterVectorStyledLayer( 'my_polygs', 'geom', 
  XB_Create( XB_LoadXML( 'C:/xml-samples/sld-se-styles/SE-LocalCouncils2.xml' ),
    1, 1 ) );
-----
1

SELECT RegisterVectorStyledLayer( 'my_polygs', 'geom', 0,
  XB_Create( XB_LoadXML( 'C:/xml-samples/sld-se-styles/SLD-LocalCouncils.xml' ),
    1, 1 ) );
-----
1

Please note: you can also use RegisterStyledVectorLayer() in order to update an already existing definition; in this case you simply have to pass a further argument specifying the style_id value (as in the third example).

SELECT RegisterRasterStyledLayer( 'srtm', 
  XB_Create( XB_LoadXML( 'C:/xml-samples/sld-se-styles/SE-Raster2.xml' ),
    1, 1 ) );
-----
1

SELECT RegisterRasterStyledLayer( 'srtm', 
  XB_Create( XB_LoadXML( 'C:/xml-samples/sld-se-styles/SE-Raster3.xml' ),
    1, 1 ) );
-----
1

SELECT RegisterRasterStyledLayer( 'srtm', 1,
  XB_Create( XB_LoadXML( 'C:/xml-samples/sld-se-styles/SLD-Raster.xml' ),
    1, 1 ) );
-----
1

The RegisterRasterStyledLayer() SQL function is strictly analogue to RegisterVectorStyledLayer(), and behaves in exactly the same manner except as in that it's intended to support Raster Coverages.
Caveat: this function may will probably be affected by several changes in future releases accordingly to RasterLite-2 development.

SELECT *
FROM SE_vector_styled_layers_view;

f_table_name	f_geometry_column	style_id	title	abstract	style	schema_validated	schema_uri
my_points	geom	0	SE test - Point (Railway Stations)	a complex variable style (depending on actual scale)	XmlBLOB-VectorStyle sz=1042 (XMLsz=5010) SchemaValidated	1	http://schemas.opengis.net/sld/1.0.0/StyledLayerDescriptor.xsd
my_lines	geom	0	SLD basic test - Line (Railways)	a style using "don't care" rendering	XmlBLOB-VectorStyle sz=596 (XMLsz=1091) SchemaValidated	1	http://schemas.opengis.net/sld/1.0.0/StyledLayerDescriptor.xsd
my_lines	geom	1	SLD advanced test - Line (Railways)	a complex variable style (depending on actual scale)	XmlBLOB-VectorStyle sz=719 (XMLsz=2255) SchemaValidated	1	http://schemas.opengis.net/sld/1.0.0/StyledLayerDescriptor.xsd
my_polygs	geom	0	SLD basic test - Polygon (Local Councils)	a style using "don't care" rendering	XmlBLOB-VectorStyle sz=594 (XMLsz=1029) SchemaValidated	1	http://schemas.opengis.net/sld/1.0.0/StyledLayerDescriptor.xsd
my_polygs	geom	1	SE basic test - bare symbolizer - Polygon (Local Councils)	a style using "don't care" rendering	XmlBLOB-VectorStyle sz=539 (XMLsz=687) SchemaValidated	1	http://schemas.opengis.net/se/1.1.0/FeatureStyle.xsd

The SE_vector_styled_layers table is supported by a corresponding VIEW (not surprisingly) named SE_vector styled_layers_view.
If you are curious enough, you'll quickly discover that this VIEW exactly corresponds to the original table, and that its added values is in that further title, abstract, schema_validated and schema_uri columns are made immediately available.
This simply requires respectively invoking the XB_GetTitle(), XB_GetAbstract(), XB_IsSchemaValidated() and XB_GetSchemaURI() SQL functions on behalf of the XmlBLOB payload (style).

SELECT *
FROM SE_raster_styled_layers_view;

coverage_name	style_id	title	abstract	style	schema_validated	schema_uri
srtm	0	SE basic test	Raster Symbolizer - Digital Elevation Model	XmlBLOB-RasterStyle sz=693 (XMLsz=2120) SchemaValidated	1	http://schemas.opengis.net/se/1.1.0/FeatureStyle.xsd
srtm	1	SLD raster - fantasy raster	a style using "don't care" rendering	XmlBLOB-RasterStyle sz=758 (XMLsz=2549) SchemaValidated	1	http://schemas.opengis.net/sld/1.0.0/StyledLayerDescriptor.xsd

The SE_raster_styled_layers table is supported by a corresponding VIEW named SE_raster styled_layers_view; both them are strictly analogue to the ones intended to support Vector Styled Layers, with only very limited differences.

Styled Groups handling

SELECT RegisterStyledGroup( 'group_1', 'srtm', 0 );
-----
1

SELECT RegisterStyledGroup( 'group_1', 'my_polygs', 'geom', 1 );
-----
1

SELECT RegisterStyledGroup( 'group_1', 'my_lines', 'geom', 1 );
-----
1

SELECT RegisterStyledGroup( 'group_1', 'my_points', 'geom', 0 );
-----
1

The RegisterStyledGroup() SQL function is always expected to be used in order to correctly define any Styled Group; all Group items will automatically get the most appropriate paint_order value accordingly to insertion order within the same Group.

you can pass to this function the group_name, f_table_name, f_geometry_column and vector_style_id arguments in order to register a Vector Styled Layer.
or alternatively you can pass the group_name, coverage_name and raster_style_id arguments in order to register a Raster Styled Layer.
Please note: the first attempt to register an item belonging to some not yet defined Group will implicitly create the Styled Group itself.

SELECT SetStyledGroupInfos( 'group_2', 'some title', 'some abstract' );
-----
1

SELECT RegisterStyledGroup( 'group_2', 'srtm', 1 );
-----
1

SELECT RegisterStyledGroup( 'group_2', 'my_polygs', 'geom', 0 );
-----
1

SELECT RegisterStyledGroup( 'group_2', 'my_points', 'geom', 0 );
-----
1

This second example will simply create yet another Styled Group; anyway in this case we'll explicitly create first a new Group by specifying an appropriate title and an abstract by invoking the SetStyledGroupInfos() SQL function.

SELECT RegisterStyledGroup( 'group_2', 'my_lines', 'geom', 0, 1 );
-----
1

Please note: you can also use the RegisterStyledGroup() SQL function in order to update an already defined item. In this case a further argument corresponding to paint_order has to be specified. (in other worlds: the Group item uniquely identified by group_name and paint_order will be the target for the intended update).

SELECT SetStyledGroupInfos( 'group_1', 'my title', 'my abstract' );
-----
1

SELECT SetStyledGroupInfos( 'group_2', 'new title', 'new abstract' );
-----
1

Please note: you can freely use the SetStyledGroupInfos() SQL function at any time in order to update (or set if not already defined) the title and abstract describing a Styled Group.

SELECT *
FROM SE_styled_groups_view
ORDER BY group_name, paint_order ASC;

group_name	group_title	group_abstract	paint_order	type	layer_name	geometry_column	style_id	style_title	style_abstract	style	geometry_type	coord_dimension	srid
group_1	my title	my abstract	0	raster	srtm	NULL	0	SE basic test	Raster Symbolizer - Digital Elevation Model	XmlBLOB-RasterStyle sz=693 (XMLsz=2120) SchemaValidated	NULL	NULL	NULL
group_1	my title	my abstract	1	vector	my_polygs	geom	1	SE basic test - bare symbolizer - Polygon (Local Councils)	a style using "don't care" rendering	XmlBLOB-VectorStyle sz=539 (XMLsz=687) SchemaValidated	3	2	4326
group_1	my title	my abstract	2	vector	my_lines	geom	1	SLD advanced test - Line (Railways)	a complex variable style (depending on actual scale)	XmlBLOB-VectorStyle sz=719 (XMLsz=2255) SchemaValidated	2	2	4326
group_1	my title	my abstract	3	vector	my_points	geom	0	SE test - Point (Railway Stations)	a complex variable style (depending on actual scale)	XmlBLOB-VectorStyle sz=1042 (XMLsz=5010) SchemaValidated	1	2	4326
group_2	new title	new abstract	0	raster	srtm	NULL	1	SLD raster - fantasy raster	a style using "don't care" rendering	XmlBLOB-RasterStyle sz=758 (XMLsz=2549) SchemaValidated	NULL	NULL	NULL
group_2	new title	new abstract	1	vector	my_polygs	geom	0	SLD basic test - Polygon (Local Councils)	a style using "don't care" rendering	XmlBLOB-VectorStyle sz=594 (XMLsz=1029) SchemaValidated	3	2	4326
group_2	new title	new abstract	2	vector	my_points	geom	0	SE test - Point (Railway Stations)	a complex variable style (depending on actual scale)	XmlBLOB-VectorStyle sz=1042 (XMLsz=5010) SchemaValidated	1	2	4326

The SE_styled_groups and SE_styled_group_refs tables are supported by a corresponding VIEW named SE_styled_groups_view.

Caveat: this View will surely by subject to changes in future versions, accordingly to RasterLite-2 development.
The current implementation simply is kind-of interim placeholder.

back to XmlBlob main page