Data source

My dataset consists of 108, 078 road traffic accidents in Greater Manchester between 1st January 1998 and 30th September 2008. Each accident is represented by a point object. The points are located using Ordnance Survey grid references. To get a feel for the dataset visit www.gmtu.gov.uk/gmaccidents. What is a road accident? The word accident will be used throughout. Some organisations prefer the use of collision rather than accident since accident implies that no-one was at fault. See more discussion here. Bizarrely the issue crops up in the film Hot Fuzz.

The task is to get this data into postgresSQL. From there it can be served as a Web Feature Service (WFS) using GeoServer. The WFS will then act as input into my (yet to be developed)Web Processing Service (WPS).

postgreSQL was installed with the postGIS component (postGIS add spatial capabilities to postGRES). A new database, accidents, was created using the postGIS template.

The source data is held in MapInfo format. This was exported to shapefile format. The shapefile was converted into a file of postGRES SQL INSERT statements using shp2pgsql.exe. From the DOS prompt:

shp2pgsql -c -s 27700 c:\temp\accidents_point accident accidents > c:\temp\accidents.sql

accidents.sql was run against the accidents database in postgreSQL.

The table fields are:

gid: unique identifier added by postGIS
accidentID: original unique identifier
year: year of accident
severity: accident severity. 1=fatal, 2=serious, 3=slight The definition of accident severity can be found here.
numbercasu: number of casualties
date: date of accident
easting: OSGB easting
northing: OSGB northing
ward: ward (definition) where the accident occured.
lacode: numeric code for the local authority (definition) where the accident occured

The import into postgreSQL was tested in QGIS which can open postGIS tables:


Click here for larger image.