[GIS] Querying Oracle geometry data type

geometryoracle-dbmssdo-geometrysqlst-geometry

How to query or find the geometry data type a given Oracle Database is using in SQL Developer (i.e. SDO_Geometry, ST_Geometry, etc)?

I thought I could just describe a feature class within the database and get the result, but it just returns the field types. The geometry field just returns 'NUMBER', which isn't helpful.

Best Answer

You can check the SDO metadata table to get spatial information:

select * from MDSYS.ALL_SDO_GEOM_METADATA where TABLE_NAME = 'tablename'

Related Solutions

[GIS] Can’t select geometry from Oracle DB using SQL

Just because Oracle supports the creation of custom objects in the database doesn't mean that all clients will be able to read their values. In this case, your PyODBC client doesn't know how to unpack the SDE.ST_GEOMETRY column type, so it hasn't told Oracle how it will handle geometry, and Oracle is generating the error saying it hasn't been told how to pass ST_GEOMETRY to the PyODBC driver.

There are a number of options open to you. The easiest are to request something the the ODBC client should be able to handle, like a CLOB or BLOB column:

SELECT sde.ST_AsText(shape) FROM usr.feat_class

-or-

SELECT sde.ST_AsBinary(shape) FROM usr.feat_class

ArcGIS 9.3.1 is sufficiently elderly to no longer be supported ("Retired" support status, as of Jan 2014). It's quite possible you may run into bugs in the SDE.ST_GEOMETRY implementation that have been fixed since install. At a minimum, you want to be sure that the terminal service pack (SP2) and various post-SP2 patches have been applied to the local Desktop install, to the application server binaries (if present), and to the listener support libraries that permit SQL*Plus spatial queries using SDE.ST_GEOMETRY.

[GIS] ST_GEOMETRY to SDO_GEOMETRY

One possible solution is to get ESRI's ST_Geometry into a WKT or WKB notation, then use that to construct SDO_GEOMETRY objects. Something like this (assuming table SHAPES contains a column called SHAPE of type sde.ST_Geometry:

select sdo_geometry(sde.st_astext(shape)) from shapes;

You could use that as a view:

create view shapes_sdo as 
select ... sdo_geometry(sde.st_astext(shape)), ...
from shapes;

Now the catch is that this will not have any spatial index, so will be very slow to display and impossible to use with any spatial query. Maybe then you can create a copy instead, in a new table:

create table shapes_sdo as 
select ... sdo_geometry(sde.st_astext(shape)), ...
from shapes;

Then setup the usual metadata and spatial index. That can then be used by any GIS tool on the planet (including all open source tools). If you need to maintain the two copies in sync, just add a trigger to do that.

Best Answer

Related Solutions

[GIS] Can’t select geometry from Oracle DB using SQL

[GIS] ST_GEOMETRY to SDO_GEOMETRY

Related Question