In [3]:

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 [4]:

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

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

In [5]:

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

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

In [6]:

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

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

Out[6]:

['spatial_ref_sys', 'nyc_census_blocks_2000', 'nyc_subway_stations']

ST_Intersects¶

ST_Intersects(geometry A, geometry B)

Example 1: ST_Intersects¶

Find all streets within 50 meters of subway stations (using buffer).

ST_Intersects( geometry geomA , ST_Buffer(geometry geomB, distance))

ST_Buffer

SELECT street.name, subway.borough, street.geom

FROM ch05.streets AS street, ch05.subway AS subway

WHERE _________

In [ ]:

nei_intersect = text("""
""")

sub_neighborhoods = gpd.read_postgis(nei_intersect, con=engine)

all_sub = text(""" SELECT * FROM ch05.subway""")
all_subway = gpd.read_postgis(all_sub, con = engine)

fig, ax = plt.subplots(figsize=(12, 10))

sub_neighborhoods.plot(ax=ax)
all_subway.plot(ax=ax, facecolor = 'none',edgecolor='black')


plt.title('NYC Neighborhoods with Subway Stations in Manhattan', fontsize=15)
plt.legend()
plt.grid(True)

nei_intersect = text(""" """) sub_neighborhoods = gpd.read_postgis(nei_intersect, con=engine) all_sub = text(""" SELECT * FROM ch05.subway""") all_subway = gpd.read_postgis(all_sub, con = engine) fig, ax = plt.subplots(figsize=(12, 10)) sub_neighborhoods.plot(ax=ax) all_subway.plot(ax=ax, facecolor = 'none',edgecolor='black') plt.title('NYC Neighborhoods with Subway Stations in Manhattan', fontsize=15) plt.legend() plt.grid(True)

C:\Users\Administrator\AppData\Local\Temp\ipykernel_6904\2354028167.py:18: UserWarning: No artists with labels found to put in legend.  Note that artists whose label start with an underscore are ignored when legend() is called with no argument.
  plt.legend()

Exercise 01 ST_Intersects¶

Find all one-way streets within 50 meters of subway stations.

column [oneway] in street = 'yes'

SELECT street.name, street.geom

FROM ch05.streets AS street, ch05.subway AS subway

WHERE _____________________________

In [ ]:

nei_intersect = text("""
""")

sub_neighborhoods = gpd.read_postgis(nei_intersect, con=engine)

all_sub = text(""" SELECT * FROM ch05.subway""")
all_subway = gpd.read_postgis(all_sub, con = engine)

fig, ax = plt.subplots(figsize=(12, 10))

sub_neighborhoods.plot(ax=ax)
all_subway.plot(ax=ax, facecolor = 'none',edgecolor='black')


plt.title('NYC Neighborhoods with Subway Stations in Manhattan', fontsize=15)
plt.legend()
plt.grid(True)

nei_intersect = text(""" """) sub_neighborhoods = gpd.read_postgis(nei_intersect, con=engine) all_sub = text(""" SELECT * FROM ch05.subway""") all_subway = gpd.read_postgis(all_sub, con = engine) fig, ax = plt.subplots(figsize=(12, 10)) sub_neighborhoods.plot(ax=ax) all_subway.plot(ax=ax, facecolor = 'none',edgecolor='black') plt.title('NYC Neighborhoods with Subway Stations in Manhattan', fontsize=15) plt.legend() plt.grid(True)

C:\Users\Administrator\AppData\Local\Temp\ipykernel_6904\1355374284.py:19: UserWarning: No artists with labels found to put in legend.  Note that artists whose label start with an underscore are ignored when legend() is called with no argument.
  plt.legend()

Example 02: ST_Intersects¶

Identify neighborhoods where there are subway stations, count the total count of subway in each neighborhoods

• Combination between WHERE AND GROUP BY

SELECT nbh.boroname, COUNT(*) AS station_count, nbh.geom

FROM ch05.neighborhoods AS nbh, ch05.subway as subway

WHERE _______________________________

GROUP BY ______________________

In [ ]:

nei_intersect = text("""
""")

sub_neighborhoods = gpd.read_postgis(nei_intersect, con=engine)

sub_neighborhoods.plot()

sub_neighborhoods.head()

nei_intersect = text(""" """) sub_neighborhoods = gpd.read_postgis(nei_intersect, con=engine) sub_neighborhoods.plot() sub_neighborhoods.head()

Out[ ]:

	boroname	station_count	geom
0	Brooklyn	5	MULTIPOLYGON (((581448.518 4499392.214, 583061...
1	Brooklyn	4	MULTIPOLYGON (((581037.025 4497503.442, 581046...
2	Brooklyn	3	MULTIPOLYGON (((585179.531 4504772.245, 585620...
3	Brooklyn	9	MULTIPOLYGON (((585179.531 4504772.245, 585005...
4	Brooklyn	10	MULTIPOLYGON (((583702.47 4498276.23, 584297.4...

Exercise 02: ST_Intersects¶

1. Identify neighborhoods with more than 10 subway stations using HAVING COUNT(*)

SELECT nbh.boroname, COUNT(*) AS station_count, nbh.geom

FROM ch05.neighborhoods AS nbh, ch05.subway as subway

WHERE ——————————————————————

GROUP BY ——————————————————————————

HAVING ——————————————————————————;

In [ ]:

nei_intersect = text("""
""")

sub_neighborhoods = gpd.read_postgis(nei_intersect, con=engine)

sub_neighborhoods.plot()

nei_intersect = text(""" """) sub_neighborhoods = gpd.read_postgis(nei_intersect, con=engine) sub_neighborhoods.plot()

Out[ ]:

<Axes: >

Example 03: ST_Intersects¶

List all subway stations where express trains stop that are within 50 meters of residential roads.

column[express] in subway = 'express' column[type] in sbuway = 'residential'

SELECT subway.name, street.name, subway.geom

FROM ch05.subway AS subway, ch05.streets AS street

WHERE ———————————————————— AND —————————————————— AND ——————————————————————————;

In [ ]:

nei_intersect = text("""""")

sub_neighborhoods = gpd.read_postgis(nei_intersect, con=engine)

sub_neighborhoods.plot()

nei_intersect = text("""""") sub_neighborhoods = gpd.read_postgis(nei_intersect, con=engine) sub_neighborhoods.plot()

Out[ ]:

<Axes: >

ST_Disjoint¶

The opposite of ST_Intersects is ST_Disjoint(geometry A , geometry B).

Example 01: ST_Disjoint¶

List neighborhoods Disjoint with 500 meters buffer of subway stations.

Directly using ST_Disjoint(), do you find any problem with the results?

SELECT nbh.boroname, nbh.geom

FROM ch05.neighborhoods AS nbh, ch05.subway

WHERE ——————————;

In [ ]:

streets = text("""
""")

sub_streets = gpd.read_postgis(streets, con=engine)

all_sub = text("""SELECT * FROM ch05.subway""")

fig, ax = plt.subplots(figsize=(12, 10))

sub_streets.plot(ax=ax)
all_subway.plot(ax=ax, facecolor = 'none',edgecolor='black')


plt.title('NYC Neighborhoods Disjoint with 500m buffer of subway stations', fontsize=15)
plt.legend()
plt.grid(True)

streets = text(""" """) sub_streets = gpd.read_postgis(streets, con=engine) all_sub = text("""SELECT * FROM ch05.subway""") fig, ax = plt.subplots(figsize=(12, 10)) sub_streets.plot(ax=ax) all_subway.plot(ax=ax, facecolor = 'none',edgecolor='black') plt.title('NYC Neighborhoods Disjoint with 500m buffer of subway stations', fontsize=15) plt.legend() plt.grid(True)

C:\Users\Administrator\AppData\Local\Temp\ipykernel_6904\3173271475.py:17: UserWarning: No artists with labels found to put in legend.  Note that artists whose label start with an underscore are ignored when legend() is called with no argument.
  plt.legend()

Exercise 01: ST_Disjoint - Subquery with NOT EXISTS¶

List neighborhoods Disjoint with 500 meters buffer of subway stations.

• Remove neighborhoods that intersect (Not Disjoint) with subway

SELECT nbh.boroname, nbh.geom

FROM ch05.neighborhoods AS nbh

WHERE NOT EXISTS (————————————);

In [ ]:

streets = text("""""")

sub_streets = gpd.read_postgis(streets, con=engine)

all_sub = text("""SELECT * FROM ch05.subway""")

fig, ax = plt.subplots(figsize=(12, 10))

all_subway.plot(ax=ax, facecolor = 'none',edgecolor='black')
sub_streets.plot(ax=ax)

plt.title('NYC Neighborhoods Disjoint with 500m buffer of subway stations', fontsize=15)
plt.legend()
plt.grid(True)

streets = text("""""") sub_streets = gpd.read_postgis(streets, con=engine) all_sub = text("""SELECT * FROM ch05.subway""") fig, ax = plt.subplots(figsize=(12, 10)) all_subway.plot(ax=ax, facecolor = 'none',edgecolor='black') sub_streets.plot(ax=ax) plt.title('NYC Neighborhoods Disjoint with 500m buffer of subway stations', fontsize=15) plt.legend() plt.grid(True)

C:\Users\Administrator\AppData\Local\Temp\ipykernel_6904\3639610047.py:18: UserWarning: No artists with labels found to put in legend.  Note that artists whose label start with an underscore are ignored when legend() is called with no argument.
  plt.legend()

Exercise 02: ST_Intersect and ST_Buffer¶

List street segments that outside of 500m buffer of subway station

SELECT street.name, street.geom

FROM ch05.streets AS street

WHERE NOT EXISTS (——————————————————)

In [ ]:

streets = text("""""")

sub_streets = gpd.read_postgis(streets, con=engine)

all_sub = text("""SELECT * FROM ch05.subway""")

fig, ax = plt.subplots(figsize=(12, 10))

all_subway.plot(ax=ax, facecolor = 'none',edgecolor='black')
sub_streets.plot(ax=ax)

plt.title('NYC Neighborhoods Disjoint with 500m buffer of subway stations', fontsize=15)
plt.legend()
plt.grid(True)

streets = text("""""") sub_streets = gpd.read_postgis(streets, con=engine) all_sub = text("""SELECT * FROM ch05.subway""") fig, ax = plt.subplots(figsize=(12, 10)) all_subway.plot(ax=ax, facecolor = 'none',edgecolor='black') sub_streets.plot(ax=ax) plt.title('NYC Neighborhoods Disjoint with 500m buffer of subway stations', fontsize=15) plt.legend() plt.grid(True)

C:\Users\Administrator\AppData\Local\Temp\ipykernel_44280\2351892699.py:22: UserWarning: No artists with labels found to put in legend.  Note that artists whose label start with an underscore are ignored when legend() is called with no argument.
  plt.legend()

ST_DWithin¶

ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid)

Example 1: ST_DWithin¶

List street segments that have distance over than 500 meters to subway staiton

SELECT street.name, street.geom

FROM ch05.streets AS street

WHERE NOT EXISTS (————————————————————);

In [ ]:

streets = text("""
    """)

sub_streets = gpd.read_postgis(streets, con=engine)

all_sub = text("""SELECT * FROM ch05.subway""")

fig, ax = plt.subplots(figsize=(12, 10))

all_subway.plot(ax=ax, facecolor = 'none',edgecolor='black')
sub_streets.plot(ax=ax)

plt.title('NYC Neighborhoods not within 500m buffer of subway stations', fontsize=15)
plt.legend()
plt.grid(True)

streets = text(""" """) sub_streets = gpd.read_postgis(streets, con=engine) all_sub = text("""SELECT * FROM ch05.subway""") fig, ax = plt.subplots(figsize=(12, 10)) all_subway.plot(ax=ax, facecolor = 'none',edgecolor='black') sub_streets.plot(ax=ax) plt.title('NYC Neighborhoods not within 500m buffer of subway stations', fontsize=15) plt.legend() plt.grid(True)

C:\Users\Administrator\AppData\Local\Temp\ipykernel_6904\512485809.py:19: UserWarning: No artists with labels found to put in legend.  Note that artists whose label start with an underscore are ignored when legend() is called with no argument.
  plt.legend()