1 2 3 Previous Next 35 Replies Latest reply: Mar 3, 2006 10:38 AM by jsharma-Oracle Go to original post RSS
      • 15. Re: ora-29902
        476843
        While using the georaptor extension I add my data layer to the geospatial view. I get no message until I try to zoom to layer.
        I then get - SpatialView error
        Sql error java.sqlsqlexception: ORA-29902: error in executing
        ODCIndexStart() routine
        ORA-13208: internal error while evaluating [window SRID does not match layer
        SRID] operator
        ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10l", line 391
        ORA-06512: at line 1

        Geometry column: SHAPE
        SQL: SELECT t.SHAPE FROM PONCACITYLIMITS t
        WHERE SDO_FILTER(t.SHAPE,MDSYS.SDO_GOEOMETRY(2003,NULL,NULL,
        MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
        MDSYS.SDO_ORDINATE_ARRAY(?,?,?,?)),'querytype=WINDOW')='TRUE'

        How do I get these two SRIDs to match? Where are these two SRIDs stored?
        • 16. Re: ora-29902
          91061
          all of the data in poncacity_limits has an associated srid. It is the same for each geometry, and is stored in each geometry, and it must match the srid stored in user_sdo_geom_metadata for that table.

          In the query window below, you have specified a null srid (the value after 2003). This might be better set to the srid of the data layer.

          SELECT t.SHAPE FROM PONCACITYLIMITS t
          WHERE SDO_FILTER(t.SHAPE,MDSYS.SDO_GOEOMETRY(2003,NULL,NULL,
          MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
          MDSYS.SDO_ORDINATE_ARRAY(?,?,?,?)),'querytype=WINDOW')='TRUE'
          • 17. Re: ora-29902
            476843
            Hi Dan,
            Am I correct in assuming that the query being run here from georaptor cannot be run from sqlplus because of the line 4 statement with querytype=window and the window values are not being passed in the query?
            SQL> MDSYS.SDO_ORDINATE_ARRAY(?,?,?,?)), 'querytype = WINDOW') = 'TRUE';
            when I run the statement from sqlplus I get
            ERROR at line 4:
            ORA-00911: invalid character
            Would the ordinate array parameters be the min max xy values from the layer?
            • 18. Re: ora-29902
              91061
              usually this kind of query is executed to show the data in an area of interest (maybe a for display purposes).

              In this case, the values would be set to the lower left (long, lat) upper right (long, lat) of the data coordinates that map to the screen.
              • 19. Re: ora-29902
                476843
                I'm so confused. I don't know how to determine if my spatial index is 100% accurate. all the software I see it in says it is valid. (I think this has to do with up-to-date?) If I use java OEM (9.2)spatial index advisor, I see the data just fine. I do get a message that I can't add other data because it must be the same extents. (All of my SDE data has the same min max extents.)

                If I view data in esri the initial view is ok but any movement or other actions causes a failed drawing of citylimits.

                If I view my data in geo-raptor I can get the initial view and then if I choose update metadata I get ora-29902
                If I view in mapguide I get ora-29902
                I have submitted oracle tar, autodesk support requests, and esri technical support requests.
                When I am successful with this one data layer I need to repeat with 100s more.
                I can't find a way to be successful with this one data layer.
                Please help.
                • 20. Re: ora-29902
                  91061
                  Hi Brad,

                  Let's take a deep breath and figure out where we are! You are trying to get three things going simultaneously, none of which are "simple". Lets focus on one at a time.

                  Spatial - you have to get this working first, and feel comfortable that it is working.
                  You have some data that relates to Northern OK (SRID 41106).

                  You set the SDO_SRID in each geometry to 41106.
                  You set the SRID in the USER_SDO_GEOM_METADATA view to 41106.

                  You built the index.

                  To test if the index is working, you can do something like this.

                  Log into Oracle as the table owner of PONCACITYLIMITS.

                  SELECT OBJECTID
                  FROM PONCACITYLIMITS
                  WHERE (MDSYS.SDO_FILTER(PONCACITYLIMITS.SHAPE,
                  SDO_GEOMETRY(2003,41106,NULL,
                  SDO_ELEM_INFO_ARRAY(1,1003,3),
                  SDO_ORDINATE_ARRAY(-53051137.835721,20622178.905232, 60426619.065681,15056436.190447)),'querytype=window') = 'TRUE');

                  This will tell you whether things will work in spatial or not.
                  • 21. Re: ora-29902
                    476843
                    SQL> SELECT OBJECTID
                    2 FROM PONCACITYLIMITS
                    3 WHERE (MDSYS.SDO_FILTER(PONCACITYLIMITS.SHAPE,
                    4 SDO_GEOMETRY(2003,41106,NULL,
                    5 SDO_ELEM_INFO_ARRAY(1,1003,3),
                    6 SDO_ORDINATE_ARRAY(-53051137.835721,20622178.905232, 60426619.065681,150564
                    36.190447)),'querytype=window') = 'TRUE');

                    no rows selected
                    • 22. Re: ora-29902
                      91061
                      OK, now lets try this:

                      select sdo_tune.extent_of('PONCACITYLIMITS','SHAPE')
                      from dual;

                      It looks like I missed a (-) when I copied the query from much higher, try this:
                      SELECT OBJECTID
                      FROM PONCACITYLIMITS
                      WHERE (MDSYS.SDO_FILTER(PONCACITYLIMITS.SHAPE,
                      SDO_GEOMETRY(2003,41106,NULL,
                      SDO_ELEM_INFO_ARRAY(1,1003,3),
                      SDO_ORDINATE_ARRAY(-53051137.835721,-20622178.905232, 60426619.065681,15056436.190447)),'querytype=window') = 'TRUE');

                      Message was edited by:
                      Dan Abugov
                      • 23. Re: ora-29902
                        476843
                        Success!
                        OBJECTID
                        ----------
                        3
                        7
                        6
                        322
                        321
                        8
                        2
                        1
                        11
                        4
                        5

                        11 rows selected
                        • 24. Re: ora-29902
                          91061
                          Taking this one step at a time, since the ESRI folks have given you a step-by-step methodology, lets now try to feel comfortable that it works:

                          1. Execute 'sdelayer -o delete' to unregister the layer from ArcSDE.
                          2. Execute 'sdetable -o unregister' to drop the table registration from ArcSDE.

                          NOTE: Do not execute 'sdetable -o delete' as this will physically drop the data. Also, the metadata will have been dropped after executing these commands and will need to be re-inserted. This is currently a known issue with ArcSDE.

                          3. Reinsert the metadata for this layer.
                          4. Rebuild the spatial index.
                          5. Register the data again with ArcSDE using 'sdelayer -o register'.

                          Let us know how you make out with this. Again, lets go one step at a time....
                          • 25. Re: ora-29902
                            476843
                            the sdelayer command worked ok. the sdetable command returns this.
                            ?

                            C:\Documents and Settings\Administrator>sdetable -o unregister -t PONCACITYLIMITS -u xxx -p xxx


                            ArcSDE 9.1 Oracle9i Build 1351 Wed Nov 9 11:02:02 PST 2005
                            Attribute Administration Utility
                            -----------------------------------------------------
                            Delete registration for table PONCACITYLIMITS. Are you sure? (Y/N): y
                            Error: Object can't be deleted -- other object(s) depend on it (-222).
                            Error: Unable to delete registration for table PONCACITYLIMITS

                            Should I go on to the next step? or is there something I need to do here?
                            Another thought.
                            what portion of the following should be used to update the metadata?
                            UPDATE PONCACITYLIMITS S set s.SHAPE.SDO_SRID = 41106
                            UPDATE USER_SDO_GEOM_METADATA SET SRID=41106 WHERE TABLE_NAME='PONCACITYLIMITS'
                            UPDATE USER_SDO_GEOM_METADATA SET DIMINFO = MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',1975700,2441700,4608.334),MDSYS.SDO_DIM_ELEMENT('Y',445600,911600,4608.334)) WHERE TABLE_NAME = 'PONCACITYLIMITS'

                            Message was edited by:
                            Brad Nesom
                            • 26. Re: ora-29902
                              91061
                              Hi Brad,

                              I'm not an SDE person so I was hoping someone else would be able to help.

                              However, my guess is there will be a problem unless the commands work.

                              So I would try this:

                              1) Try dropping the spatial index
                              drop index A42_IX1;

                              Try again to unregsiter the table. My guess is you won't be able to move forward unless the table is unregistered.

                              If you still can't unregister the table, try deleting the metadata in user_sdo_geom_metadata as well:

                              delete from user_sdo_geom_metadata were table_name='PONCACITYLIMITS' and column_name='SHAPE';

                              Now try unregistering the table.

                              Hopefully everything will be OK.

                              To reinsert the metadata try the following:

                              insert into USER_SDO_GEOM_METADATA values ('PONCACITYLIMITS',
                              'SHAPE', SDO_DIM_ARRAY(
                              SDO_DIM_ELEMENT('X',1975700,2441700,4608.334),
                              SDO_DIM_ELEMENT('Y',445600,911600,4608.334)),41106);

                              When it comes time to recreate the index you should be able to do this:

                              CREATE INDEX A42_IX1 on PONCACITYLIMITS (SHAPE)
                              indextype is mdsys.spatial_index;

                              I hope this helps.
                              • 27. Re: ora-29902
                                476843
                                There is also a single column index on objectid. Probably drop that also before deleting the metadata?
                                Also when re-inserting is there any problem with using the same scale as what other featuresets are using? This feature set has... 1975700,2441700,4608.334
                                scale in other data is 1975700,2441700,.000005

                                I really appreciate you hanging in and teaching me so much.
                                • 28. Re: ora-29902
                                  Ivan Bush
                                  Brad,

                                  You need to get both of the SDE commands to work before you can go forward with this. I am not sure what the error message -222 means as I have not seen it before.

                                  Dan is right in saying that the best way to attack this is to do it bit by bit. My suggestion is to copy PONCACITYLIMITS to a new table and go through the who;e process again. Check that each step works after step is done.

                                  You are not trying to do anything that has not been done successfully before. Hence I know it will work.

                                  Best of luck.

                                  Ivan
                                  • 29. Re: ora-29902
                                    476843
                                    Dan,
                                    I dropped both the spatial and single column indices. Still can't unregister using sdetable command.
                                    When I run the sql provided I get no rows..
                                    Dan said. "If you still can't unregister the table, try deleting the metadata in user_sdo_geom_metadata as well:"

                                    delete from user_sdo_geom_metadata where table_name='PONCACITYLIMITS' and column_name='SHAPE';
                                    0 rows deleted.
                                    What does this statement delete? Does it need to be something like "delete * from"?