OTP Transit System Map. Following along https://github.com/openplans/OpenTripPlanner/wiki/GettingStartedSystemMap. Tested in Ubuntu 12.04 LTS

OTP Transit System Map.md

Transit System Map from GTFS data

###Dependencies

Add export JAVA_HOME=/usr/lib/jvm/java-7-oracle to ~/.bashrc

# Install tomcat
sudo apt-get install tomcat7
# It may be necessary to edit nano /etc/default/tomcat7 if you are 
# getting JAVA_HOME errors.  Uncomment the JAVA_HOME line

# Install geoserver
cd /var/lib/tomcat7/webapps
http://sourceforge.net/projects/geoserver/files/GeoServer/2.4.0/geoserver-2.4.0-war.zip
unzip geoserver-2.4.0-war.zip
service tomcat7 restart
# Geoserver will be accessible at http://localhost:8080/geoserver (takes awhile to load)

# Install gtfsdb
sudo apt-get install python-setuptools
sudo easy_install pip geoalchemy gtfsdb 

Setup postgres db

First we need to make sure that postgis.sql and spatial_ref_sys.sql exist in the postgis folder (likely /usr/share/postgresql/9.1/contrib/postgis-2.1/). If not, we need to download the postgis tarball and copy the files over:

cd /usr/share/postgresql/9.3/contrib
wget http://download.osgeo.org/postgis/source/postgis-2.1.1.tar.gz
tar xvfz  postgis-2.1.1.tar.gz
cp postgis-2.1.1/postgis.sql postgis-2.1.1/spatial_ref_sys.sql postgis-2.1
rm -r postgis-2.1.1 postgis-2.1.1.tar.gz

/usr/share/postgresql/9.3/contrib

export GTFS_DB=gtfs.seattle.metro
psql -U postgres -c "drop database if exists \"$GTFS_DB\";"
psql -U postgres -c "create database \"$GTFS_DB\";"
psql -U postgres -d "$GTFS_DB" -c "CREATE EXTENSION fuzzystrmatch;"
psql -U postgres -d "$GTFS_DB" -c "CREATE EXTENSION postgis;"
psql -U postgres -d "$GTFS_DB" -c "CREATE EXTENSION postgis_topology;"
psql -U postgres -d "$GTFS_DB" -c "CREATE EXTENSION postgis_tiger_geocoder;"

####Load in data with gtfsdb

gtfsdb-load --database_url postgresql://postgres@localhost/$GTFS_DB --is_geospatial http://metro.kingcounty.gov/GTFS/google_transit.zip

To make this work with qGIS, you need to alter the primary key:

psql -U postgres -d $GTFS_DB
ALTER TABLE routes DROP CONSTRAINT routes_pkey CASCADE;
ALTER TABLE routes ADD COLUMN id SERIAL primary key;
ALTER TABLE stops DROP CONSTRAINT stops_pkey CASCADE;
ALTER TABLE stops ADD COLUMN id SERIAL primary key;
ALTER TABLE patterns DROP CONSTRAINT patterns_pkey CASCADE;
ALTER TABLE patterns ADD COLUMN id SERIAL primary key;
\q

For examples, see https://github.com/albatrossdigital/transit-maps.

Geoserver

• Login with admin, geoserver
• Add new workspace, name: gtfs
• Create new store (right sidebar) and enter PostGIS settings, name: gtfs
• Layers -> Add new resource -> select gtfs:gtfs, do once for Routes, once for Stops

Tilemill

See this gist for details on setting up tilemill.

• Create new project
• Add new PostGIS layer, use host=localhost port=5432 user=postgres dbname=transit as connection string. Add spatial_ref_sys as the "Table or Subquery". For more info on PostGIS and Tilemill: http://www.mapbox.com/tilemill/docs/guides/postgis-work/.

Looking at PostGIS tables

psql -U postgres -d transit
\d

For more details on table size:

SELECT
   relname as "Table", 
   pg_size_pretty(pg_total_relation_size(relid)) As "Size", 
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

Example output:

       Table        |  Size   | External Size 
--------------------+---------+---------------
 stop_times         | 5688 kB | 3016 kB
 spatial_ref_sys    | 3136 kB | 176 kB
 fare_rules         | 272 kB  | 168 kB
 trips              | 224 kB  | 104 kB
 universal_calendar | 184 kB  | 136 kB
 routes             | 72 kB   | 64 kB
 shapes             | 72 kB   | 56 kB
 stops              | 56 kB   | 48 kB
 agency             | 48 kB   | 40 kB
 transfers          | 48 kB   | 40 kB
 patterns           | 40 kB   | 32 kB
 calendar           | 40 kB   | 32 kB
 calendar_dates     | 40 kB   | 32 kB
 geometry_columns   | 32 kB   | 24 kB
 fare_attributes    | 32 kB   | 24 kB
 feed_info          | 32 kB   | 24 kB
 route_type         | 32 kB   | 24 kB
 frequencies        | 24 kB   | 16 kB
 stop_feature_type  | 24 kB   | 16 kB
 stop_features      | 24 kB   | 24 kB

Alternative (from http://gis.stackexchange.com/questions/49737/any-tools-to-convert-gtfs-general-transit-feed-specification-in-shp-kml):

• GTFS to KML
• KML to .shp

easy_install transitfeed
http://sourceforge.net/projects/pygearth/files/latest/download?source=files
tar xvzf "download?source=files"
rm "download?source=files"
cd pyGEarth-0.1.2/



### References
* GTFS data and sources: http://www.gtfs-data-exchange.com/
* [GTFS data and sources from Google](http://code.google.com/p/googletransitdatafeed/wiki/PublicFeeds)
* [SQLAlchemy tutorial](http://www.geoalchemy.org/tutorial.html)
* [gtfsdb docs](https://pypi.python.org/pypi/gtfsdb)
* [Google Groups post](https://groups.google.com/forum/#!searchin/opentripplanner-dev/system$20map%7Csort:date/opentripplanner-dev/0iqXocv56bQ/eCEqkOZfUSEJ)
* [Code For America: Transit map in Tilemill](https://github.com/codeforamerica/Transit-Map-in-TileMill)