Working with Vector Data –The Basics - PostGIS - page 150

FROM chp03.cities AS c1
CROSS JOIN
chp03.cities AS c2
WHERE c1.pop_2000 > 1000000 AND
c2.pop_2000 > 1000000 AND c1.name <
c2.name
ORDER BY distance_900913 DESC;
name | name |
distance_900913
--------------+--------------+------------------
Los Angeles | New York |
5012.39789777705
New York | San Diego |
4930.76973825481
Los Angeles | Philadelphia |
4865.7736877805
...
Los Angeles | San Diego |
215.396531218742
New York | Philadelphia |
170.272806220365
(36 rows)
2. Now, write the same query as we did in the previous recipe, but in a more
compactexpressionandbyusingPostgreSQL Common Table Expression
( CTE ):
WITH cities AS (
SELECT name, the_geom FROM
chp03.cities
WHERE pop_2000 > 1000000 )
SELECT c1.name, c2.name,
ST_Distance(ST_Transform(c1.the_geom,
900913), ST_Transform(c2.the_geom,
900913))/1000 AS distance_900913
FROM cities c1 CROSS JOIN cities c2
where c1.name < c2.name
ORDER BY distance_900913 DESC;