I registered few 3d features in GSA (Powermap v8i v08.11.07.97) from existing Oracle 10g database. To connect to database I'm using oracle client 11.1. When I'm executing query database from view Powermap is creating sql similar to:
SELECT GGAZ.PEGAZ_ODCINEK_GAZ_LN.G3E_FID ,'GGAZ_PEGAZ_ODCINEK_GAZ_LN' ,GGAZ.PEGAZ_ODCINEK_GAZ_LN.G3E_FID ,GGAZ.PEGAZ_ODCINEK_GAZ_LN.G3E_CID ,GGAZ.PEGAZ_ODCINEK_GAZ_LN.G3E_CNO ,GGAZ.PEGAZ_ODCINEK_GAZ_LN.G3E_FNO ,GGAZ.PEGAZ_ODCINEK_GAZ_LN.G3E_ID ,GGAZ.PEGAZ_ODCINEK_GAZ_LN.LTT_DATE ,GGAZ.PEGAZ_ODCINEK_GAZ_LN.LTT_ID ,GGAZ.PEGAZ_ODCINEK_GAZ_LN.LTT_STATUS ,GGAZ.PEGAZ_ODCINEK_GAZ_LN.LTT_TID ,GGAZ.PEGAZ_ODCINEK_GAZ_LN.ZNACZNIK_IMPORTU ,GGAZ.PEGAZ_ODCINEK_GAZ_LN.G3E_GEOMETRY FROM GGAZ.PEGAZ_ODCINEK_GAZ_LN
WHERE
SDO_FILTER ( GGAZ.PEGAZ_ODCINEK_GAZ_LN.G3E_GEOMETRY,
SDO_GEOMETRY(3007, 2178, NULL, SDO_ELEM_INFO_ARRAY(1, 1006, 6, 1, 1003, 1, 16, 1003, 1, 31, 1003, 1, 46, 1003, 1, 61, 1003, 1, 76, 1003, 1), SDO_ORDINATE_ARRAY( 7427165,38323555, 5488881,03895589, -100, 7427165,38323555, 5489403,20002557, -100, 7427887,22169813, 5489403,20002557, -100, 7427887,22169813, 5488881,03895589, -100, 7427165,38323555, 5488881,03895589, -100, 7427165,38323555, 5488881,03895589, -100, 7427887,22169813, 5488881,03895589, -100, 7427887,22169813, 5488881,03895589, 1000, 7427165,38323555, 5488881,03895589, 1000, 7427165,38323555, 5488881,03895589, -100, 7427887,22169813, 5488881,03895589, -100, 7427887,22169813, 5489403,20002557, -100, 7427887,22169813, 5489403,20002557, 1000, 7427887,22169813, 5488881,03895589, 1000, 7427887,22169813, 5488881,03895589, -100, 7427887,22169813, 5489403,20002557, -100, 7427165,38323555, 5489403,20002557, -100, 7427165,38323555, 5489403,20002557, 1000, 7427887,22169813, 5489403,20002557, 1000, 7427887,22169813, 5489403,20002557, -100, 7427165,38323555, 5489403,20002557, -100, 7427165,38323555, 5488881,03895589, -100, 7427165,38323555, 5488881,03895589, 1000, 7427165,38323555, 5489403,20002557, 1000, 7427165,38323555, 5489403,20002557, -100, 7427165,38323555, 5488881,03895589, 1000, 7427887,22169813, 5488881,03895589, 1000, 7427887,22169813, 5489403,20002557, 1000, 7427165,38323555, 5489403,20002557, 1000, 7427165,38323555, 5488881,03895589, 1000)), 'querytype=WINDOW') = 'TRUE'
Oracle returns error ORA-13369: niepoprawna wartość etype w formacie 4-cyfrowym format
Table has primary index and spatial index defined as
CREATE INDEX "GGAZ"."PEGAZ_ODCINEK_GAZ_LN_SIDX" ON "GGAZ"."PEGAZ_ODCINEK_GAZ_LN" ( "G3E_GEOMETRY" ) INDEXTYPE IS "MDSYS"."SPATIAL_INDEX" PARAMETERS ('LAYER_GTYPE=MULTICURVE SDO_INDX_DIMS=3');and contains 3d elements, spatial index is valid
Could anyone tell me what im doing wrong? I need very very much this functionality because table contains 250k+ elements.
Looks like there is a incomplete or wrongly defined geometry for the type defined in the record. You are defining a 3007 etype and the rest of teh SDO_GEOMETRY record may not fit this definition. Please make sure this is correct.
From Oracle online documentation......
1007
n = 1 or 3
Solid consisting of multiple surfaces that are completely enclosed in a three-dimensional space, so that the solid has an interior volume. A solid element can have one exterior surface defined by the 1006 elements and zero or more interior boundaries defined by the 2006 elements. The value n in the Interpretation column must be 1 or 3.
Subsequent triplets in the SDO_ELEM_INFO array describe the exterior 1006 and optional interior 2006 surfaces that make up the solid element.
If n is 3, the solid is an optimized box, such that only two three-dimensional points are required to define it: one with minimum values for the box in the X, Y, and Z dimensions and another with maximum values for the box in the X, Y, and Z dimensions. For example: SDO_GEOMETRY(3008, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1007,3), SDO_ORDINATE_ARRAY(1,1,1, 3,3,3))
SDO_GEOMETRY(3008, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1007,3), SDO_ORDINATE_ARRAY(1,1,1, 3,3,3))
but problem is that entire sql query is generated by Powermap Database Query tool with chosen view as area, when I select all elements query runs fine and elements are generated correctly. If there is any place where I can take some control above generation process pls point me to it.
Ok, good information concerning the view vs. all records.
Most generally, when a view will not retrieve, but all records do, it means that the spatial index was created, but did not compile or index correctly. I have seen this as incorrect data in the geometry column. There are Oracle SDO routines that can be ran against a table to check to see if the sdo_geometry information is correct. These are well documented online:
docs.oracle.com/.../toc.htm
You can try defining a 2D spatial index even on 3D data. Maybe that will work better.
Hope this helps,
Martin
I've seen this several times, both in good? old GeoGraphics and in Bentley Map. In my case (if I remember correct) the filter query used the wrong srid (NULL in my case). Could it be that your file has a different coordinate system?
Regards,
Krister
Owner consultant at Surell Consulting AB