Working with Oracle Spatial


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:

Show Geometry in KeepTool

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:

sdo geometry

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:

KML File

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:

Paterson Park Google Earth

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
http://www.oracle.com/technetwork/database/options/spatialandgraph/downloads/navteq-lic-168395.html . 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:

  • Create a user named WORLD_SAMPLE and grant it unlimited quota on the USERS tablespace.
  • Ensure that the datafile of the USERS tablespace is able to auto-extend up to a maximum of 5 GB to hold the data.
  • Import the dump file from the CMD command line
    imp WORLD_SAMPLE/WORLD_SAMPLE@orcl file=world_sample2010.dmp log=world_sample.log full=y
    KeepTool has tested the import with both Oracle 11g Release 2 and an Oracle 12c pluggable database.
  • Connect as user WORLD_SAMPLE and execute the following SQL statements:
    INSERT INTO user_sdo_maps SELECT * FROM sdo_maps;
    INSERT INTO user_sdo_themes SELECT * FROM sdo_themes;
    INSERT INTO user_sdo_styles SELECT * FROM sdo_styles;
    INSERT INTO user_sdo_cached_maps SELECT * FROM sdo_cached_maps;
    COMMIT;

The output from the data import will appear like this:

Data import

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:

SDO Geometry

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):

kml export

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

Germany 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:

  • Create a table column of the MDSYS.SDO_GEOMETRY object data type
  • Insert a record into the view USER_SDO_GEOM_METADATA that describes the coordinate system and the bounds of the spatial column.
  • Create a spatial index.

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

spatial columns world

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

  • Copy a script template to the clipboard. This code template can be used to copy the spatial column configuration of one spatial column to another.
    This includes statements to

    • Drop the existing spatial index
    • Delete and re-insert a row into USER_SDO_GEOM_METADATA
    • Convert the existing data into the specified new co-ordinate system.
    • Re-create the spatial index
  • Perform a KML export. You can specify the dimension limits in Google Earth, especially when these do not include the entire world as in the case of the WORLD_SAMPLE database.
  • Create a new spatial index
  • Rebuild an existing the spatial index.

Summary:

KeepTool Hora provides many useful functions for working with spatial data. By creating KML files, you can quickly show spatial data on a map. What’s more, Hora facilitates the configuration of maintenance operations for spatial columns.


 

We hope that you have found this short introduction to Hora’s support for Oracle Spatial useful. If it has awakened your interest, you may want to check out more about Spatial on the web, as well as the Oracle Spatial Developer’s guide for your version of Oracle. There, you will also find ideas for accessing spatial data in PL/SQL.

Let us know what you think of this article by emailing us at news@keeptool.com. We hope to see you again soon in a future article on another KeepTool feature.