## pgRouting on Windows 7

Drive time isochrones with pgRouting and PostGIS on Windows 7 Pro.  I am using Postgresql 9.0, 9.1 and 9.2, PostGIS 2.0.3 and pgRouting 1.0.7-dev.  Anita Graser has some excellent pgRouting resources on her blog  and I used them in conjunction with Jo Cook’s pgRouting with ITN guide to load and build my network dataset.

One of the problems I had while following this post was that I had more than the three airports she used in her example and to manually update the table 116 times for each play park in the county was not an option.

So, how to write a looping function in Postgresql to loop through all the play parks and calculate all the drive times for each facility in one go?

Postgresql docs are a good place to go and this is what worked for me in the end.  Once the table is complete finish up using Anita’s how to.

-- 1. create calculate playpark costs function
CREATE OR REPLACE FUNCTION insertPlayParkCosts() RETURNS INTEGER AS $$-- NOTE: function returns an integer representing the next play park nearest node id DECLARE nn INTEGER; BEGIN -- NOTE: loops through every nearest_node, calculating drive times and inserting records into the table FOR nn IN SELECT nearest_node FROM osplayparks_pts LOOP INSERT INTO catchment_pp ( SELECT id, the_geom, (SELECT sum(cost) FROM ( SELECT * FROM shortest_path(' SELECT objectid AS id, source::int4 AS source, target::int4 AS target, traveltime::float8 AS cost FROM angus_routes', nn, -- this updates each time with a new value for nearest_node id, false, false)) AS foo ) AS cost FROM vertices_tmp); END LOOP; END;$$ LANGUAGE 'plpgsql';

-- 2. run calculate playpark cost function
SELECT * FROM insertPlayParkCosts();

## Installing pgRouting on Windows 7

This quick guide outlines the steps I took to get Postgresql, PostGIS and pgRouting installed on Windows 7 Pro.

1. Install Postgresql

Download the installer from EnterpriseDB.com, double click to start the installer and accept most of the defaults except for the port to run the server on.  I installed 9.0.13, 9.1.9 and 9.2.4 running each server on a different port.  So 9.0.13 is running on 5432, 9.1.9 on 5433 and 9.2.4 on 5434.

The installers install the correct version of pgAdmin III for each version of the database and it is recommended to use the right pgAdmin III with the right Postgresql.  This ensures database integrity and interactive functionality suitable for each version.  For Postgresql 9.2 use pgAdmin 1.16.1, for Postgresql 9.1 use 1.14.3 and for Postgresql 9.0 use 1.12.3.

2. Install PostGIS

For Postgresql 9.0 install http://download.osgeo.org/postgis/windows/pg90/postgis-pg90-setup-1.5.5-1.exe and select the correct instance of Postgresql on port 5432

For Postgresql 9.1 install http://download.osgeo.org/postgis/windows/pg91/postgis-pg91-setup-1.5.5-1.exe and select the correct instance of Postgresql on port 5433. I could have installed a later version of PostGIS on 9.1 but I needed 1.5.5 to work with AstunTechnology’s PortableGIS Loader! (See archaeogeek’s blog)

For Postgresql 9.2 install http://download.osgeo.org/postgis/windows/pg92/postgis-pg92-setup-2.0.3-2.exe and select the correct instance of Postgresql on port 5434

3. Install pgRouting

For the development versions of pgRouting visit Boston GIS and get 1.0.7dev for Windows 32-bit and PostgreSQL 9.2 (I am running 32-bit Windows).  pgRouting contains the following features:

• Dijkstra algorithm – Shortest path algorithm, which named in honor of Prof. Dr. Edsger Wybe Dijkstra who has invented it
• A-star (A*) algorithm – Shortest path algorithm using heuristical function
• Driving distance – area person can cover in certain time from start point using road network
• TSP Travelling Salesman Problem solution
• Shooting star (Shooting*) algorithm – Shortest path algorithm for real road networks with turn restrictions, traffic lights and one way streets.

To install and enable pgRouting (read the README) copy the contents of /lib (.dlls required for problem solving) and /share/extension (.sql files to enable pgrouting in the database) to the C:\Program Files\PostgreSQL\9.2\lib\ and C:\Program Files\PostgreSQL\9.2\share\extension folders respectively.

Then, using the psql prompt or pgAdmin III, do:

CREATE EXTENSION pgrouting;

Now you should have a pgRouting-enabled PostGIS database.

There are older version of pgRouting available to download from Github.  You’ll need to extract the zip archive and run the various SQL files to install the required routing functionality as well as copying the .DLL files to the correct folder as above.  Boston GIS have also release dev versions of pgRouting 2.0.0 which I have not tried.

## Batch Exporting to PostGIS from Oracle

The process in the previous post loaded one layer at a time.  This batches the process.

Some preparation of a text file with a list of all Oracle table names to be extracted and loaded into PostGIS is required.

rem This reads a list of table names from txt file and passes them to ogr2ogr which then exports from Oracle into PostGIS
for /F %%f in (C:\path\to\your\textfile\OracleTables.txt) do ogr2ogr -a_srs EPSG:27700 -overwrite -f "PostgreSQL" -nln yourschema.%%f PG:"host=localhost user=youruser password=yourpassword dbname=yourdatabase" OCI:"oracleuser/oraclepassword@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclehost)(PORT = 1521)))(CONNECT_DATA = (SID =oraclesid))):oracleschema.%%f"
rem
pause

## Exporting Data from Oracle to PostGIS

Traditionally exporting from Oracle to PostGIS involved an intermediate step Oracle to Shapefile and then Shapefile to PostGIS. But this means that field names longer than 10 characters in Oracle get truncated in the conversion to Shapefile. And using the QGIS or PgAdminIII shp2pgsql gui has no easy way to set the schema or SRID other than manual intervention. I guess it could be run from the command line though…

Using Safe’s FME you can go directly from Oracle to PostGIS. But there is no way to define the destination schema in the PostGIS database. The PostGIS writer expects there to be a schema with the same name as the Oracle schema in order to complete the extraction and translation. See https://groups.google.com/forum/#!topic/fmetalk/zQsUUJNR5h8

So, open-source here we come. ogr2ogr can read and write to a number of different formats including Oracle (this uses the proprietary OCI.dll). On Windows install OSGeo4W with GDAL/OGR. Run the setup.exe and “Advanced Install” to install the gdal-oracle10g package. See http://stackoverflow.com/questions/9789686/migrate-spatial-data-from-oracle-to-postgresql

The command to issue at the command line is (all on one line):

ogr2ogr -a_srs <srs> -overwrite -f "PostgreSQL" -nln <postgis schema>.<postgis table> PG:"host=<postgis host> user=<postgis user> password=<postgis password> dbname=<postgis database>" OCI:"<oracle user>/<oracle password>@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = <port number>)))(CONNECT_DATA = (SID =<sid name>))):<oracle schema>.<oracle table>"

<srs> = in the EPSG format e.g EPSG:27700

This uses ogr2ogr to connect to an Oracle database, pull a table from a schema and write it to a defined schema in PostGIS database.

Nice!

Sources:

## Open-source GIS cluster

This is the challenge for this week.

PostgreSQL + PostGIS + MapServer + MapProxy + Quantum GIS on Windows 7 32-bit

The aim is to create a scalable mapping solution based on open-source software that is easy to use and maintain, is fast and responsive and that can provide data in a number of different formats. Windows installers are available for most of the applications I wanted to use which made life a bit easier. Doing the same thing on Linux might be even easier. The corporate proxy and firewall certainly made things more complicated than they should have been.

## Inver-Inver 2012

The Steeple Church is raising funds for the Church redevelopment project.  Part of the plan is to help raise £10000 by walking and cycling across Scotland from West to East in four days.  You can donate through the following JustGiving account.

• Friday : Inverie – Roy Bridge : walk 16.5 miles(to west end of Loch Arkaig) and cycle 25 miles
• Saturday : Roy Bridge – Glen Feshie Hostel : cycle 52 miles
• Sunday : Glen Feshie Hostel – Braemar : walk 19 miles (to Linn of Dee) and cycle 7 miles
• Sunday : Braemar – Inverbervie : cycle 58 miles

The actual route taken is more direct than that shown on the map and does a better job of joining the dots (The route shown is approximate).  The support vehicles will probably have to travel the route shown in blue.

## Embedding maps

In an effort to make more useful information available to local residents without being overly technical, part of what I have been working on is making simple maps to embed in a website.  These return some (hopefully) useful information.  There is a move away from monolithic web GIS applications that do everything to making spatial information available everywhere across a website.  These simple maps can be embedded on any page with the website.  Each map will only display information relevant to the content on the page.

Waste collection dates (custom complex map viewer)

My Location report (default complex map viewer) Continue reading Embedding maps

## Unconnected black hole

According to a recent survey by the BBC, Newtyle is a veritable void of connectivity. Using Android devices to record mobile signal strength data was collected over a three week period. The resulting coverage map can be seen on the BBC site – http://www.bbc.co.uk/news/technology-14582499. If you put in a Newtyle postcode (PH12 8TU will do) you can see that the village is just about the only black spot on the map.  Do bear in mind that less than 50,000 people from across the UK took part in the survey and not many of them came from Newtyle.