Lab 04: Spatial Relationship

Due date: Thursday, Feb 20 submitted as PDF or HTML to Canvas Lab04 link. This lab counts 9 % toward your total grade.

Objectives:

1. Understand Spatial Relationships.
2. Implement Subqueries.

For each task:

1. Provide the SQL Query
2. Plot the output.

In [ ]:

import matplotlib.pyplot as plt
from sqlalchemy import create_engine
from sqlalchemy import inspect
from sqlalchemy import text
import os
import geopandas as gpd

import matplotlib.pyplot as plt from sqlalchemy import create_engine from sqlalchemy import inspect from sqlalchemy import text import os import geopandas as gpd

In [ ]:

host = 'localhost'
database = 'Your database'
user = 'postgres'
port = '5432'
password = os.getenv('SQL_PASSWORD')

host = 'localhost' database = 'Your database' user = 'postgres' port = '5432' password = os.getenv('SQL_PASSWORD')

In [ ]:

connection_string = f"postgresql://{user}:{password}@{host}:{port}/{database}"

connection_string = f"postgresql://{user}:{password}@{host}:{port}/{database}"

In [ ]:

engine = create_engine(connection_string)
insp = inspect(engine)
insp.get_table_names()

engine = create_engine(connection_string) insp = inspect(engine) insp.get_table_names()

In-class Practice¶

1. ST_Equals¶

Task 1¶

Use ST_Equals() to find the name of the subway station where its geometry is 0101000020266900002A40A25A52DE2141EBE9FA836A365141

In [ ]:

query = """
SELECT __________
FROM _________
WHERE ________;
"""

subway_stations = gpd.read_postgis(query, engine, geom_col='geom')
print(subway_stations)

query02 = """
________________
"""

all_neighborhood = gpd.read_postgis(query02, engine, geom_col='geom')


# Plotting  all_neighborhood as the background, and pinpoint the station
fig, ax = plt.subplots(figsize=(10, 8))
all_neighborhood.plot(ax=ax, color = 'none')
subway_stations.plot(ax=ax, color='', label='')

plt.legend()
plt.title('')
plt.show()

query = """ SELECT __________ FROM _________ WHERE ________; """ subway_stations = gpd.read_postgis(query, engine, geom_col='geom') print(subway_stations) query02 = """ ________________ """ all_neighborhood = gpd.read_postgis(query02, engine, geom_col='geom') # Plotting all_neighborhood as the background, and pinpoint the station fig, ax = plt.subplots(figsize=(10, 8)) all_neighborhood.plot(ax=ax, color = 'none') subway_stations.plot(ax=ax, color='', label='') plt.legend() plt.title('') plt.show()

2. ST_Intersects / ST_Disjoint()¶

Task 2¶

Please find neighborhoods that intersect with 5th Ave by using ST_Intersects; highlight the street in red and plot the neighborhood

In [ ]:

query = """
SELECT __________
FROM ch05.streets AS street, ch05.neighborhoods AS nbh
WHERE ___________;
"""

query = """ SELECT __________ FROM ch05.streets AS street, ch05.neighborhoods AS nbh WHERE ___________; """

3. ST_Crosses()¶

Task 3¶

Please find streets that cross Howland Hook in Staten Island; highlight the streets in red, plot the neighborhoods, and grey out Howland Hook

In [ ]:

query = """
SELECT _________________
FROM _________________
WHERE _____________________________;
"""

query = """ SELECT _________________ FROM _________________ WHERE _____________________________; """

4. ST_Overlaps()¶

Task 4¶

Check if any overlapping streets exist

In [ ]:

query = """
SELECT _________
FROM _________
JOIN __________
ON _________
AND __________;
"""

query = """ SELECT _________ FROM _________ JOIN __________ ON _________ AND __________; """

5. ST_Touches()¶

6. ST_Within() and ST_contains()¶

Task 5¶

Please find out and plot the subway stations within Sunny Side, Queens

In [ ]:

query = """

"""

query = """ """

6. ST_Distance / ST_DWithin¶

Task 6¶

Find the street that are within 50m of the subway station its alt_name is Yankee Stadium, and calculate their distance

In [ ]:

query = """

"""

query = """ """

Task 1¶

Select subway stations with the colors RED and GREY, then plot them in two different colors on the map.

In [ ]:

query = """

"""

query = """ """

Task 2: ST_Crosses¶

Using ST_Crosses to identify the streets that cross with the 100 m buffer around each station.

In [ ]:

query = """

"""

query = """ """

Task 3: ST_Centroid¶

Calculate the centroid of each neighborhood polygon using ST_Centroid and visualize them on the map.

In [ ]:

query = """

"""

query = """ """

Task 4. Nearest subway station to each centroid¶

Use these centroids to find the subway station closest to each neighborhood center.

ST_Centroid

ST_Distance

MIN(ST_Distance()): The subquery is looking for the minimum distance between the neighborhood's centroid and all subway stations.

In [ ]:

query = """

"""

query = """ """