PostGIS Cookbook - Second Edition

How to do it...

The following steps will guide you through the iterative process required to improve query performance:

1. To find a school's nearest police station and the distance between each school in San Francisco and its nearest station, we will start by executing the 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 distance        FROM ( -- 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, ...