Database Reference
In-Depth Information
How to do it...
Thefollowingstepswillguideyouthroughtheiterativeprocessrequiredtoimprove
query performance:
1. To find a school's nearest police station and the distance between each
schoolinSanFranciscoanditsneareststation,wewillstartbyexecutingthe
following query:
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 ( --
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 distance
FROM ( -- get schools in San Francisco
SELECT
ca.gid,
ca.name,
ST_Transform(ca.geom, 3310) AS
geom_3310
FROM sfpoly sf
JOIN caschools ca
ON ST_Intersects(sf.geom,
ST_Transform(ca.geom, 3310))
) sc
