Database Software

Hi Joe,

When you pass the query window in, it should have the SDO_SRID field filled in unless the geometry layer you are querying against doesn't have it set. So try the following:
select srid from user_sdo_geom_metadata where table_name='your_table_name' and column_name='geometry_column_name';
Whatever value is returned should be used in the SDO_SRID field of the SDO_GEOMETRY query window that is passed into the operator.

As a quick point of interest: Oracle Spatial/Locator does not require matching SDO_SRID values between the query window and the spatial layer. Advanced applications can make use of this functionality. The requirement is:
If the layer SDO_SRID is null, the SDO_GEOMETRY SDO_SRID must be NULL.
If the layer SDO_SRID is georeferenced (projected or geodetic), the query window SDO_SRID must be georeferenced.
If the layer SDO_SRID is local (or non-georeferenced), the SDO_SRID of the query window must also be local.

Hey dan. Thanks for responding so abruptly. I queried my database as you recommended and discovered that the SRID value for all my spatial tables is 8307. I then updated the window query so that instead of holding null for the SRID it now holds 8307. Again my application appears to work fine for some queries but not for all of them. I now get the following error when I perform certain window queries:

java.sql.SQLException: ORA-29902: error in executing ODCIIndexStart() routine
ORA-13373: Element of type Extent is not supported for Geodetic data
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_9I", line 368
ORA-06512: at line 1

I am a little baffled by this. Have you any suggestions on the matter. Thanks once again, Joe

Hi Joe,

In Oracle9i, the optimized rectangle type is not supported for geodetic data. There are other possible issues also with geodetic data (for instance, no polygon can be greater than 1/2 the surface area of the earth).

In Oracle9i, there is a function:
sdo_cs.viewport_transform (geom, to_srid)
That function takes as input an optimized rectangle with SRID set to 0, and the SRID to put the optimized rectangle in.
If the SRID is associated with projected data or is null, all the function does is return the geometry back with SDO_SRID set to the value of the passed in parameter.
If the SRID is associated with a geodetic coordinate system, spatial will densify along lines of latitude, and if the geometry is > 1/2 the surface area of the earth it will break it up.

here is an example usage:
SELECT a.state
FROM geod_states a
WHERE sdo_filter(
a.geom,
sdo_cs.viewport_transform(
mdsys.sdo_geometry(2003, 0, NULL,
mdsys.sdo_elem_info_array(1,1003,3),
mdsys.sdo_ordinate_array(-90,35,-75,45)),
8307),
'querytype=window') = 'TRUE';


In Oracle Spatial 10g, there is no longer a need for sdo_cs.viewport_transform - if you specify the optimized rectangle type directly in spatial in a geodetic coordinate system, a viewport_transform-like operation is automatically done under the covers.

Note because the returned geometry will be in a different format (may be a multi-polygon, and will not necessarily be valid for all usage), Oracle recommends using viewport_transform only with SDO_FILTER, and SDO_RELATE with the anyinteract mask, as well as SDO_NN and SDO_WITHIN_DISTANCE.

Hope this helps.

Hi dan once again. Cheers for the advice. I inserted the sdo_cs.viewport_transform statement into each of my window/rectangle queries and now they are all working perfectly. However, I am now encountering a different error when I try to perform point queries in the same application. Again, annoyingly, the point queries work fine for certain queries but not for all. Essentially what I am doing is selecting a point on my map (inside a polygon) and passing this point value(lat, lon) into my query building mechanism. Unfortunately I get the following error for SOME of the point queries:

java.sql.SQLException: ORA-29902: error in executing ODCIIndexStart() routine
ORA-01476: divisor is equal to zero
ORA-06512: at "MDSYS.SDO_CS", line 222.

A sample query would be structured as follows:

query = "select geom from States where SDO_FILTER(geom, (MDSYS.SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(x1,y1, NULL),NULL, NULL)), 'QUERYTYPE=WINDOW')='TRUE'";

What I find perplexing is the fact thayt it works fine for some queries but not all and I cannot figure out why it gives me the above error and when it will give me the error. Any ideas? Thanks, Joe

P.S. If it results in the above error after I click at a certain point and then I click on that point again it sometimes works on the second click. This just adds to my confusion!

Hi Joe,
To debug this last error could you change your app to log the queries to find out what you are sending to spatial. I would have to guess there is something malformed with the query point you are passing in (just a guess though). I haven't seen this error under these circumstances before.
If you can trace the query right as it is being sent to the database that would be best.
Regards,
dan
ps - viewport transform is for 1,1003,3 element types only - it won't work with points. You probably aren't doing this, but it is worth checking.

Hi all,

I am encountering the same error but I'm unable to change the SDO_FILTER statement as this is generated by a third party product. Is there anything I can do in the database that will allow me to perform a filter with a null SRID?

The feedback that I'm getting from MapBuilder is:

FINEST [oracle.sdovis.theme.pgtp] [ RDSIDENTLSFCE ]: 469707.1567164179,402999.0,469994.84328358213,403099.0
FINEST [oracle.sdovis.theme.pgtp] [ RDSIDENTLSFCE ]: SELECT ROWID, POLYGON, 'C.NATURALSURFACEFILL', null, 'null', -1 FROM OSMM2.TOPOGRAPHICAREA WHERE MDSYS.SDO_FILTER(POLYGON, MDSYS.SDO_GEOMETRY(2003, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 3), MDSYS.SDO_ORDINATE_ARRAY(?, ?, ?, ?)), 'querytype=WINDOW') = 'TRUE' AND (DESCRIPTIVEGROUP = 'Roadside' AND OSMMSTYLE_NAME = 'naturalSurfaceFill' )
......
ERROR [oracle.sdovis.theme.pgtp] *** Exception while querying theme: RDSIDENTLSFCE
ERROR [oracle.sdovis.theme.pgtp] java.sql.SQLException: ORA-29902: error in executing ODCIIndexStart() routine
ORA-13208: internal error while evaluating [window SRID does not match layer SRID] operator
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 286
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
...

Thought I'd update everyone with my findings here.

We have a user called "OS" who has read only access to the GIS tables belonging to user "OSMM2". We are trying to connect both Map Builder and a proprietary product to the "OS" user and set up our styles and themes under that user.

Turns out the error I was getting above was because Map Builder (and the proprietary product that also got the same error) couldn't find the correct entry in the USER_SDO_GEOM_METADATA table.

I had originally set up the OS user's user_sdo_theme with a table_name of "OSMM2.TOPOGRAPHICAREA", but the entry in all_sdo_geom_metadata was just "TOPOGRAPHICAREA" (with an owner of "OSMM2").

To correct this I created a public synonym for TOPOGRAPHICAREA and also inserted into the OS user's USER_SDO_GEOM_METADATA view the values (SELECT * FROM ALL_SDO_GEOM_METADATA WHERE OWNER = 'OSMM2').

Now the OS user can retrieve the correct SRID for the given table and hence the error about not having the correct SRID disappears.