DOWNLOAD Oracle Database XE  Google Earth

TAGS xe, php, spatial, All

Build a Google Earth Interface on Oracle Database XE

Get an overview of spatial data, explore ways to add spatial attributes to existing data, and learn how to use Google Earth to "fly" over aerial imagery including that data.

Published August 2006

How we look at our data forms our view of the world. Until recently most of us have ignored location because we lacked the tools (or the ability to use those tools) needed to acquire, manage, and present the spatial component of our data. This means we have missed the geospatial component of human experience. Everything that we do, think, or experience, we do, think, or experience somewhere.

Until recently it has been complicated to connect a spatially enabled database with a visualization engine: to put our customers (and whole supply chain) onto a map. That is now a solved problem. In this article you will get an introduction to spatial data, explore ways to add spatial attributes to existing data, and learn how to use the free Google Earth program to let us ‘fly’ over aerial imagery that includes your data.

Some of these techniques are a bit awkward since we are in the early days of non-specialist access to geospatial visualization and analysis tools. Some of these tools are amazing, such as the ability to use Google Sketch Up to add 3D models of any feature to Google Earth, and to do that for free! But sometimes the tools are missing ‘obvious’ features, like a report writer that supports cross-tabbing, but can’t handle multiple levels of summation. But like the early days of any technology, the greatest rewards will go to those who take the time to learn the technology early, while the standards are incomplete and some of the tools still awkward.

To use spatial tools you need to add spatial attributes to your data. Spatial attributes are present in any information that has a location or spatial component. There are a huge number of details about spatial coordinate systems, from which geode you use (your model of the shape of the earth), to the Datum (where is the zero point on the X-Y-Z axis you choose), to the Projection of the curved surface of the Earth onto a flat map (which enables the curved surface of a sphere to be represented on a flat map). This is a rich, deep, and fascinating topic of study that we can safely ignore for now.

For our purposes we represent a location on the Earth with latitude and longitude. Lines of latitude circle the earth like layers of a wedding cake, with the base, or latitude 0, being the equator. There are 90 degrees of latitude from the equator to the North, or South, poles. Each degree of Latitude is roughly 69 miles. North of the equator we talk about degrees of latitude North, south of the equator we say degrees of latitude south.

Degrees of longitude divide the earth from North to South Poles like slices of an orange. While the equator is a natural feature, the lines of longitude start at the arbitrarily defined Prime Meridian that passes through Greenwich England. Lines of longitude converge as they approach the poles. A degree of longitude varies from roughly 69 miles at the equator, to zero miles at the north or south poles.

To simplify things for computer representation it is customary to represent degrees of latitude south of the equator and degrees of longitude West of the Prime Meridian as negative numbers. Latitude and longitude are commonly represented either as degrees, minutes, and seconds, or as decimal degrees. Decimal degrees are much simpler to work with, so this is what we’ll use in this article.

For example, Oracle Headquarters is at 500 Oracle Parkway, Redwood Shores, CA 94065, or ‘roughly’ at 37.529526,-122. 263969 (37.529526 degrees North Latitude by 122. 263969 degrees West Longitude).

This is an example of having more precision than is reasonable! You don’t need six decimals of precision in geographic coordinates to show a building. But how many digits do you need? We know that one degree of latitude is 69 miles. So a tenth of a degree is 6.9 miles, a hundredth of a degree is .69 miles, and so on (See table below). Four digits of precision is more than enough if we just have a single point to mark the building.

Next, let's consider the three main forms of spatial data: points, lines, and polygons.

Points are easy: a single latitude and longitude marks a single spot. Lines and Polygons are simply a series of connected points. Lines are used for linear features like roads and rivers, while polygons are lines where the last point connects back to the first, thus defining an area. The route that a delivery truck takes would be a line, while a sales territory would likely be described as a polygon.

Now I'll explain how you can use the free Oracle Database XE database to experiment with storing, managing and analyzing spatial data.

On Simplicity

The easiest way to store a latitude and longitude is in a number field. Using number fields to store coordinates works well for doing simple things with points. Once your needs grow you can use Locator, an XE subset of the full Oracle Spatial tools found in commercial Oracle offerings. With Locator you move from thinking of a simple lat/long to working with "Geometries." The sdo_geometry data type can store points, lines, polygons, as well as more complicated geometries like multi points, multi lines and multi polygons. You also get operators that include allowing you to calculate distances between geometries, find nearest neighbors, and determine if two geometries intersect ("Does Interstate 70 go into Colorado?").

Locator is great to use once you move beyond putting points on a map, but for simple problems use simple tools.

Oracle Database XE, available as a download from Oracle Technology Network, is available in Windows and Linux versions. For a Linux installation, download the RPM, and then install it:

rpm –ivh oracle-xe-10.2.0.1-1.0.i386.rpm 

I had two minor issues with my installation. First, I did not have enough swap space enabled. Rather than create a larger swap partition on my hard drive, I set up, and activated, more swap space by following these Red Hat instructions. To summarize, you need to create a file to be used for swap, designate it as a swap file, and then turn it on. This set of commands works:

dd if=/dev/zero of=/path-to-swap/swapfile bs=1024 count=1200000 
mkswap /path-to-swap/swapfile
swapon /path-to-swap/swapfile

You configure and largely work with Oracle Database XE through the Web interface. If you’ve installed XE on your local machine, point your browser to http://127.0.0.1:8080/apex. You may run into a slight issue if you’ve installed it on a remote server, as by default the web interface only works for a local client.

You can enable access to remote clients by using the Administration section of the Web interface. (Alternatively, you can edit the XE config file located at /etc/init.d/oracle-xe.) There are two problems with this approach, however. For one, it exposes your database management in a manner that raises more security concerns. But the larger problem is that in order to enable remote access to the Web interface you need to have local Web access. This is a challenge for a remote headless server.

One way of addressing this problem is to use the Open SSH program that comes with Linux and Macintosh OSX. Open SSH includes a built-in Socks 4 proxy. You can connect with your remote server using this command:

ssh -D1080 username@yourserver.com

It will then look like you have set up a normal ssh connection to your server, but in the background SSH will be listening to port 1080, and carrying all those requests to your remote server. This also means that all your HTTP traffic is now encrypted until it gets to your server. This can be a very useful feature when you are on untrusted public networks!

The final step here is set up your browser proxy. In Firefox, select Preferences->General->Connection. Set your socks host as localhost on port 1080, select Socks v4, and where it says no proxy for make sure to remote 127.0.0.1 and localhost.

Once you have done this you can use the Web interface to do almost everything with your database. Since business data is (often) spatial data, let’s set up an example of adding latitude and longitude to addresses.

Create an address table with the SQL section of the Web Interface.

create table address (name varchar(128), address1 varchar(128), 
 address2 varchar(128), city varchar(128), state char(2), 
 zip char(9), latitude number(7,5), longitude number(8,5))

Load a few addresses:

insert into address (name, address1, city, state, zip) 
values ('Oracle', '500 Oracle Parkway', 'Redwood Shores', 'CA', '94065')

insert into address (name, address1, city, state, zip) 
values ('OReilly Media ', '1005 Gravenstein Highway North', 'Sebastopol', 'CA', '95472')

The process of adding latitude and longitude to other data is called geocoding. The full version of Oracle Spatial includes the SDO_GCDR package, which supports Geocoding. With Oracle Database XE you can use the Geocoder.us Web service to add geocodes for our addresses. For two addresses you would probably just look up the coordinates and manually update them, but you never have just two addresses!

Geocoder.us provides several Web services interfaces that take an address and return coordinates. The simplest is the Comma Separated Values (CSV) interface. You can enter a URL in your browser and get the coordinates. This address:

http://rpc.geocoder.us/service/csv?address=500 Oracle Parkway,Redwood Shores,CA,94065

returns:

37.529526,-122.263969,500 Oracle Pky,Redwood City,CA,94065

You can also call this from PHP. This code will take an address from the command line, call geocoder.us, and return the coordinates:

<?PHP
$address = $argv[1];
echo "query address: $address \n";
$url = "http://rpc.geocoder.us/service/csv?address=" . (urlencode($address));
$w = fopen($url,"r");
$result = fgetcsv($w,8000);
fclose($w);

$latitude = $result["0"];
$longitude = $result["1"];

echo "latitude $latitude longitude $longitude\n";
?>

Include the address and call this from the command line. (When you call PHP from the command line you can add the –q switch to suppress the normal http content-type headers):

php -q ./php_work.php '1600 pennsylvania ave, washington, dc' 

which returns:

query address: 1600 pennsylvania ave, washington, dc 
latitude 38.898748 longitude -77.037684

That gives you the simplest case for connecting with Geocoder.us. The next step is to get your addresses from the database, and then to update the database with the latitude and longitude returned from geocoder.us.

First you need to get PHP working with Oracle—see these instructions. The one "gotcha" I experienced with these instructions was that my copy of apxs was not in the default location, so when I ran configure I replaced --with-apxs2=/usr/local/apache/bin/apxs with --with-apxs2=/usr/sbin/apxs.

The following code will read our address table, geocode each address, and then update the table with the latitude and longitude.

<?PHP

# create a connection to the database instance on localhost. If you 
# have not done anything 'clever' the username 'system' will work
# with the password that you defined at installation
$conn=oci_connect('username','password', "//127.0.0.1/XE");

# Query our address table 
$sql = "SELECT name, address1, city, state, zip from address";

# oci_parse is part of the Oracle PHP library to parse the SQL statement
$stmt = oci_parse($conn, $sql);

# oci_execute not surprisingly executes the statement we parsed in the previous line
oci_execute($stmt);

# This loads the associative array $row with the values of each row in our
# database in turn 
while ( $row = oci_fetch_assoc($stmt) ) {
 # print_r dumps a variable, including all of the keys for an associative array 
 print_r($row);
 # assemble the query variable for our call to geocoder.us
 $address = $row["ADDRESS1"] . "," . $row["CITY"] . "," . $row["STATE"] . " " . $row["ZIP"];
 # pull the name out of the associative array 
 $name = $row["NAME"];
 # the url to the free service of geocoder.us to return the data in CSV format 
 $url = "http://rpc.geocoder.us/service/csv?address=" . (urlencode($address));
 # open the url 
 $w = fopen($url,"r");
 #parse the CSV returned from the page into the array $result
 $result = fgetcsv($w,8000);
 fclose($w);

 $latitude = $result["0"];
 $longitude = $result["1"];

 # query to update the address table with the lat/long we got from geocoder.us
 # granted it is poor database design to have such an uncertain key as 'name' 
 # be our primary key…I'll leave it as an exercise to the reader to implement
 # this code in a way that doesn't make DBA's cry.
 $sqlu = "update address set =$latitude, =$longitude where NAME='$name'";
 echo "sqlu $sqlu\n";
 # as before, parse the SQL statement
 $stmtu = oci_parse($conn, $sqlu);
 # and execute the statement
 oci_execute($stmtu);
}
?>

3D Maps Without Sherpas

All that effort has provided you with a database that includes spatial data. Now you know where your customers are! There are a lot of things you can do with that information, such as market analysis, planning advertising campaigns and sales trips, and routing delivery trucks, but the first thing we all want is to see our data on a map. Two years ago it was a pain to display data on a map; you either had to use expensive and specialized tools, or complicated tools with Himalayan learning curves. Luckily, it is now much easier.

Google Earth is a desktop application for Windows and the Mac that goes beyond the map and provides a 3D model of the world. In a growing number of urban areas, this also includes 3D building outlines. You can also use Google Sketch Up to add your own 3D models to the display.

Google Earth is available at http://earth.google.com. There is a free version, as well as Google Earth Plus for $20 a year and Google Earth Pro for $400 a year. The pay versions add features such as global positioning system (GPS) integration. (See a comparison grid here.) The Web site instructions are easy enough, so download the program and start it up! When you do this you are initially shown a view of the Earth, as though you were in space. You can then zoom in, search for places, explore the world, and generally satisfy your urge to make like Lewis and Clark—a modern-day member of the Corps of Discovery.

There is an active Google Earth community that publishes files of spatial information. For example, go here to read about, and download, a Google Earth file of the Lewis and Clark Voyage of Discovery: 29917-lewis_and_clark_expedition.kmz. You'll notice that Google Earth files have the extension KML, for Keyhole Markup Language (Google Earth was originally called Keyhole), or KMZ for Keyhole Markup compressed with gzip. The KMZ file can be uncompressed with WinZip or Stuffit Expander.

I love my GUI, except when I try and script things, so from an OSX terminal command line, you can enter:

gunzip -S ".kmz" 29917-lewis_and_clark_expedition.kmz 

When you open the file, you’ll see it is simply XML. Okay, perhaps not entirely simple, but well within the range of easy to read. Explore the Google Earth KML documentation, or just dig in!

For example, here is the placemark describing Camp Disappointment.

<Placemark>
 <description><![CDATA[
	<a href="http://www.lewis-clark.org/content/content-article.asp?ArticleID=1069">
	Click to read entry</a>]]></description> 
 <name>17: Camp Disappointment</name> 
 <LookAt>
 <longitude>-112.820632</longitude>
 <latitude>48.716670</latitude>
 <range>1000.000</range> 
 <tilt>0</tilt>
 <heading>0</heading>
 </LookAt>
 <styleUrl>root://styles#khStyle929</styleUrl>
 <Point>
 <coordinates>-112.820632,48.716670,0</coordinates>
 </Point>
</Placemark>

When you bring up the Lewis and Clark file, you’ll see how Google Earth renders this description of Camp Disappointment.

The <name> element is shown on the map at the X, Y, Z coordinates specified within the<coordinates> element of the Point element (longitude = X, latitude = Y, altitude = Z, or 0 in this example). When you single click on the point you see a pop up, called a description balloon, with the contents of the name and description elements, and options to get Directions to or from this point.

Make special note that the description can contain a URL. So if this placemark described one of your customers you could include additional attributes about the customer within the callout, and then embed a link to the customer’s page within your CRM system.

The <styleURL> element contains a link to a URL of the "pushpin" that marks this location. In this case the style is contained on the local file system. The description of a style can also be contained within the KML document, or at an external URL.

Finally you get to the <LookAt> element. When you double-click on a placemark you are moved to the longitude and latitude specified within the <LookAt> element, and given the point of view described by the <heading>, <tilt>, and <range> elements. This allows you to create a placemark where you specify a point of view. For example, this is a complete KML file that says, "Go to Crissy Field in San Francisco and look at the Golden Gate Bridge":

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Placemark>
 <name>crissy field</name>
 <LookAt>
 <longitude>-122.4592370657115</longitude>
 <latitude>37.8050682478946</latitude>
 <altitude>0</altitude>
 <range>1000.275193579794</range>
 <tilt>90</tilt>
 <heading>315</heading>
 </LookAt>
 <styleUrl>root://styles#default</styleUrl>
 <Point>
 <coordinates>-122.4592370657115,37.8050682478946,0</coordinates> 
 </Point>
</Placemark>
</kml>

In the interests of simplification, you can eliminate many of those attributes. Here is about the simplest useful KML description of two distinct places:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Folder>
<Placemark>
 <name>First Place</name>
 <Point>
 <coordinates>-122.5,37.8,0</coordinates>
 </Point>
</Placemark>
<Placemark>
 <name>Random Place</name>
 <Point>
 <coordinates>-122.6,37.9,0</coordinates>
 </Point>
</Placemark>
</Folder>
</kml>

And here is a PHP program to grab our geocoded addresses and produce a KML file:

<?PHP

$conn=oci_connect('username','password', "//127.0.0.1/XE");
$sql = "SELECT name, address1, city, state, zip, latitude, longitude from address a";
$stmt = oci_parse($conn, $sql);
oci_execute($stmt);

print '<?xml version="1.0" encoding="UTF-8"?>' . "\n";
print '<kml xmlns="http://earth.google.com/kml/2.0">' . "\n";
print '<Folder>' . "\n";
while ( $row = oci_fetch_assoc($stmt) ) {
 $address = $row["ADDRESS1"] . "," . $row["CITY"] . "," . $row["STATE"] . " " . $row["ZIP"];
 $name = $row["NAME"];
 $latitude = $row["LATITUDE"];
 $longitude = $row["LONGITUDE"];
 print "<Placemark>\n";
 print " <name>$name</name>\n";
 print " <description>$address</description>\n";
 print " <Point>\n";
 print " <coordinates>$longitude,$latitude,0</coordinates>\n";
 print " </Point>\n";
 print "</Placemark>\n";
}
print '</Folder>' . "\n";
print '</kml>' . "\n";
?>

This is the caveman coder’s version that uses print statements designed to show all the details. In practice, you would want to use an XML library. Take a look at the Pear/XML/sql2xml class documented at: http://php.chregu.tv/sql2xml/. Furthermore, by using dynamic URL parameters and variables, we could easily add interesting context to this query (or other queries) whereby our spatial analysis would mean that much more.

Stay Focused

You now have the ability to create a database of addresses, add latitude and longitude to that data by geocoding, and then export the data so that you can see your data on Google Earth (assuming you are not still reading journal entries from Lewis and Clark and the Voyage of Discovery, and getting lost in pleasurable exploration and Location Creep!).