alderian72

Need Help with using SDO_NN in a query task

Discussion created by alderian72 on Jul 5, 2011
Hi, big problems. Got a system going to test on Friday and suddenly found out that it's not working properly. I'm using SDO_NN with sdo_batch_size as the layer I'm querying has other criteria (with sdo_num_res I get less than the desired results as SDO_NN runs before pure textual condition). Unfortunately, through ArcGIS Server the results I'm getting back are incorrect and it's missing an obvious nearest feature. To make matters worse, when I tested out a sample WHERE clause in ArcMap it also displayed the wrong set of features, but when I then did a "select all" features, the highlights were clustered around the centre point of the SDO_NN.

Here's the sample SDO_NN WHERE clause:

SDO_NN(SHAPE, MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(357582,174474,0),NULL,NULL), 'sdo_batch_size=0') = 'TRUE' AND ROWNUM <= 10


We have also tried the following, containing a sub-query, which appears to give the right results, but takes ages:

MI_PRINX IN (SELECT OBJECTID FROM COMMUNITY.CV_COMMUNITY_VENUES WHERE SDO_NN(CV_COMMUNITY_VENUES.SHAPE, MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(357582,174474,0),NULL,NULL), 'sdo_batch_size=0') = 'TRUE') AND ROWNUM <= 10


Incidentally, the source layer either has a definition query, or is a view, containing the condition specifying that a publish flag field is true, as some data cannot be published yet.



If anyone has had experience of this, has an example of successful use of this, or knows of a way of getting ArcGIS Server to return the nearest X features relative to a point, please comment.

Outcomes