Database Reference
In-Depth Information
3. For large distances such as the ones in this case, it is not correct to use
a planar spatial reference system, but you should make the calculations
taking into consideration the earth's curvature. For example, the previously
used Mercator planar system, while it is very good to use for map outputs,
is very bad for measuring distances and areas, as it assesses directions.
For this purpose, it would be better to use a spatial reference system that
is able to measure distance. You can also use the
ST_Distance_Sphere
or
ST_Distance_Spheroid
functions (the first being quicker, but less ac-
curate, as it performs calculations on a sphere and not a spheroid). An
evenbetteroptionisconvertingthegeometriestothegeographydatatype,
ST_Distance
,asitwillautomaticallymakethecalculationsusingthespher-
oid. Note that this is exactly equivalent to using
ST_DistanceSpheroid
.
Trytocheckthedifferencebetweenthevariousapproaches,usingthesame
query as before:
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 d_900913,
ST_Distance_Sphere(c1.the_geom,
c2.the_geom)/1000 AS d_4326_sphere,
ST_Distance_Spheroid(c1.the_geom,
c2.the_geom,
'SPHEROID["GRS_1980",6378137,298.257222101]')/
1000 AS d_4326_spheroid,
ST_Distance(geography(c1.the_geom),
geography(c2.the_geom))/1000 AS
d_4326_geography
FROM cities c1 CROSS JOIN cities c2
where c1.name < c2.name
ORDER BY d_900913 DESC;
name | name | d_900913
| d_4326_sphere | d_4326_spheroid |
d_4326_geography
