Database Reference
In-Depth Information
FROM sfpoly sf
JOIN caschools ca
ON ST_Intersects(sf.geom,
ST_Transform(ca.geom, 3310))
) sc
CROSS JOIN ( -- get police stations in
San FranciscoSELECT
ca.*,
ST_Transform(ca.geom, 3310) AS
geom_3310
FROM sfpoly sf
JOIN capolice ca
ON ST_Intersects(sf.geom,
ST_Transform(ca.geom, 3310))
) po
ORDER BY 1, 2, 4
)
SELECT
di.school,
police_address,
distance
FROM ( -- for each school, get the
minimum distance to a police station
SELECT
gid,
school,
min(distance) AS distanceFROM scpo
GROUP BY 1, 2
ORDER BY 2
) di
JOIN ( -- for each school, collect the
police station addresses ordered by
distance
SELECT
gid,
school,
(array_agg(police_address))[1] AS
police_address
Search WWH ::




Custom Search