Making of the Charlottetown Transit Map
To create the Charlottetown Transit Map, I needed information about routes, stops, and schedules. Fortunately the City of Charlottetown has done a good job of spreading paper copies of the route map and schedule around; they make PDF versions available on their website. With these pages I had everything I needed to start.
To start I created a MySQL table called routes to hold information about each of the four bus routes:
CREATE TABLE `routes` ( `routenumber` int(11) NOT NULL default '0', `routename` char(50) NOT NULL default '' );
As only Route #1 was in operation when I started, I simply added one record to this table:
INSERT INTO `routes` VALUES (1,'Winsloe - University Ave.');
Next I created a table called 'stops to hold information about each place the bus stops:
CREATE TABLE `stops` ( `routenumber` int(11) NOT NULL default '0', `stopnumber` int(11) NOT NULL default '0', `stopname` char(50) NOT NULL default '', `latitude` double(11,6) default '0.000000', `longitude` double(11,6) default '0.000000', `street_no` int(11) default NULL, `street_nm` char(50) NOT NULL default '' );
I then went through the printed schedule and created a record for each stop.
INSERT INTO `stops` VALUES (1,4,'Royal Bank / Coop Basics',46.242129,-63.132420,NULL,'');
For each stop I entered the route number (always 1 in this case, as I was only dealing with Route #1), the stop number (there are no official stop numbers that I know of, so I just assigned them incrementally), the stop name (from the schedule), and the latitude and longitude of the stop.
To geocode the stop locations (get their latitude and longitude) I used Google Maps as follows:
- Zoomed in to Charlottetown.
- For each stop, I double-clicked on the map at the stop location; this re-centers the map at that location.
- After the double-click, I clicked on the "Link to this page" hyperlink in the map sidebar; this results in a URL like this:
- The latitude (46.237016) and longitude (-63.128686) are encoded in the URL; I pulled these out, and used them in the table as values for that stop.
- Repeated for each stop.
The street_no and street_nm fields are always empty at present; they're for possible use in later versions of the map application.
To allow the route map to be overlaid on the Google Map required that I obtain the latitude and longitude of each "intersection" on the route -- in reality it's each "place where the road makes a turn."
CREATE TABLE `intersections` ( `routenumber` int(11) NOT NULL default '0', `intersectionnumber` int(11) NOT NULL default '0', `latitude` double(11,5) NOT NULL default '0.00000', `longitude` double(11,5) NOT NULL default '0.00000', `notes` text NOT NULL );
I used the same Google Maps geocoding technique that I used to determine the location of each stop to geocode each intersection, and inserted a record for each one:
INSERT INTO `intersections` VALUES (1,1,46.23444,-63.12808,'Queen and Grafton');
I use the notes field to keep track of the name of the intersection and, optionally, any additional information.
The schedule table is where the heart of the application lies. For each time the bus stops at a given location (from the stops table), there's a record in the schedule table:
CREATE TABLE `schedule` ( `routenumber` int(11) NOT NULL default '0', `stopnumber` int(11) NOT NULL default '0', `stoptime` time NOT NULL default '00:00:00', `flags` char(5) NOT NULL default '', `inout` char(1) NOT NULL default '' );
The routenumber and stopnumber are from the routes and stops tables respectively; the stoptime is the time of the stop, the flag field contains any special flags about the stop (for example cs is used at present on the printed schedule to indicate curb stop only) and the inout field contains i for an inbound trip and o for an outbound trip.
So for each stop on the schedule, a record gets inserted:
INSERT INTO `schedule` VALUES (1,1,'06:45:00','','o');
This is the record for a 6:45 a.m. stop on Route #1, Stop #1, the Confederation Centre of the Arts, on an outbound trip.