Database structure - HY-OHTUPROJ-OSRM/osrm-project GitHub Wiki
The routing data in the PostGIS database consists of the output of the command osm2pgsql --slim with route-data.osm.pbf (converted from Digiroad) as input, with the addition of other tables created by the routing-api program. The schema is defined in the file createdatabase.js. The polygon table uses the WGS 84 Web Mercator projection (SRID 3857) matching the Digiroad material.
osm2pgsql Tables
planet_osm_ways
| Column | Data Type | Description |
|---|---|---|
| id | bigint | Unique OSM way ID. |
| nodes | ARRAY | Array of node IDs (ordered) that make up the way. |
| tags | ARRAY | Array of alternating key-value tag strings (e.g. ['highway','primary','name','Main Street']). |
planet_osm_nodes
| Column | Data Type | Description |
|---|---|---|
| id | bigint | Unique OSM node ID. |
| lat | integer | Latitude in 1e-7 degrees (divide by 10⁷ to get WGS84 latitude). |
| lon | integer | Longitude in 1e-7 degrees (divide by 10⁷ to get WGS84 longitude). |
Tables
zones
| Column | Type | Description |
|---|---|---|
| id | SERIAL | Primary key |
| type | TEXT | Type/category of the zone |
| name | TEXT | Name of the zone |
| effect_value | DOUBLE PRECISION | Numeric effect value (e.g., restriction weight) |
| source | TEXT | Source of the zone data |
| geom | GEOMETRY(POLYGON) | Zone geometry in WGS84 (EPSG:4326), must be valid |
| updated_at | TIMESTAMP | Last update timestamp (default: now) |
| created_at | TIMESTAMP | Creation timestamp (default: now) |
temporary_routes
| Column | Type | Description |
|---|---|---|
| id | SERIAL | Primary key |
| type | TEXT | Type/category of the route |
| name | TEXT | Name or label of the route |
| status | BOOLEAN | Active status of the route (default: true) |
| tags | JSONB | Optional tags in JSON format (default: '[]') |
| geom | GEOMETRY(LINESTRING) | Route geometry in WGS84 (EPSG:4326), must be valid |
| length | DOUBLE PRECISION | Length of the route (in meters) |
| speed | INTEGER | Speed limit (km/h or unit specified) |
| max_weight | DOUBLE PRECISION | Maximum allowed weight (tons or unit specified) |
| max_height | DOUBLE PRECISION | Maximum allowed height (meters) |
| description | TEXT | Description or notes |
| direction | INTEGER | Direction info (default: 2, e.g., both directions) |
| created_at | TIMESTAMP | Creation timestamp (default: now) |
| updated_at | TIMESTAMP | Last update timestamp (default: now) |
municipalities
| Column | Type | Description |
|---|---|---|
| id | SERIAL | Primary key |
| code | TEXT | Unique municipality code |
| name | TEXT | Name of the municipality (not null) |
disconnected_links
| Column | Type | Description |
|---|---|---|
| id | SERIAL | Primary key |
| start_node | INTEGER | Start node ID (required) |
| start_node_name | TEXT | Optional name for the start node |
| start_node_lat | DOUBLE PRECISION | Latitude of the start node |
| start_node_lon | DOUBLE PRECISION | Longitude of the start node |
| end_node | INTEGER | End node ID (required) |
| end_node_name | TEXT | Optional name for the end node |
| end_node_lat | DOUBLE PRECISION | Latitude of the end node |
| end_node_lon | DOUBLE PRECISION | Longitude of the end node |
| distance | DOUBLE PRECISION | Distance between start and end nodes |
| county_code | TEXT | Optional county code |
| county_name | TEXT | Optional county name |
| temp_road_id | INTEGER | Reference to a temporary route (nullable) |
| hide_status | BOOLEAN | If true, link is hidden (default: false) |
| created_at | TIMESTAMP | Creation timestamp (default: now) |
| updated_at | TIMESTAMP | Last update timestamp (default: now) |