Hi all,
it seems to be no way to specify an ordering for features returned
by a query to a DataStore.
Maybe it's because ordering is not supported in WFS either...Is there any prevision of implementing it in GeoTools any time soon???
If not, I found these possible solutions / workarounds:
.) Create an ordered view in the RDBMS and query that (CONS: don't like it)..) Create a WrappingDataStore capable of sorting features from a wrappee
(CONS: it's a waste of resource, since the RDBMS can sort on its own)..) Patching XXXDataStore (for me it will be PostgisDataStore) to interpret
special "ordering" Filters putting them into an "ORDER BY" clause instead
that into the "WHERE". (CONS: it'll work only with "patched" DataSto
Looks like it's time to start some work on this. Norman was asking for
this last week. Basically I think what we want to do the work as a
special filter. I think eventually we will aim for a combination of your
2 and 3 suggestions, similar to how we handle filters in general now. We
use a SortBy filter, and if the datastore can turn it into SQL it does, if
it can't (is not an rbdms) then we do the sorting ourselves in java.
Norman said he might have some people who could implement this - Paulo, if
you can help let him know. I'll present a plan of action, though there
are a few places you're going to have to figure out yourselves, since it
is a special filter, so it is not as straight forward as possible.
This is going to require some API changes, but they are supported by the
new wfs and filter specs, so they should be fine.
There is a new schema in the filter package called sort.xsd:
<xsd:schema targetNamespace="http://www.opengis.net/ogc"
elementFormDefault="qualified" version="1.1.0">
<xsd:include schemaLocation="expr.xsd"/>
<!-- ============================================= -->
<!-- SORTBY EXPRESSION -->
<!-- ============================================= -->
<xsd:element name="SortBy" type="ogc:SortByType"/>
<!-- ============================================= -->
<!-- COMPLEX TYPES -->
<!-- ============================================= -->
<xsd:complexType name="SortByType">
<xsd:sequence>
<xsd:element name="SortProperty" type="ogc:SortPropertyType"
maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="SortPropertyType">
<xsd:sequence>
<xsd:element ref="ogc:PropertyName"/>
<xsd:element name="SortOrder" type="ogc:SortOrderType"
minOccurs="0"/>
</xsd:sequence>
</xsd:complexType>
<xsd:simpleType name="SortOrderType">
<xsd:restriction base="xsd:string">
<xsd:enumeration value="DESC"/>
<xsd:enumeration value="ASC"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:schema>
(this is publically available in the ebRim profile for catalog spec).
In the WFS spec it will be added to the Query, as an element just like
Filter, PropertyName, etc.
We should add it in a similar manner to geotools. A few classes will need
to be added/modified.
1) A new SortBy class. Following the ogc schemas would indicate this
should _not_ actually be a filter, but should use the same propertyName
element as Filter - an Expression, for us:
http://svn.refractions.net/geotools/geotools/trunk/gt/module/main/src/org/geotools/filter/AttributeExpression.java
So the class should just model the xml schema for sort.xsd
2) Modify SQLEncoder to accept a SortBy class (add a visit method with
SortBy as the class). I believe this should be at the same level of
filter or Expression. I am not sure if orderedBy is a standard SQL
construct - if it is then put it in SQLEncoder, if it is not then each
subclass should implement.
3) Add the SortBy type to the FilterCapabilities for SQLEncoder, so it
gets picked up. This will involve creating a type. I think we want it in
its own class, since that's the way the OGC models it, similar to
ExpressionType or FilterType. And looking at the code the SortBy class
should extend the SortByType, to follow the rest of the Filter package.
http://svn.refractions.net/geotools/geotools/trunk/gt/module/main/src/org/geotools/filter/ExpressionType.java
http://svn.refractions.net/geotools/geotools/trunk/gt/module/main/src/org/geotools/filter/FilterType.java
4) Figure out where the DataStore should ask for the SortBy. My guess
is to use the SQLBuilder, and add an orderedBy method, along the lines of
the where method. And add the SortBy argument to the buildSQLQuery.
Be sure to handle null SortBy's. This method should just ask the encoder
to encode the SQL statement, and add it on.
5) Modify the Query (and DefaultQuery) class to have a SortBy member.
Modify the datastore to pass the SortBy off to the sql builder when it
requests its reader.
http://svn.refractions.net/geotools/geotools/trunk/gt/module/main/src/org/geotools/data/Query.java
6) Figure out what to do if the DataStore does not support a SortBy.
Initially just throwing an exception is fine (be sure to make sure the
datastore is checking to see if it is supported - by checking the filter
capabilities (note I'm not 100% sure that it should go in
FilterCapabilities - perhaps it should be its own capability, but that's
the place I would try it out first)). But we also should look into
handling the sorting in Java, like we do with filters. In filters the
Impl class can perform the process, see
http://svn.refractions.net/geotools/geotools/trunk/gt/module/main/src/org/geotools/filter/GeometryFilterImpl.java
the contains method. Perhaps the sortby should have a sort method or some
such. Should probably leverage java comparable stuff, maybe it could
change the way the compareTo method of a Feature is done, so as to utilize
the SortBy the user has set (which propertyName and ascending or
descending order). Then we could just call Collections.sort(List) or some
such on the FeatureCollection (would have to bring our FeatureList
construct into effect, and this would have a nasty overhead since we'd
need to load everything in memory, but it is something that we should
eventually implement).
Ok I believe this should be sufficient for getting things working in
GeoTools.
There are a few more things to get it in GeoServer -
1) Make a SAX parser for the new SortBy element. This is probably trivial
enough to just do inside the FeatureHandler:
http://svn.refractions.net/geoserver/trunk/geoserver/src/org/vfny/geoserver/requests/wfs/FeatureHandler.java
Though it could also be done as its own (sax)handler, in which case it
could go into GeoTools, but I think that would just cause nasty weird
stacks, like what we had to do with Features and Filters and their parents
in geoserver. Basically this all needs to be moved to the new xml
framework that David Z has done. And SortBy probably needs to be added to
it. I still have not dug fully into it, but I am pretty positive it is
the answer to our problems.
I think that's actually all that is needed in GeoServer.
I will put all of this in a JIRA task. I don't have the time to tackle,
but hopefully one of you guys can. Let me know your jira name and I can
assign the task to you, and you can post your progress and post the new
code there when it's done. And I imagine you'll be able to get commit
rights if you'd like them, since this is a decent sized contribution. And
feel free to ping me if I missed anything or if it doesn't make sense.
best regards,
Chris