A presentation titled "Managing Space, Time and Conflation Issues in a Geospatial Road Database" was presented at the GeoTec2003 conference in Vancouver. The related paper can be downloaded from here.

The BC Digital Road Atlas (BCDRA) database provides information, current and historical, for roads in British Columbia. Before diving into the tables inside the database, a few key concepts need to be discussed.

This diagram demonstrates the important topological concepts used by the datamodel.

The roads are represented by a series of small lines, each called a road segment (about 200,000 in the DRA). The segment end points (called intersections) of each of these road segments are shown in magenta. Each of these linear road segments is given a unique number called rd_segment_id, as shown in the image. The individual line segments themselves are uninteresting and homogeneous, meaning that no signifigant events occur on the segment like an intersection, speed limit change, or name change. Spatial datatypes in the database allow for storage and retrieval of point, line and polygon features. These structures are provided by the PostGIS spatial extension to the PostgreSQL database.

Temporal changes are also stored in the database, which is why there are so many admit_date, retire_date, and most_recent columns in the database. Lets look at a simple example - rd_segment_id 165599, in the above diagram, has its speed limit changed from 50 km/h to 60 km/h on 2001-09-06. The speed limit is recorded in the speed column of the road_nav table.

Prior to the change, we'll see a row in road_nav that looks like:

When the change is made, we'll retire this row and insert a new row. The result looks like:

So, the admit_date is the date when that information first became valid, and the retire_date is when that information became obsolete. Most_recent is a convenience so you can easily access information that is most up-to-date.

Lets assume that this road segment undergoes several changes:

So, to find the speed limit on 2001-10-15, we would do this search:

SELECT * FROM road_nav WHERE rd_segment_id=165599 AND
admit_date <= '2001-10-15' AND
(not(retire_date < '2001-10-15') or retire_date = NULL)

Meaning, return rows that where admitted before our date (reject data about future events), and were not retired before our date (reject data that was superceeded).

There are some cases when this becomes more tricky. This occurs when a road segment undergoes a significant topological change - like splitting in two or its endpoints moving.

Here's an example of road segment 16874 being split into 25000 and 25001 to accomidate a new road (25002).

So, direct segment-to-segment tracking is not upheld. Although this appears to be a major problem, in practice its not because most user queries will still work. For example, 'What was the speed on 2001-10-15 for the 2100 block of 34th avenue?'.

Directional adjectives (left,right,to,and from) are used in this model. These are refering to how the road segment was digitized - from the first point to the last point.

Overview of Data Model

larger version

Road Segment

The road_segment table contains the topological details of each of the line segments. The actual geometry of the segment is in the geometry table. Column Description rd_segment_id Universal road segment identifier from_intersection Reference to a road_intersection entry defining the first point in the segment. to_intersection Reference to a road_intersection entry defining the last point in the segment. mslink Reference to GISI's unique road segment identifier

---

Road Info

The Road Info tables contain information about the road segments like speed, presence of stop signs at the end points, speed bumps, and grade. Each road segment will have an entry in these tables. The road_nav table

Column	Description
rd_segment_id	Universal road segment identifier
lane_left	On a two way road, the number of lanes to the left of the dividing line. On a one way road, the number of lanes to the left of the center line (rounded down).
lane_right	On a two way road, the number of lanes to the right of the dividing line. On a one way road, the number of lanes to the right of the center line (rounded up).
exit_angle	Bearing (degrees) of the line connecting the last two points of the road segment.
entry_angle	Bearing (degrees) of the line connecting the first two points of the road segment.
travector_direction	"F" - One Way travel (From ->To direction) "R" - One Way travel (To ->From direction) "B" - Two Way travel
length	Length of the road segment (meters)
speed	Speed limit on the road segment (km/h)
from_stop	Information about the From Point code Meaning null no restriction C Cul de Sac D Dead End L Traffic Light O Overpass R Roundabout S Stop Sign U Underpass Y Yield Sign
to_stop	Information about the To Point (see above)
railway_crossing	Total number of railway crossings on the road segment
accessable	GISI accessable code CODE interpretation remark 0 or null always   1 Restricted in some way   > 1 Restricted in some way Future usage, linked to specific or general lookups