Database Reference
In-Depth Information
CROSS JOIN ( -- get police stations
in San FranciscoSELECT
ca.address,
ST_Transform(ca.geom, 3310) AS
geom_3310FROM sfpoly sf
JOIN capolice ca
ON ST_Intersects(sf.geom,
ST_Transform(ca.geom, 3310))
) po
ORDER BY 1, 2, 4
) scpo
GROUP BY 1, 2
ORDER BY 2
) diJOIN ( -- for each school, collect
the police station addresses ordered by
distance
SELECT
gid,
school,
(array_agg(police_address))[1] AS
police_address
FROM ( -- get distance between every
school and every police station in San
Francisco
SELECT
sc.gid,
sc.name AS school,
po.address AS police_address,
ST_Distance(po.geom_3310,
sc.geom_3310) AS distanceFROM ( -- get
schools in San Francisco
SELECT
ca.gid,
ca.name,
ST_Transform(ca.geom, 3310) AS
geom_3310FROM sfpoly sf
JOIN caschools ca
ON ST_Intersects(sf.geom,
