GeoServer

DWITHIN filter query fails when a request is made against a feature from an Oracle datastore

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Minor Minor
  • Resolution: Fixed
  • Affects Version/s: 2.0.2
  • Fix Version/s: 2.1.4
  • Component/s: Oracle
  • Labels:
    None
  • Environment:
    not relevant
  • Number of attachments :
    1

Description

the following GetMap
http://10.112.80.27:8080/Geoserver/wms?service=WMS&version=1.1.0&request=GetMap&layers=cadrede_gis:GIR_INFRA_NODES&styles=&bbox=-89309,-111933,-88509,-111133&width=449&height=512&srs=EPSG:27492&format=image/png&EXCEPTIONS=application/vnd.ogc.se+xml&CQL_FILTER=DWITHIN(LOCATION,POINT(-88909%20-111533),100,meters)

throws a sql exception from db oracle with a message ORA-13207: incorrect use of the [ INVALID UNITS] operator. The units of measure name in Oracle are in singular, so a SDO_WITHIN_DISTANCE use must be like, for example

... SDO_WITHIN_DISTANCE(location, mdsys.sdo_geometry(2001,27492,sdo_point_type(-88909,-111533,null),null,null), 'distance=1000 unit=meter') = 'TRUE' ...

the table with the units of measure
select *
from sdo_units_of_measure a
where lower(a.unit_of_meas_name) like '%meter%'
order by short_name;

the stacktrace in attachment.

Issue Links

depends upon

Bug - A problem which impairs or prevents the functions of the product. GEOT-4121 DWithin filter does not work from GeoServer when passing units parameter

  • Major - Major loss of function.
  • Resolved - A resolution has been taken, and it is awaiting verification by reporter. From here issues are either reopened, or are closed.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Andrea Aime added a comment -

As far as I know the unit is singular also in OGC services... where is "meters" coming from? Is the request valid to start with?

Show
Andrea Aime added a comment - As far as I know the unit is singular also in OGC services... where is "meters" coming from? Is the request valid to start with?
Hide
Permalink
Pedro Mendes added a comment -

Yes, is valid. I made the same request but just changing the Layer to a Shapefile datastore's feature type and it worked Ok: it returned the features within 100m of the specified point.

So, one should expect that an expression in the CQL filter containing that kind of spatial filter - DWITHIN(the_geom,POINT(-88909,-111533),100,meters) - should work for both Oracle and Shapefile's datastores?

Show
Pedro Mendes added a comment - Yes, is valid. I made the same request but just changing the Layer to a Shapefile datastore's feature type and it worked Ok: it returned the features within 100m of the specified point. So, one should expect that an expression in the CQL filter containing that kind of spatial filter - DWITHIN(the_geom,POINT(-88909,-111533),100,meters) - should work for both Oracle and Shapefile's datastores?
Hide
Permalink
Andrea Aime added a comment -

shapefiles datastore completely ignores the unit, try with unit = nanoseconds and you'll see

Show
Andrea Aime added a comment - shapefiles datastore completely ignores the unit, try with unit = nanoseconds and you'll see
Hide
Permalink
Pedro Mendes added a comment -

So what should an working expression with DWITHIN filter for an racle datastore?

Show
Pedro Mendes added a comment - So what should an working expression with DWITHIN filter for an racle datastore?
Hide
Permalink
Andrea Aime added a comment -

As far as I know the unit is used as is in the store, so I would try with the unit names that are native to Oracle. Don't know what they are, search the Oracle documentation for them

Show
Andrea Aime added a comment - As far as I know the unit is used as is in the store, so I would try with the unit names that are native to Oracle. Don't know what they are, search the Oracle documentation for them
Hide
Permalink
Pedro Mendes added a comment -

Sorry Andrea, I think I didn't explained myself well enough. I'll try to make a better description of the problem:

if the unit specified is METER the exception is 

Could not parse CQL filter list.
Encountered "meter" at line 1, column 44.
Was expecting one of:
    "feet" ...
    "meters" ...
    "statute miles" ...
    "nautical miles" ...
    "kilometers" ...
    . Parsing : DWITHIN(LOCATION,POINT(-88909 -111533),100,meter). Current Token : ","
Details:
org.geoserver.platform.ServiceException: Could not parse CQL filter list.
	at org.geoserver.wfs.kvp.CQLFilterKvpParser.parse(CQLFilterKvpParser.java:28)
	at org.geoserver.ows.util.KvpUtils.parse(KvpUtils.java:471)

if the unit specified is METERS the exception is 

org.vfny.geoserver.wms.WmsException: Rendering process failed
Rendering process failed
Exception rendering layer FeatureCollectionMapLayer[ GIR_INFRA_NODES, VISIBLE, UNSELECTED, style=StyleImpl[ name=point], data=org.vfny.geoserver.global.GeoServerFeatureLocking@1f6e6127, query=Query:
   feature type: GIR_INFRA_NODES
   filter: [Filter.INCLUDE AND [ LOCATION dwithin POINT (-88909 -111533), distance: 100.0 ]]
   [properties:  ALL ]]
java.io.IOException
null
ORA-29902: error in executing ODCIIndexStart() routine
ORA-13207: incorrect use of the [ INVALID UNITS] operator
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 333

Details:
org.vfny.geoserver.wms.WmsException: org.vfny.geoserver.wms.WmsException: Rendering process failed
	at org.vfny.geoserver.wms.responses.GetMapResponse.execute(GetMapResponse.java:447)

Oracle expects METER but that parameter in singular doesn't pass through the parser. I can only make it work when I change the unit value from METERS to METER during a debug session in the gt-jdbc-oracle module in method org.geotools.data.oracle.OracleFilterToSQL.doSDODistance().

Show
Pedro Mendes added a comment - Sorry Andrea, I think I didn't explained myself well enough. I'll try to make a better description of the problem: if the unit specified is METER the exception is Could not parse CQL filter list. Encountered "meter" at line 1, column 44. Was expecting one of: "feet" ... "meters" ... "statute miles" ... "nautical miles" ... "kilometers" ... . Parsing : DWITHIN(LOCATION,POINT(-88909 -111533),100,meter). Current Token : "," Details: org.geoserver.platform.ServiceException: Could not parse CQL filter list. at org.geoserver.wfs.kvp.CQLFilterKvpParser.parse(CQLFilterKvpParser.java:28) at org.geoserver.ows.util.KvpUtils.parse(KvpUtils.java:471) if the unit specified is METERS the exception is org.vfny.geoserver.wms.WmsException: Rendering process failed Rendering process failed Exception rendering layer FeatureCollectionMapLayer[ GIR_INFRA_NODES, VISIBLE, UNSELECTED, style=StyleImpl[ name=point], data=org.vfny.geoserver.global.GeoServerFeatureLocking@1f6e6127, query=Query: feature type: GIR_INFRA_NODES filter: [Filter.INCLUDE AND [ LOCATION dwithin POINT (-88909 -111533), distance: 100.0 ]] [properties: ALL ]] java.io.IOException null ORA-29902: error in executing ODCIIndexStart() routine ORA-13207: incorrect use of the [ INVALID UNITS] operator ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 333 Details: org.vfny.geoserver.wms.WmsException: org.vfny.geoserver.wms.WmsException: Rendering process failed at org.vfny.geoserver.wms.responses.GetMapResponse.execute(GetMapResponse.java:447) Oracle expects METER but that parameter in singular doesn't pass through the parser. I can only make it work when I change the unit value from METERS to METER during a debug session in the gt-jdbc-oracle module in method org.geotools.data.oracle.OracleFilterToSQL.doSDODistance().
Hide
Permalink
Andrea Aime added a comment -

I see, then I guess we need a unit version table, ogc to oracle, into the oracle sql filter encoder.
The procedure to get that committed is to prepare a patch with the changes along with a test showing the change
works as expected.

Show
Andrea Aime added a comment - I see, then I guess we need a unit version table, ogc to oracle, into the oracle sql filter encoder. The procedure to get that committed is to prepare a patch with the changes along with a test showing the change works as expected.
Hide
Permalink
Andrea Aime added a comment -

Has been recently fixed in Geotools

Show
Andrea Aime added a comment - Has been recently fixed in Geotools
Hide
Permalink
Andrea Aime added a comment -

Switching all issues that have been in "resolved" state for more than one month without further comments to "closed" status

Show
Andrea Aime added a comment - Switching all issues that have been in "resolved" state for more than one month without further comments to "closed" status

People

  • Assignee:
    Andrea Aime
    Reporter:
    Pedro Mendes
Vote (0)
Watch (1)

Dates

  • Created:
    Updated:
    Resolved: