Database Reference
In-Depth Information
ST_Transform(ca.geom, 3310))
)
SELECT
school,
police_address,
ST_Distance(ST_Transform(school_geom,
3310), ST_Transform(police_geom, 3310))
AS distance
FROM ( -- for each school, number and
order the police stations by how close
each station is to the school
SELECT
ROW_NUMBER() OVER (PARTITION BY
sc.gid ORDER BY sc.geom <-> po.geom) AS r,
sc.name AS school,
sc.geom AS school_geom,
po.address AS police_address,
po.geom AS police_geom
FROM sc
CROSS JOIN po
) scpo
WHERE r < 2
ORDER BY 1;
14. The query has the following performance numbers:
Time: 511.360 ms
Time: 535.226 ms
Time: 517.626 ms
Wow! Using indexed nearest-neighbor searches with the
<->
operator, we
reduced our initial query from 10 seconds to almost half a second.
How it works...
Inthisrecipe,weoptimizedaquerythatusersmaycommonlyencounterwhileusing
PostGIS.WestartedbytakingadvantageofthePostgreSQLcapabilitiestoimprove
the performance and syntax of our query. Once performance could no longer im-
