sarahjackel
asked on
Insert errors with SDO_GEOMETRY
hello,
I'm trying to use PL SQL to insert a feature into an Oracle Spatial table and am getting the following error messages:
ORA-29875: Failed in the execution of the ODCINDEXINSERT routine
ORA-13364: layer dimensionality does not match geometry dimensions
ORA-06512: at "MDSYS.SDO_IDX", line 167
ORA-06512: at "MDSYS.SDO_IDEX_METHOD_10l
I think the second error (13364) is the most telling. I got my coordinates from the map as lat/long. I see that it needs to be Cartesian-coordinate data. Am I already in this or do I need to convert the values? The map is in Nad 83 State Plane Colorado North.
thanks for your help!
Sarah
INSERT INTO OVERVIEW_LT (
FEATUREID,
CLASSID,
REVISIONNUMBER,
GEOMETRY,
VERSION,
NEXTVER,
DELSTATUS)
VALUES (
220563,
183,
0,
SDO_GEOMETRY(
2003, -- two-dimensional polygon
40990, -- the Spatial Reference Id (SRID), leave null for unspecified
NULL, -- I'm not sure
SDO_ELEM_INFO_ARRAY(1,1003
SDO_ORDINATE_ARRAY(-104.4,
-- define rectangle (lower left and upper right) with
-- Cartesian-coordinate data
),
'0',
-1,
10)
I'm trying to use PL SQL to insert a feature into an Oracle Spatial table and am getting the following error messages:
ORA-29875: Failed in the execution of the ODCINDEXINSERT routine
ORA-13364: layer dimensionality does not match geometry dimensions
ORA-06512: at "MDSYS.SDO_IDX", line 167
ORA-06512: at "MDSYS.SDO_IDEX_METHOD_10l
I think the second error (13364) is the most telling. I got my coordinates from the map as lat/long. I see that it needs to be Cartesian-coordinate data. Am I already in this or do I need to convert the values? The map is in Nad 83 State Plane Colorado North.
thanks for your help!
Sarah
INSERT INTO OVERVIEW_LT (
FEATUREID,
CLASSID,
REVISIONNUMBER,
GEOMETRY,
VERSION,
NEXTVER,
DELSTATUS)
VALUES (
220563,
183,
0,
SDO_GEOMETRY(
2003, -- two-dimensional polygon
40990, -- the Spatial Reference Id (SRID), leave null for unspecified
NULL, -- I'm not sure
SDO_ELEM_INFO_ARRAY(1,1003
SDO_ORDINATE_ARRAY(-104.4,
-- define rectangle (lower left and upper right) with
-- Cartesian-coordinate data
),
'0',
-1,
10)
Last Comment
That insert ran fine for me. Are you sure all of your values are correct? Are you substituting literal values in your example here, where you have variables in your application code?
ASKER
I am running the code directly in SQL developer as written above. Still not working.
I created the table in oracle by adding the feature through Autodesk Map 3D. How did you create your table to test? I wonder if this has something to do with it.
Or perhaps that it is a version enabled table?
I created the table in oracle by adding the feature through Autodesk Map 3D. How did you create your table to test? I wonder if this has something to do with it.
Or perhaps that it is a version enabled table?
I just guessed at your column types. This is what I ran.
create table overview_lt (
featureid number,
classid number,
revisionnumber number,
geometry sdo_geometry,
version number,
nextver number,
delstatus number)
create table overview_lt (
featureid number,
classid number,
revisionnumber number,
geometry sdo_geometry,
version number,
nextver number,
delstatus number)
ASKER
Any clues about the table being created through autodesk map?
here is the SQL associated with the table:
REM START MORGANSPATIAL OVERVIEW_LT
CREATE TABLE "MORGANSPATIAL"."OVERVIEW_
( "FEATUREID" NUMBER(20,0) NOT NULL ENABLE,
"CLASSID" NUMBER(20,0) NOT NULL ENABLE,
"REVISIONNUMBER" NUMBER NOT NULL ENABLE,
"GEOMETRY" "MDSYS"."SDO_GEOMETRY" ,
"VERSION" NUMBER(*,0),
"NEXTVER" VARCHAR2(500 BYTE),
"DELSTATUS" NUMBER(*,0),
"LTLOCK" VARCHAR2(100 BYTE),
CONSTRAINT "GK_OVERVIE7_FEATUREID_0" PRIMARY KEY ("VERSION", "FEATUREID")
USING INDEX PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE NOVALIDATE
) PCTFREE 5 PCTUSED 90 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
REM END MORGANSPATIAL OVERVIEW_LT
REM START MORGANSPATIAL OVERVIEW_PKI$
CREATE INDEX "MORGANSPATIAL"."OVERVIEW_
PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
REM END MORGANSPATIAL OVERVIEW_PKI$
REM START MORGANSPATIAL OVERVIEWGEOMETRY0
CREATE INDEX "MORGANSPATIAL"."OVERVIEWG
INDEXTYPE IS "MDSYS"."SPATIAL_INDEX" PARAMETERS ('sdo_indx_dims=2 tablespace=USERS initial=4K next=20K minextents=1 maxextents=9999 pctincrease=50');
REM END MORGANSPATIAL OVERVIEWGEOMETRY0
here is the SQL associated with the table:
REM START MORGANSPATIAL OVERVIEW_LT
CREATE TABLE "MORGANSPATIAL"."OVERVIEW_
( "FEATUREID" NUMBER(20,0) NOT NULL ENABLE,
"CLASSID" NUMBER(20,0) NOT NULL ENABLE,
"REVISIONNUMBER" NUMBER NOT NULL ENABLE,
"GEOMETRY" "MDSYS"."SDO_GEOMETRY" ,
"VERSION" NUMBER(*,0),
"NEXTVER" VARCHAR2(500 BYTE),
"DELSTATUS" NUMBER(*,0),
"LTLOCK" VARCHAR2(100 BYTE),
CONSTRAINT "GK_OVERVIE7_FEATUREID_0" PRIMARY KEY ("VERSION", "FEATUREID")
USING INDEX PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE NOVALIDATE
) PCTFREE 5 PCTUSED 90 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
REM END MORGANSPATIAL OVERVIEW_LT
REM START MORGANSPATIAL OVERVIEW_PKI$
CREATE INDEX "MORGANSPATIAL"."OVERVIEW_
PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;
REM END MORGANSPATIAL OVERVIEW_PKI$
REM START MORGANSPATIAL OVERVIEWGEOMETRY0
CREATE INDEX "MORGANSPATIAL"."OVERVIEWG
INDEXTYPE IS "MDSYS"."SPATIAL_INDEX" PARAMETERS ('sdo_indx_dims=2 tablespace=USERS initial=4K next=20K minextents=1 maxextents=9999 pctincrease=50');
REM END MORGANSPATIAL OVERVIEWGEOMETRY0
ASKER
Some more information...
I found this decription of my error at: http://www.allinterview.com/showanswers/39246.html
Cause: The spatial layer has a geometry with a different
dimensions than the dimensions specified for the layer.
Action: Make sure that all geometries in a layer have the
same dimensions and that they match the dimensions in the
SDO_DIM_ARRAY object for the layer in the
USER_SDO_GEOM_METADATA view.
Do you see a problem with my SQL code that makes sense with cause/action?
I found this decription of my error at: http://www.allinterview.com/showanswers/39246.html
Cause: The spatial layer has a geometry with a different
dimensions than the dimensions specified for the layer.
Action: Make sure that all geometries in a layer have the
same dimensions and that they match the dimensions in the
SDO_DIM_ARRAY object for the layer in the
USER_SDO_GEOM_METADATA view.
Do you see a problem with my SQL code that makes sense with cause/action?
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
THANK YOU!!!!!
As soon as I followed your advice to add the z coords and insert directly into the Overview table (not the _lt table) it worked. Very much appreciate your help.
Sarah
here is my final working insert statement:
INSERT INTO OVERVIEW (
FEATUREID,CLASSID,REVISION
OVERVIEW_SEQUENCE.nextval , 183, 0,
SDO_GEOMETRY(
3003,
40990,
NULL,
SDO_ELEM_INFO_ARRAY(1,1003
SDO_ORDINATE_ARRAY(
3531254.5636777421, 1303991.2361145422, 0, -- X1, Y1
3531254.5636777421, 1285841.7505334711, 0, -- X1, Y2
3509541.4748271788, 1285841.7505334711, 0, -- X2, Y2
3509541.4748271788, 1303991.2361145422, 0, -- X2, Y1
3531254.5636777421, 1303991.2361145422, 0 -- X1, Y1
)
));
As soon as I followed your advice to add the z coords and insert directly into the Overview table (not the _lt table) it worked. Very much appreciate your help.
Sarah
here is my final working insert statement:
INSERT INTO OVERVIEW (
FEATUREID,CLASSID,REVISION
OVERVIEW_SEQUENCE.nextval , 183, 0,
SDO_GEOMETRY(
3003,
40990,
NULL,
SDO_ELEM_INFO_ARRAY(1,1003
SDO_ORDINATE_ARRAY(
3531254.5636777421, 1303991.2361145422, 0, -- X1, Y1
3531254.5636777421, 1285841.7505334711, 0, -- X1, Y2
3509541.4748271788, 1285841.7505334711, 0, -- X2, Y2
3509541.4748271788, 1303991.2361145422, 0, -- X2, Y1
3531254.5636777421, 1303991.2361145422, 0 -- X1, Y1
)
));
Oracle Database
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
