Translating WFS Query to SQL/XML Query

1
Translating WFS Query to SQL/XML Query

• Vânia Vidal
• Fernando Lemos
• Fábio Feitosa
• Departamento de Computação
• Universidade Federal do Ceará

2
Outline

• WFS Specification
• Contributions
• Feature Type Specification
• Extension of Feature Type
• Translating WFS Query to SQL/XML Query
• Conclusions

3
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

4
WFS Specification

• WFS request
• GetCapabilities
• DescribeFeatureType
• GetFeature
• Transaction
• LockFeature

5
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

6
WFS GetFeature Request
Deegree WFS
SQL Query Q1
WFS Query

BDR
Deegree WFS Server
SQL Query Qn
result of Q1
GML

result of Qn
Our approach
WFS Query
SQL/XML Query
BDR
GML Publisher WFS Server
GML
GML
7
Feature Type Specification

• Feature Type F over a relational schema S
• F ltT, R, Agt
• 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

8
Example
Relational Schema DB_Station
XML Schema type for Feature Type F_Station
Master Relation Schema
9
Correspondence Assertions of F_Station
Master Relation Schema
10
Extension of a Feature Type

• Extension of F ltT, R, Agt 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
11
SQL/XML Definition for Extension of F_Station

• SELECT XMLELEMENT( "Extension_of_F_Station",
  XMLAGG(
• XMLELEMENT( "F_Station",
• XMLFOREST(S.CODE AS "code"),
• XMLFOREST(
• SDO_UTIL.TO_GMLGEOMETRY(S.GEOM_POINT)
  AS "geometry"),
• XMLFOREST(S.NAME AS "name"),
• XMLELEMENT( "address",
• XMLFOREST(S.STREET AS "street"),
• XMLELEMENT( "city",
• (SELECT XMLCONCAT(
• XMLFOREST(C.NAME AS "name"),
• XMLFOREST(C.AREA AS "area"))
• FROM City_rel C WHERE C.CODECITY
  S.CODECITY) ),
• XMLFOREST(S.ZIPCODE AS "zipcode") ),
• (SELECT XMLAGG(XMLELEMENT( "pluviometry",
• XMLFOREST(PL.MONTH AS
  "month"),
• XMLFOREST(PL.VALUE AS
  "value") ) )
• FROM Pluviometry_rel PL WHERE S.CODE
  PL.CODESTATION),
• XMLFOREST( (SELECT A.NAME FROM Agency_rel A

TStation / code ? Station_rel / CODE
TStation / geometry ? Station_rel / GEOM_POINT
TStation / name ? Station_rel / NAME
TStation / address ? Station_rel / NULL
TAddress / street ? Station_rel / STREET
TAddress / city ? Station_rel / FK1
TCity / name ? City_rel / NAME
TCity / area ? City_rel / AREA
TAddress / zipcode ? Station_rel / ZIPCODE
TStation / pluviometry ? Station_rel / FK2-1
TPluviometry / month ? Pluviometry_rel / MONTH
TPluviometry / area ? Pluviometry_rel / AREA
TStation / agency ? Station_rel / FK3 / NAME
12
An Instance of DB_Station
13
Extension of F_Station
14
Extension of F_Station
15
Extension of F_Station
16
Extension of F_Station
17
WFS Query

• ltQuerygt element of a GetFeature request
• delivers feature instances of a given feature
  type, where each feature instance matches a
  tuple of the Master Table
• ltQuerygt element contains
• a mandatory attribute typeName
• used to indicate the name of the feature type to
  be queried
• a sequence of zero or more ltwfsPropertyNamegt
  elements
• used to specify what properties to retrieve
• the value of each ltwfsPropertyNamegt element is
  an XPath expression that references a property or
  sub-property of the relevant feature type
• an optional ltFiltergt element
• used to define spatial and non-spatial
  constraints on a query
• encoded as described in the OGC Filter Encoding
  Implementation Spec

18
QX Canonical XQuery for QW
QW WFS Query over F_Station
The result of Qw is the result of evaluating QX
on an extension of F_Station
19
XML fragment resulting from QW
QW WFS Query
20
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 ltgmlfeatureMembergt 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 ltgmlfeatureMembergt elements
  resulting from evaluating QS on ?S and
• S2 is the set of ltgmlfeatureMembergt elements
  resulting from evaluating QX on ?F
• then S1 S2

21
TranslateWFSQuery Algorithm

• Input WFS query QW
• Output SQL/XML query QS
• Let ltP,L gt TranslateFilter( f ), where f is
  the filter of QW
• In case of
• Case 1 QW has no ltwfsPropertyNamegt elements
  Qs SELECT XMLELEMENT(
  "gmlfeatureMember",
  XMLELEMENT( "F",
  ?R?Tr ) )
  FROM R r, L WHERE P

22
TranslateWFSQuery Algorithm

• Case 2 QW has n ltwfsPropertyNamegt elements
• Let Pathi be the value of i-th
  ltwfsPropertyNamegt element of QW.
• Let Qi r TranslatePath(Pathi)
• Qs SELECT
• XMLELEMENT( "
  gmlfeatureMember ",
  XMLELEMENT( " F ",
  Q1 r
  Qn r
  ) ) FROM R r, L
  WHERE P

23
TranslateWFSQuery Algorithm

• TranslateFilter( f )
• returns a tuple ltP,Lgt
• 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

24
TranslatePath(?F) Algorithm

• Input a path ?F p1 // pn of type T
• Output SQL/XML subquery Qr that computes the
  value of path ?F
• Theorem
• For any instance t of Extension of F, where t
  ?Ar, then Qr returns a set S of pn-elements
  where S t/ p1 // pn

25
Translation for QW
QX Canonical XQuery for QW
QW WFS Query
26
SQL/XML Query
Translation for QW

• SELECT XMLELEMENT("gmlFeatureMember",
  XMLELEMENT("Station", Q1S,
  .............TranslatePath( name )
• Q2S,........................Tra
  nslatePath( address / city)
• Q3S,.TranslatePath(
  pluviometry )
• Q4S..TranslatePath(
  geometry )) )
• FROM Station_rel S, L
• WHERE P

27
SQL/XML Query
Translation for QW

• SELECT XMLELEMENT("gmlFeatureMember",
  XMLELEMENT("Station")
  XMLFOREST(S.NAME AS "name"),
• Q2S,
• Q3S,
• Q4S)
• FROM Station_rel S, L
• WHERE P

TranslatePath( name )
TStation / name ? Station_rel / NAME
28
SQL/XML Query
Translation for QW

• SELECT XMLELEMENT("gmlFeatureMember",
  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)),
• Q3S,
• Q4S )
• FROM Station_rel S, L
• WHERE P

TranslatePath( address / city )
TStation / address ? Station_rel / NULL
TAddress / city ? Station_rel / FK1
TCity / name ? City_rel / NAME
TCity / area ? City_rel / AREA
29
SQL/XML Query
Translation for QW

• SELECT XMLELEMENT("gmlFeatureMember",
  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),
• Q S
• )
• FROM Station_rel S, L
• WHERE P

TStation / pluviometry ? Station_rel / FK2-1
TPluviomety / month ? Pluviometry_rel /
MONTH
TPluviomety / value ? Pluviometry_rel /
VALUE
TranslatePath( pluviometry )
30
SQL/XML Query
Translation for QW

• SELECT XMLELEMENT("gmlFeatureMember",
  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.GEO
  M_POINT) AS "geometry") )
• FROM Station_rel S, L
• WHERE P

TStation / geometry ? Station_rel / GEOM_POINT
TranslatePath( geometry )
31
SQL/XML Query
Translation for QW

• SELECT XMLELEMENT("gmlFeatureMember",
  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.GEO
  M_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)),
• 
  'maskANYINTERACT querytypeWINDOW') 'TRUE'

ltP,L gt TraslateFilter(f )
32
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 ltfeatureMembergt elements resulting
  from QX on ?F
• S2 be a set of ltfeatureMembergt elements resulting
  from QS on ?S
• Then
• S1 S2

33
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