[GIS] Openlayers: OGC Filter problem

filtergeoserveropenlayers-2sql serverwfs

I have a problem with OGC filter.

My layer in OpenLayers has this filter: 

filter: new OpenLayers.Filter.Comparison({ 
            type: OpenLayers.Filter.Comparison.BETWEEN, 
            property: "datetime", 
            lowerBoundary: "2013-01-10T12:11:50.501Z", 
            upperBoundary: "2013-01-10T12:11:55.501Z" 
                                                    })

and the post to server is: 

<ogc:Filter xmlns:ogc="http://www.opengis.net/ogc">
<ogc:PropertyIsBetween>
<ogc:PropertyName>datetime</ogc:PropertyName>
<ogc:LowerBoundary>
<ogc:Literal>2013-01-10T12:11:50.501Z</ogc:Literal>
</ogc:LowerBoundary>
<ogc:UpperBoundary>
<ogc:Literal>2013-01-10T12:11:55.501Z</ogc:Literal>
</ogc:UpperBoundary>
</ogc:PropertyIsBetween>
</ogc:Filter>

but the reply is:

<wfs:FeatureCollection numberOfFeatures="0" timeStamp="2013-01-10T12:11:15.100Z" xsi:schemaLocation="AidAssistant http://127.0.0.1:8080/geoserver/wfs?service=WFS&version=1.1.0&request=DescribeFeatureType&typeName=AidAssistant%3Av_dati_sensori_geom_BNU1 http://www.opengis.net/wfs http://127.0.0.1:8080/geoserver/schemas/wfs/1.1.0/wfs.xsd" xmlns:ogc="http://www.opengis.net/ogc" xmlns:tiger="http://www.census.gov" xmlns:cite="http://www.opengeospatial.net/cite" xmlns:nurc="http://www.nurc.nato.int" xmlns:sde="http://geoserver.sf.net" xmlns:wfs="http://www.opengis.net/wfs" xmlns:topp="http://www.openplans.org/topp" xmlns:it.geosolutions="http://www.geo-solutions.it" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:AidAssistant="AidAssistant" xmlns:sf="http://www.openplans.org/spearfish" xmlns:ows="http://www.opengis.net/ows" xmlns:gml="http://www.opengis.net/gml" xmlns:xlink="http://www.w3.org/1999/xlink">
<gml:featureMembers></gml:featureMembers>
</wfs:FeatureCollection>

I tried to use this filter: 

filter: new OpenLayers.Filter.Logical({ 
        type: OpenLayers.Filter.Logical.AND, 
        filters: [ 
                new OpenLayers.Filter.Comparison({ 
                    type: OpenLayers.Filter.Comparison.GREATER_THAN_OR_EQUAL_TO, 
                    property: "datetime", 
                    value: 2013-01-10T12:25:40.612Z 
                }), 
                new OpenLayers.Filter.Comparison({ 
                   type: OpenLayers.Filter.Comparison.LESS_THAN_OR_EQUAL_TO, 
                   property: "datetime", 
                  value: 2013-01-10T12:25:50.612Z 
                }) 
        ] 
})

and the post is: 

<ogc:Filter xmlns:ogc="http://www.opengis.net/ogc">
<ogc:And>
<ogc:PropertyIsGreaterThanOrEqualTo>
<ogc:PropertyName>datetime</ogc:PropertyName>
<ogc:Literal>2013-01-10T12:25:40.612Z</ogc:Literal>
</ogc:PropertyIsGreaterThanOrEqualTo>
<ogc:PropertyIsLessThanOrEqualTo>
<ogc:PropertyName>datetime</ogc:PropertyName>
<ogc:Literal>2013-01-10T12:25:50.612Z</ogc:Literal>
</ogc:PropertyIsLessThanOrEqualTo>
</ogc:And>
</ogc:Filter>

and the reply is the same, 0 features.

There are features in my layer, indeed if I do this request: 

http://127.0.0.1:8080/geoserver/wfs?service=wfs&version=1.1.0&request=GetFeature&typeName=AidAssistant:v_dati_sensori_geom_BNU1&srsName=EPSG:404000&sortBy=datetime+D

I receive a lot of features.

But if I try this: 

http://127.0.0.1:8080/geoserver/wfs?service=wfs&version=1.1.0&request=GetFeature&typeName=AidAssistant:v_dati_sensori_geom_BNU1&srsName=EPSG:404000&filter=%3Cogc:Filter%20xmlns:ogc=%22http://www.opengis.net/ogc%22%3E%3Cogc:PropertyIsGreaterThanOrEqualTo%3E%3Cogc:PropertyName%3Edatetime%3C/ogc:PropertyName%3E%3Cogc:Literal%3E2013-01-10T12:25:40.612Z%3C/ogc:Literal%3E%3C/ogc:PropertyIsGreaterThanOrEqualTo%3E%3C/ogc:Filter%3E

I receive 0 features.

Sorry for the long post 🙁

Can someone help me?

Thanks.

Update:
Maybe I have found the error. Geoserver to do this query on the DB:

SELECT "node_id","datetime","geom".STAsBinary() as "geom"
  FROM "v_dati_sensori_geom_BNU1" WHERE  "datetime" BETWEEN '2013-01-3 16:45:53.000' AND '2013-01-10 16:45:58.000'

but this does not work because the right query on SQL SERVER is:

SELECT "node_id","datetime","geom".STAsBinary() as "geom"
  FROM "v_dati_sensori_geom_BNU1" WHERE  "datetime" 
  BETWEEN convert(datetime, '2013-01-3 16:45:53.000', 120)  AND convert(datetime, '2013-01-10 16:45:58.000', 120)

Workaround for this problem?

Best Answer

You should use raw MSSQL format of timedates in the filter. You can use solution from https://stackoverflow.com/questions/749615/does-ms-sql-servers-between-include-the-range-boundaries or from documentation http://msdn.microsoft.com/en-US/library/ms187922(v=SQL.90).aspx.

Does

    SELECT "node_id","datetime","geom".STAsBinary() as "geom"
      FROM "v_dati_sensori_geom_BNU1" WHERE  "datetime" 
      BETWEEN convert(datetime, '2013-01-3 16:45:53.000', 30)  
      AND convert(datetime,'2013-01-10 16:45:58.000', 30)

work correctly? '30' style is interpreted as default styly '0' (see documetation http://msdn.microsoft.com/en-us/library/aa226054(v=sql.80).aspx).

Regards,

Related Question