TransWikia.com

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

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>

One Answer

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

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP