jimmyrocks
commented
3 years ago This is the code I used for the data transformation (source_id and source_addr become source:id and source:addr)
CREATE VIEW osm_na_rail AS
SELECT
CASE
WHEN "net" = 'X' THEN 'disused'
WHEN "net" = 'A' THEN 'abandoned'
WHEN "net" = 'R' THEN 'razed'
ELSE 'rail'
END AS railway,
"subdiv" AS name,
CASE
WHEN "net" = 'M' THEN 'main'
WHEN "net" = 'I' THEN 'industrial'
WHEN "net" = 'R' THEN 'razed'
WHEN "net" = 'T' AND "passngr" = 'T' THEN 'tourism'
ELSE null
END AS "usage",
CASE
WHEN "net" = 'S' THEN 'siding'
WHEN "net" = 'Y' THEN 'yard'
ELSE null
END AS "service",
CASE WHEN "tracks" > 1 THEN "tracks"
ELSE null END as "tracks",
fraarcid AS "source:id",
'https://geo.dot.gov/server/rest/services/NTAD/North_American_Rail_Lines/MapServer/0?' as "source:addr",
wkb_geometry AS "the_geom"
FROM na_rail;
The current vector FRA data (Current as of Feb 4, 2021) is cleaned up and ready to add to our server for editing.
It needs to be migrated to the APIDB for editing.
Data Source: https://geo.dot.gov/server/rest/services/NTAD/North_American_Rail_Lines/MapServer/0
Data transformation: The following tags will be used for all FRA data railway – usually “rail” (If the net field = X then disused, if the net field = A then abandoned, if the net field = R then razed) name – (name from the {SUBDIV} field) usage – (Use the NET field: M = main, I = industrial) ii. Buffers (I propose using these to make rectangular area tasks as mentioned in #2). This is to be added to Tasking Manager.