Import/VTA Transportation
About
Our project is to import the Valley Transportation Authority's data into Open Street Map. The VTA maintains data for Santa Clara County's public transportation system including bus stops, light-rail stops, park-and-ride, and coordinates access to other transportation datasets including but not limited to bike routes, bike lanes, bike share, transit centers. an objective is to allow users across the world to access their data easily, we are going to be updating Open Street Map with this data. Furthermore, the transit stops will be updated several times a year as they are moved, canceled, or added as maintenance needs arise or pedestrian transportation needs change.
This will be managed by AmigoCloud in conjunction with the VTA
Import Plan Outline
The plan is simple:
1. VTA will upload the data to AmigoCloud
2. AmigoCloud will prep the data for manual insertion into Open Street Map
3. AmigoCloud will be using JOSM to import the data after reviewing each data point.
4. Additional data tags will be added to the open street map data to allow the users of that data more accurate information
5. The VTA data will be modified to use the same tags as are already defined in Open Street Map.
Goals
1. Import the following datasets:
- bus stops
- light rail
- bike paths
- park and ride lots
2. determine a process that can be used to semi-automate future updates of the data on a scheduled basis for the following datasets:
- bus stops
Schedule
March 2014 - Review and import the data
April 2014 - Update the bus stops
Import Data
Background
Data Sources:
Data License: Public Domain
Type of license (if applicable): Still looking for a link...
ODbL Compliance verified: yes
OSM Data Files
Here are the OSM Data Files that have been modified in AmigoCloud as well as run through OGR2OSM to create an osm file that we can manually import into OpenStreetMap
Import Type
This will be a onetime import for most datasets and recurring for the bus stops. It will be imported using JOSM
Data Preparation
Data Reduction & Simplification
---
Tagging Plans
All custom tags will be prefixed with vta: , others that come up may be proposed to be added to the standard. Such as "disabled van spots" which are a subset of disabled spots
Changeset Tags
Describe how you'll use changeset tags in the import.
Park and Ride Tags
| Key | Default | Value | Comment |
|---|---|---|---|
| amenity | N/A | parking | Tag either an area or a central node, but not both. |
| park_ride | None | yes | |
| name | None | string | The name of the car park |
| capacity | None | number | The amount of available parking spaces, including all special parking spaces (e.g., disabled). |
| capacity:disabled | None | yes; no; number | Defines whether or not dedicated disabled parking spaces are available, usually reserved only for holders of a disabled parking permit |
| capacity:disabled_v | None | number | Number of handicapped van spots |
| vta:id | None | GUID, string | VTA specific record identifier |
| vta:owner | None | string | Owner of the data |
| capacity:bike_locke | None | number | Number of bike lockers |
| capacity:bike_rack | None | number | Number of bike racks |
| capacity:compact | None | number | Number of compact car spots |
| capacity:standard | None | number | Number of standard parking spots |
| capacity:kiss_n_ride | None | number | Number of passenger drop-off and pick-up parking spots |
| capacity:loading_zn | None | yes; no; number | Loading zone count |
| capacity:motorcycle | None | number | Number of motorcycle spots |
More info on Tag:amenity=parking
Screenshot of JOSM with VTA tags:
Light Rail Tags
There are no light rail tags that are being propogated from VTA to OSM. There is an inclusion of a vta_id tag that will help match these in the future.
Bus Stop Tags
| OSM Tag Name | VTA Field Name (Or hard coded string) | Comment |
|---|---|---|
| name | stpnam | Bus Stop Name |
| bench | vta_bench | Whether or not there is a bench present. if == 0 then No, otherwise then Yes |
| shelter | shelter_pa | Whether or not there is a shelter present. |
| ref | rti_stop | stop reference number |
| highway | "bus_stop" | What the highway type is. Will be set to bus_stop |
| operator | "VTA" | Who operates the bus stop |
| information | tactile_si | when not None, will be set to "tactile_letters" indicating there is braille on the sign. |
| wheelchair | ada_access (No if Null or No, otherwise what is in the field) | indicates if it is wheelchair accessable |
| vta:stop | stop | The vta stop number |
| vta:direction | st_dir | the direction of the stop |
| vta:trash | vta_trash | indicates the presence of a trash can |
| vta:private_bench | private_be | indicates if there are private benches |
| vta:id | vta_id | a unique identifier used to identify this stop from all others. |
| vta:bench_pad | vta_bench_pad | indicates the presence of a bench pad |
| vta:sidewalk_width | sidewalk_w | the length along the sidewalk the bus stop takes |
| vta:jurisdiction | jurisd | the juristdiction of the bus stop |
| vta:route_list | route_lis | the list of all numeric routes that service this stop. |
Transform the data for bus stops
| Step | Description | Resulting table name.
Save a Permanent Dataset with this name |
Info |
|---|---|---|---|
| 1 | Upload latest VTA bus stop dataset | VTA Original | |
| 2 | Add GUID field vta_id to VTA Original dataset if it's not already there. | VTA Original | Use Schema Editor. Later the fields that start with "vta_" will to be renamed to "vta:" using Advanced Queries |
| 3 | Download OSM bus stops to AmigoCloud dataset. This script will download all the
existing bus stops from OSM server for the Bay Area and create a dataset “OSM_Data” in the AmigoCloud project <PROJECT ID> |
OSM_Data | github / amigocloud / amigocloud_samples / python / query_osm.py To run this script: $python query_osm.py <PROJECT ID> <AMIGOCLOUD TOKEN> |
| 4 | Generate geometry field for OSM data.The OSM_Data will not have a geometry field, but will have lat/lon text fields.
We need to convert it into geometry using following SQL Query. |
OSM_Data_Geom | SELECT *, ST_SetSRID( ST_MakePoint( CAST(lon AS FLOAT), CAST(lat AS FLOAT) ), 4326) AS geom
FROM <OSM Data>
|
| 5 | Preliminary adjust VTA dataset schema to match OSM schema requirements.
Later there will be another step to create field names like "vta:xxx" |
VTA Converted | SELECT
stpnam AS name,
(case when bench_pad = '0' then 'No' else 'Yes' end) AS bench,
shelter_pa AS shelter,
'bus_stop' AS highway,
rti_stop AS ref,
'VTA' AS operator,
stop AS ""vta_stop"",
st_dir AS ""vta_direction"",
vta_trash,
private_be AS vta_private_bench,
bench_pad AS ""vta_bench_pad"",
sidewalk_w AS ""vta_sidewalk_width"",
(CASE WHEN tactile_si = 'None' THEN '' ELSE 'tactile_letters' END) AS information,
(CASE WHEN ada_access is NULL THEN 'No' ELSE ada_access END) AS ""wheelchair"",
jurisd AS ""vta_jurisdiction"",
routes_lis AS vta_route_list,
vta_id,
wkb_geometry
FROM <VTA Original>
|
| 6 | Buffer the VTA Dataset. We are going to need it to snap existing
OSM bus stops to VTA bus stops. |
VTA Buffered | SELECT st_buffer(wkb_geometry, 0.0002)
FROM <VTA Converted>
|
| 7 | Snap OSM points to VTA using buffer centroids. This step will identify
the existing OSM bus stops that are within close range of VTA bus stops and snap OSM's points to VTA's. |
OSM Snapped | SELECT ST_Centroid(buffer.st_buffer), osm.* FROM <VTA Buffered> as buffer, <OSM_Data_Geom> as osm
WHERE ST_INTERSECTS( osm.geom, buffer.st_buffer)
|
| 8 | Link OSM records to VTA records. Add osm_amigo_id field to VTA
table to reference the corresponding OSM's record |
VTA Linked | SELECT osm.amigo_id as osm_amigo_id, vta.* FROM <VTA Converted> as vta, <OSM Snapped> as osm
WHERE ST_INTERSECTS( osm.geom, st_buffer(vta.wkb_geometry, 0.0002))
|
| 9 | Remove duplicates from linked table. Previous steps often generate
duplicate records. This step will clean those. |
DELETE FROM <VTA Linked>
WHERE amigo_id IN (SELECT amigo_id
FROM (SELECT amigo_id, ROW_NUMBER() OVER (partition BY vta_id ORDER BY vta_id) AS rnum
FROM <VTA Linked> ) t
WHERE t.rnum > 1);
| |
| 10 | Generate OSM Update table. This dataset has records that already exist
in OSM, but filled with all the metadata available from VTA |
OSM Update | SELECT osm.id, vta.* FROM <VTA Linked> AS vta, <OSM Snapped> AS osm
WHERE vta.osm_amigo_id LIKE osm.amigo_id
|
| 11 | Generate OSM Insert table. These records don't exist in OSM. | OSM Insert | SELECT * FROM <VTA Converted>
WHERE vta_id NOT IN (SELECT vta_id FROM <OSM Update>)
|
| 12 | Rename OSM Update table fields to match OSM schema requirements | Do not save as
Permanent Dataset. Instead just export the result as CSV. |
SELECT
id,
name,
bench,
shelter,
highway,
ref,
operator,
vta_stop AS "vta:stop",
vta_direction AS "vta:direction",
vta_trash AS "vta:trash",
vta_private_bench AS "vta:private_bench",
vta_bench_pad AS "vta:bench_pad",
vta_sidewalk_width AS "vta:sidewalk_width",
information,
wheelchair,
vta_jurisdiction AS "vta:jurisdiction",
vta_route_list AS "vta:route_list",
vta_id AS "vta:id",
wkb_geometry
FROM <OSM Update>
|
| 13 | Rename OSM Insert table fields to match OSM schema requirements | Do not save as
Permanent Dataset. Instead just export the result as CSV. |
SELECT
name,
bench,
shelter,
highway,
ref,
operator,
vta_stop AS "vta:stop",
vta_direction AS "vta:direction",
vta_trash AS "vta:trash",
vta_private_bench AS "vta:private_bench",
vta_bench_pad AS "vta:bench_pad",
vta_sidewalk_width AS "vta:sidewalk_width",
information,
wheelchair,
vta_jurisdiction AS "vta:jurisdiction",
vta_route_list AS "vta:route_list",
vta_id AS "vta:id",
wkb_geometry
FROM <OSM Insert>
|
| 14 | Download JOSM application | JOSM | |
| 15 | In JOSM application install "opendata" plugin for importing CSV files | JOSM/Plugins/OpenData | |
| 16 | Import CSV file generated from OSM Update table into JOSM.
Select all the points, delete property "amigo_id". Upload data to OSM. |
||
| 17 | Edit any data manually if necessary using JOSM. Upload data to OSM. | ||
| 18 | Repeat step 16 and 17 using CVS file generated from OSM Insert table |
Transform the data for Bike Lanes
SELECT
(CASE when class = 'I' or class = '0' then 'cycleway' else '' END) as highway,
(CASE when class = 'II' then 'lane'
when class = 'III' then 'shared_lane'
when class = 'IV' then 'track'
else ''
END ) as cycleway,
(CASE when surface = 'Paved' then 'paved'
when surface = 'Unpaved' then 'unpaved'
when surface = 'Gravel' then 'gravel'
when surface = 'On-Street' then 'paved'
when surface = 'Service Road' then ''
when surface = 'Shared Roadway' then 'paved'
END ) as surface,
class as vta_class,
class_type as vta_class_type,
trail_owne as vta_trail_owner,
trail_name as name,
vta_id,
wkb_geometry
FROM dataset_8930
| OSM Tag Name | VTA Field Name (Or hard coded string) | Comment |
|---|---|---|
| highway | "cycleway", "road", or "living_street" as derived from field class | Used to tag cycleways independent of roads or on a road. Class 0 (bike boulevards) are living_street, class 1 an independent bike path is a cycleway, and all others are roads. |
| cycleway | "lane", "shared_lane", or "track" as derived from field class | class II is a bike lane, class III is a shared lane, and class IV is a isolated track parallel to the road. |
| surface | surface | The kind of surface present. VTA data has "On-Street", "Service Road", and "Shared Roadway" which are in this field, but not actual surfaces. They will be replaced with "Paved", otherwise the field value is made lowercase to match osm. |
| vta:class | class | The numerical class as determined by the state's legal definition |
| vta:class_type | class_type | description of the class number |
| name | trail_name | name of the trail if present. |
| vta:trail_owner | trail_owner | the jurisdiction that owns the bike trail |
| vta:id | vta_id | a unique identifier used to identify this stop from all others. |
Data Transformation
All data is in WGS_1984
Data Transformation Results
Post a link to your OSM XML files.
Data Merge Workflow
Team Approach
This is a team effort. The team will contain the following individuals: [Users/Daniel_caldwell]
References
List all factors that will be evaluated in the import.
Workflow
Prepare the data for JOSM by following this procedure:
- Update the data on AmigoCloud to make any fields match. this can be done by adding needed fields (like VTA_ID) or using an advanced query to change the data's field names and values to correspond to the appropriate values for OSM.
- Export the data from AmigoCloud in GeoJSON format. This will maintain column names over the field limit of 10 characters.
- Use the open source tool ogr2osm to convert the data to an OSM format. Use the AmigoCloud translation. The source is below
- once the data is converted to OSM, load the data into JSOM
- after the data is loaded into JOSM, convert to GPX Layer (using GPX plugin)
- once converted to GPX, right click and choose "download along path" and download the OSM data locally.
- reload the OSM data that you converted previously
- select the data that you want to port over to OSM from the import layer and copy it.
- paste that data into the DataLayer downloaded from OSM.
- begin manually reviewing all the data that you need to merge in.
Source code for translation of AmigoCloud to OSM format using ogr2osm
'''
A translation function for data from AmigoCloud. Removes amigo_id, changes vta_ to vta:
'''
def filterTags(attrs):
if not attrs:
return
if 'amigo_id' in attrs:
del attrs['amigo_id']
if 'shape_leng' in attrs:
del attrs['shape_leng']
for current_attribute in list(attrs):
attr_name = current_attribute
attr_value = attrs[current_attribute]
# delete if there is no value to avoid empty tags in OSM
if len(attrs[attr_name]) == 0:
del attrs[attr_name]
continue
# fix vta prefixes from vta_ to vta:
elif 'vta_' in attr_name:
new_attr_name = attr_name.replace('vta_', 'vta:')
del attrs[attr_name]
attrs[new_attr_name] = attr_value
return attrs
Steps for updating the Light Rail Data
Updating the Light Rail Data will be done using JOSM.
The general workflow for updating data in JOSM is as follows:
- Select the two geometries
- Ensure the geometries match
- Ensure that any tagged points are shared between the two geometries
- Select “Replace Geometry"
- Review the tags and their values
- Approve and watch the geometry get replaced, ensuring that the replacement happened in the correct direction.
This seem simple enough but there are a few cases that need to be handled correctly to make this work.
Case 1: The geometries do not match
Example 1: There is new data in the data you pasted
Solution: This happens when your data is more up-to-date than the OSM Data, not really an issue so nothing to do here. Just leave the data as is and make sure it is tagged appropriately
Example 2: The data you imported does not contain the data in OSM
This happens where something is mistagged in the OSM data or something that has been deleted needs to be deleted.
Solution: Review the tags of existing data to see if something is mistagged or not downloaded. If your data is more up to date, remove the data from OSM.
Example 3: A way is longer in OSM than in the imported data
This happens when there are places where lines touch in the input data, but in the downloaded OSM data, the lines are connected into one way.
Solution: Connect your ways to match the OSM Data, if the tags that you are copying over match for the corresponding data in OSM. If not, you will have to split the osm way where your data is split in order to properly migrate the tags. The ways in OSM are most likely split due to differences in relations and/or tags so you cannot connect them as it will corrupt the data.
Example 4: A way is longer in the imported data than in the OSM Data
Solution: Connect your ways to match the OSM Data, if the tags that you are copying over match for the corresponding data in OSM. If not, you will have to split the osm way where your data is split in order to properly migrate the tags. The ways in OSM are most likely split due to differences in relations and/or tags so you cannot connect them as it will corrupt the data.
Case 2: There are tagged nodes that exist in OSM, but not in your input data
Example 1: Railroad crossing a street
Solution: Find out what street is crossing the railroad for the imported lines. Select that road, and the imported lines and run the tool to generate nodes where the lines intersect. After the nodes are generated, drag the existing railroad's nodes using the ctrl key to snap them to the new nodes. The nodes will now match so you can replace the geometry later on.
Example 2: A node used to identify a station
Solution: Create new nodes on the imported data's ways for the new station. After the nodes are generated, drag the existing railroad's nodes using the ctrl key to snap them to the new nodes. The nodes will now match so you can replace the geometry later on.
Steps for updating the Bus Stop Data
Detail the steps you'll take during the actual import.
For every bus stop, zoom to that bus stop. once there determine if an existing stop exists. If it does, select both stops and choose replace geometry. This will merge the tags and replace the geometry, maintaining any relations or existing tags. If no existing stop exists, then leave it inserted as is.
Steps for updating the Bike Lanes and Routes
Information to include:
- Step by step instructions
- Changeset size policy
- Revert plans
Conflation
Identify your approach to conflation here.
QA
Add your QA plan here.