Get TFL Tube data with Powershell

The London Datastore has loads of datasets available that we can use for free. One of the datasets available is a list of TFL Station Locations. The station location feed is a geo-coded KML feed of most of London Underground, DLR and London Overground stations. Here’s Powershell script that will extract this data from a url and write it to a pipe-delimited file ready for import into the database of your choice.

$xml = New-Object XML
$url = "http://www.tfl.gov.uk/tfl/syndication/feeds/stations.kml"
$csvFile = "$env:UserProfile\Desktop\tfl_tubes.csv";

# Empty file if it already exists
Set-Content -Path $csvFile $null;
# Add headers to file
Add-Content -Path $csvFile "Station|Address|Coordinates";

# Load the xml
$xml.Load($url);
$stations = $xml.kml.Document.Placemark;

foreach($station in $stations)
{
	$name = $station.name;
	$description = $station.description;
	$coordinates = $station.Point.coordinates
	# This data needs cleaning a bit
	$name = $name.Trim();
	$description = $description.Trim();
	$coordinates = $coordinates.Trim();
	# Ad line to the csv file
	Add-Content -Path $csvFile "$name|$description|$coordinates";
}

After running the script check your desktop for a file called tfl_tubes.csv which should look something like below.