DOWNLOAD Oracle Database Confluence Geoserver Release 1.5 Google Earth version 3.0+ Sample code

TAGS spatial, gis, All

Integrating Oracle Spatial with Google Earth

by Justin Lokitz

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

Published July 2007

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:

1. Log in to your Oracle database instance as a user with system privileges: $> SQLPLUS system/password.
2. Create a new database user (call it whatever you like-I call mine ORAGIS): SQL> create user oragis identified by oragis.
3. Grant your new user resource and connect privileges: SQL> grant resource,connect to oragis.
4. Log out of Oracle: SQL> exit.
5. Import the COUNTIES dump (export) file into the ORAGIS (or your user's) schema: $> imp oragis/oragis file=counties.dmp full=y.

A Look at Database Objects

At this point, you have what you need in order to start "asking" Oracle Locator interesting questions about the relationships between geospatial and nongeospatial data. Yet before you proceed, take a look at the structures you have added to the database and how they work. To do this

1. Log in to your Oracle database as the user who "owns" the counties data: $> sqlplus oragis/oragis.
2. Perform a DESCRIBE on the counties table to see what it is made of: SQL> desc counties.

The output should look something like this:

As you can see, most of the information in this table consists of text (VARCHAR2) and numbers (NUMBER). However, the GEOM row, which stores the actual ordinates that make up the geometric representations for your counties, is prescribed the MDSYS.SDO_GEOMETRY database type. Taking a closer look at this type, you see that it can be described by the following:

SQL> desc SDO_GEOMETRY

From this description - and the Oracle Spatial User's Guide and Reference - you can determine several characteristics of the data being stored:

• SDO_GTYPE describes the type of geometry (point, line string, or polygon).
• SDO_SRID is used to identify the coordinate system.
• SDO_POINT describes the latitude and the longitude.
• SDO_ELEM_INFO describes how to use the numbers stored in the SDO_ORDINATES section.
• SDO_ORDINATES describes the coordinate values that make up the boundary of a spatial object.

Examining a single column's data....

SQL> select geom from counties where county = 'San Francisco';

GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

SDO_GEOMETRY(2003, 8265, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
SDO_ORDINATE_ARRAY(-122.3915, 37.707813, -122.3916, 37.708401,
-122.38088, 37.710056, -122.37258, 37.718094, -122.36247, 37.715759, -122.35625, 37.730015,
-122.36532, 37.732609....))

you see that whether you are storing points, lines, or polygons (as in this example), all of the data is managed within a single type (and in a single column) without your having to worry about the complications of using binary data; in other words, this is more a white-box than a black-box implementation. Storing the location data in this manner allows for flexibility as well as access via standard access interfaces such as SQL, JDBC, and ODBC.

Oracle Spatial R-Tree Indexing

Oracle Spatial provides powerful indexing called an R-tree index. Per the Oracle Spatial User's Guide and Reference, "a spatial index, like any other index, provides a mechanism to limit searches, but in this case the mechanism is based on spatial criteria such as intersection and containment. A spatial index is needed to:

• Find objects within an indexed data space that interact with a given point or area of interest (window query)
• Find pairs of objects from within two indexed data spaces that interact spatially with each other (spatial join)"

For this reason, when you create tables in Oracle Database that contain location (or geospatial) information, it is a good practice to index the location data by using a spatial index.

Query the Data

Once the data has been loaded, you can perform interesting analysis operations such as the following:

"Give me any counties that are inside, covered by, and/or touch the state of California (which can't be satisfied with a non-spatial query on scalar attribute state)."

select /*+ ordered */ c.COUNTY,c.STATE_ABRV,c.TOTPOP,c.POPPSQMI
from states s, counties c
where s.state = 'California'
and sdo_anyinteract (c.geom, s.geom) = 'TRUE';
	  

Here, you are asking Oracle Database to return all counties that have any kind of topological interaction with the state of California. The result of this query should return counties from California as well as Oregon, Nevada, and Arizona, 74 counties out of 3,300 in the U.S. It is not possible to resolve this query by using only the STATE column in the COUNTIES table, because it will not include counties in other states that adjoin or have any interaction with California.

At this point, you have a great foundation for performing even more interesting, visual analysis based on your COUNTIES table. Before you decide to jump in and simply use Oracle Locator or Oracle Spatial to manage all of your data, you will want to take a look at the following Web sites and documents:

oracle.com/technology/products/spatial/index.html

oracle.com/technology/products/spatial/htdocs/training.html

oracle.com/technology/obe/10gr2_db_vmware/datamgmt/spatial/spatial.htm

Transforming Oracle Data with GeoServer

GeoServer, according to the Confluence Web site, "is an Open Source server that connects your information to the Geospatial Web." In other words, as mentioned earlier in this article, GeoServer can act as a communication bridge between other geospatial services/servers, whereby data formats can be easily and dynamically transformed (on the fly). For our purposes, this is a huge benefit. Whereas you can always write a little bit of code (PL/SQL, Java, PHP) to output Oracle Locator/Oracle Spatial data as Google Earth KML, this is not always practical. Plus, this is where open source servers such as GeoServer shine.

Installing GeoServer

To get started with GeoServer, do the following:

1. Go to the Confluence-GeoServer home page .
2. Read some of the fine print and prerequisites in the Quickstart document.
3. Download and install the newest version of GeoServer.
4. Download and install the newest version of the Oracle DataStore Extension for GeoServer.

Before you do this, you may want to see what GeoServer comes loaded with by default. To do this

• Start GeoServer and navigate to the GeoServer home page: http://localhost:8080/geoserver.
• Click Config : http://localhost:8080/geoserver/config/index.do.
• Log in, using admin/geoserver as the default username/password.
• Click Config again: http://localhost:8080/geoserver/config/index.do.
• Click Data : http://localhost:8080/geoserver/config/data/index.do.
• Click Stores : http://localhost:8080/geoserver/config/data/store.do.
• Click New : http://localhost:8080/geoserver/config/data/storeNew.do.

Note that when you attempt to create a new data store, without having installed the Oracle DataStore Extension for GeoServer first, Oracle connection options do not show up in the list.

Installing Oracle DataStore Extension

To install the Oracle DataStore Extension for GeoServer:

• Unzip geoserver-1.5.0-oracle-plugin.zip into its own directory.
• Copy gt2-oracle-spatial-2.3.1.jar and ojdbc14.jar into the [GEOSERVER_HOME]\Webapps\geoserver\WEB-INF\lib directory.
• Restart GeoServer.
• When you follow the substeps of step 4 above and reach step g, you should see two listings for Oracle in the description list when you attempt to create a new data store.

Configure GeoServer to Query Oracle Database

Once the Oracle DataStore Extension for GeoServer has been successfully installed, you can use GeoServer to query Oracle databases directly.

Create a GeoServer Data Store for Oracle Database

With GeoServer up and running and the Oracle DataStore Extension installed, you are now ready to configure GeoServer to query the COUNTIES table you imported earlier. To do this:

1. Click Config. again: http://localhost:8080/geoserver/config/index.do.
2. Click Data: http://localhost:8080/geoserver/config/data/index.do.
3. Click Stores : http://localhost:8080/geoserver/config/data/store.do.
4. Click New : http://localhost:8080/geoserver/config/data/storeNew.do.
5. Select Oracle from the description list, and name your store oragis (see Figure 1).


Figure 1: Selecting Oracle as the new GeoServer DataStore [located at /technology/pub/images/lokitz-spatial-geoserver-f1.gif; all other images use same naming convention]


6. Leave topp as the default namespace, and fill out the host, port, username, password, and instance (SID or service) information for the Oracle instance where into which you imported the COUNTIES table (see Figure 2).
7. Click Submit.
   
   Figure 2: Creating a new Oracle DataStore within the GeoServer framework



8. Click Apply, in the upper left corner, and then Save. At this point, your data store should have saved and loaded (as an XML file) without incident.

Create a GeoServer Feature Type for Counties

With the data loaded, you will want to create a feature type, a representation of some of the data in your data store. To do this

1. Go to the Config -> Data menu again: http://localhost:8080/geoserver/config/data/index.do
2. Click FeatureType : http://localhost:8080/geoserver/config/data/typeSelect.do.
3. Click New : http://localhost:8080/geoserver/config/data/typeNew.do.
4. Select oragis:::COUNTIES from the Feature Type Name list (see Figure 3).
   
   Figure 3: Selecting the COUNTIES table for your new GeoServer feature



5. Click New.
6. Fill out the feature form with the following values (see Figure 4):
         a. Style: polygon.
         b. SRS: 4326.
         c. Title: COUNTIES.
         d. Bounding Box: click Generate.
         e. Leave the rest of the values as their defaults.
   *Note: Item d configures a primary-filter bounding box query to Oracle Spatial. GeoServer does not currently support common geospatial queries for location intelligence such as "nearest neighbor" or "within distance" or other types of relationship queries. However, Oracle Application Server MapViewer, a feature of every Oracle Application server, fully supports these kinds of queries and more.
7. Click Submit.
   
   Figure 4: Creating a feature type for your COUNTIES data
   
   
8. Click Apply, in the upper left corner, and then Save. At this point, your feature type (metadata) should have saved and loaded (as an XML file) without incident.

Map Styles

You are now ready to test the GeoServer connection to your data store and feature type. At this point, you should be able to output data in several formats, based on your COUNTIES table. However, because you have applied only a simple polygon style to our feature, there may not be much to see. After a quick test of your connections, you will create more-interesting stylization of the data set. For now

1. On the GeoServer home page, click WMS Capabilities : http://localhost:8080/geoserver/wms?service=WMS&request=GetCapabilities.
2. Here you will see an XML document/page with a list of capabilities for your GeoServer implementation. Scroll through the list until you find <Name>topp:COUNTIES</Name>. This will tell you what the WMS server, as part of GeoServer, is able to output (see Figure 5).


Figure 5: GeoServer WMS-XML output

Obtain a Map Image from GeoServer in a Browser

To test your COUNTIES feature, open a new browser window and go to the following URL:

http://localhost:8080/geoserver//wms?service=WMS&request=GetMap&format=image/
png&width=800&height=600&srs=EPSG:4326&layers=topp:COUNTIES
&styles=polygon&bbox=-177.1,13.71,-61.48,76.63

This is a WMS request that tells GeoServer to output an image of type PNG with a width of 800 pixels and a height of 600 pixels, with an SRS (coordinate system) of 4326, for the COUNTIES feature, with a very simple polygon style and within a bounding box of the following dimensions: minimum longitude (-177.1), minimum latitude (13.71), maximum longitude (-61.48), and maximum latitude (76.63).

After processing your request, GeoServer should return an image that looks like Figure 6:

At this point, you are ready to integrate your Oracle table (using a GeoServer feature) with Google Earth. Before you do that, we will explore how to use mapping styles to define a more interesting and descriptive rendering of the counties information. To do so, you need to apply a new style (or Styled Layer Descriptor) to your feature.

Creating a Styled Layer Descriptor

A Styled Layer Descriptor, or SLD, makes your maps colorful. It tells the server how the map should be rendered-for instance, whether to draw lines in black or to color them in blue with a nice outline and a text label. SLD is an XML-based language detailed in an open specification available here. SLD files created for GeoServer can be reused with any Web Mapping Service (WMS)-compliant application (http://docs.codehaus.org/display/GEOSDOC/1.3+Style+Your+Map).

The next step is to create an SLD to stylize the map of U.S. counties. The referenced tutorial on the GeoServer Web site provides detailed instructions. Alternatively, several SLDs that come with the GeoServer installation can be used as templates. One SLD that can serve our needs is called popshade.sld. To see a description of this SLD, do the following:

1. Navigate to the [GEOSERVER_HOME]\data_dir\styles directory.
2. In a text editor, open popshade.sld.

Using SLD Rules and Filters Sections

• Within the <FeatureTypeStyle> tag are several <Rule> sections.
• Within the rule sections, there are filter sections (<ogc:Filter..>) that describe what scalar properties to look for in a given feature as well as what logic to perform. For instance, in the first rule section, there is a filter that defines this rule as being applicable only to data within the feature in which PERSONS is between 2,000,000 and 4,000,000.

<ogc:Filter xmlns:gml="http://www.opengis.net/gml">
   <ogc:PropertyIsBetween>
    <ogc:PropertyName>PERSONS</ogc:PropertyName>
    <ogc:LowerBoundary>
     <ogc:Literal>2000000</ogc:Literal>
    </ogc:LowerBoundary>
    <ogc:UpperBoundary>
     <ogc:Literal>4000000</ogc:Literal>
    </ogc:UpperBoundary>
   </ogc:PropertyIsBetween>
 </ogc:Filter>

Creating an SLD for the COUNTIES Table

Customize the popshade.sld SLD for the COUNTIES table (and COUNTIES feature) by editing the SLD:

1. Save a copy of popshade.sld to another directory (outside of data_dir) as countypopshade.sld.
2. Edit the name, title, and abstract sections: <Name>CountyPopDensity</Name>
3. Create a couple of new rules sections, so that when GeoServer renders the data, it will return five or six shading styles based on POPPSQMI.
4. For the first rule, define a filter that looks at the values of POPPSQMI and applies specific color and text styles based on rows where POPPSQMI is fewer than 10:

<Rule>
    <!-- like a linesymbolizer but with a fill too -->
    <ogc:Filter xmlns:gml="http://www.opengis.net/gml">
    <ogc:PropertyIsLessThan>
    <ogc:PropertyName>POPPSQMI</ogc:PropertyName>
    <ogc:Literal>10</ogc:Literal>
      </ogc:PropertyIsLessThan>
    </ogc:Filter>
    <PolygonSymbolizer>
       <Fill>
          <!-- CssParameters allowed are fill (the color) and fill-opacity -->
          <CssParameter name="fill">#0000FF</CssParameter>
          <CssParameter name="fill-opacity">0.7</CssParameter>
       </Fill>     
    </PolygonSymbolizer>
     <TextSymbolizer>
          <Label>
    	     <ogc:PropertyName>COUNTY</ogc:PropertyName>
          </Label>
          <Font>
    	     <CssParameter name="font-family">Times New Roman</CssParameter>
    	     <CssParameter name="font-style">Normal</CssParameter>
    	     <CssParameter name="font-size">10</CssParameter>
          </Font>
    	  <Fill>
    	    <CssParameter name="fill">#000000</CssParameter>
         </Fill>
    </TextSymbolizer>
</Rule>

5. Next, create several rules with filters where POPPSQMI is between two numbers:

<Rule>
    <!-- like a linesymbolizer but with a fill too -->
    <ogc:Filter xmlns:gml="http://www.opengis.net/gml">
      <ogc:PropertyIsBetween>
        <ogc:PropertyName>POPPSQMI</ogc:PropertyName>
        <ogc:LowerBoundary>
          <ogc:Literal>10</ogc:Literal>
        </ogc:LowerBoundary>
        <ogc:UpperBoundary>
          <ogc:Literal>1000</ogc:Literal>
        </ogc:UpperBoundary>
      </ogc:PropertyIsBetween>
    </ogc:Filter>
    <PolygonSymbolizer>
       <Fill>
        <!-- CssParameters allowed are fill (the color) and fill-opacity -->
        <CssParameter name="fill">#00FF00</CssParameter>
	    <CssParameter name="fill-opacity">0.7</CssParameter>
       </Fill>     
    </PolygonSymbolizer>
    <TextSymbolizer>
	<Label>
	  <ogc:PropertyName>COUNTY</ogc:PropertyName>
       </Label>

      <Font>
	    <CssParameter name="font-family">Times New Roman</CssParameter>
	    <CssParameter name="font-style">Normal</CssParameter>
	    <CssParameter name="font-size">10</CssParameter>
      </Font>
      <Fill>
	    <CssParameter name="fill">#000000</CssParameter>
      </Fill>
    </TextSymbolizer>
 </Rule> 

6. Finally, create a rule for POPPSQMI values greater than 30000:

<Rule>
    <!-- like a linesymbolizer but with a fill too -->
    <ogc:Filter xmlns:gml="http://www.opengis.net/gml">
      <ogc:PropertyIsGreaterThan>
       <ogc:PropertyName>POPPSQMI</ogc:PropertyName>
       <ogc:Literal>30000</ogc:Literal>
      </ogc:PropertyIsGreaterThan>
    </ogc:Filter>
    <PolygonSymbolizer>
       <Fill>
          <!-- CssParameters allowed are fill (the color) and fill-opacity -->
          <CssParameter name="fill">#FF0000</CssParameter>
	      <CssParameter name="fill-opacity">0.7</CssParameter>
       </Fill>     
    </PolygonSymbolizer>
    <TextSymbolizer>
   <Label>
	  <ogc:PropertyName>COUNTY</ogc:PropertyName>
	</Label>

	<Font>
	  <CssParameter name="font-family">Times New Roman</CssParameter>
      <CssParameter name="font-style">Normal</CssParameter>
	  <CssParameter name="font-size">10</CssParameter>
	</Font>
	<Fill>
	  <CssParameter name="fill">#000000</CssParameter>
	</Fill>
     </TextSymbolizer>
</Rule>

7. You can find an example of the completed (well-defined) file in the sample code download.
8. Save your new SLD.

Loading the Counties SLD

1. Navigate to the Config -> Data -> Style page for GeoServer: http://localhost:8080/geoserver/config/data/style.do.
2. Click New to create a new style based on your SLD: http://localhost:8080/geoserver/config/data/styleNew.do .Give the popshade.sld file a new name, countypopdensity (see Figure 7).
   
   Figure 7: Naming a new GeoServer style (SLD)



3. Browse for and select the countypopshade.sld you created (see Figure 8).
4. Click Submit.
   
   Figure 8: Creating a GeoServer style based on your custom SLD



5. Click Apply, in the upper left corner, and then Save. At this point, your style should have saved and loaded (as an XML file) without incident.

Associate the Counties SLD with the Counties Feature

1. Navigate to the Config -> Data -> FeatureType menu.
2. Select oragis:COUNTIES from the Feature Types list, and click Edit.
3. Select countypopdensity from the Style list (see Figure 9).
   
   Figure 9: Updating the COUNTIES feature type with your new style



4. Scroll to the bottom of the page, and click Submit.
5. Click Apply, in the upper left corner, and then Save. At this point, your updated feature type should have saved and loaded (as an XML file) without incident.
6. Now rerun your WMS request, substituting countypopdensity for polygon for the Style parameter:
   http://localhost:8080/geoserver//wms?service=WMS&request=GetMap&format=image/png&width=800&height=600&srs=
   EPSG:4326&layers=topp:COUNTIES&styles=countypopdensity&bbox=-177.1,13.71,-61.48,76.63.
7. The returned image should now look something like Figure 10:


Figure 10: Sample WMS output for the COUNTIES feature type, using your new style

As you can see, GeoServer is using some filters and other metadata to create an Oracle database query that returns a data set suitable for thematic shading. Behind the scenes, the query that is being created to return the result set within the bounding box (-177.1,13.71,-61.48,76.63) is using an Oracle Locator/Oracle Spatial operator called SDO_FILTER. Although there is no easy way to extend GeoServer's WMS server to use more-advanced queries, such as the one you used in the "Query the Data" section, you could certainly use Oracle Spatial and Oracle Application Server MapViewer to develop applications that do allow for advanced queries to be output by use of Oracle Application Server MapViewer's native WMS services.
8. To reflect your own preferences, you may want to use the above procedures to update countypopshade.sld to your own liking.

Now you are ready to integrate your new feature with Google Earth.

Integrating Oracle Locator/GeoServer with Google Earth

With all of the GeoServer-to-Oracle work completed, you are now ready to move onto the final steps whereby you will integrate Oracle with Google Earth via GeoServer.

Use the Google Earth GUI to View Counties Data in Oracle Database

Google Earth provides an inexpensive and flexible way to view not only location information but also the geospatial relationships therein. Just as Oracle Locator and Oracle Spatial can be used to provide server-based geospatial and nongeospatial analysis, Google Earth can be used as a front-end user interface for this kind of information and analysis. However, until now there has been one caveat: if you wanted to marry Oracle Database and Google Earth, you had to either create custom interfaces to Oracle Database that could output KML or KMZ (the Google Earth markup language, uncompressed or compressed) or buy Google Earth Enterprise and utilize the services of the Google Geospatial Sales Engineering team to do the integration for you. You now have a third, somewhat easier, more flexible option: to use GeoServer to output KML/KMZ from Oracle Spatial without having to do the heavy lifting yourself. GeoServer can output KML/KMZ from Oracle Spatial automatically, using either the standard WMS format we saw earlier, whereby the "format" value is changed from PNG to application/vnd.google-earth.kml+XML for example:

http://localhost:8080/geoserver//wms?service=WMS&request=GetMap&format=application/
vnd.google-earth.kmz+XML&width=1024&height=1024&srs=EPSG:4326&layers=topp:COUNTIES
&styles=countypopdensity

Or we can simply use the KML Reflector, another feature of GeoServer, which simply encapsulates most of the information we would include in a WMS call into a simplified URL.

To enable you to do this, there are some great instructions on the GeoServer Web site (docs.codehaus.org/display/GEOSDOC/Google+Earth ), which are condensed here:

1. Start up Google Earth.
2. Move the globe until you are centered on your favorite part of the United States (see Figure 11).
   
   Figure 11: Centering Google Earth on your favorite U.S. location



3. Select Network Link from the Add menu (see Figure 12).
   
   Figure 12: Opening the Google Earth Network Link menu



4. Name your network link something like County Pop Density.
5. Enter the KML/KMZ URL from your GeoServer into the Link field (see Figure 13):
   http://localhost:8080/geoserver/wms/kml_reflect?layers=COUNTIES.
   
   Figure 13: Adding the network link information for your GeoServer instance
   
   Note: according to the GeoServer-Google Earth Web page (noted above), the KML reflector utility has been developed to enable users to easily output KML/KMZ without having to tweak WMS requests.
6. Click OK.
7. Now you should see your counties data placed on top of the Google Earth-supplied data (see Figures 14 and 15). Very cool!
   
   Figure 14: Zoom in/out and pan the map to different regions to see new views of the data
   
   Figure 15: Your Oracle counties data integrated with Google Earth

Auto-Refresh Network Data in Google Earth

You can also set up your network link to periodically refresh its view of the data. To do this

1. Right-click your County Pop Density link in the Places section in Google Earth (see Figure 16).
   
   Figure 16: Editing the network link properties



2. Click the Refresh tab, and toggle some of the refresh options.

Conclusion

As the usefulness of location information increases, so do the technologies capable of using location information at hand. And as with most other technological trends, expanding usage of the data means an increase in the number of applications and data formats, resulting in greater complexity. For instance, even though powerful tools such as Oracle Locator/Oracle Spatial and Google Earth make the data in question incredibly transparent and immediately useful, both tools also add complexity to the overall architecture, because they are not inherently integrated out of the box (and this is just one example). Today one of the most effective and popular ways of solving the innate complexity problems brought about by commercial software solutions is to use open source software.

Within the location information arena, the best example of a bridge-building open source software solution is GeoServer. With solutions such as GeoServer in place, we no longer have to make the tough platform choices we used to make when it comes to creating, storing, managing, and utilizing location data. Instead, we can pick the best solution for the job and reach out to the open source community to help us with the rest.

This article has explored this very topic: how to interconnect two extremely popular and powerful location information solutions, Oracle Locator/Oracle Spatial and Google Earth, using the well-known, well-supported GeoServer open source solution. In the end, you not only created a way to analyze location-and other-information within Oracle Database but you were also able to directly connect a great user interface on top by using Google Earth. In sum, you have created an advanced enterprise business intelligence system.