Opened 18 years ago

Last modified 18 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:1 by warmerdam, 18 years ago

Howard, 

Did you update the SRID in the user_sdo_geom_metadata table?  I added the
coordinate system in question to my oracle10g instance and then updated the
user_sdo_geom_metadata entry for that table to use the new srid.  Ogrinfo 
reported the new coordinate system just fine. 

I ran your script (with a few minor alterations) against my layer and it
seemed to work fine. 

So, assuming you set the layer level srid properly, you will need to produce 
a more detailed set of steps for me to try and reproduce your problem. 

comment:2 by hobu, 18 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 warmerdam, 18 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 hobu, 18 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 hobu, 18 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 hobu, 18 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 hobu, 18 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 hobu, 18 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.