Import/VTA Transportation

From OpenStreetMap Wiki
Jump to: navigation, search

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:

VTA Data from AmigoCloud

VTA Open Data Portal


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

https://drive.google.com/folderview?id=0B-UXCZ40o6OxfnBFcTBfWlR5VzdnT2FCelhjMXNsWEhKQlVnZTFwOGtnR1ZiWll6MWR2T0k&usp=sharing

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:

Screen Shot After adding VTA tags.png


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:

  1. 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.
  2. Export the data from AmigoCloud in GeoJSON format. This will maintain column names over the field limit of 10 characters.
  3. Use the open source tool ogr2osm to convert the data to an OSM format. Use the AmigoCloud translation. The source is below
  4. once the data is converted to OSM, load the data into JSOM
  5. after the data is loaded into JOSM, convert to GPX Layer (using GPX plugin)
  6. once converted to GPX, right click and choose "download along path" and download the OSM data locally.
  7. reload the OSM data that you converted previously
  8. select the data that you want to port over to OSM from the import layer and copy it.
  9. paste that data into the DataLayer downloaded from OSM.
  10. 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:

  1. Select the two geometries
  2. Ensure the geometries match
  3. Ensure that any tagged points are shared between the two geometries
  4. Select “Replace Geometry"
  5. Review the tags and their values
  6. 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.