Database Reference
In-Depth Information
--------------+--------------+-----------+---------------+-----------------+------------------
Los Angeles | New York | 5012.3..
| 3935.7.. | 3944.4..
| 3944.4..
New York | San Diego | 4930.7..
| 3906.8.. | 3915.0..
| 3915.0..
...
New York | Philadelphia | 170.2..
| 129.6.. | 129.7..
| 129.7..
(36 rows)
4. Youcaneasilyverifyfromtheoutputthatthereisabigdifferencewithusing
the planar system (EPSG:900913, as in the
d_900913
column) when con-
fronted with systems that take into consideration the curvature of the earth.
How it works...
IfyouneedtocomputetheminimumCartesiandistancebetweentwopoints,youcan
use the PostGIS
ST_Distance
function. This function accepts the two-point geo-
metries as input parameters, and these geometries must be specified in the same
spatial reference system
.
If the two input geometries are using different spatial references, you can use the
ST_Transform
function on one or both of them to make them consistent with a
single spatial reference system.
Togetbetterresults,youshouldconsidertheearth'scurvature,whichismandatory
when measuring large distances, and use the
ST_Distance_Sphere
or the
ST_Distance_Spheroid
functions.Alternatively,use
ST_Distance
,butcastthe
input geometries to the
geography spatial data type
, which is optimized for this
kindofoperation.ThegeographytypestoresthegeometriesintheWGS84lonlat
degrees, but it always returns the measurements in meters.
Inthisrecipe,youhaveusedPostgreSQL CTE,whichisahandywaytoprovidea
subqueryinthecontextofthemainquery.YoucanconsideraCTEasatemporary
table used only within the scope of the main query.
