Re: [cx-oracle-users] SDO_GEOMETRY parser (Anthony Tuininga)

[Tibor A. <ti...@in...>]

On Wed, Nov 12, 2008 at 5:03 PM, Paul Dziemiela <pdz...@in...>wrote:

> Hi there,
>
> I may not have been reading these threads closely enough.  Anthony wants to
> pass out Oracle Spatial objects whole via cx_Oracle?


He already does. I'll attach my test script, I hope it explains how to use
the SdoGeometry class.



>
>
> That sounds great but my first question is how?
>
> The SDO_GEOMETRY object is an object comprised of
> NUMBER,
> NUMBER,
> SDO_POINT object
> SDO_ELEM_INFO_ARRAY object
> SDO_ORDINATE_ARRAY object
>
> The array objects are just VARRAYs of NUMBER.
> So how would you pass this stuff out through cx_Oracle again?  I looked at
> the code but I don't quite get how the data comes across from Oracle.
>
> The only array/list thing I thought that was supported was an associative
> array indexed by BINARY_INTEGER.


As said, that is already taken care of. There is convenient access to all
the elements of the object and also their nested values..VARRAYS converted
to lists, oracle numbers to floats.


>
>
> The way I would normally do this would be to convert the SDO_GEOMETRY on
> the
> Oracle side first to WKT, WKB, KML or GML and then pass it to cx_Oracle as
> a
> blob and then use python tools to parse or display it.


Yes, there is the SDO_UTIL package with TO_WKTGEOMETRY, TO_GMLGEOMETRY . The
biggest disadvantage is that it's implemented in Java inside Oracle and
therefore not available in Oracle XE. I'm not sure about license for Oracle
Locator (Standard Edition). And It's probably also slower. Also I think at
least TO_GMLGEOMETRY crashes on Geometry Collections.

My test script for my  SdoGeometry object follows:
import cx_Oracle
import simplejson
from shapely.geometry import asShape
from parsegeom import SdoGeometry

con = cx_Oracle.connect("uas/uas@iimsdemo")
cur = con.cursor()

test_data =
["MDSYS.SDO_GEOMETRY(2002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(-560704.155,-1265827.399,-560772.91,-1265669.043,-560831.806,-1265531.747))",
"MDSYS.SDO_GEOMETRY(2002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(-552205.845,-1274831.432,-552318.748,-1274649.987))",
"""MDSYS.SDO_GEOMETRY(2007,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1,9,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(0,0,10,0,10,10,0,0,20,0,40,0,40,40,40,20,20,0))""",
'MDSYS.SDO_GEOMETRY(2007,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1,9,1003,1),MDSYS.SDO_ORDINATE_ARRAY(1,1,10,1,10,10,1,1,11,1,20,1,20,10,11,1))',
'MDSYS.SDO_GEOMETRY(2006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,5,2,1),MDSYS.SDO_ORDINATE_ARRAY(-572493.608498,-1278953.984257,-572442.021442,-1278884.175201,-572450.088024,-1278878.444985,-572393.993456,-1278794.902054))',
"""SDO_GEOMETRY(
    2003,  -- two-dimensional polygon
    NULL,
    NULL,
    SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,1), -- polygon with hole
    SDO_ORDINATE_ARRAY(2,4, 4,3, 10,3, 13,5, 13,9, 11,13, 5,13, 2,11, 2,4,
        7,5, 7,10, 10,10, 10,5, 7,5)
  )""",
"""SDO_GEOMETRY(
      2001,
      NULL,
      SDO_POINT_TYPE(12, 14, NULL),
      NULL,
      NULL)""",
"MDSYS.SDO_GEOMETRY(2006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,7,2,1),MDSYS.SDO_ORDINATE_ARRAY(-565103.996283,-1286038.100927,-565074.954589,-1286088.923892,-565055.247725,-1286123.843072,-565110.565238,-1286159.453721,-565016.352599,-1286098.777324))"]

for sel in test_data:
    cur.execute('select '+sel+' from dual')
    test_geom = cur.fetchone()[0]

    p=SdoGeometry(test_geom) # the conversion

    print "dimensions:"+str(p.dimensions())

    print 'SDO_ORDINATES:'+str(p.SDO_ORDINATES)

    print 'particular:' + str(p.get_quadruplets())

    print 'to_geo: ' + simplejson.dumps(p.__geo_interface__) #printing
geojson

    print 'wktfrom shapely: ' + asShape(p).wkt # printing wktgeometry, the
numbers are too long.

    print 'bounds shapely: ' + str(asShape(p).bounds) #determining MBR

    cur.execute('select SDO_UTIL.TO_WKTGEOMETRY('+sel+') FROM DUAL') # the
Oracle version of WKT for comparison
    wktstring = cur.fetchone()[0]
    print "wktfrom oralce:"+str(wktstring)

    print '-----\n'