Working with Oracle Spatial

30 Jun 2015

Oracle Spatial provides powerful features for working with spatial data such as points and polygons. It is implemented in the MDSYS schema. As long as you have licensed Oracle’s spatial option, KeepTool can help you take full advantage of it.

Let’s start Hora and connect to the OE sample schema. The CUSTOMERS.CUST_GEO_LOCATION column instantiates the MDSYS.SDO_GEOMETRY type and contains geometrical data. Provided that you are using a SQL*Net connection rather that a direct TCP/IP connection (which does not support object types), you can see this column’s data content:

The grid cells of the spatial data column are read-only and contain a compact representation of the spatial data within the object type structure. Using the context menu that appears when clicking the ellipsis with the right mouse button, you can select the “Show Geometry” menu item. This opens a modal dialog that shows the spatial data structure in detail:

As you can see, the data is a two-dimensional point expressed as WGS84 coordinates. The “SQL” field at the bottom converts the data to a SQL expression. You can simply copy it to the SQL scratchpad and execute it there as part of a SELECT statement:

The same data and context menu items appear in the results grid. Now select the “Create KML file” menu item. This shows a “File save” dialog to let you choose a name for the KML file and optionally open it using your operating system’s default application for KML files, which might be Google Earth, as shown below:

The yellow pin shows the location of the pin on the aerial view of Baltimore, Maryland. You can include the locations from all rows of the data set into the KML file by using the context menu of the grid rather than that of the data cell.

For a more complex example, you can download a NAVTEQ sample database from . After accepting the license terms, choose to download the 767 MB ZIP file that contains, among other things,  a readme file, two scripts for importing data and for deleting it later, and a DMP file in Oracle 10 imp/exp-Format that is almost 2 GB in size.

The basic steps for getting the sample into your database are:

The output from the data import will appear like this:

After executing the above inserts as user WORLD_SAMPLE, when you view the MAP_WORLD table in Hora’s data content browser, you will see some more complex spatial data. We have already seen points in the OE schema; now we can also visualize polygons that enclose areas:

You can view these in Google Earth as well. Just filter the contents to continent = ‘Europe’ (eliminating ‘Bermuda’ from the above example) and run the “KML export” menu item on the grid’s context menu (not the data cell’s):

After giving the KML file a name, we can see the data in Google Earth:

When you click an outlined shape, a popup window provides information from other fields of the database record. In this example, we’ve clicked on the outline of Germany.

Finally, we would like to draw your attention to some configuration steps necessary for working with spatial data. There are three steps required to support a new spatial column in the database:

Hora’s Schema | Spatial Columns sheet provides an overview of all spatial columns of a given schema, including information about metadata and indexes.

The grid view lets you compare the coordinate system and dimension information of the columns with one another and monitor the status of the spatial index.

In addition, the grid’s context menu lets you

Back to the overview