SpatiaLite: VirtualXPath-advanced

back to XmlBlob main page

Few advanced VirtualXPath examples

Now you are supposed to have acquired at least a basic level of understanding and knowledge about XPath.
It's time to quickly examine few specic cases specifically related to the GeoSpatial scenario.

SLD/SE styles

now you have to import all XML samples from the sld-styles folder; performing a full XML validation isn't strictly necessary.
be sure to import this XML sample into a DB table named styles.
then create and XPath Virtual Table named xpath_styles supporting the above table.

SELECT t.file_name, x.node
FROM xpath_styles AS x
JOIN styles AS t ON (t.pk_uid = x.pkid)
WHERE x.xpath_expr = '//dflt:LineSymbolizer'
GROUP BY t.file_name, x.node;

file_name	node
ferovie_se.xml	dflt:LineSymbolizer
ferrovie_basic.xml	dflt:LineSymbolizer

A query identifying all styles defining a LineSymbolyzer.

SELECT t.file_name, x.parent, x.node, x.value
FROM xpath_styles AS x
JOIN styles AS t ON (t.pk_uid = x.pkid)
WHERE x.xpath_expr = '//dflt:Label/ogc:PropertyName/text()'
GROUP BY t.file_name, x.node, x.value;

file_name	parent	node	value
regioni_basic.xml	dflt:Label	ogc:PropertyName	nome_reg
regioni_se.xml	dflt:Label	ogc:PropertyName	nome_reg
stazioni_basic.xml	dflt:Label	ogc:PropertyName	name
stazioni_se.xml	dflt:Label	ogc:PropertyName	name

... a second query identifying the attribute/column names used to support Labeling.

SELECT t.file_name, x.parent, x.node, x.value
FROM xpath_styles AS x
JOIN styles AS t ON (t.pk_uid = x.pkid)
WHERE x.xpath_expr = '//dflt:Mark/dflt:WellKnownName/text()'
GROUP BY t.file_name, x.parent, x.node, x.value;

file_name	parent	node	value
stazioni_basic.xml	dflt:Mark	dflt:WellKnownName	circle
stazioni_se.xml	dflt:Mark	dflt:WellKnownName	circle
stazioni_se.xml	dflt:Mark	dflt:WellKnownName	star

... yet another query reporting all marker shapes used as a PointSymbolizer.

SELECT value
FROM xpath_styles
WHERE xpath_expr = '//dflt:CssParameter[@name="fill"]/text()'
GROUP BY value;

value
#000000
#0000ff
#8F00FF
#DC143C
#FFFFFF
#d0d0d0
#d0d0ff
#d0ffd0
#ffd0d0
#ffffd0

... a further query enumerating all fill colors.

SELECT t.file_name, x.parent, x.node, x.value
FROM xpath_styles AS x
JOIN styles AS t ON (t.pk_uid = x.pkid)
WHERE x.xpath_expr = '//dflt:CssParameter[@name="stroke-width"]/text()'
GROUP BY t.file_name, x.parent, x.node, x.value;

file_name	parent	node	value
ferovie_se.xml	dflt:Stroke	dflt:CssParameter	0.5
ferovie_se.xml	dflt:Stroke	dflt:CssParameter	1.5
ferovie_se.xml	dflt:Stroke	dflt:CssParameter	8
ferrovie_basic.xml	dflt:Stroke	dflt:CssParameter	1
regioni_basic.xml	dflt:Stroke	dflt:CssParameter	0.25
regioni_se.xml	dflt:Stroke	dflt:CssParameter	0.25
stazioni_se.xml	dflt:Stroke	dflt:CssParameter	1

And finally a last query showing all stroke widths used by LineSymbolizers.

Sensors

now you have to import all XML samples from the sensors folder; performing a full XML validation isn't strictly necessary.
be sure to import this XML sample into a DB table named sensors.
then create and XPath Virtual Table named xpath_sensors supporting the above table.

SELECT t.file_name, x.value
FROM xpath_sensors AS x
JOIN sensors AS t ON (t.pk_uid = x.pkid) 
WHERE xpath_expr = '//dflt:Term[@definition="urn:ogc:def:identifier:OGC:1.0:longName"]/dflt:value/text()';

file_name	value
SensorML_Profile_for_Discovery_Example.xml	Weather station 123 on top of the IfGI building
SensorML_Profile_for_Discovery_Example.xml	OSIRIS Thermometer at weather station 123
sml-existing_keywords.xml	OSIRIS Thermometer at weather station 123
sml-gmet-intervention_on_land.xml	OSIRIS Thermometer at weather station 123
sml-gmet-none.xml	OSIRIS Thermometer at weather station 123
sml-no_keywords-boundedby.xml	OSIRIS Thermometer at weather station 123
sml-no_keywords-description.xml	OSIRIS Thermometer at weather station 123
sml-no_keywords-name.xml	OSIRIS Thermometer at weather station 123
sml-no_keywords-none2.xml	OSIRIS Thermometer at weather station 123

Retrieving the sensors' long names.

SELECT t.file_name, x.value
FROM xpath_sensors AS x
JOIN sensors AS t ON (t.pk_uid = x.pkid) 
WHERE xpath_expr = '//dflt:Term[@definition="urn:ogc:def:classifier:OGC:1.0:sensorType"]/dflt:value/text()';

file_name	value
SensorML_Profile_for_Discovery_Example.xml	weather station
SensorML_Profile_for_Discovery_Example.xml	thermometer
sml-existing_keywords.xml	thermometer
sml-gmet-intervention_on_land.xml	thermometer
sml-gmet-none.xml	thermometer
sml-no_keywords-boundedby.xml	thermometer
sml-no_keywords-description.xml	thermometer
sml-no_keywords-name.xml	thermometer
sml-no_keywords-none2.xml	thermometer

Extracting the sensors' types.

SELECT t.file_name, MakePointZ(x.value*1.0, y.value*1.0, z.value*1.0) AS Geometry
FROM sensors AS t
JOIN xpath_sensors AS x ON (t.pk_uid = x.pkid)
JOIN xpath_sensors AS y ON (t.pk_uid = y.pkid)
JOIN xpath_sensors AS z ON (t.pk_uid = z.pkid)
WHERE t.pk_uid = 1
  AND x.xpath_expr = '//swe:Position//swe:Quantity[@axisID="x"]//swe:value/text()'
  AND y.xpath_expr = '//swe:Position//swe:Quantity[@axisID="y"]//swe:value/text()'
  AND z.xpath_expr = '//swe:Position//swe:Quantity[@axisID="z"]//swe:value/text()';

file_name	Geometry
SensorML_Profile_for_Discovery_Example.xml	BLOB sz=68 GEOMETRY

Creating a Point Geometry corresponding to the sensor's position.

we'll perform three distinct XPath queries; each one will return one of the XYZ coordinates.
the relational JOINs are intended to reassemble all cordinates on the same row, thus allowing MakePointZ() to build a Geometry.
the x.value*1.0 term (and alike) is simply intended to promote a Text value into a genuine Double, as required by MakePointZ().

ISO Metadata / INSPIRE Metadata

now you have to import all XML samples from the metadata folder; performing a full XML validation isn't strictly necessary.
be sure to import this XML sample into a DB table named meta.
then create and XPath Virtual Table named xpath_meta supporting the above table.

SELECT t.file_name, x.value
FROM meta AS t
JOIN xpath_meta AS x ON (t.pk_uid = x.pkid)
WHERE x.xpath_expr = '//gmd:contact/gmd:CI_ResponsibleParty/gmd:organisationName/gco:CharacterString/text()';

file_name	value
AreasOfOutstandingNaturalBeauty.xml	Natural England
IGNF_BDCARTOr_2-5.xml	IGN-F
IGNF_BDORTHOr_2-0.xml	Institut Géographique National (IGN-F)
inspire-data-example.xml	SDI Unit, Institute for Environment & Sustainability, Joint Research Centre
ISO-Coll_ESA-ERS_ORB_PRC_xS-I-en-formatted.xml	ESA/ESRIN
metadata-9.xml	Office International de l'eau

Identifying the responsible party.

SELECT t.file_name, x.value
FROM meta AS t
JOIN xpath_meta AS x ON (t.pk_uid = x.pkid)
WHERE x.xpath_expr = '//gmd:abstract/gco:CharacterString/text()';

file_name	value
AreasOfOutstandingNaturalBeauty.xml	AONBs are designated areas where protection is afforded to protect and manage the areas for visitors and local residents. By using this data you are accepting the Terms of Use for Natural England’s Information and Data as published at: http://www.naturalengland.org.uk/copyright. If you wish to use the data for commercial purposes you should contact Natural England's Enquiry Service, tel: 0845 600 3078, email: enquiries@naturalengland.org.uk.
IGNF_BDCARTOr_2-5.xml	La BD CARTO(r) est une base de données vectorielles décrivant sur l'ensemble du territoire français l'information cartographique aux échelles départementales et régionales.
IGNF_BDORTHOr_2-0.xml	Le produit BD ORTHO® V2 est une collection de mosaïques d'orthophotographies numériques en couleurs ou en Infra Rouge couleurs, rectifiées dans la projection adaptée au territoire couvert.
inspire-data-example.xml	IMAGE2000 product 1 individual orthorectified scenes. IMAGE2000 was produced from ETM+ Landsat 7 satellite data and provides a consistent European coverage of individual orthorectified scenes in national map projection systems. The year 2000 was targeted as reference year, but a deviation of maximum 1-year was allowed to obtain a full coverage of Europe, which involves approximately 450 Landsat TM Frames. Where Landsat 7 data were not available, Landsat 5 data have been used instead. The spatial resolution is 25 metres for multispectral and 12.5 metres for panchromatic imagery.
ISO-Coll_ESA-ERS_ORB_PRC_xS-I-en-formatted.xml	The Precise RAnge and Range-Rate Equipment (PRARE) (more info here ) is a highly accurate microwave ranging system aboard the European Remote Sensing Satellites ERS-1 (more info here and here ) and ERS-2 (more info here and here ). The applications of PRARE are: * precise satellite orbit determination (e.g. of altimeter and geodynamic satellites) * earth gravity fieldrecovery * precise point positioning (tectonics) * earth rotationparameter determination and measurement of the ionospheric totalelectron content (TEC) From this collection you may order products of the following types: - ERS-1/2 Precise Orbit Product (ERS.ORB.PRC)
metadata-9.xml	Le chaînage est réalisé automatiquement sur les cours d'eau codés de la BDCarthage. La méthode a été définie par le Groupe Information Géographique sur l'Eau (GIGE) du SIE. Quand un chaînage à jour existe dans un bassin sur la version de Carthage en cours, celui-ci est réutilisé.

Extracting the abstracts.

SELECT t.file_name, x.value
FROM meta AS t
JOIN xpath_meta AS x ON (t.pk_uid = x.pkid)
WHERE x.xpath_expr = '//gmd:MD_Keywords/gmd:keyword/gco:CharacterString/text()';

file_name	value
AreasOfOutstandingNaturalBeauty.xml	Protected sites
AreasOfOutstandingNaturalBeauty.xml	environment
IGNF_BDCARTOr_2-5.xml	ISO 19139 ; IGN-F
IGNF_BDCARTOr_2-5.xml	BDCARTO
...	...
inspire-data-example.xml	Land cover
ISO-Coll_ESA-ERS_ORB_PRC_xS-I-en-formatted.xml	existing
ISO-Coll_ESA-ERS_ORB_PRC_xS-I-en-formatted.xml	Geology
ISO-Coll_ESA-ERS_ORB_PRC_xS-I-en-formatted.xml	Land cover
ISO-Coll_ESA-ERS_ORB_PRC_xS-I-en-formatted.xml	Spectral/Engineering
ISO-Coll_ESA-ERS_ORB_PRC_xS-I-en-formatted.xml	Platform Characteristics
...	...
ISO-Coll_ESA-ERS_ORB_PRC_xS-I-en-formatted.xml	Satellite Orbits

Identifying the keyworks.

SELECT t.file_name, x.value
FROM meta AS t
JOIN xpath_meta AS x ON (t.pk_uid = x.pkid)
WHERE x.xpath_expr = '//gmd:MD_Constraints/gmd:useLimitation/gco:CharacterString/text()';

file_name	value
AreasOfOutstandingNaturalBeauty.xml	Use limitation
IGNF_BDORTHOr_2-0.xml	Base de donnée soumise aux conditions générales d'utilisation des fichiers IGN
inspire-data-example.xml	no conditions apply
ISO-Coll_ESA-ERS_ORB_PRC_xS-I-en-formatted.xml	http://earth.esa.int/dataproducts/accessingeodata/

Querying legal restrictions or use limitations.

SELECT t.file_name, x.value
FROM meta AS t
JOIN xpath_meta AS x ON (t.pk_uid = x.pkid)
WHERE x.xpath_expr = '//gmd:spatialResolution/gmd:MD_Resolution/gmd:distance/gco:Distance/text()';

file_name	value
AreasOfOutstandingNaturalBeauty.xml	1
IGNF_BDCARTOr_2-5.xml	15.00
IGNF_BDORTHOr_2-0.xml	0.50
inspire-data-example.xml	25.0

Inquirying the spatial resolution.

back to XmlBlob main page