Geographic Information Systems Asked on January 8, 2021
I’m using a WFS and trying to join two/three datasets together in a URL query.
Two of the datasets are linked via an aspatial table in the middle like so:
| Table 1 | | Table 2 | | Table 3 |
| PFI | Item | Geom | > | PFI | AFI | > | AFI | Item2 | Geom |
| 1 | House| foo | | 1 | 45 | | 45 | Street| bar |
| 2 | House| foo | | 2 | 78 | | 78 | Road | bar |
I need to join the tables in a WFS URL request and return all results that match the filter. I keep getting the error “Join query must specify a filter” when trying to specify how the join matches.
In SQL I would do something like:
SELECT * FROM TABLE1
LEFT JOIN TABLE2
ON TABLE1.PFI = TABLE2.PFI
WHERE TABLE1.PFI = 1
My main question is how do I achieve this in the WFS URL request?
WFS I’m using:
http://services.land.vic.gov.au/catalogue/publicproxy/guest/dv_geoserver/wfs?request=getCapabilities
I’ve attempted to use both CQL_Filter= and Filter=:
Example:
http://services.land.vic.gov.au/catalogue/publicproxy/guest/dv_geoserver/wfs?service=wfs&version=2.0.0&request=getfeature&typenames=datavic:VMADD_ADDRESS,datavic:VMPROP_PARCEL_PROPERTY&CQL_FILTER=datavic:VMADD_ADDRESS/PROPERTY_PFI='41116831';datavic:VMADD_ADDRESS/PROPERTY_PFI=datavic:VMPROP_PARCEL_PROPERTYS/PROPERTY_PFI
XML &Filter= variation:
<Filter>
<And>
<PropertyIsEqualTo>
<PropertyName>/datavic:VMADD_ADDRESS</PropertyName>
<Literal>41116831</Literal>
</PropertyIsEqualTo>
<PropertyIsEqualTo>
<PropertyName>/datavic:VMADD_ADDRESS/PROPERTY_PFI</PropertyName>
<PropertyName>/datavic:VMPROP_PARCEL_PROPERTY/PROPERTY_PFI</PropertyName>
</PropertyIsEqualTo>
</And>
</Filter>
EDIT:
The below query is the closest I’ve gotten to getting it working, but receives a different error:
http://services.land.vic.gov.au/catalogue/publicproxy/guest/dv_geoserver/wfs?service=wfs&version=2.0.0&request=getfeature&typenames=datavic:VMADD_ADDRESS,datavic:VMPROP_PARCEL_PROPERTY&CQL_FILTER=(datavic:VMADD_ADDRESS.PROPERTY_PFI=datavic:VMPROP_PARCEL_PROPERTY.PROPERTY_PFI)
ERROR:
<ows:ExceptionReport xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:ows="http://www.opengis.net/ows/1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0.0" xsi:schemaLocation="http://www.opengis.net/ows/1.1 http://services.land.vic.gov.au/catalogue/publicproxy/guest/dv_geoserver/schemas/ows/1.1.0/owsAll.xsd">
<ows:Exception exceptionCode="OperationProcessingFailed" locator="GetFeature">
<ows:ExceptionText>
Error occurred getting features Cannot query this feature source with ADDRESS since it serves only VMADD_ADDRESS Cannot query this feature source with ADDRESS since it serves only VMADD_ADDRESS
</ows:ExceptionText>
</ows:Exception>
</ows:ExceptionReport>
THis isn't the complete answer but it may help you move forward.
First I'm not sure how you go about doing a WFS 2.0 join using a URL (I haven't looked in the standard but if you can then it will probably show you), so I'm using POST and an XML file.
One of your problems is that you are trying to use Filter v1.1 when you need to use Filter Encoding Standard 2.0 for WFS2.0. So you need to send a query like:
<?xml version="1.0"?>
<wfs:GetFeature xmlns:wfs="http://www.opengis.net/wfs/2.0" xmlns:fes="http://www.opengis.net/fes/2.0" xmlns:sf="http://www.openplans.org/spearfish" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" service="WFS" version="2.0.0" xsi:schemaLocation="http://www.opengis.net/wfs/2.0 http://schemas.opengis.net/wfs/2.0/wfs.xsd">
<wfs:Query
typeNames="datavic:VMADD_ADDRESS datavic:VMPROP_PARCEL_PROPERTY datavic:VMPROP_PARCEL_VIEW"
aliases="a b c">
<fes:Filter>
<fes:And>
<fes:PropertyIsEqualTo>
<fes:ValueReference>a.PROPERTY_PFI</fes:ValueReference>
<fes:ValueReference>b.PROPERTY_PFI</fes:ValueReference>
</fes:PropertyIsEqualTo>
<fes:PropertyIsEqualTo>
<fes:ValueReference>b.PARCEL_PFI</fes:ValueReference>
<fes:ValueReference>c.PFI</fes:ValueReference>
</fes:PropertyIsEqualTo>
</fes:And>
</fes:Filter>
</wfs:Query>
</wfs:GetFeature>
Note the use of aliases
to distinguish the typenames in the query and ValueReference
has replaced PropertyName
.
This "works" in the sense that I get a different error message:
<ows:ExceptionReport xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:ows="http://www.opengis.net/ows/1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0.0" xsi:schemaLocation="http://www.opengis.net/ows/1.1 http://services.land.vic.gov.au/catalogue/publicproxy/guest/dv_geoserver/schemas/ows/1.1.0/owsAll.xsd">
<ows:Exception exceptionCode="NoApplicableCode">
<ows:ExceptionText>Extracted invalid join sub-filter [ a.PROPERTY_PFI = b.PROPERTY_PFI ], it users more than one feature type + []</ows:ExceptionText>
</ows:Exception>
</ows:ExceptionReport>
I'm not entirely sure what that means though, looking at the code it looks possible that it hasn't found any prefixes rather than too many.
Answered by Ian Turton on January 8, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP