D 2013-03-18T12:08:36.764 L ISO\sMetadata U sandro W 17091 back to XmlBlob main page

ISO Metadata - Intro

Starting since version 4.1.0 SpatiaLite will support ISO Metadata; all metadata documents exactly are XML Documents, so the whole ISO Metadata implementation simply is a further specialized extension based on XmlBLOB.
Several different standard specifications are collectively grouped under the generic name of ISO Metadata; the common core reference is always based on ISO 19115 / 19139, but many further specialized extensions exist: one of the best known being e.g. the INSPIRE EU Initiative.

DB Tables implementing ISO Metadata

SELECT CreateIsoMetadataTables(); The CreateIsoMetadataTables() SQL function will attempt to create all DB Tables required in order to enable ISO Metadata support.
Any attempt to directly create or otherwise manipulate these Tables is strongly discouraged; you are always expected to properly invoke CreateIsoMetadataTables(), which will correctly define all required Primary / Foreign Key constraints, as will create all validating Triggers expected to support these Tables. SELECT CreateIsoMetadataTables( 1 ); Please note: CreateIsoMetadataTables() 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 Metadata 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 Metadata 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 ISO Metadata 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 ISO_metadata table

This table is specifically intended to store all ISO Metadata documents:

id simply is simply intended as an internal ID (Primary Key): no special meaning is assumed other than ensuring uniqueness within the DB table.
md_scope specifies the intended scope of each Metadata document; acceptable values are:
'undefined','fieldSession', 'collectionSession', 'series', 'dataset', 'featureType', 'feature', 'attributeType', 'attribute', 'tile', 'model', 'catalogue', 'schema', 'taxonomy', 'software', 'service', 'collectionHardware', 'nonGeographicDataset', 'dimensionGroup'
metadata is the corresponding Metadata document (expected to be a valid XmlBLOB of the ISO Metadata type).
fileId and parentId respectively correspond to the values of the <gmd:fileIdentifier> and <gmd:parentIdentifier> tags internally declared by the XML document itself.
geometry is a MULTIPOLYGON (bounding box) and corresponds to the <gmd:EX_GeographicBoundingBox> tag(s) internally declared by the XML document itself.
Please note: ISO Metadata coordinates are always expected to be expressed as longitudes and latitudes, so SRID=4326 WGS84 is always assumed.

the ISO_metadata_reference table

This table is specifically intended to represent any possible reference joining ISO Metadata documents and geographic features stored within the same DB (layers, coverages, features, tiles and so on):

reference_scope specifies the intended scope of each reference; acceptable values are:
'table','column', 'row', 'row/col'
table_name is the name of some DB table (layer or coverage).
column_name is the name of some column belonging to the same table (e.g. geometry).
row_id_value could eventually reference a single row in the same table.
timestamp is intended to specify the data-time when the latest change to the reference occurred.

both md_file_id and md_parent_id (Foreign Key) are intended to reference the corresponding ISO Metadata documents stored within the ISO_metadata table.
Please note well: such references are fully based on internal numeric IDs, not an the corresponding XML tag values.

A practical tutorial by real examples
In this example we'll use the samples contained into the xml-samples/iso-metadata 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 CreateIsoMetadataTables(); ----- 1 And we'll suppose that the ISO Metadata supporting tables have been created in strict mode, i.e. always requiring a successful XML Schema validation. SELECT RegisterIsoMetadata( 'dataset', XB_Create( XB_LoadXML( 'C:/xml-samples/iso-metadata/inspire-data-example.xml' ) ) ); ----- 0 The RegisterIsoMetadata() SQL function inserts a registration into the ISO_metadata table. Any attempt aimed to directly perform any INSERT or UPDATE SQL statement on behalf of ISO_metadata is strongly discouraged; always using the abstract function RegisterIsoMetadata() is warmly recommended, and will preserve long-term compatibility against subsequent releases.

the RegisterIsoMetadata() SQL function in its simpler form simply requires passing two arguments:

the first argument correspond to md_scope.

the second argument is expected to be an appropriate XmlBLOB containing the Metadata 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 RegisterIsoMetadata( 'dataset', XB_Create( XB_LoadXML( 'C:/xml-samples/iso-metadata/inspire-data-example.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 RegisterIsoMetadata( 'dataset', XB_Create( XB_LoadXML( 'C:/xml-samples/iso-metadata/inspire-data-example.xml' ), 1, 1 ) ); ----- 0 Anyway a second attempt to insert yet another time the same Metadata document will be rejected; this is because a uniqueness constraint forbids to insert two documents declaring the same fileId value. SELECT RegisterIsoMetadata( 'dataset', XB_Create( XB_LoadXML( 'C:/xml-samples/iso-metadata/db_topografico_iso19139.xml' ), 1, 1 ) ); ----- 1 SELECT RegisterIsoMetadata( 'dataset', XB_Create( XB_LoadXML( 'C:/xml-samples/iso-metadata/unita_volumetrica_iso19139.xml' ), 1, 1 ), 2 ); ----- 1 SELECT RegisterIsoMetadata( 'dataset', XB_Create( XB_LoadXML( 'C:/xml-samples/iso-metadata/unita_insediativa_iso19139.xml' ), 1, 1 ), 'f0780c41-023d-41be-9ed4-f79bdb4a7886' ); ----- 1 Please note: the RegisterIsoMetadata() function can be also invoked in order to update an already defined Metadata document; in this case you simply have to pass the corresponding id as the third argument.
Alternatively, if the Metadata document to be replaced/updated declares an internal <fileIdentifier> tag value, you can pass the fileID value as the third argument.
In any case both fileId and parentId will be correctly updated so to exactly correspond to the internal tags declared within the XML documents itself; you can safely rely on this, because a couple of Triggers will always take care to ensure a strict correspondence.

SELECT * FROM ISO_metadata_view;

id md_scope title abstract geometry fileIdentifier parentIdentifier metadata schema_validated metadata_schema_uri

0 undefined NULL NULL NULL NULL NULL BLOB sz=4 UNKNOWN type -1 NULL

1 dataset Image2000 Product 1 (nl2) Multispectral IMAGE2000 product 1 individual orthorectified scenes ... BLOB sz=141 GEOMETRY 029097fd-2ef2-487c-a5ca-6ec7a3dbac53 NULL XmlBLOB-ISOmetadata sz=4423 (XMLsz=18026) SchemaValidated 1 http://schemas.opengis.net/iso/19139/20060504/gmd/gmd.xsd

2 dataset Unita' Insediativa Aree adibite all'installazione ... BLOB sz=141 GEOMETRY cd76c9a2-8138-4e7e-ad6b-2e105c37e65c cc27d885-2795-4998-9bae-6f6872b181ce XmlBLOB-ISOmetadata sz=7355 (XMLsz=58098) SchemaValidated 1 http://www.isotc211.org/2005/gmd/gmd.xsd

The ISO_metadata table is supported by a corresponding VIEW (not surprisingly) named ISO_metadata_view.
Please note: a default row identified by id=0 is always defined in the ISO_metadata table (being automatically created by CreateIsoMetadata()); its intended scope is the one to define kind-of universal-placeholder allowing to define undefined/unknown metadata references still preserving full relational integrity.

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, geometry(*), fileId(*), parentId(*), schema_validated and metadata_schema_uri columns are made immediately available.
(*)Please note: the geometry, fileId and parentId columns are always directly exposed in the base table; all them are immediately set by appropriate Triggers, so to always correspond to the actual tag-values internally declared by the XML document itself.
The following SQL functions, once invoked on behalf of the XmlBLOB payload (metadata), will immediately extract the corresponding values from within the XML document: XB_GetTitle(), XB_GetAbstract(), XB_GetGeometry(), XB_GetFileId(), XB_GetParentId(), XB_IsSchemaValidated() and XB_GetSchemaURI().

advanced SQL functions supporting ISO Metadata
SELECT GetIsoMetadataId( 'cd76c9a2-8138-4e7e-ad6b-2e105c37e65c' ); ----- 2 The GetIsoMetadataId() SQL function will return the internal id value corresponding to some fileId; if no corresponding Metadata document exists, then ZERO will be returned. UPDATE ISO_metadata SET metadata = XB_SetParentId ( metadata , 'new-parent-id' ) WHERE id = 2; ----- 1 UPDATE ISO_metadata SET metadata = XB_SetParentId ( metadata , 'another-parent-id' ) WHERE id = GetIsoMetadataId( 'cd76c9a2-8138-4e7e-ad6b-2e105c37e65c' ); ----- 1 The XB_SetFileId() and XB_SetParentId() SQL functions are respectively intended to change the <fileIdentifier> or <parentIdentifier> tag values internally declared within the XML document itself; the supporting Triggers will then take care to immediately make visible this change in the corresponding table/column values.
Please note:

the new XmlBLOB object (the one containing the modified identifier) returned by these functions will support compression and schema validation exactly as defined by the input XmlBLOB.

these functions simply allow to change/modify an already defined XML tag value.

UPDATE ISO_metadata SET metadata = XB_AddParentId ( metadata , CreateUUID() , 'gmd' , 'http://www.isotc211.org/2005/gmd' , 'gco' , 'http://www.isotc211.org/2005/gco' ) WHERE id = 1; ----- 1 The XB_AddFileId() and XB_AddParentId() SQL functions are respectively intended to insert a <fileIdentifier> <parentIdentifier> tag values internally declared within the XML document itself; the supporting Triggers will then take care to immediately make visible this change in the corresponding table/column values.
Please note:

the new XmlBLOB object (the one containing the modified identifier) returned by these functions will support compression and schema validation exactly as defined by the input XmlBLOB.

these functions simply allow to insert a new (not already defined) XML tag value.

as you can easily notice, the XB_Add??Id() functions require more arguments than the corresponding XB_Set??Id() functions:

third and fourth arguments are expected to specify the namespace to be adopted for the <fileIdentifier> or <parentIdentifier> XML tags.

fifth and sixth arguments play exactly the same identical role, this time defining the namespace to be adopted for the <CharacterString> XML tag.

all these arguments could be set to NULL when appropriate.

Remember:

an XML namespace is always identified by an URL and may eventually be associated to a prefix.
So, assuming that a gco namespace is already defined in the target XML document (... xmlns:gco="http://www.isotc211.org/2005/gco" ...), you can simply pass 'gco', NULL (or NULL, 'http://www.isotc211.org/2005/gco') as the fifth and sixth arguments, and in both cases a <gco:CharacterString> XML tag will be created, because you can reference an already defined namespace by prefix or by URL indifferently.

if a so called default namespace is already defined in the target XML document (... xmlns="http://www.isotc211.org/2005/gmd" ...), you can simply pass 'gmd', NULL (or NULL, 'http://www.isotc211.org/2005/gmd', or else NULL, NULL) as the fourth and fifth arguments, and in any case a <fileIdentifier> XML tag (no prefix) will be created.

anyway: always passing a full qualified namespace (explicitly specifying both prefix and URL) is surely better and avoid many possible pitfalls; in this case an appropriate xmlns declaration will be automatically inserted into the XML document.

back to XmlBlob main page Z e04beeaa038af8a84fa30573d60db5ad

id	md_scope	title	abstract	geometry	fileIdentifier	parentIdentifier	metadata	schema_validated	metadata_schema_uri
0	undefined	NULL	NULL	NULL	NULL	NULL	BLOB sz=4 UNKNOWN type	-1	NULL
1	dataset	Image2000 Product 1 (nl2) Multispectral	IMAGE2000 product 1 individual orthorectified scenes ...	BLOB sz=141 GEOMETRY	029097fd-2ef2-487c-a5ca-6ec7a3dbac53	NULL	XmlBLOB-ISOmetadata sz=4423 (XMLsz=18026) SchemaValidated	1	http://schemas.opengis.net/iso/19139/20060504/gmd/gmd.xsd
2	dataset	Unita' Insediativa	Aree adibite all'installazione ...	BLOB sz=141 GEOMETRY	cd76c9a2-8138-4e7e-ad6b-2e105c37e65c	cc27d885-2795-4998-9bae-6f6872b181ce	XmlBLOB-ISOmetadata sz=7355 (XMLsz=58098) SchemaValidated	1	http://www.isotc211.org/2005/gmd/gmd.xsd