Howto: Open Street Map in Oracle

Open Street Map is crowd sourced street map of the world.  As you would expect, it's a lot of data and the xml data file for the world is around 100gb uncompressed.   There are two ways of getting OSM data into Oracle, you can down load the shape files from CloudMade or GeoFabrik or download the planet_osm file, load it into PostgreSQL/PostGIS and then load it into Oracle.

Loading OSM shape files into Oracle

Loading shape files into Oracle is pretty easy and there are a lot of tools (both freeware and commercial) for loading data such as Oracle's Map Builder, Safe Software's FME, GDAL's ogr2ogr, and even a java based shp2sdo utility from Oracle.  The downside of using shape files is that there are a lot of them.  This requires plenty of of disk space for unzipping files as well as shell scripting to download and load the data into Oracle if you are averse to clicking links or pushing buttons repeatedly.  The other problem is that the field names maybe different from the planet_latest.osm file, which means some examples for configuring services may need additional tweaking. 

Loading OSM into PostgresSQL

The other way is to load the osm data into PostgreSQL/PostGIS using osm2pgsql.  There main advantage is that you get the raw data and your data looks like everyone else's so you can reuse their work without much tweaking.  You also avoid all the link clicking, scripting,  unzipping and loading multiple shapefiles.

There are Windows and debian based linux binaries for osm2pgsql.  I use centos, so I had to build osm2pgsql using howtos from here and here.  I had a couple of problems loading the data and it seemed that the system memory was overwhelmed by the amount of data.  Using the slim option (store temporary data in the database) fixed this problem.   Load the data using osm2pgsql: 

./osm2pgsql -c -s -C -H ang -P 5432 -d osm -E EPSG:4326 -U postgres -W ../planet-latest.osm

Loading the planet_latest.osm file can take a couple of days even on a dual processor server.  It is an awful lot of XML to parse and insert into a database.

Ogr2ogr: vector data convertor

The next step is to move the data from Postgres to Oracle using the gdal utility ogr2ogr.   

There are Windows binaries for ogr2ogr but the minimalist windows executables do not include the OCI driver for Oracle.  For Windows, your best bet is to download the OSGeo4W installer and add the OCI driver to the install list.  The OS X binary from Kyng Chaos lacks OCI support, so you will have to build gdal on OS X.  On linux you will need to build gdal.  To build gdal with OCI, you will need  GEOS, PROJ4, and the Oracle Client installed.  Make sure you have ORACLE_HOME set so that make can get to the Oracle libs.  Remember to add the OCI option when configuring:

% ./configure --with-oci % make % sudo make install

Loading OSM from PostgreSQL to Oracle using ogr2ogr

Using ogr2ogr is straight forward, however there a few things that you will need to do ahead of time.  Ogr2ogr cannot create a new database so you have to set one up before moving the data from PostgreSQL to Oracle. 

1. Create a table space to hold the data.  Make sure autoextend is on to accommodate all the data.

sqlplus system/password

   sqlplus> CREATE TABLESPACE PLANET_OSM_TS \  LOGGING \  DATAFILE 'C:\DATA\PLANET_OSM.DBF' \  SIZE 5G \  AUTOEXTEND ON \   EXTENT MANAGEMENT LOCAL;

2. create a user and assign them to the new tablespace

sqlplus> CREATE USER OSM IDENTIFIED BY OSM DEFAULT TABLESPACE PLANET_OSM_TS;

3. grant the user privileges

sqlplus> GRANT CONNECT,RESOURCE TO OSM;

4. make sure that the PostgreSQL machine can see the Oracle machine.  If you have the Oracle client installed,  add the Oracle SID to the C:\Oracle\product\10.1.0\Client_1\network\ADMIN\tnsnames.ora file:

osm_planet = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = osm_planet) ) )

Ogr2ogr makes use of the SID to connect to Oracle.  You can test to see if the connection is working with tnsping:

tnsping osm_planet

Once the database is set up, moving OSM from PostgreSQL to Oracle is straight forward.   I did hit one snag.  Before translating the data, make sure that the applications using this data are not tied to a specific Oracle SRID.  For example, the OSM data was in WGS84 (EPSG:4326) and an application I used tied WGS84 to Oracle SRID 8307.  Ogr2ogr uses the first exact match of WKT (Well Known Text) as the target SRID; this happened to be SRID 8192.  The WKT for 8192 and 8307 are exactly the same, but the application was hardwired to use SRID 8307 , so my sql requests failed because the SRIDs did not match.  To avoid problems like this, you can use the -lco (layer creation option) to set the SRID or any other parameters.  

ogr2ogr -f OCI OCI:osm/osm PG:"host=localhost user=postgres password=postgres dbname=osm port=5432" -lco OVERWRITE=yes -lco SRID=8307 -nln planet_osm_line planet_osm_line

Rinse, lather and repeat for planet_osm_roads, planet_osm_point, and planet_osm_polygon layers, if so desired.  Loading the data will take most of the day, particularly planet_osm_line table which contains around 22 million records.

Transparency Camp09: Mapping Session

Transparency Camp is a bar camp for open government advocates (government representatives, technologists, developers, NGOs, wonks and activists) to share knowledge on how to use new technologies to make our government transparent and meaningfully accessible to the public. Andrew Turner of GeoCommons and I ran a session on mapping, public participation and open data. Andrew avoided the "powerpoint" approach did a wonderful job of moderating the session and made it very interactive. He captured the discussion by dividing it into two sections Mapping and GeoData and Solutions.  Mapping and GeoData was further divided into two categories: Difficulties and Goals. Solutions was the beginning of a mind map I've summarized the session roughly according to Andrew's categories of Difficulties and Goals. Data input and output The conversation started out with common frustrations which can be divided into two problems: getting data in and getting maps out. Getting data into a mapping interface remains problematic. The session members commonly used csv, shape files and geotiffs to overlay on a base map source such as Google Maps. While data is available from the web sites and from government agencies, the problem is that the data is poorly described. Data sources from different government offices are unique bit contain common data. In addition, the usefulness of a particular data set is not known until they have completed the entire process of building a map. The primary output problem was printing maps. On-line mapping applications do not provide an easy way to create printed maps, especially large format (D or E size) maps. There was also a concern about licensing and copyright when printing maps from an on-line source such as Google Maps. 'Easy' was the word of day and the goal with regards to data input and out put. The ability to preview the data or even have meaningful metadata (i.e. fitness of use ranking, popularity, etc) was needed. Also the problem of having multiple schemas of similar data could be addressed with a common community schema. To make it easy, there could be a graphical tool that lets one map a data set to the community schema by simply drawing lines between two fields. Motivations Session members said that their motivation for mapping was to tell a story and that maps were a way to tie data to communities. Maps make data real and concrete and inherently provoke a visceral reaction. Maps are used as an exploratory interface so that patterns of data can be revealed. Maps are used as a way to plan, coordinate and share information through a variety of contexts. A distinction was made about different contexts; for example a map showing the locations of services did not work because it didn't readily answer the question of "how close is the service." In that case proximity was more important actual location. Andrew summed it up nicely, "Geographic doesn't always mean cartographic." Although, most members agreed that visualization of location was the primary use of maps, it was obvious that they also used maps in a more sophisticated way to communicate the implications of data. Current web mapping platforms remain focused on the "where is" aspect of maps. The next generation web mapping platforms should implement the basic cartographic thematic forms of isopleths, chloropleths, dot density, and proportional symbols. Open data and fear A good part of the discussion was about open data and why organizations did not want to release it.  Fear was the main reason cited for not releasing open data; below is a list of fears:

• liability
• may expose problems with the data
• data may be used against an organization
• some organizations fear that it will reveal patterns of behavior that will be criticized
• concerns about real time location/tracking of archeological sites or endangered species
• security and privacy are of concern

What they really want

To summarize, here is want they wanted:

easy 

• data preview 
• a common way to view data from different sources (a normalization tool)
• making sophisticated maps guided by wizards
• printed map output, or press ready map output for brochures

cheap 

• the applications should be low cost or free
• require minimal staff training
• the amount of time spent on maintenance should be low
• use a minimal amount of bandwidth (interesting comment that hints at an interest in hosted solutions)

additional geowankery

Google Maps can only handle KMLs with points, lines, and polys. Ground overlays are strictly verboten. However, you can overlay images in Google Maps using mapplets. The process is simple and well documented. The basic steps are: 1. write your mapplet, the example below contains all the tags that the example on google doesn't show for your cut-n-paste enjoyment: <?xml version="1.0" encoding="UTF-8"?> <module> <moduleprefs title="1929 Map of San Antonio, Texas" height="300"> <require feature="sharedmap"> </require></moduleprefs> <content type="html"><![CDATA[ <small> This is a georeferenced map of San Antonio, Texas in 1929. The original map is at http://www.tsl.state.tx.us/arc/maps/images/map1052a.jpg </small> <script> var map = new GMap2(); map.setCenter(new GLatLng(29.4208378,-98.4938900), 12); // ground overlay var boundaries = new GLatLngBounds(new GLatLng(29.3400022,-98.5543116), new GLatLng(29.5016733,-98.4334683)) var oldmap = new GGroundOverlay("http://www.tsl.state.tx.us/arc/maps/images/map1052a.jpg", boundaries) map.addOverlay(oldmap) </script> ]]></content> </module> 2. Upload your code to a server or to Google page creator. 3. Install it on Google Maps directory. 4. Share the map using a long url like this: http://maps.google.com/ig/add?synd=mpl&pid=mpl&moduleurl=http://www.geouptime.com/mapplet_san_antonio_1929.xml Google will ask you if you want to add it to maps (I assume it means your maps). Clicking on Add it to Maps opens the mapplet. Voila!

Making historic information more available

I write on another blog documenting the history of my house in San Antonio, Texas. As part of the research we found that there was streetcar service close the house. A quick search turned up a map of the streetcar lines at the Texas Transportation Museum. I thought it would be neat to overlay that map over other map services. I first tried to georectify the Texas Transportation Museum map using Metacarta Labs Map Rectifier, but the quality of the scan was too poor to successfully add enough ground control points for a good warp. Another search turned up a much better scan of a 1929 map at the University of Texas Perry-Castaneda Library Historic Map collection. This map was scanned at a very high resolution and it was easy to create a sufficient number of ground control points to use a cubic fit georegistration using Metacarta's Map Rectifier. The result was a 215mb tif file which I was able to download. Thanks Metacarta! The next step was to trace the streetcar lines using uDig. I used version 1.1.1 which is verrrry slooowww when it comes to handling largish tif files. I heard that raster handling is improved in 1.2 beta as well as more digitizing tools, so you might want to use that. Once I figured out the editing tools it was fairly easy to digitize the streetcar lines. Always the critic, I have to say the the editing tools could use some context sensitive menus available through a right click. I saved the streetcar lines as a shape file and I saw that uDig has a number of tools for cleaning up the data and validating the output. Nice. I converted the shape file kml using ogr2ogr. The first time I tried it ate my shape file, no idea why. I noticed that my gdal install was way out of date (version 1.4), so I installed version 1.6 for OS X from the Kyng Chaos Wiki. Using ogr2ogr was straight forward:

ogr2ogr -f KML sa_streetcars.km streetcar_lines.shp

I tweaked the KML in a text editor so that each streetcar line was a consistent color. Then I uploaded it to myGoogle maps. The 1929 map overlays fairly well but not exactly. I think it was important to use a historic map for digitizing the lines instead of using a modern map such as Open Street Map, Google Maps, or the Census Bureau's TIGER street files because there have been many changes to the street network since the streetcars were in service. The lines may not overlay perfectly, but I think its a better representation. Here's the data and final map:
View Larger Map

orchestra practice

Yesterday was the first practice for my church orchestra.  I was wondering who would show up and it turned out to be a diverse group. 

• 3 cellos
• 3 violins
• 2 violas
• 1 oboe/english horn
• 1 flute
• 3 clarinets 
• 2 alto saxes

We actually sounded OK and I was more or less able to keep up/play in time with everyone.  The director is our church's choral director but she did a great job mixing and matching sections of the different pieces.   I was mighty pleased since I haven't played in an orchestra since junior high.  One of the other cellists even asked me if I wanted to get together and work on duets.  I am so jazzed.

subtle reminders

I was in Chicago on Tuesday and I stayed at the Kimpton Allegro.  Lo and behold there was a picture of what looks like an x-ray of a cello in my room!  Just another reminder to get may butt in gear with the Prakticello build.

I've found a tail piece on ebay so far, and I'm looking for a neck and fingerboard.  Finding a source for spruce is turning out more elusive.

Building a Prakticello: The Plans Arrive

This is a departure from my cheesy java bits, but I'm building a Prakticello; so this will be a build log of sorts. 

I travel a bit and I really miss cello practice when I'm out of town. I thought about getting a silent or electric cello but none of them seem to be overhead compartment friendly except the Ned Steinberger electric cellos. The NS cellos look awesome, and in my imaginary world I can see myself playing Bjork or the Clash's "Straight to hell" and becoming a rock star cellist. But I bought a cello over the summer, so I'm just not ready to plunk down $2K for another instrument. Once again cheapness (or more kindly, thrift) crushes my dreams. Another mitigating factor is that I joined the Christmas orchestra at my church. While the pieces are not particularly difficult, most of the songs are in F major and I'm still kinda banging around C major in the Suzuki 2 pieces.  Yeah, I know I should practice my scales more often. Anyway, I don't want to sound like an ass because my extension sucks. I ordered a set of plans from the inventor of the Prakticello, Mr. Ernest Nussbaum. The plans were written circa 1985 and they have the sort of charm of plans ordered from the back of Popular Mechanics. The plans are hand drawn (as opposed to be done in a CAD package) and the hand lettering is charming, not Leroy lettering but you can see faint lines drawn in to guide the lettering. The plans come with a 22 page instruction manual written in a Courier font adding a typewritten charm. You can imagine Mr Nussbaum banging away on an IBM Selectric. The plans are actually for a Traviola, which is the electrified version of the Prakticello. They contain the build of materials and general instructions which include building a small amp. For my purposes, I'll stick to the silent cello build for now. There are a couple of photos of the Prakticello in the manual and the website, but they really don't do the Prakticello justice. I found these pics on a discussion board:

checking for well formed documents in OS X

I sometimes have to generate XML documents from some random source of data. It's typically a one off task, so I just write a quick program. However, I usually get the format wrong or forget a special character. OS X (and linux) happens to have a neato utility for checking for well formedness called xmlwf

Making GeoWebCache authenticate to a WMS

GeoWebCache is a java implementation of TileCache.  Geowebcache does not connect to secured a WMS, so I added support for it (yay! for open source).

The WMS GetMap requests are in WMSMetaTile.java (../geowebcache-0.8.3/src/main/java/org/geowebcache/layer/wms).  I thought that the best place to put the username and password was in the WMS service url, which is set in the properties file for the layer (../geowebcache/WEB-INF).  This takes the form of http://username:password@www.mapserver.com/ .  

Alternatively you can also put username and password in the vendor parameters, but that could get dodgy if you have other vendor parameters.

From googling RFC 2716 related documents, the username and password has to be sent in the http Authorization request header in the form of username:password encoded in base64.  Real secure, huh?

So here's the code:

    private void forwardRequest(WMSParameters wmsparams, String backendURL,
        WMSLayerProfile profile) throws IOException, ConnectException,
        ServiceException {

    // extract username and password from service url if there
    String encoding = null;
    int upTest = backendURL.indexOf("@");
    if (upTest>0) {
     String[] temp = backendURL.split("@");
     backendURL = "http://"+temp[1];
     String usernamePassword = temp[0].substring(temp[0].lastIndexOf("/")+1,temp[0].length());
     encoding = new sun.misc.BASE64Encoder().encode (usernamePassword.getBytes());    
    }

    //Get user name and password from vendor parameters
    //Vendor parameter key is "logon"
    //Need to add iterator in case of multiple vendor parameters
    /*
    if (profile.vendorParameters!=null) {
       String[] temp =profile.vendorParameters.split("=");
       if (temp[0].equalsIgnoreCase("logon") {
          String usernamePassword= temp[1];
          //usernamePassword = usernamePassword.substring(usernamePassword.indexOf("=")+1,usernamePassword.length());
          String encoding = new sun.misc.BASE64Encoder().encode (usernamePassword.getBytes());    
       }
    }
    */
         
    // Create an outgoing WMS request to the server
    Request wmsrequest = new Request(backendURL, wmsparams);
    URL wmsBackendUrl = new URL(wmsrequest.toString());
    log.info("the request: "+wmsrequest.toString());
    URLConnection wmsBackendCon = wmsBackendUrl.openConnection();

    //if username and password required set header
    if (!(encoding==null)) {
       wmsBackendCon.setRequestProperty  ("Authorization", "Basic " + encoding);
    }

...

bits and stuff

Some bits I always seem to forget. Splitting a delimited line of text in bash line = a|b first=${line%|*} second=${line#*|} echo $first echo $second Post in curl (OS X) curl --data-ascii "var1=one&var2=two" http://server.com/myapp