assignment02/query10.sql at main · Mayaano/assignment02 · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
with stops_with_geog as (
    select
        rs.stop_id,
        rs.stop_name,
        rs.stop_lon,
        rs.stop_lat,
        st_setsrid(
            st_makepoint(rs.stop_lon, rs.stop_lat), 4326
        )::geography as stops_geog
    from septa.rail_stops as rs
)

select
    stops.stop_name,
    stops.stop_lon,
    stops.stop_lat,
    stops.stop_id::integer as stop_id,
    round(st_distance(
        stops.stops_geog,
        parcels.geog
    )::numeric) || ' meters '
    || case
        when degrees(st_azimuth(
            stops.stops_geog::geometry,
            st_centroid(parcels.geog::geometry)
        )) between 337.5 and 360
        or degrees(st_azimuth(
            stops.stops_geog::geometry,
            st_centroid(parcels.geog::geometry)
        )) between 0 and 22.5 then 'N'
        when degrees(st_azimuth(
            stops.stops_geog::geometry,
            st_centroid(parcels.geog::geometry)
        )) between 22.5 and 67.5 then 'NE'
        when degrees(st_azimuth(
            stops.stops_geog::geometry,
            st_centroid(parcels.geog::geometry)
        )) between 67.5 and 112.5 then 'E'
        when degrees(st_azimuth(
            stops.stops_geog::geometry,
            st_centroid(parcels.geog::geometry)
        )) between 112.5 and 157.5 then 'SE'
        when degrees(st_azimuth(
            stops.stops_geog::geometry,
            st_centroid(parcels.geog::geometry)
        )) between 157.5 and 202.5 then 'S'
        when degrees(st_azimuth(
            stops.stops_geog::geometry,
            st_centroid(parcels.geog::geometry)
        )) between 202.5 and 247.5 then 'SW'
        when degrees(st_azimuth(
            stops.stops_geog::geometry,
            st_centroid(parcels.geog::geometry)
        )) between 247.5 and 292.5 then 'W'
        else 'NW'
    end
    || ' of ' || parcels.address as stop_desc
from stops_with_geog as stops
cross join
    lateral (
        select
            p.address,
            p.geog
        from phl.pwd_parcels as p
        where p.address is not null
        order by stops.stops_geog <-> p.geog
        limit 1
    ) as parcels