Specifying join on multiple datasets in a WFS URL request (CQL/Filter)?

Question

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&service=WFS&

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>

Answer 1

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.