Opened 19 years ago
Last modified 19 years ago
#717 closed defect (invalid)
OCI and custom SRIDs
Reported by: | hobu | Owned by: | warmerdam |
---|---|---|---|
Priority: | high | Milestone: | |
Component: | OGR_SRS | Version: | unspecified |
Severity: | normal | Keywords: | |
Cc: |
Description
I'm on oracle 9.2. Server is on Solaris 9, and client is win32. I inserted a new SRID into MDSYS.CS_SRS with these properties: CS_NAME: Albers Equal Area (North America USGS) AUTH_SRID:102003 SRID:102003 AUTH_NAME:USGS WKTEXT: PROJCS["Equal-Area Projection USGS (United States)", GEOGCS [ "NAD 83 (Continental US)", DATUM ["NAD 83 (Continental US)", SPHEROID ["GRS 80", 6378137.000000, 298.257222]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]], PROJECTION ["Albers Conical Equal Area"], PARAMETER ["Standard_Parallel_1", 29.500000], PARAMETER ["Standard_Parallel_2", 45.500000], PARAMETER ["Central_Meridian", -96.000000], PARAMETER ["Latitude_Of_Origin", 23.000000], UNIT ["Meter", 1.000000000000]] I loaded in my data and set the sdo_srid of the geometries to 102003. I connected with Python GDAL (CVS about a week old). My script does this: op = drv.Open('OCI:username/password@oranri_design.cssm.iastate.edu:segments_history_test') lyr = op.GetLayer() fea = lyr.GetFeature(0) fea2 = fea.Clone() fea2.SetField(5, 2004) # change one of the values print lyr.GetSpatialRef() # <-- this returns None print lyr.CreateFeature(fea2) # <-- returns code 6 lyr.CommitTransaction() Full Traceback: D:\Python>gdal_oracle.py ----------- OGR Drivers --------------- ESRI Shapefile UK .NTF SDTS TIGER S57 MapInfo File DGN VRT AVCBin REC Memory CS GML ODBC PostgreSQL OCI OCI: Userid=username, Password=password, Database=oranri_design.cssm.iastate.ed OCI: Prepare(SELECT SRID FROM ALL_SDO_GEOM_METADATA WHERE TABLE_NAME = 'segment _history_test' AND COLUMN_NAME = 'SHAPE') OCI: Prepare(SELECT "SHAPE","OBJECTID","POLY_ID","STATE","COUNTY","PSU","YEAR", SURVEY" FROM segments_history_test ) OCI: Prepare(SELECT "SHAPE","OBJECTID","POLY_ID","STATE","COUNTY","PSU","YEAR", SURVEY" FROM segments_history_test ) ------------ segments_history_test Information ------------- OCI: Prepare(SELECT COUNT(*) FROM segments_history_test ) Feature count: 295042 OBJECTID: 387 POLY_ID: 53057_041202R STATE: 53 COUNTY: 57 PSU: 041202R YEAR: 2003 SURVEY: 2003 Spatial Reference: None OCI: Prepare(INSERT INTO segments_history_test (SHAPE, "OBJECTID", "POLY_ID", " TATE", "COUNTY", "PSU", "YEAR", "SURVEY") VALUES (MDSYS.SDO_GEOMETRY(3003,NULL, ULL,:elem_info,:ordinates), 387, '53057_041202R', 53, 57, '041202R', 2004, 2003 ) ERROR 1: ORA-29875: failed in the execution of the ODCIINDEXINSERT routine ORA-13365: Layer SRID does not match geometry SRID ORA-06512: at "MDSYS.SDO_INDEX_METHOD_9I", line 319 ORA-06512: at line 1 in INSERT INTO segments_history_test (SHAPE, "OBJECTID", "POLY_ID", "STATE", " OUNTY", "PSU", "YEAR", "SURVEY") VALUES (MDSYS.SDO_GEOMETRY(3003,NULL,NULL,:ele _info,:ordinates), 387, '53057_041202R', 53, 57, '041202R', 2004, 2003) CreateFeature Return Value: 6
Change History (8)
comment:2 by , 19 years ago
hmm. It's gotta be my layer information that is bad then. Here is what I set the DIMINFO in USER_SDO_GEOM_METADATA: update USER_SDO_GEOM_METADATA set DIMINFO = ( MDSYS.SDO_DIM_ARRAY( MDSYS.SDO_DIM_ELEMENT('X' ,-2477000, 2359015, 1), MDSYS.SDO_DIM_ELEMENT('Y' ,183900, 3126100, 1) ) ) where table_name = 'SEGMENTS_HISTORY_TEST' I also tried setting them to LAT/LON instead of X/Y with no results. The SRID in that table is correctly set to 102003 I ensured that all of the sdo_srid's on the geometries in the SHAPE column of segments_history_test are actually 102003. Is there anywhere else I can look? I can't seem to get the thing to give me a reference. I'm willing to hack some code and do some more detailed debugging if you have any ideas. Thanks Howard
comment:3 by , 19 years ago
Howard, What does a "select * from user_sdo_geom_metadata where table_name = 'segments_history_test'" look like? And the a corresponding select on the mdsys.cs_srs table. eg. SELECT WKTEXT, AUTH_SRID, AUTH_NAME FROM MDSYS.CS_SRS WHERE SRID = 102003; I believe that OGR is inserting new features with the wrong geometry SRID because it is confused about what SRID is in use for the table. So, most likely the srid column in the user_sdo_geom_metadata is set improperly.
comment:4 by , 19 years ago
SELECT WKTEXT, AUTH_SRID, AUTH_NAME FROM MDSYS.CS_SRS WHERE SRID = 102003 WKTEXT AUTH_SRID AUTH_NAME PROJCS["Equal-Area Projection USGS (United States)", GEOGCS [ "NAD 83", DATUM ["NAD 83", SPHEROID ["GRS 80", 6378137.000000, 298.257222]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]], PROJECTION ["Albers Conical Equal Area"], PARAMETER ["Standard_Parallel_1", 29.500000], PARAMETER ["Standard_Parallel_2", 45.500000], PARAMETER ["Central_Meridian", -96.000000], PARAMETER ["Latitude_Of_Origin", 23.000000], UNIT ["Meter", 1.000000000000]] 102003 Oracle select * from user_sdo_geom_metadata where table_name = 'SEGMENTS_HISTORY_TEST' TABLE_NAME COLUMN_NAME DIMINFO SRID SEGMENTS_HISTORY_TEST SHAPE (null) 102003 I noticed if I used your query with the table_name as segments_history_test instead of SEGMENTS_HISTORY_TEST, no records were returned. So, I changed my layer statement to use the uppercase layer name. Now I can properly return a SRID and reference, but still can't insert. output: ----------- OGR Drivers --------------- ESRI Shapefile UK .NTF SDTS TIGER S57 MapInfo File DGN VRT AVCBin REC Memory CSV GML ODBC PostgreSQL OCI OCI: Userid=username, Password=password, Database=oranri_design.cssm.iastate.edu OCI: Prepare(SELECT SRID FROM ALL_SDO_GEOM_METADATA WHERE TABLE_NAME = 'SEGMENTS _HISTORY_TEST' AND COLUMN_NAME = 'SHAPE') OCI: Prepare(SELECT WKTEXT, AUTH_SRID, AUTH_NAME FROM MDSYS.CS_SRS WHERE SRID = 102003) OCI: Prepare(SELECT "SHAPE","OBJECTID","POLY_ID","STATE","COUNTY","PSU","YEAR"," SURVEY" FROM SEGMENTS_HISTORY_TEST ) OCI: Prepare(SELECT "SHAPE","OBJECTID","POLY_ID","STATE","COUNTY","PSU","YEAR"," SURVEY" FROM SEGMENTS_HISTORY_TEST ) ------------ SEGMENTS_HISTORY_TEST Information ------------- OCI: Prepare(SELECT COUNT(*) FROM SEGMENTS_HISTORY_TEST ) Feature count: 295042 OBJECTID: 387 POLY_ID: 53057_041202R STATE: 53 COUNTY: 57 PSU: 041202R YEAR: 2003 SURVEY: 2003 spatial reference: PROJCS["Equal-Area Projection USGS (United States)", GEOGCS["NAD 83", DATUM["NAD 83", SPHEROID["GRS 80",6378137.000000,298.257222]], PRIMEM["Greenwich",0.000000], UNIT["Decimal Degree",0.01745329251994330]], PROJECTION["Albers Conical Equal Area"], PARAMETER["Standard_Parallel_1",29.500000], PARAMETER["Standard_Parallel_2",45.500000], PARAMETER["Central_Meridian",-96.000000], PARAMETER["Latitude_Of_Origin",23.000000], UNIT["Meter",1.000000000000], AUTHORITY["Oracle","102003"]] PROJCS["Equal-Area Projection USGS (United States)", GEOGCS["NAD 83", DATUM["NAD 83", SPHEROID["GRS 80",6378137.000000,298.257222]], PRIMEM["Greenwich",0.000000], UNIT["Decimal Degree",0.01745329251994330]], PROJECTION["Albers Conical Equal Area"], PARAMETER["Standard_Parallel_1",29.500000], PARAMETER["Standard_Parallel_2",45.500000], PARAMETER["Central_Meridian",-96.000000], PARAMETER["Latitude_Of_Origin",23.000000], UNIT["Meter",1.000000000000], AUTHORITY["Oracle","102003"]] OCI: Prepare(INSERT INTO SEGMENTS_HISTORY_TEST (SHAPE, "OBJECTID", "POLY_ID", "S TATE", "COUNTY", "PSU", "YEAR", "SURVEY") VALUES (MDSYS.SDO_GEOMETRY(3003,102003 ,NULL,:elem_info,:ordinates), 387, '53057_041202R', 53, 57, '041202R', 2004, 200 3)) ERROR 1: ORA-29875: failed in the execution of the ODCIINDEXINSERT routine ORA-13364: Layer Dimensionality does not match geometry dimensions ORA-06512: at "MDSYS.SDO_INDEX_METHOD_9I", line 319 ORA-06512: at line 1 in INSERT INTO SEGMENTS_HISTORY_TEST (SHAPE, "OBJECTID", "POLY_ID", "STATE", "C OUNTY", "PSU", "YEAR", "SURVEY") VALUES (MDSYS.SDO_GEOMETRY(3003,102003,NULL,:el em_info,:ordinates), 387, '53057_041202R', 53, 57, '041202R', 2004, 2003) 6 SEGMENTS_HISTORY_TEST Script: import sys sys.path.insert(0,r'd:\cvs\gdal\gdal\pymod') import os os.environ['CPL_DEBUG']='on' import gdal import ogr import osr print '----------- OGR Drivers ---------------' for i in range(ogr.GetDriverCount()): print ogr.GetDriver(i).GetName(), print drv = ogr.GetDriverByName('OCI') op = drv.Open('OCI:username/password@oranri_design.cssm.iastate.edu:SEGMENTS_HISTORY_TEST') state = 19 lyr = op.GetLayer() #lyr =op.ExecuteSQL("SELECT * from segments_history_test where STATE='%s'"%state) fea = lyr.GetFeature(0) geom = fea.GetGeometryRef() def print_layer_info(layer): defn = layer.GetLayerDefn() first_feature = layer.GetFeature(0) print '------------ %s Information -------------' % defn.GetName() print 'Feature count: %s ' % (layer.GetFeatureCount()) for i in range(defn.GetFieldCount()): fld_defn = defn.GetFieldDefn(i) print '%s: %s' % (fld_defn.GetName(), first_feature.GetField(i)) print_layer_info(lyr) drv2 = ogr.GetDriverByName('ESRI Shapefile') ds1 = drv2.CreateDataSource(r'd:\temp\gdal_test_shapes') #ds1.CopyLayer(lyr,'foo2') fea.GetField(5) fea.SetField(5,2004) fea2 = fea.Clone() print 'spatial reference: ',lyr.GetSpatialRef() ##ref = osr.SpatialReference() ##ref.ImportFromWkt("""PROJCS["USA_Contiguous_Albers_Equal_Area_Conic_USGS_version", ##GEOGCS["GCS_North_American_1983", ##DATUM["D_North_American_1983", ##SPHEROID["GRS_1980",6378137.0,298.257222101]], ##PRIMEM["Greenwich",0.0], ##UNIT["Degree",0.0174532925199433]], ##PROJECTION["Albers"], ##PARAMETER["False_Easting",0.0], ##PARAMETER["False_Northing",0.0], ##PARAMETER["Central_Meridian",-96.0], ##PARAMETER["Standard_Parallel_1",29.5], ##PARAMETER["Standard_Parallel_2",45.5], ##PARAMETER["Latitude_Of_Origin",23.0], ##UNIT["Meter",1.0]]""") #ref.SetAuthority( 'GEOGCS', 'USGS', 102003) #fea2.GetGeometryRef().AssignSpatialReference(ref) print fea2.GetGeometryRef().GetSpatialReference() lyr.StartTransaction() print lyr.CreateFeature(fea2) print lyr.GetName() lyr.CommitTransaction()
comment:5 by , 19 years ago
ogr is inserting as sdo_gtype 3003 and my layer geometries are 2003 How can I force the geometries to 2003? I tried GetGeometryRef().FlattenTo2d() to no avail. user_sdo_geom_metadata says nothing about the dimensionality. Is that stored in the indexing or somewhere else?
comment:6 by , 19 years ago
it is stored in MDSYS.SDO_DIM_INFO. If there are two entries, it is 2d. If there are three entries it is 3d. Once I did that, the insert worked. Is there any way to force OGR to use 2d geometries when it tries to insert?
comment:7 by , 19 years ago
If I hack line 442 of ogrociwriteablelayer.cpp to look like: *pnGType = nDimension == 2 ? 2003 : 2003; It forces things to 2d and works.
comment:8 by , 19 years ago
ok, so my hack allows me to insert geometry, but it isn't valid :( Doing a sdo_geom.validate_geometry_with_context(a.shape,b.diminfo) returns code 13011 for the two geometries that I've subsequently inserted with OGR. The original geometry *is* still valid, however. ORA-13011 value is out of range Cause: A specified dimension value is outside the range defined for that dimension. Action: Make sure that all values to be encoded are within the defined dimension range.
Note:
See TracTickets
for help on using tickets.