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