-
Notifications
You must be signed in to change notification settings - Fork 1
/
sql.js
109 lines (104 loc) · 3.36 KB
/
sql.js
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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
export default {
parcels: (buildingParcelOverlapSqFt) => {
const buildingParcelOverlapSqM = buildingParcelOverlapSqFt * 0.092903;
return `
create table parcels_analyzed as
with buildings_with_bsl as (
select bu.geom
from buildings bu
join bsls bs on st_intersects(bu.geom, bs.geom)
group by bu.geom
), parcels_with_building_with_bsl as (
select p.ogc_fid
from parcels p
join buildings_with_bsl bwb on
st_intersects(p.geom, bwb.geom) and
st_area(st_intersection(p.geom, bwb.geom)::geography) > ${buildingParcelOverlapSqM}
group by p.ogc_fid
), parcels_with_bsl as (
select p.ogc_fid
from parcels p
join bsls b on st_intersects(p.geom, b.geom)
group by p.ogc_fid
), parcels_with_building as (
select p.ogc_fid
from parcels p
join buildings b on
st_intersects(p.geom, b.geom) and
st_area(st_intersection(p.geom, b.geom)::geography) > ${buildingParcelOverlapSqM}
group by p.ogc_fid
)
select
p.*,
(pbu.ogc_fid is not null) as has_building,
(pbb.ogc_fid is not null or pbs.ogc_fid is not null) as has_bsl,
pbu.ogc_fid as pbu,
pbs.ogc_fid as pbs
from parcels p
left join parcels_with_building_with_bsl pbb on p.ogc_fid = pbb.ogc_fid
left join parcels_with_bsl pbs on p.ogc_fid = pbs.ogc_fid
left join parcels_with_building pbu on p.ogc_fid = pbu.ogc_fid
;
`;
},
bsls: (tailorAddressColumn) => {
let bslAddressColumn;
if (tailorAddressColumn === 'tailor_address_street') {
bslAddressColumn = 'address_primary_norm';
} else if (tailorAddressColumn === 'tailor_address_full') {
bslAddressColumn = 'address_full_norm';
} else {
throw new Error(
`Unrecognized address match column: ${tailorAddressColumn}`
);
}
return `
create table bsls_analyzed as
select
b.*,
(a.${tailorAddressColumn} is not null) as is_known_address,
st_length(st_makeline(a.geom, b.geom)::geography) * 3.28084 as distance_to_known_address
from bsls b
left join addresses a on a.${tailorAddressColumn} = b.${bslAddressColumn}
;
`;
},
// TODO lateral join and order by distance
addressMatchLines: (tailorAddressColumn) => {
let bslAddressColumn;
if (tailorAddressColumn === 'tailor_address_street') {
bslAddressColumn = 'address_primary_norm';
} else if (tailorAddressColumn === 'tailor_address_full') {
bslAddressColumn = 'address_full_norm';
} else {
throw new Error(
`Unrecognized address match column: ${tailorAddressColumn}`
);
}
return `
create table bsls_address_match_lines as
with l as (
select
b.location_id,
b.${bslAddressColumn},
closest_a.${tailorAddressColumn} as match_address,
st_makeline(closest_a.geom, b.geom) as geom
from bsls b,
lateral (
select
a.tailor_address_street,
a.geom
from addresses a
where a.tailor_address_street = b.address_primary
order by st_distance(a.geom, b.geom)
limit 1
) closest_a
)
select
*,
st_length(geom) * 3.28084 as distance_ft
from l
;
`;
},
};