Support for 3D Oracle Spatial Geometries

[Michael Sexton]

Hi

I have recently started using Django and tried to use it with our legacy Oracle Spatial database. The geometries we store in Oracle are all 3D and as such don’t work with Django. It boils down to this:

select sdo_util.to_wktgeometry(sdo_geometry(3001, null, SDO_POINT_TYPE(120,-30, 0), null, null))  from dual;

Yielding the following error:

ORA-13199: 3D geometries are not supported by geometry WKB/WKT generation.

ORA-06512: at "MDSYS.MD", line 1723

ORA-06512: at "MDSYS.MDERR", line 17

ORA-06512: at "MDSYS.SDO_UTIL", line 2509

However, as I discovered, all is not lost. A similar query:

select sdo_util.to_gmlgeometry(sdo_geometry(3001, null, SDO_POINT_TYPE(120,-30, 0), null, null))  from dual;

Delivers something we can work with:

<gml:Point srsName="SDO:" xmlns:gml="http://www.opengis.net/gml"><gml:coordinates decimal="." cs="," ts=" ">120.0,-30.0,0.0 </gml:coordinates></gml:Point>

I am new to Python and Django, so I looked through the code, forked the Django project, and made five changes:

Call to Oracle uses SDO_UTIL.TO_GMLGEOMETRY to output GML:

https://github.com/comtesacristain/django/commit/4a5732d3069fc648b887c7c7000e99f45fe60d56

GML regex (this probably needs work as I am terrible at regexes):

https://github.com/comtesacristain/django/commit/96d06f8392432121610a07ed8d7a1fc4c7f5aa8c

Accessed part of the GDAL C libraries to read from GML:

https://github.com/comtesacristain/django/commit/3cefe8651a8df60bbade66ec0410c67ee2bde1d9

Checked against the GML regex and used GDAL module

https://github.com/comtesacristain/django/commit/2eaad60782f2d72836b026bd2cca0d9a1cfff541

Part of GDAL module which makes the call to the GDAL C API

https://github.com/comtesacristain/django/commit/ad5808a5b4b0b7e6b1efb4d29dc09e7d15a344b7

I was wondering what it would take to have these made part of the Django project? I am not the greatest coder, but I thought these might help with what is currently a bug in the use of Django for 3D spatial geometries in Oracle.

Best Regards

Michael Sexton.

[Claude Paroz]

Hi Michael,

At least, I found nothing shocking in your patches, so in my non-oracle-savvy eyes, this looks rather fine. It would then be interesting to see if some tests from django.contrib.gis.tests.geo3d.tests can pass with Oracle.
Please create a ticket and link a pull request with your proposal, and we'll be able to further discuss this issue.

Claude

[Jani Tiainen]

Hi,

We ran exactly same situation and we actually wrote did a slightly different solution - we now do have (yet to be opensourced) a backend that actually reads SDO_GEOMETRY directly and writes a string that resembles SDO_GEOMETRY and is parsed to real SDO_GEOMETRY with small PL/SQL package utility function.

Advantages for that approach is that it's fastest solution so far (Apparently Oracle context switch between pl/sql and java is dead slow) and it works even on Oracle XE versions. Downside is of course requirement to install one time pl/sql package.

Now there are few caveats depending on version of Oracle you use. Oracle does return a faulty geometries some times, specially for extents, those may (or may not) pass as GML.

--

Jani Tiainen

On Mon, 3 Nov 2014 16:37:04 +1100
Michael Sexton <mick....@gmail.com> wrote:

> Hi
>
> I have recently started using Django and tried to use it with our legacy Oracle Spatial database. The geometries we store in Oracle are all 3D and as such don’t work with Django. It boils down to this:
>
> select sdo_util.to_wktgeometry(sdo_geometry(3001, null, SDO_POINT_TYPE(120,-30, 0), null, null)) from dual;
>
> Yielding the following error:
>
> ORA-13199: 3D geometries are not supported by geometry WKB/WKT generation.
> ORA-06512: at "MDSYS.MD", line 1723
> ORA-06512: at "MDSYS.MDERR", line 17
> ORA-06512: at "MDSYS.SDO_UTIL", line 2509
>
> However, as I discovered, all is not lost. A similar query:
>
> select sdo_util.to_gmlgeometry(sdo_geometry(3001, null, SDO_POINT_TYPE(120,-30, 0), null, null)) from dual;
>
> Delivers something we can work with:
>

> <gml:Point srsName="SDO:" xmlns:gml="http://www.opengis.net/gml <http://www.opengis.net/gml>"><gml:coordinates decimal="." cs="," ts=" ">120.0,-30.0,0.0 </gml:coordinates></gml:Point>

>
> I am new to Python and Django, so I looked through the code, forked the Django project, and made five changes:
>
> Call to Oracle uses SDO_UTIL.TO_GMLGEOMETRY to output GML:

> https://github.com/comtesacristain/django/commit/4a5732d3069fc648b887c7c7000e99f45fe60d56 <https://github.com/comtesacristain/django/commit/4a5732d3069fc648b887c7c7000e99f45fe60d56>

>
> GML regex (this probably needs work as I am terrible at regexes):

> https://github.com/comtesacristain/django/commit/96d06f8392432121610a07ed8d7a1fc4c7f5aa8c <https://github.com/comtesacristain/django/commit/96d06f8392432121610a07ed8d7a1fc4c7f5aa8c>

>
> Accessed part of the GDAL C libraries to read from GML:

> https://github.com/comtesacristain/django/commit/3cefe8651a8df60bbade66ec0410c67ee2bde1d9 <https://github.com/comtesacristain/django/commit/3cefe8651a8df60bbade66ec0410c67ee2bde1d9>

>
> Checked against the GML regex and used GDAL module

> https://github.com/comtesacristain/django/commit/2eaad60782f2d72836b026bd2cca0d9a1cfff541 <https://github.com/comtesacristain/django/commit/2eaad60782f2d72836b026bd2cca0d9a1cfff541>

>
> Part of GDAL module which makes the call to the GDAL C API

> https://github.com/comtesacristain/django/commit/ad5808a5b4b0b7e6b1efb4d29dc09e7d15a344b7 <https://github.com/comtesacristain/django/commit/ad5808a5b4b0b7e6b1efb4d29dc09e7d15a344b7>

>
> I was wondering what it would take to have these made part of the Django project? I am not the greatest coder, but I thought these might help with what is currently a bug in the use of Django for 3D spatial geometries in Oracle.
>
> Best Regards
>
> Michael Sexton.
>
>
>
>

> --
> You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
> To post to this group, send email to django-d...@googlegroups.com.
> Visit this group at http://groups.google.com/group/django-developers.
> To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/A82D27CC-DA81-49B7-9933-040C88821BC9%40gmail.com.
> For more options, visit https://groups.google.com/d/optout.