Oracle Spatial features supported by KeepTool

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 the Oracle 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 in Hora’s data content browser:

The grid cells of the Oracle 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
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:

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:

• 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.

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.

That is great support for working with Oracle Spatial.

See how it works!

Explore possibilities of KeepTool.

Try for free