>> Integrating Oracle Spatial with Google Earth

Integrating Oracle Spatial with Google Earth

Learn how to use Oracle Locator/Oracle Spatial, GeoServer, and Google Earth to create a seamless, robust system for location-enabled BI.

The world is flocking to location technologies. Whether people are searching for their houses on Google Earth, tracking their kids with GPS-enabled cell phones, or utilizing the inherent location-enabling capabilities built in to their enterprise Oracle architectures, it seems that not only is everyone interested in location but their lives and businesses also depend on it. However, as with any other set of burgeoning technologies, as technology choices grow, so does the number of data formats and complexities. This is where the open source community comes in.

The open source community for geospatial technologies is not unlike other open source communities. It is fed by academics, engineers, and architects with the common purpose of creating powerful and easy-to-use frameworks and technologies that help bridge the gaps created by commercial resources.

For instance, when it comes to sharing data across the digital divide, with or without maps, Confluence GeoServer - an open source server that can be used to connect disparate geospatial data sources - excels. GeoServer developers consider their project the ideal "glue" for the geospatial Web.

GeoServer is amazingly simple in principle. Say you have some data stored in an Oracle Spatial database and you want to publish that data to the Web in a format that can be used by other software packages, such as Google Earth. To do this, you could certainly define an XML stylesheet or some other output process to translate the source data into KML (Google Earth's standard markup language). However, to do this effectively, you would probably want to account for all of the known geometry types as well as any metadata and/or data associated with the geometries (the stuff that really matters).

For a one-off project, this might not be so bad, but if you wanted to apply your homegrown transformation engine to several data sets and data designs, you would have to do a lot of work to first create a transformation framework capable of handling all of the complexities of both your data source and Google Earxth and then handling the actual transformations.

And what do you do when your needs or data (sources and output) changes? GeoServer makes this entire process a lot easier. By providing basic connectivity to Oracle Locator/Oracle Spatial feature tables in Oracle Database, GeoServer can perform simple Oracle Spatial bounding box queries (primary-filter queries), transform the results into KML, and provide the KML output as a service for Goggle Earth to render on a map. This is an attractive, low-cost solution for simple geospatial queries.

This article shows you how to use Oracle Locator/Oracle Spatial, GeoServer, and Google Earth to create a seamless, robust system for location-enabled business intelligence. In the end, I hope you can come away from this exercise with a better understanding of how to use open source technologies to extend the way you use commercial technologies.

Oracle Locator/Oracle Spatial Primer
One of the most powerful but least understood features of the Oracle relational database management system (Oracle Express Edition through Oracle Enterprise Edition) is Oracle Locator. By definition, "Oracle Locator is a feature of Oracle Database 10g Standard and Enterprise Editions that provides core location functionality needed by most customer applications." However, Oracle Locator offers a lot more than this. At face value, Oracle Locator gives users the option of storing location information (geospatial or otherwise), such as longitudes and latitudes, in the same tables and rows as the rest of data. Yet Oracle Locator goes much further: using this standard feature, users can also perform location analysis on the same data.

So when you simply want to return all information about something that happens to exist within some distance of something else, why go to a map or a GIS? Oracle Locator can do this for you right in the database. And, of course, with regards to Oracle Spatial (an option of Oracle Enterprise Edition), the rabbit hole gets deeper-much deeper. Fundamentally, Oracle Locator and Oracle Spatial are really the same. They share the same core object type (SDO_GEOMETRY) as well as the same metadata and indexing scheme.

However, whereas Oracle Locator provides impressive core location analysis functionality (such as the ability to find all the data that has some kind of topological relationship to other data), Oracle Spatial builds on top of this the capability to store and manage image and gridded raster data and metadata; create and analyze linear-referenced, network, and topology data models; turn text-based address information into longitude/latitude with geocoding; provide driving directions via an integrated routing engine; and perform deep, multidimensional spatial analysis and mining on location and other data. The name of the game for both Oracle Locator and Oracle Spatial is that data and analyses are available to any client that can connect to and query from an Oracle database.

Load the Counties Data Set
The sample data set included in the sample code, counties.dmp, comprises two tables, COUNTIES and STATES, as well as Oracle Spatial metadata and associated indexes. To load this data set into your Oracle database, do the following:

Log in to your Oracle database instance as a user with system privileges: $> SQLPLUS system/password.

Create a new database user (call it whatever you like-I call mine ORAGIS): SQL> create user oragis identified by oragis.

Grant your new user resource and connect privileges: SQL> grant resource,connect to oragis.
Log out of Oracle: SQL> exit.

Import the COUNTIES dump (export) file into the ORAGIS (or your user's) schema: $> imp oragis/oragis file=counties.dmp full=y.

More Info: http://www.oracle.com/technology/pub/articles/lokitz-spatial-geoserver.html