SpatiaLite: GraphicResources

back to XmlBlob main page

External Graphic Resources - Intro

SLD/SE styling could eventually require some external graphics resource: e.g. small images to be used as map symbols, or even as pens or stipples.
Two different kinds of graphics resources are supported:

small bitmaps (i.e. raster images), usually expected to correspond to one of the standard WEB formats (GIF, PNG or JPEG).
Scalable Vector Graphics SVG images.
the main difference between them is that raster images are very problematic to be resized, while vector images can be easily rendered at any arbitrary scale.

<PointSymbolizer>
     <Graphic>
        <ExternalGraphic>
           <OnlineResource xlink:type="simple" xlink:href="http://mywebsite.com/pointsymbol.png" />
           <Format>image/png</Format>
        </ExternalGraphic>
     </Graphic>
</PointSymbolizer>

As the above SLD/SE snippet shows, an external graphics is expected to correspond to some <OnlineResource> available on the WEB: xlink:href points to the corresponding URL, and <Format> specifies the expected mime-type.

The "SE_external_graphics" table

This DB table is specifically intended to implement External Graphics support; you should not manually create this table, always use the standard CreateStylingTables() SQL function so to create any SLD/SE related table.

xlink_href (Primary Key) exactly corresponds to the URL referenced by SLD/SE style documents.
resource (BLOB) does contain the actual graphic resource payload.
title, abstract and file_name are optional columns intended to facilitate a better human readability and to simplify overall handling of all external graphic resources stored within the DB.

The intended scope of the SE_external_graphics table is implementing kind of resource cache internally stored within the DBMS itself; each single resource is uniquely identified by the corresponding xlink:href URL.
Please note: xlink_href isn't actually a genuine URL; it simply is an internal Primary Key, so if a corresponding WEB URL doesn't exist at all this is absolutely not an issue.

Note for client apps developers: the SE_external_graphics table simply acts as an internal cache.
So the optimal access strategy expected to be implemented is as follows:

every time that some <OnlineResource xlink:href="..."> tag is encountered within any SLD/SE style document:
- a first attempt is expected to be performed so to find the required external graphic resource from within the internal cache i.e. an SQL query on behalf of the SE_external_graphics table is expected.
- if no correspondence is found, then a second attempt is expected to be performed by accessing the required WEB URL.

A practical tutorial by real examples

In this example we'll use the samples contained into the xml-samples/graphics 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 RegisterExternalGraphic( 'http://www.myserver.com/aerodrome.png', 
  BlobFromFile( 'C:/xml-samples/graphics/aerodrome.png' ) );
-----
1

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

the RegisterExternalGraphic() SQL function in its simpler form simply requires passing two arguments:
- the first argument corresponds to the pseudo URL, i.e. to xlink:href.
- the second argument is expected to be a BLOB containing the resource's payload.
- in this first example we've just inserted a PNG image; the image payload has been loaded from the external file by invoking the BlobFromFile() Import function.

SELECT RegisterExternalGraphic( 'http://www.myserver.com/aerodrome.tif', 
  BlobFromFile( 'C:/xml-samples/graphics/aerodrome.tif' ),
  'aerodrome', 'raster map symbol: aerodrome (TIFF 56x56)', 'aerodrome.tif' );
-----
0

Please note well: a validating Trigger always checks if the payload to be inserted does actually corresponds to some acceptable mime type.
In this case we've just attempted to insert a TIFF bitmap; but TIFF isn't a valid W3C format, so the Trigger has rejected this operation; and consequently RegisterExternalGraphic() in this case returned 0, i.e. failure.

SELECT RegisterExternalGraphic( 'http://www.myserver.com/aerodrome.png', 
  BlobFromFile( 'C:/xml-samples/graphics/aerodrome.png' ),
  'aerodrome', 'raster map symbol: aerodrome (PNG 56x56)', 'aerodrome.png' );
-----
1

RegisterExternalGraphic() has a more complex form accepting three more arguments, corresponding respectively to a title, an abstract and a file-name.
None of them are strictly indispensable; anyway always specifying a richer set of human readable informations surely helps in order to maintain your repository of external graphic resources in a properly ordered and easily accessible state.

SELECT RegisterExternalGraphic( 'http://www.myserver.com/helicopter.svg', 
  XB_Create( XB_LoadXML( 'C:/xml-samples/graphics/helicopter.svg' ) ) );
-----
1

In this third example we've just inserted an SVG resource; SVG is actually based on XML, so we've used the XB_Create() SQL function in order to create a valid XmlBLOB payload. And then in turn we've invoked XB_LoadXML() in order to properly load the XML Document from the external file.

SELECT RegisterExternalGraphic( 'http://www.myserver.com/bus_stop.svg', 
  XB_Create( XB_LoadXML( 'C:/xml-samples/graphics/bus_stop.svg' ) ),
  'bus stop', 'vector map symbol: bus stop (SVG)', 'bus_stop.svg' );
-----
1

Same as above, this time using the full qualified form of RegisterExternalGraphic().

SELECT RegisterExternalGraphic( 'http://www.myserver.com/bus_stop.svg', 
  XB_Create( XB_LoadXML( 'http://www.sjjb.co.uk/mapicons/svg/transport/bus_stop2.svg' ) ),
  'bus stop', 'vector map symbol: bus stop (SVG, revised)', 'bus_stop.svg' );

Please pay attention: this final example shows two interesting facets:

you can use RegisterExternalGraphic() so to update an already existing resource definition: if the xlink_href value is already defined all previously defined values will be replaced by the newer ones.
you can use XB_LoadXML() so to directly access any WEB URL.

SELECT *
FROM SE_external_graphics_view;

xlink_href	title	abstract	resource	file_name	mime_type
http://www.myserver.com/bed_and_breakfast.png	* undefined *	* undefined *	BLOB sz=2545 PNG image	* undefined *	image/png
http://www.myserver.com/aerodrome.png	aerodrome raster map	symbol: aerodrome (PNG 56x56)	BLOB sz=3725 PNG image	aerodrome.png	image/png
http://www.myserver.com/helicopter.svg	* undefined *	* undefined *	XmlBLOB-SVG sz=2825 (XMLsz=7851)	* undefined *	image/svg+xml
http://www.myserver.com/bus_stop.svg	bus stop	vector map symbol: bus stop (SVG, revised)	XmlBLOB-SVG sz=2259 (XMLsz=6121)	bus_stop.svg	image/svg+xml

The SE_external_graphics table is supported by a corresponding VIEW (not surprisingly) named SE_external_graphics_view.
If you are curious enough, you'll quickly discover that this VIEW exactly corresponds to the original table, and that its (really modest) added values is in that a further mime_type column is made immediately available. This simply requires invoking the GetMimeType() SQL function on behalf of the resource's own payload.

spatialite_gui specific support

You'll probably remember that spatialite_gui already supported a nice BlobExplorer GUI-tool allowing to pre-view any image stored as a BLOB into the DB.
The latest spatialite_gui 1.6.1 is now able to fully support SVG images as well.

useful related resources

The SJJB project (strictly related to Open Street Map) offers a really nice and very rich collection of SVG/PNG map symbols, released under the CC-0 license terms (i.e. released on the Public Domain, absolutely no imposed constrains of any kind).
Surely a highly valuable resource for all peoples interested to maps; strongly recommended.

back to XmlBlob main page