cx-oracle-users

[cx-oracle-users] SDO_GEOMETRY data type

[Mikel L. <mla...@co...>]

Hi:

I have a table with a lot of geographical data and some of those data is 
stored in a field of type SDO_GEOMETRY.

When I launch a select on the table, I get "cx_Oracle.NotSupportedError: 
Variable_TypeByOracleDataType: unhandled data type 108", so I suposse 
that cx_Oracle cannot handle this kind of data.

Is there any plan to support this data type?

I'm using Python 2.3.4, cx_Oracle 4.0.1 and Oracle 10.1.0.3.0.

Thank you,


-- 
Mikel Larreategi
mla...@co...

CodeSyntax
Azitaingo Industrialdea 3 K
E-20600 Eibar
Tel: (+34) 943 82 17 80

Re: [cx-oracle-users] SDO_GEOMETRY data type

[Anthony T. <ant...@gm...>]

Yes, there are plans to support such data types but they are not very
firm. I haven't personally had any need for such data types yet and
the project I am working on right now is taking a lot of time -- so it
hasn't been a priority. Patches (as always) are welcome. :-)

On 6/30/05, Mikel Larreategi <mla...@co...> wrote:
> Hi:
>=20
> I have a table with a lot of geographical data and some of those data is
> stored in a field of type SDO_GEOMETRY.
>=20
> When I launch a select on the table, I get "cx_Oracle.NotSupportedError:
> Variable_TypeByOracleDataType: unhandled data type 108", so I suposse
> that cx_Oracle cannot handle this kind of data.
>=20
> Is there any plan to support this data type?
>=20
> I'm using Python 2.3.4, cx_Oracle 4.0.1 and Oracle 10.1.0.3.0.
>=20
> Thank you,
>=20
>=20
> --
> Mikel Larreategi
> mla...@co...
>=20
> CodeSyntax
> Azitaingo Industrialdea 3 K
> E-20600 Eibar
> Tel: (+34) 943 82 17 80
>=20
>=20
> -------------------------------------------------------
> SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
> from IBM. Find simple to follow Roadmaps, straightforward articles,
> informative Webcasts and more! Get everything you need to get up to
> speed, fast. http://ads.osdn.com/?ad_id=3D7477&alloc_id=3D16492&op=3Dclic=
k
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] SDO_GEOMETRY data type

[Mikel L. <mla...@co...>]

Anthony Tuininga wrote:
> Yes, there are plans to support such data types but they are not very
> firm. I haven't personally had any need for such data types yet and
> the project I am working on right now is taking a lot of time -- so it
> hasn't been a priority. Patches (as always) are welcome. :-)

Thanks for your answer.

I've never written C extensions for Python, so I don't think I could do 
it now... Perhaps if I had more time to do it... but now I can't do it, 
and I just need to access those data. I'll ask DBA to change the 
datatype or to export those data as string if it's possible.


Thank you,


-- 
Mikel Larreategi
mla...@co...

CodeSyntax
Azitaingo Industrialdea 3 K
E-20600 Eibar
Tel: (+34) 943 82 17 80

[cx-oracle-users] Re: SDO_GEOMETRY data type

[Jani T. <re...@lu...>]

Mikel Larreategi kirjoitti:
> Anthony Tuininga wrote:
> 
>> Yes, there are plans to support such data types but they are not very
>> firm. I haven't personally had any need for such data types yet and
>> the project I am working on right now is taking a lot of time -- so it
>> hasn't been a priority. Patches (as always) are welcome. :-)
> 
> 
> Thanks for your answer.
> 
> I've never written C extensions for Python, so I don't think I could do 
> it now... Perhaps if I had more time to do it... but now I can't do it, 
> and I just need to access those data. I'll ask DBA to change the 
> datatype or to export those data as string if it's possible.

Well you can get around this problem by using Well-known Text format 
(WKT), since Oracle 10g supports that (You're using that right?). So, 
instead getting SDO_GEOMETRY column use column.GET_WKT() you can also 
save data in WKT format. IIRC there is also support for WKB, which is 
bit faster but needs more parsing since it's binary. With Oracle 9i + 
Spatial add-on you're out of luck.

I.e.:   SELECT ID, GEOMLOC.GET_WKT() FROM GEODATA_TABLE;

-- 

Jani Tiainen

Re: [cx-oracle-users] Re: SDO_GEOMETRY data type

[Mikel L. <mla...@co...>]

Jani Tiainen wrote:
> Well you can get around this problem by using Well-known Text format=20
> (WKT), since Oracle 10g supports that (You're using that right?). So,=20
> instead getting SDO_GEOMETRY column use column.GET_WKT() you can also=20
> save data in WKT format. IIRC there is also support for WKB, which is=20
> bit faster but needs more parsing since it's binary. With Oracle 9i +=20
> Spatial add-on you're out of luck.
>=20
> I.e.:   SELECT ID, GEOMLOC.GET_WKT() FROM GEODATA_TABLE;
>=20

Yes, I'm using "Oracle Database 10g Enterprise Edition Release=20
10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining=20
options".

When I make a query in the way you say, I get the following (I've used=20
SQLPlus to make this test, but in Python the error is the same):

SQL> select geom_wgs84.GET_WKT(), id_postal from n_edifgen_3d where=20
id_postal between 10000 and 10005;
select geom_wgs84.GET_WKT(), id_postal from n_edifgen_3d where id_postal=20
between 10000 and 10005
        *
ERROR en l=EDnea 1:
ORA-00904: "GEOM_WGS84"."GET_WKT": identificador no v=E1lido


Nevertheless, if I query the DB in this way, I get the SDO_GEOMETRY=20
datatype as ExternalLobVar object, and I can read it as string (now in=20
Python):

 >>> import cx_Oracle as db
 >>> con =3D cb.connect('user', 'mysecretpassword')
 >>> cur =3D con.cursor()
 >>> cur.execute('select a.GEOM_WGS84.GET_WKT(), a.id_postal from=20
n_edifgen_3d a where id_postal between 10000 and 10005')
[<CLOBVar object at 0x40167930>, <NumberVar object at 0x4016a0b0>]
 >>> cur.description
[('A.GEOM_WGS84.GET_WKT()', <type 'CLOBVar'>, -1, 4000, 0, 0, 1),=20
('ID_POSTAL', <type 'NumberVar'>, 127, 22, 0, -127, 1)]
 >>> b =3D cur.fetchone()
 >>> b
(<ExternalLobVar object at 0x40162060>, 10000.0)
 >>> b[0].read()
'POINT (-2.35566829112215 43.2908549259473)'

The difference is that I 'rename' the table for the query (select ...=20
from n_edifgen_3d *a*), like I read at http://tinyurl.com/99o3v

Thanks for your help Jani.


--=20
Mikel Larreategi
mla...@co...

CodeSyntax
Azitaingo Industrialdea 3 K
E-20600 Eibar
Tel: (+34) 943 82 17 80

Re: [cx-oracle-users] Re: SDO_GEOMETRY data type

[Mikel L. <mla...@co...>]

Mikel Larreategi wrote:
>  >>> import cx_Oracle as db
>  >>> con = cb.connect('user', 'mysecretpassword')
>  >>> cur = con.cursor()
>  >>> cur.execute('select a.GEOM_WGS84.GET_WKT(), a.id_postal from 
> n_edifgen_3d a where id_postal between 10000 and 10005')
> [<CLOBVar object at 0x40167930>, <NumberVar object at 0x4016a0b0>]
>  >>> cur.description
> [('A.GEOM_WGS84.GET_WKT()', <type 'CLOBVar'>, -1, 4000, 0, 0, 1), 
> ('ID_POSTAL', <type 'NumberVar'>, 127, 22, 0, -127, 1)]
>  >>> b = cur.fetchone()
>  >>> b
> (<ExternalLobVar object at 0x40162060>, 10000.0)
>  >>> b[0].read()
> 'POINT (-2.35566829112215 43.2908549259473)'

I can also get the info in this way:

 >>> cur.execute('select a.geom_wgs84.sdo_point.X, 
a.geom_wgs84.sdo_point.Y from n_edifgen_3d a where id_postal between 
10000 and 10005')
[<NumberVar object at 0x4016a140>, <NumberVar object at 0x4016a188>]
 >>> cur.fetchone()
(-2.35566829112215, 43.290854925947293)


-- 
Mikel Larreategi
mla...@co...

CodeSyntax
Azitaingo Industrialdea 3 K
E-20600 Eibar
Tel: (+34) 943 82 17 80

[cx-oracle-users] Re: SDO_GEOMETRY data type

[Jani T. <re...@lu...>]

Mikel Larreategi kirjoitti:
> Mikel Larreategi wrote:
> 
>>  >>> import cx_Oracle as db
>>  >>> con = cb.connect('user', 'mysecretpassword')
>>  >>> cur = con.cursor()
>>  >>> cur.execute('select a.GEOM_WGS84.GET_WKT(), a.id_postal from 
>> n_edifgen_3d a where id_postal between 10000 and 10005')
>> [<CLOBVar object at 0x40167930>, <NumberVar object at 0x4016a0b0>]
>>  >>> cur.description
>> [('A.GEOM_WGS84.GET_WKT()', <type 'CLOBVar'>, -1, 4000, 0, 0, 1), 
>> ('ID_POSTAL', <type 'NumberVar'>, 127, 22, 0, -127, 1)]
>>  >>> b = cur.fetchone()
>>  >>> b
>> (<ExternalLobVar object at 0x40162060>, 10000.0)
>>  >>> b[0].read()
>> 'POINT (-2.35566829112215 43.2908549259473)'
> 
> 
> I can also get the info in this way:
> 
>  >>> cur.execute('select a.geom_wgs84.sdo_point.X, 
> a.geom_wgs84.sdo_point.Y from n_edifgen_3d a where id_postal between 
> 10000 and 10005')
> [<NumberVar object at 0x4016a140>, <NumberVar object at 0x4016a188>]
>  >>> cur.fetchone()
> (-2.35566829112215, 43.290854925947293)

Sure it works that way, at least for points. But WKT works for almost 
anykind of shapes - points, shapes, linestrings, geometry collections.

With CLOB's you have to remember to read var in temporary structure in 
python if you need to work with multiple values, otherwise that 
externallobvar is not valid after next fetchone().

And best of that is you can really insert WKT back to Oracle.

-- 

Jani Tiainen

Re: [cx-oracle-users] Re: SDO_GEOMETRY data type

[Mikel L. <mla...@co...>]

Jani Tiainen wrote:
> 
> Sure it works that way, at least for points. But WKT works for almost 
> anykind of shapes - points, shapes, linestrings, geometry collections.

Ok. For the moment I just have to handle with points, but I will take 
into account your comment about WKT.

> 
> With CLOB's you have to remember to read var in temporary structure in 
> python if you need to work with multiple values, otherwise that 
> externallobvar is not valid after next fetchone().

Yes, I realized about this some months ago, when I had to fetch photos 
from a table in the same database.

Thanks,

-- 
Mikel Larreategi
mla...@co...

CodeSyntax
Azitaingo Industrialdea 3 K
E-20600 Eibar
Tel: (+34) 943 82 17 80