Translating WFS Query to
SQL/XML Query
Vânia Vidal
Fernando Lemos
Fábio Feitosa
Departamento de Computação
Universidade Federal do Ceará
Outline






WFS Specification
Contributions
Feature Type Specification
Extension of Feature Type
Translating WFS Query to SQL/XML Query
Conclusions
WFS Specification

OpenGis Consortium
 promotes
the development and use of
advanced open system standards and techniques
in the geoprocessing area and
related information technologies

WFS
 publishes
GML views of geographic features
stored in data sources
 users can query and update data stores
through a feature type schema
WFS Specification

WFS request
 GetCapabilities
 DescribeFeatureType
 GetFeature
 Transaction
 LockFeature
Contributions

Correspondence Assertions
 specify
the mappings between
feature type schemas and relational schemas

TranslateWFSQuery Algorithm
 translates
a WFS query over a feature type schema
into a SQL/XML query over the relational schema
 translation is based on the correspondence assertions
WFS GetFeature Request
Deegree WFS
SQL Query Q1
…
WFS Query
GML
Deegree
WFS Server
SQL Query Qn
result of Q1
BDR
…
result of Qn
Our approach
WFS Query
SQL/XML Query
GML Publisher
WFS Server
GML
BDR
GML
Feature Type Specification

Feature Type F over a relational schema S
F = <T, R, A>
where
T is the XML Schema type for feature instances
R is the name of the master relation schema,
which contains the geometric attribute
A is the set of correspondence assertions,
which fully specifies T in terms of R
Example
XML Schema type for
Relational Schema DB_Station
Feature Type F_Station
TStation
code
geometry
name
address (TAddress)
street
city (TCity)
name
area
zipcode
Pluviometry* (TPluviometry)
month
value
agency
Master Relation Schema
Station_rel
CODE
GEOM_POINT
NAME
STREET
ZIPCODE
CODECITY (FK)
CODEAGENCY (FK)
Pluviometry_rel
CODESTATION (FK)
MONTH
VALUE
City_rel
CODECITY
NAME
AREA
Agency_rel
CODEAGENCY
NAME
PHONE
FAX
Correspondence Assertions of F_Station
TStation
code (integer)
Master Relation Schema
1:[TStation/code][Station_rel/CODE]
geometry (abstractgeometry) 2:[TStation/geometry][Station_rel/GEOM_POINT]
name (string)
3:[TStation/name][Station_rel/NAME]
address (TAddress)
4:[TStation/address][Station_rel/NULL]
street (string)
city (TCity)
5:[TAddress/street][Station_rel/STREET]
6:[TAddress/city][Station_rel/FK1]
name (string)
7:[TCity/name][City_rel/NAME]
area (number)
8:[TCity/area][City_rel/ARE
A]
9:[TAddress/zipcode][Station_rel/ZIPCODE]
zipcode (string)
pluviometry (TPluviometry)
10:[TStation/pluviometry][Station_rel/FK2-1]
month (integer)
11:[TPluviometry/month][Pluviometry_rel/MONTH]
value (number)
12:[TPluviometry/value][Pluviometry_rel/VALUE]
agency (string)
13:[TStation/agency][Station_rel/FK3/NAME]
Station_rel
CODE (number)
GEOM_POINT (sdo_geometry)
NAME (varchar2)
OBSERVER (varchar2)
STREET (varchar2)
ZIPCODE (varchar2)
PHONE (varchar2)
CODECITY (number)
CODEAGENCY (number)
FK1 (City_rel)
CODECITY (number)
NAME (varchar2)
AREA (number)
FK2-1 (Pluviometry_rel)
CODESTATION (number)
MONTH (number)
VALUE (number)
FK3 (Agency_rel)
CODEAGENCY (number)
NAME (varchar2)
PHONE (varchar2)
FAX (varchar2)
Extension of a Feature Type

Extension of F = <T, R, A> for an instance s of schema S


a sequence of F-elements of type T,
such that each F-element matches a tuple of s(R)
F = { $f | $f is an instance of T and rs(R) such that $fAr}
SQL/XML query definition of an extension of F
F = SELECT
XMLELEMENT( "Extension_of_F ",
XMLAGG( XMLELEMENT( "F", [RT](r) ))
FROM R r
[RT](r) constructs the content for an instance $f of T
such that $f A r as specified by the CAs of F
SQL/XML Definition for Extension of F_Station
SELECT XMLELEMENT( "Extension_of_F_Station", XMLAGG(
XMLELEMENT( "F_Station",
[TStation / code]  [Station_rel / CODE]
XMLFOREST(S.CODE AS "code"),
[TStation / geometry]  [Station_rel / GEOM_POINT]
XMLFOREST(
SDO_UTIL.TO_GMLGEOMETRY(S.GEOM_POINT) AS "geometry"),
[TStation / name]  [Station_rel / NAME]
XMLFOREST(S.NAME AS "name"),
XMLELEMENT( "address",
[TStation / address]  [Station_rel / NULL]
XMLFOREST(S.STREET AS "street"),
[TAddress / street]  [Station_rel / STREET]
XMLELEMENT( "city",
[TAddress / city]  [Station_rel / FK1]
(SELECT XMLCONCAT(
[TCity / name]  [City_rel / NAME]
XMLFOREST(C.NAME AS "name"),
XMLFOREST(C.AREA AS "area"))
[TCity / area]  [City_rel / AREA]
FROM City_rel C WHERE C.CODECITY = S.CODECITY) ),
XMLFOREST(S.ZIPCODE AS "zipcode") ),
[TAddress / zipcode]  [Station_rel / ZIPCODE]
(SELECT XMLAGG(XMLELEMENT( "pluviometry",
[TStation / pluviometry]  [Station_rel / FK2-1]
XMLFOREST(PL.MONTH AS "month"),
[TPluviometry / month]  [Pluviometry_rel / MONTH]
XMLFOREST(PL.VALUE AS "value") ) )
[TPluviometry / area]  [Pluviometry_rel / AREA]
FROM Pluviometry_rel PL WHERE S.CODE = PL.CODESTATION),
[TStation / agency]  [Station_rel / FK3 / NAME]
XMLFOREST( (SELECT A.NAME FROM Agency_rel A
WHERE A.CODEAGENCY = S.CODEAGENCY) AS "agency")
)))
FROM Station_rel S;
An Instance of DB_Station
Station_rel
CODE
NAME
STREET
ZIPCODE
GEOM_POINT
CODE CODECITY
AGENCY
164
Serragem R. Prinicpal s/n
62755000
-4.45,-38.5
1
2309458
165
Arisco
Sitio Penha
62755000
-4.65,-38.55
2
2309458
481
Arruda
R. São Francisco,606 62113000
-3.85,-40.66
1
2312908
Pluviometry_rel
Agency_rel
CODEAGENCY
NAME
PHONE
FAX
CODESTATION MONTH
VALUE
1
FUNCEME 31011088
31011093
164
01
87.8
2
SUDENE
34339045
164
02
171.6
165
01
50.4
481
03
150
34339031
City_rel
CODECITY
NAME
AREA
2309458
OCARA
1450
2312908
SOBRAL
19820
Extension of F_Station
Station_rel
CODE
NAME
STREET
ZIPCODE
GEOM_POINT
CODE CODECITY
AGENCY
164
Serragem R. Prinicpal s/n
62755000 -4.45,-38.5
1
2309458
165
Arisco
Sitio Penha
62755000 -4.65,-38.55
2
2309458
481
Arruda
R. São Francisco,606 62113000 -3.85,-40.66
1
2312908
<Extension_of_F_Station>
<F_Station>
<F_Station>
<F_Station>
<code>165</code>
<code>481</code>
<code>164</code>
<gml:Point>
<gml:Point>
<geometry>
<gml:coordinates cs=","
<gml:coordinates cs=","
<gml:Point>
decimal="." ts="">
decimal="." ts="">
<gml:coordinates cs=","
-4.65,-38.55
-3.85,-40.66
decimal="." ts="">
</gml:coordinates>
</gml:coordinates>
-4.45,-38.5
</gml:Point>
</gml:Point>
</gml:coordinates>
<name>Arisco</name>
<name>Arruda</name>
</gml:Point>
<address>
<address>
</geometry>
<street>Sitio Penha</street>
<street>R. Sao Francisco, 606</street>
<name>Serragem</name>
<city>…</city>
<city>…</city>
<address>
<zipcode>62755000</zipcode>
<zipcode>62113000</zipcode>
<street>R. Principal s/n</street> </address>
</address>
<city>…</city>
<pluviometry>…</pluviometry>
<pluviometry>…</pluviometry>
<zipcode>62755000</zipcode> <agency>…</agency>
<agency>…</agency>
</address>
</F_Station>
</F_Station>
<pluviometry>…</pluviometry>
</Extension_of_F_Station>
<pluviometry>…</pluviometry>
<agency>…</agency>
</F_Station>
Extension of F_Station
Agency_rel
CODEAGENCY
NAME
PHONE
FAX
1
FUNCEME 31011088
31011093
2
SUDENE
34339045
34339031
<Extension_of_F_Station>
<F_Station>
<F_Station>
<F_Station>
<code>165</code>
<code>481</code>
<code>164</code>
<geometry>…</geometry>
<geometry>…</geometry>
<geometry>…</geometry>
<name>Arisco</name>
<name>Arruda</name>
<name>Serragem</name>
<address>…</address>
<address>…</address>
<address>…</address>
<pluviometry>
<pluviometry>
<pluviometry>
<month>1</month>
<month>3</month>
<month>1</month>
<value>50.4</value>
<value>150</value>
<value>87.8</value>
</pluviometry>
</pluviometry>
</pluviometry>
<agency>SUDENE</agency> <agency>FUNCEME</agency>
<pluviometry>
</F_Station>
</F_Station>
<month>2</month>
</Extension_of_F_Station>
<value>171.6</value>
</pluviometry>
<agency>FUNCEME</agency>
</F_Station>
Extension of F_Station
Pluviometry_rel
CODESTATION MONTH
VALUE
164
01
87.8
164
02
171.6
165
01
50.4
481
03
150
<Extension_of_F_Station>
<F_Station>
<F_Station>
<F_Station>
<code>165</code>
<code>481</code>
<code>164</code>
<geometry>…</geometry>
<geometry>…</geometry>
<geometry>…</geometry>
<name>Arisco</name>
<name>Arruda</name>
<name>Serragem</name>
<address>…</address>
<address>…</address>
<address>…</address>
<pluviometry>
<pluviometry>
<pluviometry>
<month>1</month>
<month>3</month>
<month>1</month>
<value>50.4</value>
<value>150</value>
<value>87.8</value>
</pluviometry>
</pluviometry>
</pluviometry>
<agency>SUDENE</agency> <agency>FUNCEME</agency>
<pluviometry>
</F_Station>
</F_Station>
<month>2</month>
</Extension_of_F_Station>
<value>171.6</value>
</pluviometry>
<agency>FUNCEME</agency>
</F_Station>
Extension of F_Station
City_rel
CODECITY
NAME
AREA
2309458
OCARA
1450
2312908
SOBRAL
19820
<Extension_of_F_Station>
<F_Station>
<code>164</code>
<geometry>…</geometry>
<name>Serragem</name>
<address>
<street>…</street>
<city>
<name>OCARA</name>
<area>1450</area>
</city>
<zipcode>62755000</zipcode>
</address>
<pluviometry>…</pluviometry>
<pluviometry>…</pluviometry>
<agency>…</agency>
</F_Station>
<F_Station>
<F_Station>
<code>165</code>
<code>481</code>
<geometry>…</geometry>
<geometry>…</geometry>
<name>Arisco</name>
<name>Arruda</name>
<address>
<address>
<street>…</street>
<street>…</street>
<city>
<city>
<name>OCARA</name>
<name>SOBRAL</name>
<area>1450</area>
<area>19820</area>
</city>
</city>
<zipcode>62755000</zipcode>
<zipcode>62113000</zipcode>
</address>
</address>
<pluviometry>…</pluviometry>
<pluviometry>…</pluviometry>
<agency>…</agency>
<agency>…</agency>
</F_Station>
</F_Station>
</Extension_of_F_Station>
WFS Query

<Query> element of a GetFeature request:


delivers feature instances of a given feature type,
where each feature instance matches a tuple of the Master Table
<Query> element contains:

a mandatory attribute typeName


a sequence of zero or more <wfs:PropertyName> elements



used to indicate the name of the feature type to be queried
used to specify what properties to retrieve
the value of each <wfs:PropertyName> element is an XPath expression that
references a property or sub-property of the relevant feature type
an optional <Filter> element


used to define spatial and non-spatial constraints on a query
encoded as described in the OGC Filter Encoding Implementation Spec
QW : WFS Query over F_Station
<wfs:Query typeName="F_Station">
<wfs:PropertyName>name</wfs:PropertyName>
<wfs:PropertyName>address / city</wfs:PropertyName>
<wfs:PropertyName>pluviometry</wfs:PropertyName>
<wfs:PropertyName>geometry</wfs:PropertyName>
<ogc:Filter>
<ogc:And> <ogc:PropertyIsEqualTo>
<ogc:PropertyName>agency</ogc:PropertyName>
<ogc:Literal>FUNCEME</ogc:Literal>
</ogc:PropertyIsEqualTo>
<ogc:BBox>
<ogc:PropertyName>geometry</ogc:PropertyName>
<gml:Envelope>
<gml:lowercorner>-5.2 -42.5</gml:lowercorner>
<gml:upperCorner>-2.5 -38.7</gml:upperCorner>
</gml:Envelope> </ogc:BBox> </ogc:And>
</ogc:Filter>
</wfs:Query>
QX : Canonical XQuery for QW
FOR $f in document("F")/Station
WHERE $f satisfies the filter of QW
RETURN <gml:featureMember>{
<F_Station> {
$f/name,
$f/address/city,
$f/pluviometry,
$f/geometry
}</F_Station>
}<gml:featureMember>
The result of Qw is the result of evaluating QX on
an extension of F_Station
XML fragment resulting from QW
QW : WFS Query
<wfs:Query typeName="F_Station">
<wfs:PropertyName>name</wfs:PropertyName>
<wfs:PropertyName>address / city</wfs:PropertyName>
<wfs:PropertyName>pluviometry</wfs:PropertyName>
<wfs:PropertyName>geometry</wfs:PropertyName>
<ogc:Filter>
<ogc:And> <ogc:PropertyIsEqualTo>
<ogc:PropertyName>agency</ogc:PropertyName>
<ogc:Literal>FUNCEME</ogc:Literal>
</ogc:PropertyIsEqualTo>
<ogc:BBox>
<ogc:PropertyName>geometry</ogc:PropertyName>
<gml:Envelope>
<gml:lowercorner>-5.2 -42.5</gml:lowercorner>
<gml:upperCorner>-2.5 -38.7</gml:upperCorner>
</gml:Envelope> </ogc:BBox> </ogc:And>
</ogc:Filter>
</wfs:Query>
<gml:FeatureMember>
<F_Station>
<name>Serragem</name>
<city> <name>OCARA</name>
<area>1450</area> </city>
<pluviometry> <month>1</month>
<value>87.8</value> </pluviometry>
<pluviometry> <month>2</month>
<value>171.6</value> </pluviometry>
<geometry>
<gml:Point>
<gml:coordinates cs="," decimal="." ts="">
-4.45,-38.5</gml:coordinates> </gml:Point>
</geometry>
</F_Station>
</gml:FeatureMember>
<gml:FeatureMember>
<F_Station>
<name>Arruda</name>
<city> <name>SOBRAL</name>
<area>19820</area> </city>
<pluviometry> <month>3</month>
<value>150</value> </pluviometry>
<geometry>
<gml:Point>
<gml:coordinates cs="," decimal="." ts="">
-3.85,-40.66</gml:coordinates> </gml:Point>
</geometry>
</F_Station>
</gml:FeatureMember>
WFS Query
Definition:
Let
QW be a WFS Query over feature type F
Qx be the canonical XQuery for QW
QS be a SQL/XML query over S which returns a set of
<gml:featureMember> elements.
Then, we say that QS is a correct translation for QW iff,
for any instance S of S,
if F is the extension of F on S
S1 is the set of <gml:featureMember> elements
resulting from evaluating QS on S and
S2 is the set of <gml:featureMember> elements
resulting from evaluating QX on F
then S1 = S2
TranslateWFSQuery Algorithm
Input: WFS query QW
Output: SQL/XML query QS
Let <P,L > := TranslateFilter( f ), where f is the filter of QW
In case of
Case 1: QW has no <wfs:PropertyName> elements
Qs = SELECT
XMLELEMENT( "gml:featureMember",
XMLELEMENT( "F",
[RT][r]
))
FROM R r, L
WHERE P
TranslateWFSQuery Algorithm
Case 2: QW has n <wfs:PropertyName> elements
Let Pathi be the value of i-th <wfs:PropertyName> element of QW.
Let Qi [r] := TranslatePath(Pathi)
Qs = SELECT
XMLELEMENT( " gml:featureMember ",
XMLELEMENT( " F ",
Q1 [r]
…
Qn [r]
))
FROM R r, L
WHERE P
TranslateWFSQuery Algorithm

TranslateFilter( f )
returns a tuple <P,L>
where
P is an SQL conditional expression
L is a list of relations names required to process P
such that,
for any instance $t of T if $t A r, where r is a tuple of R,
then $t satisfies f iff r satisfies P
TranslatePath(F) Algorithm
Input: a path F = p1 /…/ pn of type T
Output: SQL/XML subquery Q[r] that computes the value of path F
Theorem:
For any instance $t of Extension of F, where $t Ar,
then Q[r] returns a set S of pn-elements where S = $t/ p1 /…/ pn
Translation for QW
QW : WFS Query
<wfs:Query typeName="F_Station">
<wfs:PropertyName>name</wfs:PropertyName>
<wfs:PropertyName>address / city</wfs:PropertyName>
<wfs:PropertyName>pluviometry</wfs:PropertyName>
<wfs:PropertyName>geometry</wfs:PropertyName>
<ogc:Filter>
<ogc:And> <ogc:PropertyIsEqualTo>
<ogc:PropertyName>agency</ogc:PropertyName>
<ogc:Literal>FUNCEME</ogc:Literal>
</ogc:PropertyIsEqualTo>
<ogc:BBox>
<ogc:PropertyName>geometry</ogc:PropertyName>
<gml:Envelope>
<gml:lowercorner>-5.2 -42.5</gml:lowercorner>
<gml:upperCorner>-2.5 -38.7</gml:upperCorner>
</gml:Envelope> </ogc:BBox> </ogc:And>
</ogc:Filter>
</wfs:Query>
QX : Canonical XQuery for QW
FOR $f in document("F")/Station
WHERE $f satisfies the filter of QW
RETURN <gml:featureMember>{
<F_Station> {
$f / name,
$f / address/city,
$f / pluviometry,
$f / geometry
}</F_Station>
}<gml:featureMember>
Translation for QW
SQL/XML Query
SELECT XMLELEMENT("gml:FeatureMember",
XMLELEMENT("Station",
Q1[S], .........…..…………………..TranslatePath( name )
Q2[S],……........................TranslatePath( address / city)
Q3[S],…………….…………TranslatePath( pluviometry )
Q4[S]…………………………..TranslatePath( geometry )
))
FROM Station_rel S, L
WHERE P
Translation for QW
SQL/XML Query
SELECT XMLELEMENT("gml:FeatureMember",
XMLELEMENT("Station")
XMLFOREST(S.NAME AS "name"),
Q2[S],
Q3[S],
Q4[S]
TranslatePath( name )
)
FROM Station_rel S, L
WHERE P
[TStation / name]  [Station_rel / NAME]
Translation for QW
SQL/XML Query
SELECT XMLELEMENT("gml:FeatureMember",
XMLELEMENT("Station")
XMLFOREST(S.NAME AS "name"),
XMLELEMENT( "city",
(SELECT XMLCONCAT(
XMLFOREST(C.NAME AS "name"),
XMLFOREST(C.AREA AS "area") )
FROM City_rel C
WHERE C.CODECITY = S.CODECITY)),
Q3[S],
Q4[S]
TranslatePath( address / city )
)
FROM Station_rel S, L
WHERE P
[TStation / address]  [Station_rel / NULL]
+ [TAddress / city]  [Station_rel / FK1]
+ [TCity / name]  [City_rel / NAME]
+ [TCity / area]  [City_rel / AREA]
Translation for QW
SQL/XML Query
SELECT XMLELEMENT("gml:FeatureMember",
XMLELEMENT("Station")
XMLFOREST(S.NAME AS "name"),
[TStation / pluviometry]  [Station_rel / FK2-1]
XMLELEMENT( "city",
+ [TPluviomety / month]  [Pluviometry_rel / MONTH]
(SELECT XMLCONCAT(
+ [TPluviomety / value]  [Pluviometry_rel / VALUE]
XMLFOREST(C.NAME AS "name"),
XMLFOREST(C.AREA AS "area") )
FROM City_rel C
WHERE C.CODECITY = S.CODECITY)),
(SELECT XMLAGG(XMLELEMENT("pluviometry",
XMLFOREST(PL.MONTH AS "month"),
XMLFOREST(PL.VALUE AS "value") ) )
FROM Pluviometry_rel PL
WHERE S.CODE = PL.CODESTATION),
Q[ S ]
)
FROM Station_rel S, L
WHERE P
TranslatePath( pluviometry )
Translation for QW
SQL/XML Query
SELECT XMLELEMENT("gml:FeatureMember",
XMLELEMENT("Station")
XMLFOREST(S.NAME AS "name"),
XMLELEMENT( "city",
[TStation / geometry]  [Station_rel / GEOM_POINT]
(SELECT XMLCONCAT(
XMLFOREST(C.NAME AS "name"),
XMLFOREST(C.AREA AS "area") )
FROM City_rel C
WHERE C.CODECITY = S.CODECITY)),
(SELECT XMLAGG(XMLELEMENT("pluviometry",
XMLFOREST(PL.MONTH AS "month"),
XMLFOREST(PL.VALUE AS "value") ) )
TranslatePath( geometry )
FROM Pluviometry_rel PL
WHERE S.CODE = PL.CODESTATION),
XMLFOREST(SDO_UTIL.TO_GMLGEOMETRY(S.GEOM_POINT) AS "geometry")
)
FROM Station_rel S, L
WHERE P
Translation for QW
SQL/XML Query
SELECT XMLELEMENT("gml:FeatureMember",
XMLELEMENT("Station")
XMLFOREST(S.NAME AS "name"),
XMLELEMENT( "city",
(SELECT XMLCONCAT(
XMLFOREST(C.NAME AS "name"),
XMLFOREST(C.AREA AS "area") )
FROM City_rel C
WHERE C.CODECITY = S.CODECITY)),
(SELECT XMLAGG(XMLELEMENT("pluviometry",
XMLFOREST(PL.MONTH AS "month"),
XMLFOREST(PL.VALUE AS "value") ) )
FROM Pluviometry_rel PL
WHERE S.CODE = PL.CODESTATION),
XMLFOREST(SDO_UTIL.TO_GMLGEOMETRY(S.GEOM_POINT) AS "geometry")
)
FROM Station_rel S, Agency_rel A
WHERE S.CODEAGENCY = A.CODEAGENCY AND A.NAME = 'FUNCEME' AND
mdsys.sdo_relate( S.GEOM_POINT, mdsys.sdo_geometry(2003, NULL, NULL,
mdsys.sdo_elem_info_array(1, 1003, 3),
mdsys.sdo_ordinate_array(-5.2, -42.5, –2.5, -38.7)),
'mask=ANYINTERACT querytype=WINDOW') = 'TRUE'
<P,L > := TraslateFilter(f )
TranslateWFSQuery Algorithm
Theorem:
Let
Qw be a WFS Query over F
QX be a canonical XQuery for QW
QS be a SQL/XML query over S generated by TranslateWFSQuery
S1 be a set of <featureMember> elements resulting from QX on F
S2 be a set of <featureMember> elements resulting from QS on S
Then
S1 = S2
Conclusions

Contributions:



a formalism to specify the mapping between
a feature type schema and a relational database schema
an algorithm that translates
a WFS query over a feature type schema into
a SQL/XML query over the relational database schema,
based on feature type's correspondence assertions.
Future work:

development of GML Publisher, a framework for publishing
geographic data stored in relational databases as GML