Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

You should use raw MSSQL format of timedates in the filter. You can use solution from http://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,

share|improve this answer
    
My mistake, was: convert (datetime, '2013-01-3 16:45:53.000', 120) –  pask23 Jan 11 '13 at 8:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.