1

I'm working on a project to link existing entities in an Oracle database with their geolocation, but the database is a tabular not spatial. Is there a compatible type that can store the geolocation or another way to serialize the geodata and store it in a column with a suitable datatype?

I did search for suitable datatypes but all say that its SDO_GEOMETRY which is not found in the tabular Oracle.

  • 1
    Welcome to GIS SE. As a new user please take the Tour. – Vince Dec 12 '16 at 12:27
2

Well, you can still just store the location information (I am assuming you mean long/lat GPS coordinates) as two X and Y (or longitude and latitude) number columns. Or you could also use a text string encoded in GeoJSON. Or an XMLTYPE holding a GML encoding.

Those are all possible and legal, and fine if all you want is to store the coordinates, without doing any sort of processing in the database. If you want to perform any kind of spatial querying or processing (like: searching for entities that are within some distance of another, measure the distance between entities etc) then, as Vince indicated, you need to use some specialized data type. The simplest (and cheapest: it's free) is to use Oracle's native spatial type called SDO_GEOMETRY.

That type comes with Oracle Locator, the free part of Oracle Spatial. It is installed by default in all variants of the Oracle Database (including the free Express Edition). If it does not exist in your database, that means that the DBA (or whoever created it) did an explicit choice of NOT including it in the database. To add it, just ask him/her to use the DBCA (Database Configuration Assistant) to add it.

1

Oracle Spatial is not required (or even used) to store simple geometries, but Locator is (part of Intermedia). If you want to store geometries as spatially indexed objects, you need to use the the RDBMS' native storage protocol (or a third-party one like Esri's SDE.ST_GEOMETRY). While some GIS packages can treat X And Y columns as event layers, these are not true spatial types (no index), and shouldn't be considered equivalent. In reality, there is no such thing as "tabular Oracle", and with the Spatial Types and Functions addition to the SQL standard, failure to install Locator could be considered sabotage to the Oracle installation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.