In [1]:
# This notebook does some checks that ST_Distance is computing the distance correctly in meters, using checks from Google maps
# Some reference values: Distance from London WC2N 5AQ 51.5092488,-0.0978045 to York: 53.9598567,-1.0874146 YO32 4TW 280.42km
# Distance from Liverpool L2 5UZ 53.4061444,-2.9873456 to Norwich NR3 1ES 52.6261985,1.2968884 299.48km
In [2]:
from mylibrary.connections import cursor, conn, engine, Automapped_Base, session
In [42]:
# select * from
# (select * from a left join b on)
sql = """
select london, york, st_distance(london,york)/1000 as distance_km from
(select geom as london from tt.all_postcodes where postcode = 'WC2N5AQ') as a,
(select geom as york from tt.all_postcodes where postcode = 'YO324TW') as b
"""
import pandas as pd
pd.read_sql(sql, conn)
Out[42]:
In [74]:
sql = """
select norwich, liverpool, st_distance(norwich,liverpool)/1000 as distance_km from
(select geom as norwich from tt.all_postcodes where postcode = 'NR3 1ES') as a,
(select geom as liverpool from tt.all_postcodes where postcode = 'L2 5UZ') as b
"""
pd.read_sql(sql, conn)
Out[74]:
In [75]:
# Check distance is also working in the stations data According to Google maps this is 137.44km
sql = """
select farringdon, coventry, st_distance(farringdon,coventry)/1000 as distance_km from
(select geom as farringdon from tt.all_stations where station_name='Farringdon') as a,
(select geom as coventry from tt.all_stations where station_name = 'Coventry') as b
"""
pd.read_sql(sql, conn)
Out[75]:
In [80]:
# Finally check it's working for the staff locations data
# Distance should be 160ish
#
sql = """
select person1, person1pc, person2, person2pc, st_distance(person1,person2)/1000 as distance_km from
(select geom as person1, postcode as person1pc from tt.staff_locations order by random() limit 1) as a,
(select geom as person2, postcode as person2pc from tt.staff_locations order by random() limit 1) as b
"""
# pd.read_sql(sql, conn)
Out[80]:
In [86]:
# Finally, check units of meaurement of ST_Buffer
# Distance from Farringdon station to Elephant and Castle is 2.93 km
sql = """
select nlc, station_name, st_distance((select geom from tt.all_stations where station_name = 'Farringdon'), a.geom)/1000 as distance
from tt.all_stations as a
where
st_contains(
ST_Buffer((select geom from tt.all_stations where station_name = 'Farringdon'),5000),
a.geom)
"""
pd.read_sql(sql, conn)
Out[86]:
In [ ]: