Importing OpenStreetMap data into Postgres

I have already set up PostGres 9.1 and PostGIS 2.0 installed. Create a new Postgres DB named GIS.
Run CREATE EXTENSION postgis on this database to add all of the required postgis functions and the spatial reference data.

 Run legacy.sql and rtpostgis_legacy.sql on your new GIS database to add in the legacy function names. OSM2PSQL has not been compiled recently, and still utilizes the old functions...

 You also have to recreate one function in the version I am using... supposedly this function is added back in the latest release... 2.1 I believe...

 CREATE OPERATOR CLASS gist_geometry_ops  
      FOR TYPE geometry USING GIST AS  
      STORAGE box2df,  
      OPERATOR    1    << ,  
      OPERATOR    2    &<      ,  
      OPERATOR    3    && ,  
      OPERATOR    4    &>      ,  
      OPERATOR    5    >>      ,  
      OPERATOR    6    ~=      ,  
      OPERATOR    7    ~      ,  
      OPERATOR    8    @      ,  
      OPERATOR    9    &<| ,  
      OPERATOR    10    <<| ,  
      OPERATOR    11    |>> ,  
      OPERATOR    12    |&> ,  
      OPERATOR    13    <-> FOR ORDER BY pg_catalog.float_ops,  
      OPERATOR    14    <#> FOR ORDER BY pg_catalog.float_ops,  
      FUNCTION    8    geometry_gist_distance_2d (internal, geometry, int4),  
      FUNCTION    1    geometry_gist_consistent_2d (internal, geometry, int4),  
      FUNCTION    2    geometry_gist_union_2d (bytea, internal),  
      FUNCTION    3    geometry_gist_compress_2d (internal),  
      FUNCTION    4    geometry_gist_decompress_2d (internal),  
      FUNCTION    5    geometry_gist_penalty_2d (internal, internal, internal),  
      FUNCTION    6    geometry_gist_picksplit_2d (internal, internal),  
      FUNCTION    7    geometry_gist_same_2d (geom1 geometry, geom2 geometry, internal);  

This is referenced in the following bug: http://trac.osgeo.org/postgis/ticket/1287 Then just run the osm2psql importer using the following command and you will be good.

 osm2pgsql -c -d gis -U postgres -W -H localhost -P 5432 -S default.style C:\gisdatafiles\2012-12-05_new-brunswick.osm  

Note that this imports the tables using spherical mercator projections... SRID=900913 There are command line switches that are supposed to allow you to change this, but I could not get them to work. For me this is just a temporary table anyway, so to add a translation field and then translate to another SRID is very easy to do so I did not bother wasting any more time trying to get that to work. (See my other blog post for instructions)

Where in the world? Adventures in GeoSpatial coding

I have recently been experimenting with GeoSpatial coding... there are some excellent and really cool technologies out there to help with the support of GeoSpatial coding.

I have been playing around with a little application to map as many geo-coded things as I can within my little corner of the world.  This is really just a geeky hobby, but has allowed me to touch on some REALLY cool technologies...

This blog is to capture some of the learnings I have had, for my future reference, and may even help others voyaging into this area.

My current task is to create a local mapping portlet showing all of the buildings that are mapped by the OpenStreetMaps project (openstreetmaps.org).  This dataset is a crowd sourced open geospatial dataset.  Depending on where you are in the world, there will be more or less accurate data available.  I am currently testing for Saint John, New Brunswick, Canada... and there is pretty decent data for this city.

The technologies I am using for this task are a PostGres 9.1 Database, installed with PostGIS extensions.
I have brought down the OpenStreetMap.org dataset for New Brunswick from the GEOFABRIK mirror site:  http://download.geofabrik.de/openstreetmap/north-america/canada/
And have imported it into my PostGres database using the OSM2PGSQL tools:
http://wiki.openstreetmap.org/wiki/Osm2pgsql

Note:  This did not work first try... as it turns out there have been some changes to the latest version of the PostGIS library which actually break this application since it has not been re-compiled against the new postGIS library.  There is good information on the PostGIS FAQ for this issue... basically you have to run a series of PostGIS .sql files which are included with the installation to make the functions backwards compatible... then do your import, and then revert the changes back with some other scripts....  there was one other problem I encountered too... which they did not have a script for, but for which there was a bug report on their site when I did a google search of the error... I copied the sql from the bug report 'fix' and ran it, and it worked after that.  I am sorry I do not remember specifically what this was, it was a couple of weeks ago when I ran this step... also, this utility may be updated by the time you do the import and/or the postGIS may have gone up a version so these instructions may be moot.  Either way, it was not that difficult to get running....

So at this point, I had a database called GIS which contained all of the data from the OpenMap dump for New Brunswick.  I have created my own application, with my own domain objects, so the next step was to bring the data over to my domain.  When you import the openmap data, all data is stored according to the type of geospatial data.   So for example, polygon data is stored in the planet_osm_polygon table.  In my domain I had created an 'open_street_map_building' table with corresponding JPA EJB Domain object.
To populate the data, it was just a matter of doing a simple PostGres dblink query (since my tables are in different databases:

 INSERT into open_map_building (id,amenity,leisure,name,om_id,shop,sport,tourism,way) 
     select nextval('building_seq'),amenity,leisure,name,om_id,shop,sport,tourism,way 
     from dblink('dbname=gis password=passwordhere', 
     'select amenity,leisure,name,osm_id,shop,sport,tourism,way from planet_osm_polygon where building <>''''') 
     as t1(amenity varchar(255),leisure varchar(255),name varchar(255),om_id bigint,shop varchar(255),sport varchar(255),tourism varchar(255),way geometry); 


The openstreetmap data is flattened out in the imported schema to have a variety of optional 'tags' inserted as columns.  This is fine, and as you can see I brought in a few of them that I was interested in for buildings...
I also imported the local business data into another table... basically POINT data and the name of the business...

Once this was brought in, I was able to load up inside of my EJBs as JPA objects.  I used the Hibernate Spatial library to provide the link between my Glassfish v3.1.2 container and my PostGres database.  By using the Hibernate Spatial library, you will be able to use the Spatial Datatypes in your db queries, and can map them in your JPA domain objects ... VERY COOL!  This was a pain in the ass to get working, I must admit.  You have to be very careful to put the hibernate spatial jars in the correct place or the EJB3 classloader will not be able to locate them... put them in the glassfish/domain/domain1/lib folder and make sure there are not other copies upstream (in the glassfish/lib).

my glassfish/domain/domain1/lib folder contains the following:
antlr-2.7.7.jar
commons-collections-3.1.jar
dom4j-1.6.1.jar
hibernate-commons-annotations-4.0.1.Final.jar
hibernate-core-4.0.0.Final.jar
hibernate-entitymanager-4.0.0.Final.jar
hibernate-spatial-4.0-M1.jar
javassist-3.15.0-GA.jar
jboss-logging-3.1.0.GA.jar
jboss-transaction-api_1.1_spec-1.0.0.Final.jar
jts-1.12.jar
log4j-1.2.17.jar

I then created a Liferay Portlet using Vaadin 6 and the OpenLayers add-in
https://vaadin.com/directory#addon/openlayers-wrapper
And map the OpenMap building onto it...


COOL.  I have a map dynamically displaying the business locations....
Now I want to retrieve a collection of buildings within the perimeter of the map being displayed...

Here I ran into a bit of a snag initially.  PostGIS provides some excellent spatial functions (and they seem to run really fast too...)

So I wrote an EJB method that would accept the lat/long of the bottom left and upper right corners of my map.  I added a column to my open_map_buildings table called 'centerpoint' which I populated by calling the postgis method UPDATE open_map_building SET centerpoint = ST_Centroid(way);
This worked great.  Then I attempted to so a query:

Select * from open_map_building where ST_Contains(ST_MakeBox2D(ST_Point(bottom, left),ST_Point(top,right)),centerpoint);
This however always returned no results... strange...

So I decided to look at the points in the database.  To make a human readable version of the GEOMETRY fields, just use the ST_AsText() function:  Select ST_AsText(centerpoint) FROM open_map_building.
I noticed that the coordinates looked strange compared to my lat/longs.  That is when I realized that the spatial_reference system being used were different.  I am not going to go into the gory details, do a google search to get lots of great information... but basically, there are a few standard spatial reference systems that are used.  Most GPS systems use WGS84 (srid=4326 in postGIS).... when the data is imported from the openstreetmap data, it is in Sperical Mercator format though... srid=3857 ... I believe...

Once I realized this, it was a fairly simple matter to create a new field and populate it with new coordinates translated into  WGS84.  To do this, I actually added two fields, one for the POLYGON containing the actual building perimeter, and one for the POINT containing the center of the building...

The postgis function to create the columns are:

 SELECT AddGeometryColumn('open_map_building','bldg_perimeter',4326,'POLYGON',2);

 SELECT AddGeometryColumn('open_map_building','bldg_center',4326,'POINT',2);


Then UPDATE was simply:

 UPDATE open_map_building SET bldg_perimeter = ST_Transform(way,4326); 

 UPDATE open_map_building SET bldg_center = ST_Transform(centerpoint,4326); 


Then to retrieve the buildings was a piece of cake:

SELECT id, name,ST_AsText(bldg_center) as way FROM open_map_building where ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(-66.06097291707987,45.27056897229072),ST_Point(-66.0572178244588,45.27283408390478)),4326),bldg_center); 

Creating a Vaadin Portlet for Liferay

Creating a basic Vaadin Portlet for Liferay is pretty simple...

Install the latest Liferay IDE using the Eclipse built in Help->Install New Software mechanism...
You will have to have download the liferay sdk, and the latest version of Liferay/Tomcat bundle and install them locally.

Once complete you can create a new portlet using the New->Liferay->Portlet and selecting Vaadin as the presentation library. This will create a working 'Hello World' application with one minor change... (There is currently a bug... you will have to modify the portlet.xml and change the init-param section as follows: application ) 

Once this change has been made, set up your new Liferay installation under the 'Servers' tab in Eclipse, start it, and deploy the portlet to the server by right clicking the server, and selecting the 'Add and Remove' option. Browse to your local installation using http://localhost:8080 in a web browser, log in as the adminstrator (test@liferay.com/test by default), add a new page and add the newly created portlet to the page ... Add->More->Samples (it will show up under the 'Samples' section. This will display your portlet with a label saying 'Hello PortletName' 

This is a great way to get started easily (except that the ability to make a maven project for your portlet is not yet available by default... it is in Liferay IDE 2.0, though... scheduled for December 2012... I can't wait!) The biggest problem with not having a maven build is the lack of versioning for the dependencies. To counter this, I put a pom.xml of my own into the root of the project. I use this pom to manage the dependencies... running 'mvn install' will grab down the proper dependencies, and copy them to the correct folder so that the ant build can find them, then run the ant build. Be sure to run mvn eclipse:eclipse to recreate the .classpath file so that Eclipse can find the new .jar, and refresh your Eclipse project...

My test pom.xml is as follows (I am working on a OpenLayers project right now... just add whatever dependencies you need to this pom.  You can find the appropriate dependencies sections on the Vaadin site under each respective Add-On:

 <project xmlns="http://maven.apache.org/POM/4.0.0"  
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
      xsi:schemaLocation="http://maven.apache.org/POM/4.0.0  
                           http://maven.apache.org/xsd/maven-4.0.0.xsd">  
      <modelVersion>4.0.0</modelVersion>  
      <groupId>com.mdahatter</groupId>  
      <version>1.0-SNAPSHOT</version>  
      <artifactId>local-buzz</artifactId>  
      <name>Local Buzz</name>  
      <packaging>pom</packaging>  
      <dependencies>            
           <dependency>            
                 <groupId>com.vaadin.addon.chameleon.ChameleonTheme</groupId>  
                 <artifactId>vaadin-chameleon-theme</artifactId>  
                 <version>1.1.0</version>  
           </dependency>  
           <dependency>  
                <groupId>org.vaadin.vol</groupId>  
                <artifactId>openlayers-wrapper</artifactId>  
                <version>1.0.0</version>  
           </dependency>  
           <dependency>  
                  <groupId>org.vaadin.addons</groupId>  
                  <artifactId>toolkit-productivity-tools</artifactId>  
                  <version>1.2.0</version>  
           </dependency>  
      </dependencies>  
      <build>   
           <plugins>  
                <plugin>  
                     <groupId>org.apache.maven.plugins</groupId>  
                     <artifactId>maven-dependency-plugin</artifactId>  
                     <version>2.3</version>  
                     <executions>  
                          <execution>  
                               <id>copy-dependencies</id>  
                               <phase>package</phase>  
                               <goals>  
                                    <goal>copy-dependencies</goal>  
                               </goals>  
                               <configuration>  
                                    <outputDirectory>./docroot/WEB-INF/lib</outputDirectory>  
                                    <overWriteReleases>true</overWriteReleases>  
                                    <overWriteSnapshots>true</overWriteSnapshots>  
                                    <overWriteIfNewer>true</overWriteIfNewer>  
                               </configuration>  
                          </execution>  
                     </executions>  
                </plugin>  
                <plugin>  
                     <groupId>org.codehaus.mojo</groupId>  
                     <artifactId>exec-maven-plugin</artifactId>  
                     <version>1.2</version>  
                     <executions>  
                          <execution>  
                               <id>Run Ant Build</id>  
                               <phase>install</phase>  
                               <goals>  
                                    <goal>exec</goal>  
                               </goals>  
                               <configuration>  
                                    <executable>ant</executable>  
                               </configuration>  
                          </execution>  
                     </executions>  
                </plugin>  
           </plugins>  
      </build>  
      <repositories>  
           <repository>  
                <id>vaadin-addons</id>  
                <url>http://maven.vaadin.com/vaadin-addons</url>  
           </repository>  
      </repositories>  
 </project>  

Liferay and CAS SSO

I am attempting to use CAS SSO as the authentication handler for Liferay. The architecture for it is as following: I want to use the CAS-SSO as the authentication server. And I am trying to set up Liferay as the database of record for the user records, and authenticate all username/passwords against the Liferay database. Out of the box the CAS-SSO is supposed to support authenticating against a database. The mechanism CAS uses is a series of 'AuthenticationHandlers' that are injected when the server is started up, and are defined in the cas-web\WEB-INF\deployerConfigContext.xml file. The only difficult part of the configuration of this for Liferay is the fact that Liferay stores it's password in a salted and encrypted form. To get around this, a 'Password Encoder' class needs to be created that matches the Liferay mechanism. This encoder can then be injected along with the database information in the CAS configuration file as below:

 <bean class="org.jasig.cas.adaptors.jdbc.SearchModeSearchDatabaseAuthenticationHandler">  
                          <property name="tableUsers">  
                               <value>User_</value>  
                          </property>  
                          <property name="fieldUser">  
                               <value>emailAddress</value>  
                          </property>  
                          <property name="fieldPassword">  
                               <value>password_</value>  
                          </property>  
                          <property name="passwordENcoder">  
                               <bean class="com.sample.libraries.sso.LiferayPasswordEncoder">  
                                    <constructor-arg name="encodingAlgorithm" value="SHA"/>  
                               </bean>  
                          </property>  
                          <property name="dataSource" ref="dataSource" />                      
                     </bean>  

 <!-- Data source .. point this to the Liferay Database -->  
      <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">  
           <property name="driverClassName">  
                <value>com.postgressql.jdbc.Driver</value>  
           </property>  
           <property name="url">  
                <value>jdbc:postgresql://localhost:5432/lportal</value>  
           </property>  
           <property name="username">  
                <value>${user.name}</value>  
           </property>  
           <property name="password">  
                <value>${pass.word}</value>  
           </property>  
      </bean>  

Sample password encoder:

 package com.sample.libraries.sso;  
 import java.io.UnsupportedEncodingException;  
 import java.security.MessageDigest;  
 import java.security.NoSuchAlgorithmException;  
 import java.security.SecureRandom;  
 import java.util.Random;  
 import javax.validation.constraints.NotNull;  
 import org.jasig.cas.authentication.handler.PasswordEncoder;  
 import org.vps.crypt.Crypt;  
 import com.liferay.portal.kernel.util.Base64;  
 import com.liferay.portal.kernel.util.Digester;  
 import com.liferay.portal.kernel.util.Validator;  
 public class LiferayPasswordEncoder implements org.jasig.cas.authentication.handler.PasswordEncoder {  
       public static final String UTF8 = "UTF-8";  
        public static final String TYPE_CRYPT = "CRYPT";  
        public static final String TYPE_MD2 = "MD2";  
        public static final String TYPE_MD5 = "MD5";  
        public static final String TYPE_NONE = "NONE";  
        public static final String TYPE_SHA = "SHA";  
        public static final String TYPE_SHA_256 = "SHA-256";  
        public static final String TYPE_SHA_384 = "SHA-384";  
        public static final String TYPE_SSHA = "SSHA";  
        public static final DigesterImpl digesterImpl = new DigesterImpl();  
        @NotNull  
        private static String PASSWORDS_ENCRYPTION_ALGORITHM = TYPE_SHA;  
        public LiferayPasswordEncoder() {  
        }  
        public LiferayPasswordEncoder(final String encodingAlgorithm) {  
          PASSWORDS_ENCRYPTION_ALGORITHM = encodingAlgorithm;  
        }  
        public static final char[] saltChars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789./"  
            .toCharArray();  
        public static String encrypt(String clearTextPassword) {  
          return encrypt(PASSWORDS_ENCRYPTION_ALGORITHM, clearTextPassword, null);  
        }  
        public static String encrypt(String clearTextPassword,  
            String currentEncryptedPassword) {  
          return encrypt(PASSWORDS_ENCRYPTION_ALGORITHM, clearTextPassword,  
              currentEncryptedPassword);  
        }  
        public static String encrypt(String algorithm, String clearTextPassword,  
            String currentEncryptedPassword) {  
          if (algorithm.equals(TYPE_CRYPT)) {  
            byte[] saltBytes = _getSaltFromCrypt(currentEncryptedPassword);  
            return encodePassword(algorithm, clearTextPassword, saltBytes);  
          } else if (algorithm.equals(TYPE_NONE)) {  
            return clearTextPassword;  
          } else if (algorithm.equals(TYPE_SSHA)) {  
            byte[] saltBytes = _getSaltFromSSHA(currentEncryptedPassword);  
            return encodePassword(algorithm, clearTextPassword, saltBytes);  
          } else {  
            return encodePassword(algorithm, clearTextPassword, null);  
          }  
        }  
        protected static String encodePassword(String algorithm,  
            String clearTextPassword, byte[] saltBytes) {  
          try {  
            if (algorithm.equals(TYPE_CRYPT)) {  
              return Crypt.crypt(saltBytes, clearTextPassword.getBytes(UTF8));  
            } else if (algorithm.equals(TYPE_SSHA)) {  
              byte[] clearTextPasswordBytes = clearTextPassword  
                  .getBytes(UTF8);  
              // Create a byte array of salt bytes appeneded to password bytes  
              byte[] pwdPlusSalt = new byte[clearTextPasswordBytes.length  
                  + saltBytes.length];  
              System.arraycopy(clearTextPasswordBytes, 0, pwdPlusSalt, 0,  
                  clearTextPasswordBytes.length);  
              System.arraycopy(saltBytes, 0, pwdPlusSalt,  
                  clearTextPasswordBytes.length, saltBytes.length);  
              // Digest byte array  
              MessageDigest sha1Digest = MessageDigest.getInstance("SHA-1");  
              byte[] pwdPlusSaltHash = sha1Digest.digest(pwdPlusSalt);  
              // Appends salt bytes to the SHA-1 digest.  
              byte[] digestPlusSalt = new byte[pwdPlusSaltHash.length  
                  + saltBytes.length];  
              System.arraycopy(pwdPlusSaltHash, 0, digestPlusSalt, 0,  
                  pwdPlusSaltHash.length);  
              System.arraycopy(saltBytes, 0, digestPlusSalt,  
                  pwdPlusSaltHash.length, saltBytes.length);  
              // Base64 encode and format string  
              return Base64.encode(digestPlusSalt);  
            } else {  
              return digesterImpl.digest(algorithm, clearTextPassword);  
            }  
          } catch (NoSuchAlgorithmException nsae) {  
            throw new SecurityException("LiferayPasswordEncryption error:"  
                + nsae.getMessage(), nsae);  
          } catch (UnsupportedEncodingException uee) {  
            throw new SecurityException("LiferayPasswordEncryption error:"  
                + uee.getMessage(), uee);  
          }  
        }  
        private static byte[] _getSaltFromCrypt(String cryptString) {  
          byte[] saltBytes = null;  
          try {  
            if (Validator.isNull(cryptString)) {  
              // Generate random salt  
              Random random = new Random();  
              int numSaltChars = saltChars.length;  
              StringBuilder sb = new StringBuilder();  
              int x = random.nextInt(Integer.MAX_VALUE) % numSaltChars;  
              int y = random.nextInt(Integer.MAX_VALUE) % numSaltChars;  
              sb.append(saltChars[x]);  
              sb.append(saltChars[y]);  
              String salt = sb.toString();  
              saltBytes = salt.getBytes(Digester.ENCODING);  
            } else {  
              // Extract salt from encrypted password  
              String salt = cryptString.substring(0, 2);  
              saltBytes = salt.getBytes(Digester.ENCODING);  
            }  
          } catch (UnsupportedEncodingException uee) {  
            throw new SecurityException(  
                "Unable to extract salt from encrypted password: "  
                    + uee.getMessage(), uee);  
          }  
          return saltBytes;  
        }  
        private static byte[] _getSaltFromSSHA(String sshaString) {  
          byte[] saltBytes = new byte[8];  
          if (Validator.isNull(sshaString)) {  
            // Generate random salt  
            Random random = new SecureRandom();  
            random.nextBytes(saltBytes);  
          } else {  
            // Extract salt from encrypted password  
            try {  
              byte[] digestPlusSalt = Base64.decode(sshaString);  
              byte[] digestBytes = new byte[digestPlusSalt.length - 8];  
              System.arraycopy(digestPlusSalt, 0, digestBytes, 0,  
                  digestBytes.length);  
              System.arraycopy(digestPlusSalt, digestBytes.length, saltBytes,  
                  0, saltBytes.length);  
            } catch (Exception e) {  
              throw new SecurityException(  
                  "Unable to extract salt from encrypted password: "  
                      + e.getMessage(), e);  
            }  
          }  
          return saltBytes;  
        }  
        public String encode(String pwd) {  
          return encrypt(pwd);  
        }  
 }