Database Reference
In-Depth Information
ST_Transform(ca.geom, 3310))
) sc
CROSS JOIN ( -- get police stations
in San Francisco
SELECT
ca.address,
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
) scpo
GROUP BY 1, 2
ORDER BY 2
) po
ON di.gid = po.gid
ORDER BY di.school;
2. Generallyspeaking,thisisacrudeandsimplisticquery.The
scpo
subquery
occurstwiceinthequerybecauseitneedstocomputetheshortestdistance
fromaschooltoitsnearestpolicestationandthenameofthepolicestation
closest to each school. If each instance of
scpo
took 10 seconds to com-
pute,twoinstancesof
scpo
wouldtake20seconds.Thisisverydetrimental
to performance.
3. The query output looks as follows:
school
| police_address |
distance
------------------------------------------+-----------------------------+------------------
ABRAHAM LINCOLN HIGH
| 2345 24th Ave |
348.311916238521
ADDA CLEVENGER JUNIOR PREPARAT
| 2345 24th Ave |
