WFS2 Stored query using joins

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

WFS2 Stored query using joins

Bjørn Ove Grøtan
Hello

I have successfully setup a WFS 2.0 service by using Deegree 3.3.x-branch
But next, I would like to make a stored query that combines  a PropertyIsEqualTo-filter
with a geographical filter using a second table. Features in table A can intersect multiple
regions - so it is not an option to store the regionid as an attribute in table A.

Example:
Table A contains my main features (objectid,name,geom)
Table B contains a set of regions (regionid,geom)

What I want to achieve is making a StoredQueryDefinition file
witch gets the geom from table b where b.regionid={region}
and joins this with table a.geom to return all features that intersects.

I have looked at http://download.deegree.org/documentation/3.3.19/html/webservices.html
but as I do not see any examples on how to transpose my postgis-query to a StoreQueryDefinition
I ask for help - or rather, working examples on how to make such combination filters (if possible).

A query would in my mind look something like this:
http://localhost:8080/services/MyCustomWFS?request=GetFeature&storedquery_id=urn:x-inspire:storedQuery:GetFeaturesForRegion&region=1234

--
Regards
 
Bjørn Ove Grøtan

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
_______________________________________________
deegree-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/deegree-users
Reply | Threaded
Open this post in threaded view
|

Re: WFS2 Stored query using joins

Pedersen Jarle
I think you'll need to model the regionid into your tableA-feature as a datatype. Then you can create a view that joins the tables by intersections and gets the regionid. You should be able to use this view to populate your tableA-feature with regionids. Then I believe it should be possible to refer directly to the tableA-feature in the filter. Here's some SQL and a draft for the stored query (replace MYNAMESPACE, MYFEATURETYPE and MYREGIONID in stored query with correct values) plus an example for the featurestore:

SQL:
CREATE OR REPLACE VIEW tableA_tableB AS
select a.id as parentfk, b.regionid as fk from tableA a, tableB b where st_intersects(a.geom, b.geom);

StoredQuery:
<?xml version="1.0" encoding="UTF-8"?>
<wfs:StoredQueryDefinition id="urn:x-inspire:storedQuery:GetFeaturesForRegion "
    xmlns:wfs="http://www.opengis.net/wfs/2.0"
    xmlns:xlink="http://www.w3.org/1999/xlink"
    xmlns:app="MYNAMESPACE"
    xmlns:gml="http://www.opengis.net/gml/3.2" xmlns:fes="http://www.opengis.net/fes/2.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0.0" service="WFS"
    xsi:schemaLocation="http://www.opengis.net/wfs/2.0 http://schemas.opengis.net/wfs/2.0/wfs.xsd http://www.opengis.net/fes/2.0 http://schemas.opengis.net/filter/2.0/filter.xsd http://www.opengis.net/gml/3.2 http://schemas.opengis.net/gml/3.2.1/gml.xsd">
    <wfs:Title>GetFeaturesForRegion</wfs:Title>
    <wfs:Abstract>Return features for region</wfs:Abstract>
    <wfs:Parameter xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="region" type="xsd:integer"/>
    <wfs:QueryExpressionText returnFeatureTypes="app:MYFEATURETYPE"
        language="urn:ogc:def:queryLanguage:OGC-WFS::WFSQueryExpression" isPrivate="false">
        <wfs:Query xmlns:gml="http://www.opengis.net/gml/3.2"
            typeNames="schema-element(app:MYFEATURETYPE)">
            <fes:Filter>
                <fes:PropertyIsEqualTo>
                    <fes:ValueReference>app:MYREGIONID</fes:ValueReference>
                    <fes:Literal>${region}</fes:Literal>
                </fes:PropertyIsEqualTo>
            </fes:Filter>
        </wfs:Query>
    </wfs:QueryExpressionText>
</wfs:StoredQueryDefinition>

FeatureStore:
...
<Primitive path="app: MYREGIONID" mapping="fk" >
      <Join table=" tableA_tableB" fromColumns="id" toColumns="parentfk" />
</Primitive>
...


Good luck!
Jarle Pedersen

-----Original Message-----
From: Bjørn Ove Grøtan [mailto:[hidden email]]
Sent: 2. januar 2017 15.22
To: [hidden email]
Subject: [deegree-users] WFS2 Stored query using joins

Hello

I have successfully setup a WFS 2.0 service by using Deegree 3.3.x-branch But next, I would like to make a stored query that combines  a PropertyIsEqualTo-filter with a geographical filter using a second table. Features in table A can intersect multiple regions - so it is not an option to store the regionid as an attribute in table A.

Example:
Table A contains my main features (objectid,name,geom) Table B contains a set of regions (regionid,geom)

What I want to achieve is making a StoredQueryDefinition file witch gets the geom from table b where b.regionid={region} and joins this with table a.geom to return all features that intersects.

I have looked at https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fdownload.deegree.org%2Fdocumentation%2F3.3.19%2Fhtml%2Fwebservices.html&data=01%7C01%7Cjarle.pedersen%40norconsult.com%7Caa77ed22f531468f03d608d4331d3a58%7Cce25ca93004f44f4a6b5eb22b45815aa%7C1&sdata=K9wjSu1RBZp8imHoCLPTvB0no7ZYvg0Ox5PvKBhtRSY%3D&reserved=0
but as I do not see any examples on how to transpose my postgis-query to a StoreQueryDefinition I ask for help - or rather, working examples on how to make such combination filters (if possible).

A query would in my mind look something like this:
http://localhost:8080/services/MyCustomWFS?request=GetFeature&storedquery_id=urn:x-inspire:storedQuery:GetFeaturesForRegion&region=1234

--
Regards
 
Bjørn Ove Grøtan

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most engaging tech sites, SlashDot.org! https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fsdm.link%2Fslashdot&data=01%7C01%7Cjarle.pedersen%40norconsult.com%7Caa77ed22f531468f03d608d4331d3a58%7Cce25ca93004f44f4a6b5eb22b45815aa%7C1&sdata=7aRo%2FYnMyx7rwZmJlLJbj0yLIBkcjij0IxzFgG96CVM%3D&reserved=0
_______________________________________________
deegree-users mailing list
[hidden email]
https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.sourceforge.net%2Flists%2Flistinfo%2Fdeegree-users&data=01%7C01%7Cjarle.pedersen%40norconsult.com%7Caa77ed22f531468f03d608d4331d3a58%7Cce25ca93004f44f4a6b5eb22b45815aa%7C1&sdata=Ublq7V49R0fKeKq6PZKha%2FhDvuNgai3QYwPmsTL4%2BT4%3D&reserved=0
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
_______________________________________________
deegree-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/deegree-users