Database Reference
In-Depth Information
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, 3, 2
)
SELECT
DISTINCT school,
first_value(police_address) OVER
(PARTITION BY school ORDERBY distance),
first_value(distance) OVER (PARTITION
BY school ORDER BYdistance)
FROM scpo
ORDER BY 1;
8. We use the
first_value()
window function to extract the first
po-
lice_address
and
distance
values for each school sorted by the dis-
tancebetweentheschoolandapolicestation.Thoughthereadabilityofthe
SQL improves even more, it does not look like the query improves with the
use of the window functions, as seen in the following elapsed times:
Time: 4268.268 ms
Time: 4493.860 ms
Time: 4490.656 ms
9. Weshouldinspecttheexecutionplanwith
EXPLAIN ANALYZE VERBOSE
to
see what is decreasing the query performance. Due to the verbosity of the
output, we've trimmed it to just the following lines of interest:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=19.43..19.45 rows=1
