Android Bug - Solution

If, whilst building and deploying an Android app, the following error rears it´s unwanted head in the console - INSTALL_PARSE_FAILED_NO_CERTIFICATES, then many solutions exist on the web. None of them worked for me in isolation!!! In the end I had to combine two of them, - change a filename in the res folder AND then delete the bin folder (to force a re-build). then change the filename back to what it was called before. Then this error disappeared........

Importing OSM data to PostGIS 2.0

This is a note for myself, and details the lessons I have learned whilst trying to load an .osm file into a PostGIS 2.0 database.

1. don´t use osmosis, due to the fact it seems to be rarely updated and suffers from this problem which I could not solve - http://wiki.openstreetmap.org/wiki/64-bit_Identifiers.

2. osm2pgsql is much better, but for it to work with PostGIS 2.0 I had to rename the "st_astext(geometry)" function to "astext(geometry)" as otherwise it throws an error. (the old filename refers to a previous version of Postgis that used different naming conventions)

3. despite using the --password and -W flag, the postgis datbase threw an error as it required the password. Therefore I had to change the 'md5' permission for a local user to 'trust' so that no password was necessary in the pg_hba.conf file.

4. I had to paste the following code in SQL to create an operator that was missing in the PostGIS 2.0 distribution


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);

Updated GeoTools Routing Example

I have updated the code example for the GeoTools routing that I had earlier on this blog. You can find the code and example datasets below

https://sites.google.com/site/usefulpracticalgeoblog/home/routecalculationexample.

Route_Example_GeoTools.zip is the latest code example. It contains a folder with the necessary Java classes and two .shp files. One file contains the network dataset for Vienna, Austria (available from http://www.geofabrik.de/data/shapefiles.html), the other contains two destinations for the example code. It requires Geotools 2.7 libraries to run. I have tried to comment it heavily to make it clear. It uses only the distance information to calculate the weights of the edges, but that can easily be changed to incorporate anything else, such as travel time etc. This code example calculates the route from one origin to two destinations (green points on screenshot below).

Here is a screenshot of the output :

Ghostly GTFS Skyscraper

This is not some ghostly skyscraper but a collection of all the space-time paths from the public transport system in Madrid for a single day (z = time of the day). Click on the picture itself for a larger version.  I am working on some code that can eventually calculate the areas of spatio-temporal accessibility (travel time isolines) from point locations using the original (General Transit Feed Specification) GTFS data. This is the first step I guess....

GeoSimple Data Free For All!!!

If anyone is looking for a comprehensive places dataset, then try the open sourced SimpleGeo database dump (see link below)

http://archive.org/details/2011-08-SimpleGeo-CC0-Public-Spaces


From SimpleGeo: "We’re very excited to announce that the SimpleGeo’s CC0 Places data set is now available for download at no cost. If you’d like to get your hands on 21M+ POIs that cover 63 countries, we’re ready to hand that over to you in one file. The file is about 2GB in .ZIP format, and remember, with the CC0 license, this data becomes yours – free and clear – to do whatever you want."


It holds 21 million of places!!!, but with a horrible geo-json format, probably pumped out of a NoSQL database. Therefore, I have created a couple of Java classes that export it to a friendlier CSV file. First run the Clean_Geojson.java class to produce a more easily parsed file, and then run ConvertToCSV.java on the file output by the cleaning process. As the files are huge, this process avoids storing everything in memory (which may cause your JVM to become unhappy with its Heap size).

https://sites.google.com/site/usefulpracticalgeoblog/home/jai

On first look, it is more complete than any other place dataset that I have come across. Even beats OSM here in Zürich, which up until now was perhaps the best open source dataset of places.

NOTE : requires the google JSON java parser, available here http://code.google.com/p/google-gson/downloads/list
 

GeoTools Route Calculation

EDIT: THIS POST HAS BEEN REPLACED BY THE MORE RECENT BLOG POST FOUND HERE
 http://usefulpracticalgeoblog.blogspot.ch/2012/09/geotools-routing.html

Java Classes to calculate network distances between an origin and several destinations. Uses Geotools 2.7 library and JAI library, details of where to download them can be found in the Main class. Takes as input ESRI shapefiles for the network and destinations and outputs either a FeatureSource containing the routes and associated costs or just an arraylist of costs (which is here the euclidean distance) and objectIDs.

https://sites.google.com/site/usefulpracticalgeoblog/home/routecalculationexample

Also included are the two shapefiles for use with the example Main Class. When used on other datasets make sure the CRS and SRID values reflect the datasets to which they are applied, otherwise I imagine an error frenzy will ensue :-)

Installing PostGIS with Tomcat - Important Reminder

Copy the Postgres JDBC jar to $CATALINA_HOME/common/lib (This is the wrong path for my tomcat installation, i used Apache Software Foundation\apache-tomcat-6.0.26\lib instead). As with Oracle, the jars need to be in this directory in order for DBCP's Classloader to find them. This has to be done regardless of which configuration step you take next.

Taken from :
http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html#PostgreSQL

Without this the class will give a no class found error :-)

And here is a Hello World taken form

http://www.fankhausers.com/postgresql/jdbc/#hello

HelloPostgresql.java

/**
 * A demo program to show how jdbc works with postgresql
 * Nick Fankhauser 10/25/01
 * nickf@ontko.com or nick@fankhausers.com
 * This program may be freely copied and modified
 * Please keep this header intact on unmodified versions
 * The rest of the documentation that came with this demo program
 * may be found at http://www.fankhausers.com/postgresql/jdbc
 */



import java.sql.*;   // All we need for JDBC
import java.text.*;
import java.io.*;

public class HelloPostgresql
{
  Connection       db;        // A connection to the database
  Statement        sql;       // Our statement to run queries with
  DatabaseMetaData dbmd;      // This is basically info the driver delivers
                              // about the DB it just connected to. I use
                              // it to get the DB version to confirm the
                              // connection in this example.

  public HelloPostgresql(String argv[])
    throws ClassNotFoundException, SQLException
  {
    String database = argv[0];
    String username = argv[1];
    String password = argv[2];
    Class.forName("org.postgresql.Driver"); //load the driver
    db = DriverManager.getConnection("jdbc:postgresql:"+database,
                                     username,
                                     password); //connect to the db
    dbmd = db.getMetaData(); //get MetaData to confirm connection
    System.out.println("Connection to "+dbmd.getDatabaseProductName()+" "+
                       dbmd.getDatabaseProductVersion()+" successful.\n");
    sql = db.createStatement(); //create a statement that we can use later


    String sqlText = "create table jdbc_demo (code int, text varchar(20))";
    System.out.println("Executing this command: "+sqlText+"\n");
    sql.executeUpdate(sqlText);

 
    sqlText = "insert into jdbc_demo values (1,'One')";
    System.out.println("Executing this command: "+sqlText+"\n");
    sql.executeUpdate(sqlText);

 
    sqlText = "insert into jdbc_demo values (3,'Four')";
    System.out.println("Executing this command twice: "+sqlText+"\n");
    sql.executeUpdate(sqlText);
    sql.executeUpdate(sqlText);


    sqlText = "update jdbc_demo set text = 'Three' where code = 3";
    System.out.println("Executing this command: "+sqlText+"\n");
    sql.executeUpdate(sqlText);
    System.out.println (sql.getUpdateCount()+
                        " rows were update by this statement\n");


    System.out.println("\n\nNow demostrating a prepared statement...");
    sqlText = "insert into jdbc_demo values (?,?)";
    System.out.println("The Statement looks like this: "+sqlText+"\n");
    System.out.println("Looping three times filling in the fields...\n");
    PreparedStatement ps = db.prepareStatement(sqlText);
    for (int i=10;i<13;i++)
    {
      System.out.println(i+"...\n");
      ps.setInt(1,i);         //set column one (code) to i
      ps.setString(2,"HiHo"); //Column two gets a string
      ps.executeUpdate();
    }
    ps.close();

 
    System.out.println("Now executing the command: "+
                       "select * from jdbc_demo");
    ResultSet results = sql.executeQuery("select * from jdbc_demo");
    if (results != null)
    {
      while (results.next())
      {
        System.out.println("code = "+results.getInt("code")+
                           "; text = "+results.getString(2)+"\n");
      }
    }
    results.close();


    sqlText = "drop table jdbc_demo";
    System.out.println("Executing this command: "+sqlText+"\n");
    sql.executeUpdate(sqlText);


    db.close();
  }

  public static void correctUsage()
  {
    System.out.println("\nIncorrect number of arguments.\nUsage:\n "+
                       "java   \n");
    System.exit(1);
  }

  public static void main (String args[])
  {
    if (args.length != 3) correctUsage();
    try
    {
      HelloPostgresql demo = new HelloPostgresql(args);
    }
    catch (Exception ex)
    {
      System.out.println("***Exception:\n"+ex);
      ex.printStackTrace();
    }
  }
}