{
"cells": [
{
"cell_type": "markdown",
"metadata": {
"id": "Nenn5vEr1_NJ"
},
"source": [
"# Performing Spatial Queries"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "wIFWAO9r1_NM"
},
"source": [
"## Introduction\n",
"\n",
"Spatial queries allows you to select features in a layer by their spatial relationships with features from another layer. GeoPandas provides `sjoin()` and `sjoin_nearest()` functions that can be used to perform a spatial query.\n",
"\n",
"## Overview of the Task\n",
"\n",
"We will be working with 2 data layers for the city of Melbourne, Australia. Given the data layers for the pubs and bars in the city and the locations of all metro stations, we want to find out which bars and pubs are within 500 meters of a metro station.\n",
"\n",
"**Input Layers**:\n",
"* `metro_stations_accessbility.zip`: A shapefile of metro stations in Melbourne\n",
"* `Bars_and_pubs__with_patron_capacity.csv`: CSV file with location of bars and pubs in Melbournse\n",
"\n",
"**Output**:\n",
"* `spatial_query.gpkg`: A GeoPackage containing all bars and pubs within 500 meters of a metro station\n",
"\n",
"**Data Credit**\n",
"\n",
"* 2019 The City of Melbourne Open Data Portal. Data provided by Metro Trains Melbourne and Census of Land Use and Employment (CLUE)\n",
"\n",
"\n",
"**Watch Video Walkthrough** \n"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "5F4UU8ky1_NM"
},
"source": [
"## Setup and Data Download\n",
"\n",
"The following blocks of code will install the required packages and download the datasets to your Colab environment."
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"id": "LZY3cd9K1_NO"
},
"outputs": [],
"source": [
"import os\n",
"import pandas as pd\n",
"import geopandas as gpd\n",
"import matplotlib.pyplot as plt"
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"id": "4SqUL0h81_NO"
},
"outputs": [],
"source": [
"data_folder = 'data'\n",
"output_folder = 'output'\n",
"\n",
"if not os.path.exists(data_folder):\n",
" os.mkdir(data_folder)\n",
"if not os.path.exists(output_folder):\n",
" os.mkdir(output_folder)"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {
"id": "BilOp9-B1_NO"
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Downloaded data/Bars_and_pubs__with_patron_capacity.csv\n",
"Downloaded data/metro_stations_accessbility.zip\n"
]
}
],
"source": [
"def download(url):\n",
" filename = os.path.join(data_folder, os.path.basename(url))\n",
" if not os.path.exists(filename):\n",
" from urllib.request import urlretrieve\n",
" local, _ = urlretrieve(url, filename)\n",
" print('Downloaded ' + local)\n",
"\n",
"data_url = 'https://github.com/spatialthoughts/geopython-tutorials/releases/download/data/'\n",
"\n",
"download(data_url + 'Bars_and_pubs__with_patron_capacity.csv')\n",
"download(data_url + 'metro_stations_accessbility.zip')"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "GNO6nOET1_NO"
},
"source": [
"## Procedure"
]
},
{
"cell_type": "markdown",
"metadata": {
"id": "GCC7mjMJ1_NP"
},
"source": [
"Read the `Bars_and_pubs__with_patron_capacity.csv` file and convert it to a GeoDataFrame."
]
},
{
"cell_type": "code",
"execution_count": 43,
"metadata": {
"id": "mjhj_ZJO1_NP",
"outputId": "25f2f804-63c8-4ea2-a91a-5869d777955b"
},
"outputs": [
{
"data": {
"text/html": [
"
\n", " | Census year | \n", "Block ID | \n", "Property ID | \n", "Base property ID | \n", "Street address | \n", "
---|---|---|---|---|---|
0 | \n", "2002 | \n", "247 | \n", "106238 | \n", "106238 | \n", "192-202 Lygon Street | \n", "
1 | \n", "2002 | \n", "252 | \n", "106244 | \n", "106244 | \n", "160-162 Lygon Street | \n", "
2 | \n", "2006 | \n", "16 | \n", "104018 | \n", "104018 | \n", "172-192 Flinders Street | \n", "
3 | \n", "2002 | \n", "214 | \n", "106186 | \n", "106186 | \n", "414-422 Lygon Street | \n", "
4 | \n", "2008 | \n", "76 | \n", "589841 | \n", "105749 | \n", "221 Little Lonsdale Street | \n", "
... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "
3333 | \n", "2017 | \n", "85 | \n", "105746 | \n", "105746 | \n", "183-265 La Trobe Street | \n", "
3334 | \n", "2017 | \n", "74 | \n", "104662 | \n", "104662 | \n", "106-112 Hardware Street | \n", "
3335 | \n", "2017 | \n", "268 | \n", "108154 | \n", "108153 | \n", "49 Rathdowne Street | \n", "
3336 | \n", "2017 | \n", "104 | \n", "104085 | \n", "104085 | \n", "167-175 Franklin Street | \n", "
3337 | \n", "2017 | \n", "33 | \n", "100730 | \n", "100730 | \n", "12-16 Bank Place | \n", "
3338 rows × 5 columns
\n", "\n", " | Census year | \n", "Block ID | \n", "Property ID | \n", "Base property ID | \n", "Street address | \n", "
---|---|---|---|---|---|
3129 | \n", "2017 | \n", "103 | \n", "100160 | \n", "100160 | \n", "196-200 A'Beckett Street | \n", "
709 | \n", "2011 | \n", "103 | \n", "100160 | \n", "100160 | \n", "196-200 A'Beckett Street | \n", "
2553 | \n", "2015 | \n", "103 | \n", "100160 | \n", "100160 | \n", "196-200 A'Beckett Street | \n", "
247 | \n", "2008 | \n", "103 | \n", "100160 | \n", "100160 | \n", "196-200 A'Beckett Street | \n", "
2201 | \n", "2010 | \n", "103 | \n", "100160 | \n", "100160 | \n", "196-200 A'Beckett Street | \n", "
... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "
2579 | \n", "2015 | \n", "1105 | \n", "628712 | \n", "628712 | \n", "717-731 Collins Street | \n", "
2832 | \n", "2016 | \n", "66 | \n", "635138 | \n", "635138 | \n", "13 Heffernan Lane | \n", "
3069 | \n", "2017 | \n", "66 | \n", "635138 | \n", "635138 | \n", "13 Heffernan Lane | \n", "
3058 | \n", "2016 | \n", "270 | \n", "664626 | \n", "104468 | \n", "230 Grattan Street | \n", "
3228 | \n", "2017 | \n", "270 | \n", "664626 | \n", "104468 | \n", "230 Grattan Street | \n", "
3338 rows × 5 columns
\n", "\n", " | Census year | \n", "Block ID | \n", "Property ID | \n", "Base property ID | \n", "Street address | \n", "
---|---|---|---|---|---|
3129 | \n", "2017 | \n", "103 | \n", "100160 | \n", "100160 | \n", "196-200 A'Beckett Street | \n", "
1087 | \n", "2011 | \n", "409 | \n", "100441 | \n", "100441 | \n", "118-126 Ireland Street | \n", "
410 | \n", "2004 | \n", "315 | \n", "100514 | \n", "100514 | \n", "204-206 Arden Street | \n", "
1501 | \n", "2009 | \n", "33 | \n", "100727 | \n", "100727 | \n", "5-9 Bank Place | \n", "
1159 | \n", "2013 | \n", "33 | \n", "100730 | \n", "100730 | \n", "12-16 Bank Place | \n", "
... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "
1601 | \n", "2009 | \n", "2391 | \n", "616966 | \n", "616966 | \n", "2 Boundary Road | \n", "
1182 | \n", "2013 | \n", "1110 | \n", "620312 | \n", "593737 | \n", "23-37 Star Crescent | \n", "
3028 | \n", "2016 | \n", "1105 | \n", "628712 | \n", "628712 | \n", "717-731 Collins Street | \n", "
2832 | \n", "2016 | \n", "66 | \n", "635138 | \n", "635138 | \n", "13 Heffernan Lane | \n", "
3058 | \n", "2016 | \n", "270 | \n", "664626 | \n", "104468 | \n", "230 Grattan Street | \n", "
308 rows × 5 columns
\n", "\n", " | Trading name | \n", "Number of patrons | \n", "x coordinate | \n", "y coordinate | \n", "Location | \n", "
---|---|---|---|---|---|
3129 | \n", "Nomads Industry Backpackers | \n", "200 | \n", "144.957445 | \n", "-37.809985 | \n", "(-37.80998494, 144.9574447) | \n", "
1087 | \n", "Railway Hotel | \n", "241 | \n", "144.942118 | \n", "-37.806054 | \n", "(-37.80605366, 144.9421177) | \n", "
410 | \n", "North Melbourne Football Club Social Club | \n", "210 | \n", "144.941312 | \n", "-37.799055 | \n", "(-37.79905531, 144.9413118) | \n", "
1501 | \n", "Mitre Tavern | \n", "300 | \n", "144.960311 | \n", "-37.816805 | \n", "(-37.81680457, 144.9603112) | \n", "
1159 | \n", "Melbourne Savage Club | \n", "90 | \n", "144.960577 | \n", "-37.816545 | \n", "(-37.81654467, 144.9605765) | \n", "
... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "
1601 | \n", "Vodka Locka & Red Leaf Restaurant | \n", "40 | \n", "144.939233 | \n", "-37.795318 | \n", "(-37.79531826, 144.9392333) | \n", "
1182 | \n", "Harbour Town Hotel | \n", "498 | \n", "144.937830 | \n", "-37.813150 | \n", "(-37.81315031, 144.9378299) | \n", "
3028 | \n", "Bar Nicional | \n", "75 | \n", "144.950093 | \n", "-37.820687 | \n", "(-37.82068699, 144.9500934) | \n", "
2832 | \n", "Union Electric | \n", "72 | \n", "144.966591 | \n", "-37.811802 | \n", "(-37.81180178, 144.9665913) | \n", "
3058 | \n", "HJC Bar | \n", "200 | \n", "144.961247 | \n", "-37.797398 | \n", "(-37.79739836, 144.9612468) | \n", "
308 rows × 5 columns
\n", "\n", " | station | \n", "pids | \n", "he_loop | \n", "lift | \n", "geometry | \n", "
---|---|---|---|---|---|
0 | \n", "Alamein | \n", "No | \n", "No | \n", "No | \n", "POINT (145.07956 -37.86884) | \n", "
1 | \n", "Albion | \n", "Dot Matrix | \n", "No | \n", "No | \n", "POINT (144.82471 -37.77766) | \n", "
2 | \n", "Alphington | \n", "Dot Matrix | \n", "No | \n", "No | \n", "POINT (145.03125 -37.7784) | \n", "
3 | \n", "Altona | \n", "LCD | \n", "No | \n", "No | \n", "POINT (144.8306 -37.86725) | \n", "
4 | \n", "Anstey | \n", "No | \n", "No | \n", "No | \n", "POINT (144.96056 -37.7619) | \n", "
... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "... | \n", "
214 | \n", "Williams Landing | \n", "Dot Matrix | \n", "Yes | \n", "Yes | \n", "POINT (144.74719 -37.8701) | \n", "
215 | \n", "Aircraft | \n", "No | \n", "No | \n", "No | \n", "POINT (144.76081 -37.8666) | \n", "
216 | \n", "Flemington Racecourse | \n", "No | \n", "No | \n", "No | \n", "POINT (144.9072 -37.78759) | \n", "
217 | \n", "Showgrounds | \n", "No | \n", "No | \n", "No | \n", "POINT (144.91498 -37.78355) | \n", "
218 | \n", "Southland | \n", "LCD | \n", "Yes | \n", "Yes | \n", "POINT (145.04867 -37.95818) | \n", "
219 rows × 5 columns
\n", "