Hov Database Structure

Below are descriptions of each table used in the HOV Database as well as the SQL queries which may be used to create them.

Note The table definitions are generated on demand from the actual tables in MySQL, though the syntax can be adapted for other SQL databases.

Corridors

The corridors table specifies a set of traffic corridors in which data is collected. A corridor entry contains the name of the corridor, and an optionaly comment describing the corridor.

Columns

Name Description
Name The corridor's name
Comment An optional comment or description

Table Definition

CREATE TABLE `corridors` (
  `name` varchar(24) NOT NULL default '',
  `comment` varchar(128) default '',
  `state_route` char(3) default NULL,
  `created_on` datetime default NULL,
  `updated_on` datetime default NULL,
  PRIMARY KEY  (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='A table of traffic corridors'

Sites

The sites table specifies a set of traffic collection sites. Each site belongs to a corridor. Sites contain a legacy_id to facilitate importing legacy data from the previous HOV collection software.

Columns

Name Description
Name The sites's name
Corridor ID The name of corridor which this site resides on
Legacy ID A two digit numeric ID used to identify this site for the previous HOV data collection tools.
Comment An optional comment or description

Table Definition

CREATE TABLE `sites` (
  `corridor_id` varchar(24) default '',
  `name` varchar(32) NOT NULL default '',
  `legacy_id` tinyint(4) unsigned default NULL,
  `comment` varchar(128) default '',
  `arm` float default NULL,
  `created_on` datetime default NULL,
  `updated_on` datetime default NULL,
  `site_map` varchar(255) default NULL,
  PRIMARY KEY  (`name`),
  KEY `corridor_index` (`corridor_id`,`name`),
  CONSTRAINT `0_37` FOREIGN KEY (`corridor_id`) REFERENCES `corridors` (`name`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='A table of traffic collection sites'

Collection Points

The Collection Points table contains a list of each collection point available at a site. A collection point specifies various details about the spot where data is collected including direction, the number of lanes, whether there is an HOV lane present, and information about the type of lane.

Columns

Name Description
Site ID The name of the site where this collection point is located
ID A unique identifer for this collection point
Direction The direction of traffic flow at this point. It may be one of North, South, East, or West.
Lanes The number of lanes at this site
Hov Lane The number of the HOV lane if any
Ramp Type A value specifying if this is a ramp, values may be On, Off, or if it is not a ramp None.
Express Lane Determines whether or not this point is an express lane.
Comment An optional comment or description

Table Definition

CREATE TABLE `collection_points` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `site_id` varchar(32) default '',
  `direction` enum('North','South','East','West') NOT NULL default '