Database Reference
In-Depth Information
FROM scpo
GROUP BY 1, 2
ORDER BY 2
) po
ON di.gid = po.gid
ORDER BY 1;
6. Notonlyisthequerysyntacticallycleaner,theperformanceimprovedasfol-
lows:
Time: 4192.614 ms
Time: 4651.967 ms
Time: 4329.707 ms
Theexecutiontimeswentfrommorethan10secondstolessthan5seconds.
7. Thoughsomemaystopoptimizingthisqueryatthispoint,wewillcontinueto
improvethequeryperformance.Wecanusethewindowfunctions,whichare
another PostgreSQL capability introduced in v8.4. Using the window func-
tions as follows, we can get rid of the
JOIN
expression:
WITH scpo AS ( -- get distance between
every school and everypolice station in
San FranciscoSELECT
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.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
CROSS JOIN ( -- get police stations in
