Database Reference
In-Depth Information
1851.38147290568
AIM HIGH ACADEMY
| 3401 17th St |
976.082872160513
...
(234 rows)
4. ThequeryresultsdoprovidetheaddressesoftheschoolsinSanFrancisco,
theaddressesoftheclosestpolicestationtoeachofthoseschools,andthe
distance from each school to its closest police station. But, we are also in-
terested in getting the answer as fast as possible. With timing turned on in
psql
,wegetthefollowingperformancenumbersforthreerunsofthequery:
Time: 10873.610 ms
Time: 10560.931 ms
Time: 10754.971 ms
5. Justbylookingatthequeryinstep1,weseethatthereareredundantsub-
queries.Let'sgetridofthoseduplicatesusing
Common Table Expressions
(
CTEs
),introducedinPostgreSQL8.4.CTEsareusedtologicallyandsyn-
tacticallyseparateablockofSQLfromsubsequentpartsofthequery.Since
CTEsarelogicallyseparated,theyarerunatthestartofthequeryexecution
and their results are cached for subsequent use.
WITH scpo AS ( -- 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 distanceFROM ( -- get
schools in San Francisco
SELECT
ca.*,
ST_Transform(ca.geom, 3310) AS
geom_3310
