[Geoserver-devel] request for GeoServer developer ideas re: "outer join like" WFS request results

GeoServer GeoServer Developer
1

Hi,

I am working on a WFS request where we want results like an outer join, that is, we want to get all features from one table even if they do not have a match in the other.

Doing this with Ben Burns (also of Geocent) for one of our projects.

I can see that outer joins are not supported currently, so I am working to investigate how we can achieve “outer join like” results without an outer join.

Ideas:

a. use an Or filter (credit to Ben for this idea) where the first filter will get the ‘inner join’ tuples and the second filter will get the ‘outer only’ tuples.

Started investigating this, and currently not able to do a WFS join with an Or filter. (details below)

Is it feasible for me to try to add the Or filter to WFS join and see if I can get it to succeed?

Has the Or filter been tried before and it was not able to be implemented?

b. Possibly create a View with an outer join within it and then have GeoServer reference the View instead.

I have not investigated this yet, I think it may have been considered already though and may not be possible in our situation.

c. Are there other existing ideas to get “outer join like” results without an outer join?

Any background info from GeoServer developers would be appreciated.

Thank you for your time!

Jeff

====== begin - details on the Or filter attempt ======

Here is an example i tried that fails with an Or and succeeds when I replace the Or with And:

<wfs:GetFeature xmlns:wfs=“http://www.opengis.net/wfs/2.0”

xmlns:fes=“http://www.opengis.net/fes/2.0”

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=“geotoolsOnlineTests:ftjoin geotoolsOnlineTests:ft1” aliases=“ftjoin ft1”>

fes:Filter

fes:Or

fes:PropertyIsEqualTo

fes:ValueReferenceftjoin/name</fes:ValueReference>

fes:ValueReferenceft1/stringProperty</fes:ValueReference>

</fes:PropertyIsEqualTo>

fes:PropertyIsEqualTo

fes:ValueReferenceft1/id</fes:ValueReference>

fes:ValueReferenceft1/id</fes:ValueReference>

</fes:PropertyIsEqualTo>

</fes:Or>

</fes:Filter>

</wfs:Query>

</wfs:GetFeature>

I see that the wfs/GetFeature.java class disallows the Or filter, and also the Not filter, for joins.

From wfs/GetFeature.java class, line 161:

//we only support simple filters, and any of them And’ed together.

joinFilterCapabilities.addType(And.class);

===== end - details on the Or filter attempt =====

Other tidbits:

I did some looking around on GeoServer and GeoTools mail archives for background info re: joins and I saw this user report of not being able to use the Or filter:

http://osgeo-org.1560.x6.nabble.com/WFS-2-0-join-OR-problem-td5019289.html

The relevant specs that describe only inner joins:

OpenGIS Web Feature Service 2.0 Interface Standard section 7.9.2.5.3.1 Join queries

OpenGIS Filter Encoding 2.0 Encoding Standard, section 6.3.3.1.5 Join queries

Thank you,

Jeffrey Wood

Software Engineer

Geocent

Email: jeffrey.wood@anonymised.com

Ph (BR): 225-214-4346

2

If the DataStores behind the GeoServer WFS are abstracting SQL DBs (in the same schema) I would use an SQL View [1] otherwise I would probably tackle this with a WPS process. If you are looking to render the feature collection you could tie the WPS process to WMS using SLD/Rendering-Transformations [2].

[1] http://docs.geoserver.org/stable/en/user/data/database/sqlview.html
[2] http://docs.geoserver.org/stable/en/user/styling/sld-extensions/rendering-transform.html

Tom Kunicki
Software Engineer | Boundless
tkunicki@anonymised.com
917-460-7212
@boundless

On Nov 19, 2013, at 12:34 PM, Jeffrey Wood <Jeffrey.Wood@anonymised.com> wrote:

Hi,

I am working on a WFS request where we want results like an outer join, that is, we want to get all features from one table even if they do not have a match in the other.
Doing this with Ben Burns (also of Geocent) for one of our projects.
I can see that outer joins are not supported currently, so I am working to investigate how we can achieve "outer join like" results without an outer join.

Ideas:
a. use an Or filter (credit to Ben for this idea) where the first filter will get the 'inner join' tuples and the second filter will get the 'outer only' tuples.
Started investigating this, and currently not able to do a WFS join with an Or filter. (details below)
Is it feasible for me to try to add the Or filter to WFS join and see if I can get it to succeed?
Has the Or filter been tried before and it was not able to be implemented?

b. Possibly create a View with an outer join within it and then have GeoServer reference the View instead.
I have not investigated this yet, I think it may have been considered already though and may not be possible in our situation.

c. Are there other existing ideas to get "outer join like" results without an outer join?

Any background info from GeoServer developers would be appreciated.

Thank you for your time!
Jeff

====== begin - details on the Or filter attempt ======

Here is an example i tried that fails with an Or and succeeds when I replace the Or with And:

<wfs:GetFeature xmlns:wfs="http://www.opengis.net/wfs/2.0&quot;
  xmlns:fes="http://www.opengis.net/fes/2.0&quot;
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance&quot;
  service="WFS" version="2.0.0"
  xsi:schemaLocation="http://www.opengis.net/wfs/2.0 http://schemas.opengis.net/wfs/2.0/wfs.xsd&quot;&gt;
<wfs:Query typeNames="geotoolsOnlineTests:ftjoin geotoolsOnlineTests:ft1" aliases="ftjoin ft1">
   <fes:Filter>
      <fes:Or>
         <fes:PropertyIsEqualTo>
            <fes:ValueReference>ftjoin/name</fes:ValueReference>
            <fes:ValueReference>ft1/stringProperty</fes:ValueReference>
         </fes:PropertyIsEqualTo>
         <fes:PropertyIsEqualTo>
            <fes:ValueReference>ft1/id</fes:ValueReference>
            <fes:ValueReference>ft1/id</fes:ValueReference>
         </fes:PropertyIsEqualTo>
     </fes:Or>
</fes:Filter>
</wfs:Query>
</wfs:GetFeature>

I see that the wfs/GetFeature.java class disallows the Or filter, and also the Not filter, for joins.
From wfs/GetFeature.java class, line 161:
        //we only support simple filters, and any of them And'ed together.
        joinFilterCapabilities.addType(And.class);

===== end - details on the Or filter attempt =====

Other tidbits:
I did some looking around on GeoServer and GeoTools mail archives for background info re: joins and I saw this user report of not being able to use the Or filter:
http://osgeo-org.1560.x6.nabble.com/WFS-2-0-join-OR-problem-td5019289.html

The relevant specs that describe only inner joins:
OpenGIS Web Feature Service 2.0 Interface Standard section 7.9.2.5.3.1 Join queries
OpenGIS Filter Encoding 2.0 Encoding Standard, section 6.3.3.1.5 Join queries

Thank you,

Jeffrey Wood
Software Engineer
Geocent

Email: jeffrey.wood@anonymised.com
Ph (BR): 225-214-4346
------------------------------------------------------------------------------
Shape the Mobile Experience: Free Subscription
Software experts and developers: Be at the forefront of tech innovation.
Intel(R) Software Adrenaline delivers strategic insight and game-changing
conversations that shape the rapidly evolving mobile landscape. Sign up now.
http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

3

On Tue, Nov 19, 2013 at 7:34 PM, Jeffrey Wood <Jeffrey.Wood@anonymised.com>wrote:

Hi,

I am working on a WFS request where we want results like an outer join,
that is, we want to get all features from one table even if they do not
have a match in the other.

Hi,
I concur with the solutions proposed by Tom, they both should help you get
what you want, or close to it, without having
to deal with the generality required for a core change (plus WPS supports
asynch requests, which is good if you plan to extract
large amounts of data, or if the joins end up being expensive).

Anyways, let's entertain the notion of doing it in the WFS protocol, as an
extension to the base protocol instead.
If we go beyond inner joins, we should consider setting up something that
leaves the door option to the other three join types, left, right, outer,
even if the initial implementation considers only outer.

The other issue to consider is that, in general terms, a query can contain
more than two feature types to be joined, or in other terms, more
than one join.

In SQL databases you specify the type of join, and then you don't need to
overload the meaning of and or or, because it's the type of
join that specified the desired behavior.

I'm wondering if something as follows could work, that is, adding a vendor
attribute specifying what kind of join should be considered:

<wfs:Query typeNames="layer1:ft1 layer2:ft2 layer3:ft3" aliases="ft1 ft2
ft3" jointype="inner outer">

And then the filters could be reorganized and split amount joining and non
joining:
* a filter is considered part of a join if it binds togheter more than one
feature type, and associated to the join that can see all the involved types
* anything involving a single feature type is non joining instead

And then the Join class and the machinery associated to in in the JDBC data
store should be updated accordingly.

Unfortunately I'm not very much up to speed with WFS 2.0 joining, so not
sure how much of the above is actually feasible:
we'd really need Justin to chime in, as he implemented the existing joining
code.

Cheers
Andrea

--

Our support, Your Success! Visit http://opensdi.geo-solutions.it for more
information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

-------------------------------------------------------

4

Thank you to Eric, Tom and Andrea for the ideas, I appreciate your input. I have started investigating whether the GeoServer SQL View can work for us.

Thanks again,

Jeffrey Wood

Software Engineer

Geocent

Email: jeffrey.wood@anonymised.com

Ph (BR): 225-214-4346

···

On Tue, Nov 19, 2013 at 7:34 PM, Jeffrey Wood <Jeffrey.Wood@anonymised.com2983…> wrote:

Hi,

I am working on a WFS request where we want results like an outer join, that is, we want to get all features from one table even if they do not have a match in the other.

Hi,

I concur with the solutions proposed by Tom, they both should help you get what you want, or close to it, without having

to deal with the generality required for a core change (plus WPS supports asynch requests, which is good if you plan to extract

large amounts of data, or if the joins end up being expensive).

Anyways, let’s entertain the notion of doing it in the WFS protocol, as an extension to the base protocol instead.

If we go beyond inner joins, we should consider setting up something that leaves the door option to the other three join types, left, right, outer,

even if the initial implementation considers only outer.

The other issue to consider is that, in general terms, a query can contain more than two feature types to be joined, or in other terms, more

than one join.

In SQL databases you specify the type of join, and then you don’t need to overload the meaning of and or or, because it’s the type of

join that specified the desired behavior.

I’m wondering if something as follows could work, that is, adding a vendor attribute specifying what kind of join should be considered:

<wfs:Query typeNames=“layer1:ft1 layer2:ft2 layer3:ft3” aliases=“ft1 ft2 ft3” jointype=“inner outer”>

And then the filters could be reorganized and split amount joining and non joining:

  • a filter is considered part of a join if it binds togheter more than one feature type, and associated to the join that can see all the involved types

  • anything involving a single feature type is non joining instead

And then the Join class and the machinery associated to in in the JDBC data store should be updated accordingly.

Unfortunately I’m not very much up to speed with WFS 2.0 joining, so not sure how much of the above is actually feasible:

we’d really need Justin to chime in, as he implemented the existing joining code.

Cheers

Andrea

–

==

Our support, Your Success! Visit http://opensdi.geo-solutions.it for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it