summaryrefslogtreecommitdiffstats
path: root/contrib/earthdistance
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:15:05 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:15:05 +0000
commit46651ce6fe013220ed397add242004d764fc0153 (patch)
tree6e5299f990f88e60174a1d3ae6e48eedd2688b2b /contrib/earthdistance
parentInitial commit. (diff)
downloadpostgresql-14-upstream.tar.xz
postgresql-14-upstream.zip
Adding upstream version 14.5.upstream/14.5upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to '')
-rw-r--r--contrib/earthdistance/.gitignore4
-rw-r--r--contrib/earthdistance/Makefile23
-rw-r--r--contrib/earthdistance/earthdistance--1.0--1.1.sql14
-rw-r--r--contrib/earthdistance/earthdistance--1.1.sql98
-rw-r--r--contrib/earthdistance/earthdistance.c104
-rw-r--r--contrib/earthdistance/earthdistance.control6
-rw-r--r--contrib/earthdistance/expected/earthdistance.out1098
-rw-r--r--contrib/earthdistance/sql/earthdistance.sql359
8 files changed, 1706 insertions, 0 deletions
diff --git a/contrib/earthdistance/.gitignore b/contrib/earthdistance/.gitignore
new file mode 100644
index 0000000..5dcb3ff
--- /dev/null
+++ b/contrib/earthdistance/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/contrib/earthdistance/Makefile b/contrib/earthdistance/Makefile
new file mode 100644
index 0000000..f93b7a9
--- /dev/null
+++ b/contrib/earthdistance/Makefile
@@ -0,0 +1,23 @@
+# contrib/earthdistance/Makefile
+
+MODULES = earthdistance
+
+EXTENSION = earthdistance
+DATA = earthdistance--1.1.sql earthdistance--1.0--1.1.sql
+PGFILEDESC = "earthdistance - calculate distances on the surface of the Earth"
+
+REGRESS = earthdistance
+EXTRA_INSTALL = contrib/cube
+
+LDFLAGS_SL += $(filter -lm, $(LIBS))
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/earthdistance
+top_builddir = ../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/earthdistance/earthdistance--1.0--1.1.sql b/contrib/earthdistance/earthdistance--1.0--1.1.sql
new file mode 100644
index 0000000..802f151
--- /dev/null
+++ b/contrib/earthdistance/earthdistance--1.0--1.1.sql
@@ -0,0 +1,14 @@
+/* contrib/earthdistance/earthdistance--1.0--1.1.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION earthdistance UPDATE TO '1.1'" to load this file. \quit
+
+ALTER FUNCTION earth() PARALLEL SAFE;
+ALTER FUNCTION sec_to_gc(float8) PARALLEL SAFE;
+ALTER FUNCTION gc_to_sec(float8) PARALLEL SAFE;
+ALTER FUNCTION ll_to_earth(float8, float8) PARALLEL SAFE;
+ALTER FUNCTION latitude(earth) PARALLEL SAFE;
+ALTER FUNCTION longitude(earth) PARALLEL SAFE;
+ALTER FUNCTION earth_distance(earth, earth) PARALLEL SAFE;
+ALTER FUNCTION earth_box(earth, float8) PARALLEL SAFE;
+ALTER FUNCTION geo_distance(point, point) PARALLEL SAFE;
diff --git a/contrib/earthdistance/earthdistance--1.1.sql b/contrib/earthdistance/earthdistance--1.1.sql
new file mode 100644
index 0000000..9ef20ab
--- /dev/null
+++ b/contrib/earthdistance/earthdistance--1.1.sql
@@ -0,0 +1,98 @@
+/* contrib/earthdistance/earthdistance--1.1.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION earthdistance" to load this file. \quit
+
+-- earth() returns the radius of the earth in meters. This is the only
+-- place you need to change things for the cube base distance functions
+-- in order to use different units (or a better value for the Earth's radius).
+
+CREATE FUNCTION earth() RETURNS float8
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+AS 'SELECT ''6378168''::float8';
+
+-- Astronomers may want to change the earth function so that distances will be
+-- returned in degrees. To do this comment out the above definition and
+-- uncomment the one below. Note that doing this will break the regression
+-- tests.
+--
+-- CREATE FUNCTION earth() RETURNS float8
+-- LANGUAGE SQL IMMUTABLE
+-- AS 'SELECT 180/pi()';
+
+-- Define domain for locations on the surface of the earth using a cube
+-- datatype with constraints. cube provides 3D indexing.
+-- The cube is restricted to be a point, no more than 3 dimensions
+-- (for less than 3 dimensions 0 is assumed for the missing coordinates)
+-- and that the point must be very near the surface of the sphere
+-- centered about the origin with the radius of the earth.
+
+CREATE DOMAIN earth AS cube
+ CONSTRAINT not_point check(cube_is_point(value))
+ CONSTRAINT not_3d check(cube_dim(value) <= 3)
+ CONSTRAINT on_surface check(abs(cube_distance(value, '(0)'::cube) /
+ earth() - '1'::float8) < '10e-7'::float8);
+
+CREATE FUNCTION sec_to_gc(float8)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+AS 'SELECT CASE WHEN $1 < 0 THEN 0::float8 WHEN $1/(2*earth()) > 1 THEN pi()*earth() ELSE 2*earth()*asin($1/(2*earth())) END';
+
+CREATE FUNCTION gc_to_sec(float8)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+AS 'SELECT CASE WHEN $1 < 0 THEN 0::float8 WHEN $1/earth() > pi() THEN 2*earth() ELSE 2*earth()*sin($1/(2*earth())) END';
+
+CREATE FUNCTION ll_to_earth(float8, float8)
+RETURNS earth
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+AS 'SELECT cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth';
+
+CREATE FUNCTION latitude(earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+AS 'SELECT CASE WHEN cube_ll_coord($1, 3)/earth() < -1 THEN -90::float8 WHEN cube_ll_coord($1, 3)/earth() > 1 THEN 90::float8 ELSE degrees(asin(cube_ll_coord($1, 3)/earth())) END';
+
+CREATE FUNCTION longitude(earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+AS 'SELECT degrees(atan2(cube_ll_coord($1, 2), cube_ll_coord($1, 1)))';
+
+CREATE FUNCTION earth_distance(earth, earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+AS 'SELECT sec_to_gc(cube_distance($1, $2))';
+
+CREATE FUNCTION earth_box(earth, float8)
+RETURNS cube
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+AS 'SELECT cube_enlarge($1, gc_to_sec($2), 3)';
+
+--------------- geo_distance
+
+CREATE FUNCTION geo_distance (point, point)
+RETURNS float8
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE AS 'MODULE_PATHNAME';
+
+--------------- geo_distance as operator <@>
+
+CREATE OPERATOR <@> (
+ LEFTARG = point,
+ RIGHTARG = point,
+ PROCEDURE = geo_distance,
+ COMMUTATOR = <@>
+);
diff --git a/contrib/earthdistance/earthdistance.c b/contrib/earthdistance/earthdistance.c
new file mode 100644
index 0000000..e6ebfd1
--- /dev/null
+++ b/contrib/earthdistance/earthdistance.c
@@ -0,0 +1,104 @@
+/* contrib/earthdistance/earthdistance.c */
+
+#include "postgres.h"
+
+#include <math.h>
+
+#include "utils/geo_decls.h" /* for Point */
+
+#ifndef M_PI
+#define M_PI 3.14159265358979323846
+#endif
+
+PG_MODULE_MAGIC;
+
+/* Earth's radius is in statute miles. */
+static const double EARTH_RADIUS = 3958.747716;
+static const double TWO_PI = 2.0 * M_PI;
+
+
+/******************************************************
+ *
+ * degtorad - convert degrees to radians
+ *
+ * arg: double, angle in degrees
+ *
+ * returns: double, same angle in radians
+ ******************************************************/
+
+static double
+degtorad(double degrees)
+{
+ return (degrees / 360.0) * TWO_PI;
+}
+
+/******************************************************
+ *
+ * geo_distance_internal - distance between points
+ *
+ * args:
+ * a pair of points - for each point,
+ * x-coordinate is longitude in degrees west of Greenwich
+ * y-coordinate is latitude in degrees above equator
+ *
+ * returns: double
+ * distance between the points in miles on earth's surface
+ ******************************************************/
+
+static double
+geo_distance_internal(Point *pt1, Point *pt2)
+{
+ double long1,
+ lat1,
+ long2,
+ lat2;
+ double longdiff;
+ double sino;
+
+ /* convert degrees to radians */
+
+ long1 = degtorad(pt1->x);
+ lat1 = degtorad(pt1->y);
+
+ long2 = degtorad(pt2->x);
+ lat2 = degtorad(pt2->y);
+
+ /* compute difference in longitudes - want < 180 degrees */
+ longdiff = fabs(long1 - long2);
+ if (longdiff > M_PI)
+ longdiff = TWO_PI - longdiff;
+
+ sino = sqrt(sin(fabs(lat1 - lat2) / 2.) * sin(fabs(lat1 - lat2) / 2.) +
+ cos(lat1) * cos(lat2) * sin(longdiff / 2.) * sin(longdiff / 2.));
+ if (sino > 1.)
+ sino = 1.;
+
+ return 2. * EARTH_RADIUS * asin(sino);
+}
+
+
+/******************************************************
+ *
+ * geo_distance - distance between points
+ *
+ * args:
+ * a pair of points - for each point,
+ * x-coordinate is longitude in degrees west of Greenwich
+ * y-coordinate is latitude in degrees above equator
+ *
+ * returns: float8
+ * distance between the points in miles on earth's surface
+ ******************************************************/
+
+PG_FUNCTION_INFO_V1(geo_distance);
+
+Datum
+geo_distance(PG_FUNCTION_ARGS)
+{
+ Point *pt1 = PG_GETARG_POINT_P(0);
+ Point *pt2 = PG_GETARG_POINT_P(1);
+ float8 result;
+
+ result = geo_distance_internal(pt1, pt2);
+ PG_RETURN_FLOAT8(result);
+}
diff --git a/contrib/earthdistance/earthdistance.control b/contrib/earthdistance/earthdistance.control
new file mode 100644
index 0000000..5816d22
--- /dev/null
+++ b/contrib/earthdistance/earthdistance.control
@@ -0,0 +1,6 @@
+# earthdistance extension
+comment = 'calculate great-circle distances on the surface of the Earth'
+default_version = '1.1'
+module_pathname = '$libdir/earthdistance'
+relocatable = true
+requires = 'cube'
diff --git a/contrib/earthdistance/expected/earthdistance.out b/contrib/earthdistance/expected/earthdistance.out
new file mode 100644
index 0000000..26a843c
--- /dev/null
+++ b/contrib/earthdistance/expected/earthdistance.out
@@ -0,0 +1,1098 @@
+--
+-- Test earthdistance extension
+--
+-- In this file we also do some testing of extension create/drop scenarios.
+-- That's really exercising the core database's dependency logic, so ideally
+-- we'd do it in the core regression tests, but we can't for lack of suitable
+-- guaranteed-available extensions. earthdistance is a good test case because
+-- it has a dependency on the cube extension.
+--
+CREATE EXTENSION earthdistance; -- fail, must install cube first
+ERROR: required extension "cube" is not installed
+HINT: Use CREATE EXTENSION ... CASCADE to install required extensions too.
+CREATE EXTENSION cube;
+CREATE EXTENSION earthdistance;
+--
+-- The radius of the Earth we are using.
+--
+SELECT earth()::numeric(20,5);
+ earth
+---------------
+ 6378168.00000
+(1 row)
+
+--
+-- Convert straight line distances to great circle distances.
+--
+SELECT (pi()*earth())::numeric(20,5);
+ numeric
+----------------
+ 20037605.73216
+(1 row)
+
+SELECT sec_to_gc(0)::numeric(20,5);
+ sec_to_gc
+-----------
+ 0.00000
+(1 row)
+
+SELECT sec_to_gc(2*earth())::numeric(20,5);
+ sec_to_gc
+----------------
+ 20037605.73216
+(1 row)
+
+SELECT sec_to_gc(10*earth())::numeric(20,5);
+ sec_to_gc
+----------------
+ 20037605.73216
+(1 row)
+
+SELECT sec_to_gc(-earth())::numeric(20,5);
+ sec_to_gc
+-----------
+ 0.00000
+(1 row)
+
+SELECT sec_to_gc(1000)::numeric(20,5);
+ sec_to_gc
+------------
+ 1000.00000
+(1 row)
+
+SELECT sec_to_gc(10000)::numeric(20,5);
+ sec_to_gc
+-------------
+ 10000.00102
+(1 row)
+
+SELECT sec_to_gc(100000)::numeric(20,5);
+ sec_to_gc
+--------------
+ 100001.02426
+(1 row)
+
+SELECT sec_to_gc(1000000)::numeric(20,5);
+ sec_to_gc
+---------------
+ 1001027.07131
+(1 row)
+
+--
+-- Convert great circle distances to straight line distances.
+--
+SELECT gc_to_sec(0)::numeric(20,5);
+ gc_to_sec
+-----------
+ 0.00000
+(1 row)
+
+SELECT gc_to_sec(sec_to_gc(2*earth()))::numeric(20,5);
+ gc_to_sec
+----------------
+ 12756336.00000
+(1 row)
+
+SELECT gc_to_sec(10*earth())::numeric(20,5);
+ gc_to_sec
+----------------
+ 12756336.00000
+(1 row)
+
+SELECT gc_to_sec(pi()*earth())::numeric(20,5);
+ gc_to_sec
+----------------
+ 12756336.00000
+(1 row)
+
+SELECT gc_to_sec(-1000)::numeric(20,5);
+ gc_to_sec
+-----------
+ 0.00000
+(1 row)
+
+SELECT gc_to_sec(1000)::numeric(20,5);
+ gc_to_sec
+------------
+ 1000.00000
+(1 row)
+
+SELECT gc_to_sec(10000)::numeric(20,5);
+ gc_to_sec
+------------
+ 9999.99898
+(1 row)
+
+SELECT gc_to_sec(100000)::numeric(20,5);
+ gc_to_sec
+-------------
+ 99998.97577
+(1 row)
+
+SELECT gc_to_sec(1000000)::numeric(20,5);
+ gc_to_sec
+--------------
+ 998976.08618
+(1 row)
+
+--
+-- Set coordinates using latitude and longitude.
+-- Extract each coordinate separately so we can round them.
+--
+SELECT cube_ll_coord(ll_to_earth(0,0),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(0,0),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(0,0),3)::numeric(20,5);
+ cube_ll_coord | cube_ll_coord | cube_ll_coord
+---------------+---------------+---------------
+ 6378168.00000 | 0.00000 | 0.00000
+(1 row)
+
+SELECT cube_ll_coord(ll_to_earth(360,360),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(360,360),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(360,360),3)::numeric(20,5);
+ cube_ll_coord | cube_ll_coord | cube_ll_coord
+---------------+---------------+---------------
+ 6378168.00000 | 0.00000 | 0.00000
+(1 row)
+
+SELECT cube_ll_coord(ll_to_earth(180,180),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(180,180),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(180,180),3)::numeric(20,5);
+ cube_ll_coord | cube_ll_coord | cube_ll_coord
+---------------+---------------+---------------
+ 6378168.00000 | 0.00000 | 0.00000
+(1 row)
+
+SELECT cube_ll_coord(ll_to_earth(180,360),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(180,360),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(180,360),3)::numeric(20,5);
+ cube_ll_coord | cube_ll_coord | cube_ll_coord
+----------------+---------------+---------------
+ -6378168.00000 | 0.00000 | 0.00000
+(1 row)
+
+SELECT cube_ll_coord(ll_to_earth(-180,-360),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(-180,-360),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(-180,-360),3)::numeric(20,5);
+ cube_ll_coord | cube_ll_coord | cube_ll_coord
+----------------+---------------+---------------
+ -6378168.00000 | 0.00000 | 0.00000
+(1 row)
+
+SELECT cube_ll_coord(ll_to_earth(0,180),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(0,180),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(0,180),3)::numeric(20,5);
+ cube_ll_coord | cube_ll_coord | cube_ll_coord
+----------------+---------------+---------------
+ -6378168.00000 | 0.00000 | 0.00000
+(1 row)
+
+SELECT cube_ll_coord(ll_to_earth(0,-180),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(0,-180),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(0,-180),3)::numeric(20,5);
+ cube_ll_coord | cube_ll_coord | cube_ll_coord
+----------------+---------------+---------------
+ -6378168.00000 | 0.00000 | 0.00000
+(1 row)
+
+SELECT cube_ll_coord(ll_to_earth(90,0),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(90,0),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(90,0),3)::numeric(20,5);
+ cube_ll_coord | cube_ll_coord | cube_ll_coord
+---------------+---------------+---------------
+ 0.00000 | 0.00000 | 6378168.00000
+(1 row)
+
+SELECT cube_ll_coord(ll_to_earth(90,180),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(90,180),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(90,180),3)::numeric(20,5);
+ cube_ll_coord | cube_ll_coord | cube_ll_coord
+---------------+---------------+---------------
+ 0.00000 | 0.00000 | 6378168.00000
+(1 row)
+
+SELECT cube_ll_coord(ll_to_earth(-90,0),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(-90,0),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(-90,0),3)::numeric(20,5);
+ cube_ll_coord | cube_ll_coord | cube_ll_coord
+---------------+---------------+----------------
+ 0.00000 | 0.00000 | -6378168.00000
+(1 row)
+
+SELECT cube_ll_coord(ll_to_earth(-90,180),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(-90,180),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(-90,180),3)::numeric(20,5);
+ cube_ll_coord | cube_ll_coord | cube_ll_coord
+---------------+---------------+----------------
+ 0.00000 | 0.00000 | -6378168.00000
+(1 row)
+
+--
+-- Test getting the latitude of a location.
+--
+SELECT latitude(ll_to_earth(0,0))::numeric(20,10);
+ latitude
+--------------
+ 0.0000000000
+(1 row)
+
+SELECT latitude(ll_to_earth(45,0))::numeric(20,10);
+ latitude
+---------------
+ 45.0000000000
+(1 row)
+
+SELECT latitude(ll_to_earth(90,0))::numeric(20,10);
+ latitude
+---------------
+ 90.0000000000
+(1 row)
+
+SELECT latitude(ll_to_earth(-45,0))::numeric(20,10);
+ latitude
+----------------
+ -45.0000000000
+(1 row)
+
+SELECT latitude(ll_to_earth(-90,0))::numeric(20,10);
+ latitude
+----------------
+ -90.0000000000
+(1 row)
+
+SELECT latitude(ll_to_earth(0,90))::numeric(20,10);
+ latitude
+--------------
+ 0.0000000000
+(1 row)
+
+SELECT latitude(ll_to_earth(45,90))::numeric(20,10);
+ latitude
+---------------
+ 45.0000000000
+(1 row)
+
+SELECT latitude(ll_to_earth(90,90))::numeric(20,10);
+ latitude
+---------------
+ 90.0000000000
+(1 row)
+
+SELECT latitude(ll_to_earth(-45,90))::numeric(20,10);
+ latitude
+----------------
+ -45.0000000000
+(1 row)
+
+SELECT latitude(ll_to_earth(-90,90))::numeric(20,10);
+ latitude
+----------------
+ -90.0000000000
+(1 row)
+
+SELECT latitude(ll_to_earth(0,180))::numeric(20,10);
+ latitude
+--------------
+ 0.0000000000
+(1 row)
+
+SELECT latitude(ll_to_earth(45,180))::numeric(20,10);
+ latitude
+---------------
+ 45.0000000000
+(1 row)
+
+SELECT latitude(ll_to_earth(90,180))::numeric(20,10);
+ latitude
+---------------
+ 90.0000000000
+(1 row)
+
+SELECT latitude(ll_to_earth(-45,180))::numeric(20,10);
+ latitude
+----------------
+ -45.0000000000
+(1 row)
+
+SELECT latitude(ll_to_earth(-90,180))::numeric(20,10);
+ latitude
+----------------
+ -90.0000000000
+(1 row)
+
+SELECT latitude(ll_to_earth(0,-90))::numeric(20,10);
+ latitude
+--------------
+ 0.0000000000
+(1 row)
+
+SELECT latitude(ll_to_earth(45,-90))::numeric(20,10);
+ latitude
+---------------
+ 45.0000000000
+(1 row)
+
+SELECT latitude(ll_to_earth(90,-90))::numeric(20,10);
+ latitude
+---------------
+ 90.0000000000
+(1 row)
+
+SELECT latitude(ll_to_earth(-45,-90))::numeric(20,10);
+ latitude
+----------------
+ -45.0000000000
+(1 row)
+
+SELECT latitude(ll_to_earth(-90,-90))::numeric(20,10);
+ latitude
+----------------
+ -90.0000000000
+(1 row)
+
+--
+-- Test getting the longitude of a location.
+--
+SELECT longitude(ll_to_earth(0,0))::numeric(20,10);
+ longitude
+--------------
+ 0.0000000000
+(1 row)
+
+SELECT longitude(ll_to_earth(45,0))::numeric(20,10);
+ longitude
+--------------
+ 0.0000000000
+(1 row)
+
+SELECT longitude(ll_to_earth(90,0))::numeric(20,10);
+ longitude
+--------------
+ 0.0000000000
+(1 row)
+
+SELECT longitude(ll_to_earth(-45,0))::numeric(20,10);
+ longitude
+--------------
+ 0.0000000000
+(1 row)
+
+SELECT longitude(ll_to_earth(-90,0))::numeric(20,10);
+ longitude
+--------------
+ 0.0000000000
+(1 row)
+
+SELECT longitude(ll_to_earth(0,90))::numeric(20,10);
+ longitude
+---------------
+ 90.0000000000
+(1 row)
+
+SELECT longitude(ll_to_earth(45,90))::numeric(20,10);
+ longitude
+---------------
+ 90.0000000000
+(1 row)
+
+SELECT longitude(ll_to_earth(90,90))::numeric(20,10);
+ longitude
+---------------
+ 90.0000000000
+(1 row)
+
+SELECT longitude(ll_to_earth(-45,90))::numeric(20,10);
+ longitude
+---------------
+ 90.0000000000
+(1 row)
+
+SELECT longitude(ll_to_earth(-90,90))::numeric(20,10);
+ longitude
+---------------
+ 90.0000000000
+(1 row)
+
+SELECT longitude(ll_to_earth(0,180))::numeric(20,10);
+ longitude
+----------------
+ 180.0000000000
+(1 row)
+
+SELECT longitude(ll_to_earth(45,180))::numeric(20,10);
+ longitude
+----------------
+ 180.0000000000
+(1 row)
+
+SELECT longitude(ll_to_earth(90,180))::numeric(20,10);
+ longitude
+----------------
+ 180.0000000000
+(1 row)
+
+SELECT longitude(ll_to_earth(-45,180))::numeric(20,10);
+ longitude
+----------------
+ 180.0000000000
+(1 row)
+
+SELECT longitude(ll_to_earth(-90,180))::numeric(20,10);
+ longitude
+----------------
+ 180.0000000000
+(1 row)
+
+SELECT longitude(ll_to_earth(0,-90))::numeric(20,10);
+ longitude
+----------------
+ -90.0000000000
+(1 row)
+
+SELECT longitude(ll_to_earth(45,-90))::numeric(20,10);
+ longitude
+----------------
+ -90.0000000000
+(1 row)
+
+SELECT longitude(ll_to_earth(90,-90))::numeric(20,10);
+ longitude
+----------------
+ -90.0000000000
+(1 row)
+
+SELECT longitude(ll_to_earth(-45,-90))::numeric(20,10);
+ longitude
+----------------
+ -90.0000000000
+(1 row)
+
+SELECT longitude(ll_to_earth(-90,-90))::numeric(20,10);
+ longitude
+----------------
+ -90.0000000000
+(1 row)
+
+--
+-- For the distance tests the following is some real life data.
+--
+-- Chicago has a latitude of 41.8 and a longitude of 87.6.
+-- Albuquerque has a latitude of 35.1 and a longitude of 106.7.
+-- (Note that latitude and longitude are specified differently
+-- in the cube based functions than for the point based functions.)
+--
+--
+-- Test getting the distance between two points using earth_distance.
+--
+SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,0))::numeric(20,5);
+ earth_distance
+----------------
+ 0.00000
+(1 row)
+
+SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,180))::numeric(20,5);
+ earth_distance
+----------------
+ 20037605.73216
+(1 row)
+
+SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(90,0))::numeric(20,5);
+ earth_distance
+----------------
+ 10018802.86608
+(1 row)
+
+SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,90))::numeric(20,5);
+ earth_distance
+----------------
+ 10018802.86608
+(1 row)
+
+SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,1))::numeric(20,5);
+ earth_distance
+----------------
+ 111320.03185
+(1 row)
+
+SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(1,0))::numeric(20,5);
+ earth_distance
+----------------
+ 111320.03185
+(1 row)
+
+SELECT earth_distance(ll_to_earth(30,0),ll_to_earth(30,1))::numeric(20,5);
+ earth_distance
+----------------
+ 96405.66962
+(1 row)
+
+SELECT earth_distance(ll_to_earth(30,0),ll_to_earth(31,0))::numeric(20,5);
+ earth_distance
+----------------
+ 111320.03185
+(1 row)
+
+SELECT earth_distance(ll_to_earth(60,0),ll_to_earth(60,1))::numeric(20,5);
+ earth_distance
+----------------
+ 55659.48608
+(1 row)
+
+SELECT earth_distance(ll_to_earth(60,0),ll_to_earth(61,0))::numeric(20,5);
+ earth_distance
+----------------
+ 111320.03185
+(1 row)
+
+SELECT earth_distance(ll_to_earth(41.8,87.6),ll_to_earth(35.1,106.7))::numeric(20,5);
+ earth_distance
+----------------
+ 1819303.21265
+(1 row)
+
+SELECT (earth_distance(ll_to_earth(41.8,87.6),ll_to_earth(35.1,106.7))*
+ 100./2.54/12./5280.)::numeric(20,5);
+ numeric
+------------
+ 1130.46261
+(1 row)
+
+--
+-- Test getting the distance between two points using geo_distance.
+--
+SELECT geo_distance('(0,0)'::point,'(0,0)'::point)::numeric(20,5);
+ geo_distance
+--------------
+ 0.00000
+(1 row)
+
+SELECT geo_distance('(0,0)'::point,'(180,0)'::point)::numeric(20,5);
+ geo_distance
+--------------
+ 12436.77274
+(1 row)
+
+SELECT geo_distance('(0,0)'::point,'(0,90)'::point)::numeric(20,5);
+ geo_distance
+--------------
+ 6218.38637
+(1 row)
+
+SELECT geo_distance('(0,0)'::point,'(90,0)'::point)::numeric(20,5);
+ geo_distance
+--------------
+ 6218.38637
+(1 row)
+
+SELECT geo_distance('(0,0)'::point,'(1,0)'::point)::numeric(20,5);
+ geo_distance
+--------------
+ 69.09318
+(1 row)
+
+SELECT geo_distance('(0,0)'::point,'(0,1)'::point)::numeric(20,5);
+ geo_distance
+--------------
+ 69.09318
+(1 row)
+
+SELECT geo_distance('(0,30)'::point,'(1,30)'::point)::numeric(20,5);
+ geo_distance
+--------------
+ 59.83626
+(1 row)
+
+SELECT geo_distance('(0,30)'::point,'(0,31)'::point)::numeric(20,5);
+ geo_distance
+--------------
+ 69.09318
+(1 row)
+
+SELECT geo_distance('(0,60)'::point,'(1,60)'::point)::numeric(20,5);
+ geo_distance
+--------------
+ 34.54626
+(1 row)
+
+SELECT geo_distance('(0,60)'::point,'(0,61)'::point)::numeric(20,5);
+ geo_distance
+--------------
+ 69.09318
+(1 row)
+
+SELECT geo_distance('(87.6,41.8)'::point,'(106.7,35.1)'::point)::numeric(20,5);
+ geo_distance
+--------------
+ 1129.18983
+(1 row)
+
+SELECT (geo_distance('(87.6,41.8)'::point,'(106.7,35.1)'::point)*5280.*12.*2.54/100.)::numeric(20,5);
+ numeric
+---------------
+ 1817254.87730
+(1 row)
+
+--
+-- Test getting the distance between two points using the <@> operator.
+--
+SELECT ('(0,0)'::point <@> '(0,0)'::point)::numeric(20,5);
+ numeric
+---------
+ 0.00000
+(1 row)
+
+SELECT ('(0,0)'::point <@> '(180,0)'::point)::numeric(20,5);
+ numeric
+-------------
+ 12436.77274
+(1 row)
+
+SELECT ('(0,0)'::point <@> '(0,90)'::point)::numeric(20,5);
+ numeric
+------------
+ 6218.38637
+(1 row)
+
+SELECT ('(0,0)'::point <@> '(90,0)'::point)::numeric(20,5);
+ numeric
+------------
+ 6218.38637
+(1 row)
+
+SELECT ('(0,0)'::point <@> '(1,0)'::point)::numeric(20,5);
+ numeric
+----------
+ 69.09318
+(1 row)
+
+SELECT ('(0,0)'::point <@> '(0,1)'::point)::numeric(20,5);
+ numeric
+----------
+ 69.09318
+(1 row)
+
+SELECT ('(0,30)'::point <@> '(1,30)'::point)::numeric(20,5);
+ numeric
+----------
+ 59.83626
+(1 row)
+
+SELECT ('(0,30)'::point <@> '(0,31)'::point)::numeric(20,5);
+ numeric
+----------
+ 69.09318
+(1 row)
+
+SELECT ('(0,60)'::point <@> '(1,60)'::point)::numeric(20,5);
+ numeric
+----------
+ 34.54626
+(1 row)
+
+SELECT ('(0,60)'::point <@> '(0,61)'::point)::numeric(20,5);
+ numeric
+----------
+ 69.09318
+(1 row)
+
+SELECT ('(87.6,41.8)'::point <@> '(106.7,35.1)'::point)::numeric(20,5);
+ numeric
+------------
+ 1129.18983
+(1 row)
+
+SELECT (('(87.6,41.8)'::point <@> '(106.7,35.1)'::point)*5280.*12.*2.54/100.)::numeric(20,5);
+ numeric
+---------------
+ 1817254.87730
+(1 row)
+
+--
+-- Test getting a bounding box around points.
+--
+SELECT cube_ll_coord(earth_box(ll_to_earth(0,0),112000),1)::numeric(20,5),
+ cube_ll_coord(earth_box(ll_to_earth(0,0),112000),2)::numeric(20,5),
+ cube_ll_coord(earth_box(ll_to_earth(0,0),112000),3)::numeric(20,5),
+ cube_ur_coord(earth_box(ll_to_earth(0,0),112000),1)::numeric(20,5),
+ cube_ur_coord(earth_box(ll_to_earth(0,0),112000),2)::numeric(20,5),
+ cube_ur_coord(earth_box(ll_to_earth(0,0),112000),3)::numeric(20,5);
+ cube_ll_coord | cube_ll_coord | cube_ll_coord | cube_ur_coord | cube_ur_coord | cube_ur_coord
+---------------+---------------+---------------+---------------+---------------+---------------
+ 6266169.43896 | -111998.56104 | -111998.56104 | 6490166.56104 | 111998.56104 | 111998.56104
+(1 row)
+
+SELECT cube_ll_coord(earth_box(ll_to_earth(0,0),pi()*earth()),1)::numeric(20,5),
+ cube_ll_coord(earth_box(ll_to_earth(0,0),pi()*earth()),2)::numeric(20,5),
+ cube_ll_coord(earth_box(ll_to_earth(0,0),pi()*earth()),3)::numeric(20,5),
+ cube_ur_coord(earth_box(ll_to_earth(0,0),pi()*earth()),1)::numeric(20,5),
+ cube_ur_coord(earth_box(ll_to_earth(0,0),pi()*earth()),2)::numeric(20,5),
+ cube_ur_coord(earth_box(ll_to_earth(0,0),pi()*earth()),3)::numeric(20,5);
+ cube_ll_coord | cube_ll_coord | cube_ll_coord | cube_ur_coord | cube_ur_coord | cube_ur_coord
+----------------+-----------------+-----------------+----------------+----------------+----------------
+ -6378168.00000 | -12756336.00000 | -12756336.00000 | 19134504.00000 | 12756336.00000 | 12756336.00000
+(1 row)
+
+SELECT cube_ll_coord(earth_box(ll_to_earth(0,0),10*earth()),1)::numeric(20,5),
+ cube_ll_coord(earth_box(ll_to_earth(0,0),10*earth()),2)::numeric(20,5),
+ cube_ll_coord(earth_box(ll_to_earth(0,0),10*earth()),3)::numeric(20,5),
+ cube_ur_coord(earth_box(ll_to_earth(0,0),10*earth()),1)::numeric(20,5),
+ cube_ur_coord(earth_box(ll_to_earth(0,0),10*earth()),2)::numeric(20,5),
+ cube_ur_coord(earth_box(ll_to_earth(0,0),10*earth()),3)::numeric(20,5);
+ cube_ll_coord | cube_ll_coord | cube_ll_coord | cube_ur_coord | cube_ur_coord | cube_ur_coord
+----------------+-----------------+-----------------+----------------+----------------+----------------
+ -6378168.00000 | -12756336.00000 | -12756336.00000 | 19134504.00000 | 12756336.00000 | 12756336.00000
+(1 row)
+
+--
+-- Test for points that should be in bounding boxes.
+--
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0,1))*1.00001) @>
+ ll_to_earth(0,1);
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.1))*1.00001) @>
+ ll_to_earth(0,0.1);
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.01))*1.00001) @>
+ ll_to_earth(0,0.01);
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.001))*1.00001) @>
+ ll_to_earth(0,0.001);
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.0001))*1.00001) @>
+ ll_to_earth(0,0.0001);
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0.0001,0.0001))*1.00001) @>
+ ll_to_earth(0.0001,0.0001);
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT earth_box(ll_to_earth(45,45),
+ earth_distance(ll_to_earth(45,45),ll_to_earth(45.0001,45.0001))*1.00001) @>
+ ll_to_earth(45.0001,45.0001);
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT earth_box(ll_to_earth(90,180),
+ earth_distance(ll_to_earth(90,180),ll_to_earth(90.0001,180.0001))*1.00001) @>
+ ll_to_earth(90.0001,180.0001);
+ ?column?
+----------
+ t
+(1 row)
+
+--
+-- Test for points that shouldn't be in bounding boxes. Note that we need
+-- to make points way outside, since some points close may be in the box
+-- but further away than the distance we are testing.
+--
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0,1))*.57735) @>
+ ll_to_earth(0,1);
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.1))*.57735) @>
+ ll_to_earth(0,0.1);
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.01))*.57735) @>
+ ll_to_earth(0,0.01);
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.001))*.57735) @>
+ ll_to_earth(0,0.001);
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.0001))*.57735) @>
+ ll_to_earth(0,0.0001);
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0.0001,0.0001))*.57735) @>
+ ll_to_earth(0.0001,0.0001);
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT earth_box(ll_to_earth(45,45),
+ earth_distance(ll_to_earth(45,45),ll_to_earth(45.0001,45.0001))*.57735) @>
+ ll_to_earth(45.0001,45.0001);
+ ?column?
+----------
+ f
+(1 row)
+
+SELECT earth_box(ll_to_earth(90,180),
+ earth_distance(ll_to_earth(90,180),ll_to_earth(90.0001,180.0001))*.57735) @>
+ ll_to_earth(90.0001,180.0001);
+ ?column?
+----------
+ f
+(1 row)
+
+--
+-- Test the recommended constraints.
+--
+SELECT cube_is_point(ll_to_earth(0,0));
+ cube_is_point
+---------------
+ t
+(1 row)
+
+SELECT cube_dim(ll_to_earth(0,0)) <= 3;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT abs(cube_distance(ll_to_earth(0,0), '(0)'::cube) / earth() - 1) <
+ '10e-12'::float8;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT cube_is_point(ll_to_earth(30,60));
+ cube_is_point
+---------------
+ t
+(1 row)
+
+SELECT cube_dim(ll_to_earth(30,60)) <= 3;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT abs(cube_distance(ll_to_earth(30,60), '(0)'::cube) / earth() - 1) <
+ '10e-12'::float8;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT cube_is_point(ll_to_earth(60,90));
+ cube_is_point
+---------------
+ t
+(1 row)
+
+SELECT cube_dim(ll_to_earth(60,90)) <= 3;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT abs(cube_distance(ll_to_earth(60,90), '(0)'::cube) / earth() - 1) <
+ '10e-12'::float8;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT cube_is_point(ll_to_earth(-30,-90));
+ cube_is_point
+---------------
+ t
+(1 row)
+
+SELECT cube_dim(ll_to_earth(-30,-90)) <= 3;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT abs(cube_distance(ll_to_earth(-30,-90), '(0)'::cube) / earth() - 1) <
+ '10e-12'::float8;
+ ?column?
+----------
+ t
+(1 row)
+
+--
+-- Now we are going to test extension create/drop scenarios.
+--
+-- list what's installed
+\dT
+ List of data types
+ Schema | Name | Description
+--------+-------+---------------------------------------------------------------------------------------------
+ public | cube | multi-dimensional cube '(FLOAT-1, FLOAT-2, ..., FLOAT-N), (FLOAT-1, FLOAT-2, ..., FLOAT-N)'
+ public | earth |
+(2 rows)
+
+drop extension cube; -- fail, earthdistance requires it
+ERROR: cannot drop extension cube because other objects depend on it
+DETAIL: extension earthdistance depends on extension cube
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+drop extension earthdistance;
+drop type cube; -- fail, extension cube requires it
+ERROR: cannot drop type cube because extension cube requires it
+HINT: You can drop extension cube instead.
+-- list what's installed
+\dT
+ List of data types
+ Schema | Name | Description
+--------+------+---------------------------------------------------------------------------------------------
+ public | cube | multi-dimensional cube '(FLOAT-1, FLOAT-2, ..., FLOAT-N), (FLOAT-1, FLOAT-2, ..., FLOAT-N)'
+(1 row)
+
+create table foo (f1 cube, f2 int);
+drop extension cube; -- fail, foo.f1 requires it
+ERROR: cannot drop extension cube because other objects depend on it
+DETAIL: column f1 of table foo depends on type cube
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+drop table foo;
+drop extension cube;
+-- list what's installed
+\dT
+ List of data types
+ Schema | Name | Description
+--------+------+-------------
+(0 rows)
+
+\df
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+------+------------------+---------------------+------
+(0 rows)
+
+\do
+ List of operators
+ Schema | Name | Left arg type | Right arg type | Result type | Description
+--------+------+---------------+----------------+-------------+-------------
+(0 rows)
+
+create schema c;
+create extension cube with schema c;
+-- list what's installed
+\dT public.*
+ List of data types
+ Schema | Name | Description
+--------+------+-------------
+(0 rows)
+
+\df public.*
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+------+------------------+---------------------+------
+(0 rows)
+
+\do public.*
+ List of operators
+ Schema | Name | Left arg type | Right arg type | Result type | Description
+--------+------+---------------+----------------+-------------+-------------
+(0 rows)
+
+\dT c.*
+ List of data types
+ Schema | Name | Description
+--------+--------+---------------------------------------------------------------------------------------------
+ c | c.cube | multi-dimensional cube '(FLOAT-1, FLOAT-2, ..., FLOAT-N), (FLOAT-1, FLOAT-2, ..., FLOAT-N)'
+(1 row)
+
+create table foo (f1 c.cube, f2 int);
+drop extension cube; -- fail, foo.f1 requires it
+ERROR: cannot drop extension cube because other objects depend on it
+DETAIL: column f1 of table foo depends on type c.cube
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+drop schema c; -- fail, cube requires it
+ERROR: cannot drop schema c because other objects depend on it
+DETAIL: extension cube depends on schema c
+column f1 of table foo depends on type c.cube
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+drop extension cube cascade;
+NOTICE: drop cascades to column f1 of table foo
+\d foo
+ Table "public.foo"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ f2 | integer | | |
+
+-- list what's installed
+\dT public.*
+ List of data types
+ Schema | Name | Description
+--------+------+-------------
+(0 rows)
+
+\df public.*
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+------+------------------+---------------------+------
+(0 rows)
+
+\do public.*
+ List of operators
+ Schema | Name | Left arg type | Right arg type | Result type | Description
+--------+------+---------------+----------------+-------------+-------------
+(0 rows)
+
+\dT c.*
+ List of data types
+ Schema | Name | Description
+--------+------+-------------
+(0 rows)
+
+\df c.*
+ List of functions
+ Schema | Name | Result data type | Argument data types | Type
+--------+------+------------------+---------------------+------
+(0 rows)
+
+\do c.*
+ List of operators
+ Schema | Name | Left arg type | Right arg type | Result type | Description
+--------+------+---------------+----------------+-------------+-------------
+(0 rows)
+
+drop schema c;
diff --git a/contrib/earthdistance/sql/earthdistance.sql b/contrib/earthdistance/sql/earthdistance.sql
new file mode 100644
index 0000000..4145561
--- /dev/null
+++ b/contrib/earthdistance/sql/earthdistance.sql
@@ -0,0 +1,359 @@
+--
+-- Test earthdistance extension
+--
+-- In this file we also do some testing of extension create/drop scenarios.
+-- That's really exercising the core database's dependency logic, so ideally
+-- we'd do it in the core regression tests, but we can't for lack of suitable
+-- guaranteed-available extensions. earthdistance is a good test case because
+-- it has a dependency on the cube extension.
+--
+
+CREATE EXTENSION earthdistance; -- fail, must install cube first
+CREATE EXTENSION cube;
+CREATE EXTENSION earthdistance;
+
+--
+-- The radius of the Earth we are using.
+--
+
+SELECT earth()::numeric(20,5);
+
+--
+-- Convert straight line distances to great circle distances.
+--
+SELECT (pi()*earth())::numeric(20,5);
+SELECT sec_to_gc(0)::numeric(20,5);
+SELECT sec_to_gc(2*earth())::numeric(20,5);
+SELECT sec_to_gc(10*earth())::numeric(20,5);
+SELECT sec_to_gc(-earth())::numeric(20,5);
+SELECT sec_to_gc(1000)::numeric(20,5);
+SELECT sec_to_gc(10000)::numeric(20,5);
+SELECT sec_to_gc(100000)::numeric(20,5);
+SELECT sec_to_gc(1000000)::numeric(20,5);
+
+--
+-- Convert great circle distances to straight line distances.
+--
+
+SELECT gc_to_sec(0)::numeric(20,5);
+SELECT gc_to_sec(sec_to_gc(2*earth()))::numeric(20,5);
+SELECT gc_to_sec(10*earth())::numeric(20,5);
+SELECT gc_to_sec(pi()*earth())::numeric(20,5);
+SELECT gc_to_sec(-1000)::numeric(20,5);
+SELECT gc_to_sec(1000)::numeric(20,5);
+SELECT gc_to_sec(10000)::numeric(20,5);
+SELECT gc_to_sec(100000)::numeric(20,5);
+SELECT gc_to_sec(1000000)::numeric(20,5);
+
+--
+-- Set coordinates using latitude and longitude.
+-- Extract each coordinate separately so we can round them.
+--
+
+SELECT cube_ll_coord(ll_to_earth(0,0),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(0,0),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(0,0),3)::numeric(20,5);
+SELECT cube_ll_coord(ll_to_earth(360,360),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(360,360),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(360,360),3)::numeric(20,5);
+SELECT cube_ll_coord(ll_to_earth(180,180),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(180,180),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(180,180),3)::numeric(20,5);
+SELECT cube_ll_coord(ll_to_earth(180,360),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(180,360),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(180,360),3)::numeric(20,5);
+SELECT cube_ll_coord(ll_to_earth(-180,-360),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(-180,-360),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(-180,-360),3)::numeric(20,5);
+SELECT cube_ll_coord(ll_to_earth(0,180),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(0,180),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(0,180),3)::numeric(20,5);
+SELECT cube_ll_coord(ll_to_earth(0,-180),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(0,-180),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(0,-180),3)::numeric(20,5);
+SELECT cube_ll_coord(ll_to_earth(90,0),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(90,0),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(90,0),3)::numeric(20,5);
+SELECT cube_ll_coord(ll_to_earth(90,180),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(90,180),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(90,180),3)::numeric(20,5);
+SELECT cube_ll_coord(ll_to_earth(-90,0),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(-90,0),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(-90,0),3)::numeric(20,5);
+SELECT cube_ll_coord(ll_to_earth(-90,180),1)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(-90,180),2)::numeric(20,5),
+ cube_ll_coord(ll_to_earth(-90,180),3)::numeric(20,5);
+
+--
+-- Test getting the latitude of a location.
+--
+
+SELECT latitude(ll_to_earth(0,0))::numeric(20,10);
+SELECT latitude(ll_to_earth(45,0))::numeric(20,10);
+SELECT latitude(ll_to_earth(90,0))::numeric(20,10);
+SELECT latitude(ll_to_earth(-45,0))::numeric(20,10);
+SELECT latitude(ll_to_earth(-90,0))::numeric(20,10);
+SELECT latitude(ll_to_earth(0,90))::numeric(20,10);
+SELECT latitude(ll_to_earth(45,90))::numeric(20,10);
+SELECT latitude(ll_to_earth(90,90))::numeric(20,10);
+SELECT latitude(ll_to_earth(-45,90))::numeric(20,10);
+SELECT latitude(ll_to_earth(-90,90))::numeric(20,10);
+SELECT latitude(ll_to_earth(0,180))::numeric(20,10);
+SELECT latitude(ll_to_earth(45,180))::numeric(20,10);
+SELECT latitude(ll_to_earth(90,180))::numeric(20,10);
+SELECT latitude(ll_to_earth(-45,180))::numeric(20,10);
+SELECT latitude(ll_to_earth(-90,180))::numeric(20,10);
+SELECT latitude(ll_to_earth(0,-90))::numeric(20,10);
+SELECT latitude(ll_to_earth(45,-90))::numeric(20,10);
+SELECT latitude(ll_to_earth(90,-90))::numeric(20,10);
+SELECT latitude(ll_to_earth(-45,-90))::numeric(20,10);
+SELECT latitude(ll_to_earth(-90,-90))::numeric(20,10);
+
+--
+-- Test getting the longitude of a location.
+--
+
+SELECT longitude(ll_to_earth(0,0))::numeric(20,10);
+SELECT longitude(ll_to_earth(45,0))::numeric(20,10);
+SELECT longitude(ll_to_earth(90,0))::numeric(20,10);
+SELECT longitude(ll_to_earth(-45,0))::numeric(20,10);
+SELECT longitude(ll_to_earth(-90,0))::numeric(20,10);
+SELECT longitude(ll_to_earth(0,90))::numeric(20,10);
+SELECT longitude(ll_to_earth(45,90))::numeric(20,10);
+SELECT longitude(ll_to_earth(90,90))::numeric(20,10);
+SELECT longitude(ll_to_earth(-45,90))::numeric(20,10);
+SELECT longitude(ll_to_earth(-90,90))::numeric(20,10);
+SELECT longitude(ll_to_earth(0,180))::numeric(20,10);
+SELECT longitude(ll_to_earth(45,180))::numeric(20,10);
+SELECT longitude(ll_to_earth(90,180))::numeric(20,10);
+SELECT longitude(ll_to_earth(-45,180))::numeric(20,10);
+SELECT longitude(ll_to_earth(-90,180))::numeric(20,10);
+SELECT longitude(ll_to_earth(0,-90))::numeric(20,10);
+SELECT longitude(ll_to_earth(45,-90))::numeric(20,10);
+SELECT longitude(ll_to_earth(90,-90))::numeric(20,10);
+SELECT longitude(ll_to_earth(-45,-90))::numeric(20,10);
+SELECT longitude(ll_to_earth(-90,-90))::numeric(20,10);
+
+--
+-- For the distance tests the following is some real life data.
+--
+-- Chicago has a latitude of 41.8 and a longitude of 87.6.
+-- Albuquerque has a latitude of 35.1 and a longitude of 106.7.
+-- (Note that latitude and longitude are specified differently
+-- in the cube based functions than for the point based functions.)
+--
+
+--
+-- Test getting the distance between two points using earth_distance.
+--
+
+SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,0))::numeric(20,5);
+SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,180))::numeric(20,5);
+SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(90,0))::numeric(20,5);
+SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,90))::numeric(20,5);
+SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(0,1))::numeric(20,5);
+SELECT earth_distance(ll_to_earth(0,0),ll_to_earth(1,0))::numeric(20,5);
+SELECT earth_distance(ll_to_earth(30,0),ll_to_earth(30,1))::numeric(20,5);
+SELECT earth_distance(ll_to_earth(30,0),ll_to_earth(31,0))::numeric(20,5);
+SELECT earth_distance(ll_to_earth(60,0),ll_to_earth(60,1))::numeric(20,5);
+SELECT earth_distance(ll_to_earth(60,0),ll_to_earth(61,0))::numeric(20,5);
+SELECT earth_distance(ll_to_earth(41.8,87.6),ll_to_earth(35.1,106.7))::numeric(20,5);
+SELECT (earth_distance(ll_to_earth(41.8,87.6),ll_to_earth(35.1,106.7))*
+ 100./2.54/12./5280.)::numeric(20,5);
+
+--
+-- Test getting the distance between two points using geo_distance.
+--
+
+SELECT geo_distance('(0,0)'::point,'(0,0)'::point)::numeric(20,5);
+SELECT geo_distance('(0,0)'::point,'(180,0)'::point)::numeric(20,5);
+SELECT geo_distance('(0,0)'::point,'(0,90)'::point)::numeric(20,5);
+SELECT geo_distance('(0,0)'::point,'(90,0)'::point)::numeric(20,5);
+SELECT geo_distance('(0,0)'::point,'(1,0)'::point)::numeric(20,5);
+SELECT geo_distance('(0,0)'::point,'(0,1)'::point)::numeric(20,5);
+SELECT geo_distance('(0,30)'::point,'(1,30)'::point)::numeric(20,5);
+SELECT geo_distance('(0,30)'::point,'(0,31)'::point)::numeric(20,5);
+SELECT geo_distance('(0,60)'::point,'(1,60)'::point)::numeric(20,5);
+SELECT geo_distance('(0,60)'::point,'(0,61)'::point)::numeric(20,5);
+SELECT geo_distance('(87.6,41.8)'::point,'(106.7,35.1)'::point)::numeric(20,5);
+SELECT (geo_distance('(87.6,41.8)'::point,'(106.7,35.1)'::point)*5280.*12.*2.54/100.)::numeric(20,5);
+
+--
+-- Test getting the distance between two points using the <@> operator.
+--
+
+SELECT ('(0,0)'::point <@> '(0,0)'::point)::numeric(20,5);
+SELECT ('(0,0)'::point <@> '(180,0)'::point)::numeric(20,5);
+SELECT ('(0,0)'::point <@> '(0,90)'::point)::numeric(20,5);
+SELECT ('(0,0)'::point <@> '(90,0)'::point)::numeric(20,5);
+SELECT ('(0,0)'::point <@> '(1,0)'::point)::numeric(20,5);
+SELECT ('(0,0)'::point <@> '(0,1)'::point)::numeric(20,5);
+SELECT ('(0,30)'::point <@> '(1,30)'::point)::numeric(20,5);
+SELECT ('(0,30)'::point <@> '(0,31)'::point)::numeric(20,5);
+SELECT ('(0,60)'::point <@> '(1,60)'::point)::numeric(20,5);
+SELECT ('(0,60)'::point <@> '(0,61)'::point)::numeric(20,5);
+SELECT ('(87.6,41.8)'::point <@> '(106.7,35.1)'::point)::numeric(20,5);
+SELECT (('(87.6,41.8)'::point <@> '(106.7,35.1)'::point)*5280.*12.*2.54/100.)::numeric(20,5);
+
+--
+-- Test getting a bounding box around points.
+--
+
+SELECT cube_ll_coord(earth_box(ll_to_earth(0,0),112000),1)::numeric(20,5),
+ cube_ll_coord(earth_box(ll_to_earth(0,0),112000),2)::numeric(20,5),
+ cube_ll_coord(earth_box(ll_to_earth(0,0),112000),3)::numeric(20,5),
+ cube_ur_coord(earth_box(ll_to_earth(0,0),112000),1)::numeric(20,5),
+ cube_ur_coord(earth_box(ll_to_earth(0,0),112000),2)::numeric(20,5),
+ cube_ur_coord(earth_box(ll_to_earth(0,0),112000),3)::numeric(20,5);
+SELECT cube_ll_coord(earth_box(ll_to_earth(0,0),pi()*earth()),1)::numeric(20,5),
+ cube_ll_coord(earth_box(ll_to_earth(0,0),pi()*earth()),2)::numeric(20,5),
+ cube_ll_coord(earth_box(ll_to_earth(0,0),pi()*earth()),3)::numeric(20,5),
+ cube_ur_coord(earth_box(ll_to_earth(0,0),pi()*earth()),1)::numeric(20,5),
+ cube_ur_coord(earth_box(ll_to_earth(0,0),pi()*earth()),2)::numeric(20,5),
+ cube_ur_coord(earth_box(ll_to_earth(0,0),pi()*earth()),3)::numeric(20,5);
+SELECT cube_ll_coord(earth_box(ll_to_earth(0,0),10*earth()),1)::numeric(20,5),
+ cube_ll_coord(earth_box(ll_to_earth(0,0),10*earth()),2)::numeric(20,5),
+ cube_ll_coord(earth_box(ll_to_earth(0,0),10*earth()),3)::numeric(20,5),
+ cube_ur_coord(earth_box(ll_to_earth(0,0),10*earth()),1)::numeric(20,5),
+ cube_ur_coord(earth_box(ll_to_earth(0,0),10*earth()),2)::numeric(20,5),
+ cube_ur_coord(earth_box(ll_to_earth(0,0),10*earth()),3)::numeric(20,5);
+
+--
+-- Test for points that should be in bounding boxes.
+--
+
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0,1))*1.00001) @>
+ ll_to_earth(0,1);
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.1))*1.00001) @>
+ ll_to_earth(0,0.1);
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.01))*1.00001) @>
+ ll_to_earth(0,0.01);
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.001))*1.00001) @>
+ ll_to_earth(0,0.001);
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.0001))*1.00001) @>
+ ll_to_earth(0,0.0001);
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0.0001,0.0001))*1.00001) @>
+ ll_to_earth(0.0001,0.0001);
+SELECT earth_box(ll_to_earth(45,45),
+ earth_distance(ll_to_earth(45,45),ll_to_earth(45.0001,45.0001))*1.00001) @>
+ ll_to_earth(45.0001,45.0001);
+SELECT earth_box(ll_to_earth(90,180),
+ earth_distance(ll_to_earth(90,180),ll_to_earth(90.0001,180.0001))*1.00001) @>
+ ll_to_earth(90.0001,180.0001);
+
+--
+-- Test for points that shouldn't be in bounding boxes. Note that we need
+-- to make points way outside, since some points close may be in the box
+-- but further away than the distance we are testing.
+--
+
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0,1))*.57735) @>
+ ll_to_earth(0,1);
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.1))*.57735) @>
+ ll_to_earth(0,0.1);
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.01))*.57735) @>
+ ll_to_earth(0,0.01);
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.001))*.57735) @>
+ ll_to_earth(0,0.001);
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.0001))*.57735) @>
+ ll_to_earth(0,0.0001);
+SELECT earth_box(ll_to_earth(0,0),
+ earth_distance(ll_to_earth(0,0),ll_to_earth(0.0001,0.0001))*.57735) @>
+ ll_to_earth(0.0001,0.0001);
+SELECT earth_box(ll_to_earth(45,45),
+ earth_distance(ll_to_earth(45,45),ll_to_earth(45.0001,45.0001))*.57735) @>
+ ll_to_earth(45.0001,45.0001);
+SELECT earth_box(ll_to_earth(90,180),
+ earth_distance(ll_to_earth(90,180),ll_to_earth(90.0001,180.0001))*.57735) @>
+ ll_to_earth(90.0001,180.0001);
+
+--
+-- Test the recommended constraints.
+--
+
+SELECT cube_is_point(ll_to_earth(0,0));
+SELECT cube_dim(ll_to_earth(0,0)) <= 3;
+SELECT abs(cube_distance(ll_to_earth(0,0), '(0)'::cube) / earth() - 1) <
+ '10e-12'::float8;
+SELECT cube_is_point(ll_to_earth(30,60));
+SELECT cube_dim(ll_to_earth(30,60)) <= 3;
+SELECT abs(cube_distance(ll_to_earth(30,60), '(0)'::cube) / earth() - 1) <
+ '10e-12'::float8;
+SELECT cube_is_point(ll_to_earth(60,90));
+SELECT cube_dim(ll_to_earth(60,90)) <= 3;
+SELECT abs(cube_distance(ll_to_earth(60,90), '(0)'::cube) / earth() - 1) <
+ '10e-12'::float8;
+SELECT cube_is_point(ll_to_earth(-30,-90));
+SELECT cube_dim(ll_to_earth(-30,-90)) <= 3;
+SELECT abs(cube_distance(ll_to_earth(-30,-90), '(0)'::cube) / earth() - 1) <
+ '10e-12'::float8;
+
+--
+-- Now we are going to test extension create/drop scenarios.
+--
+
+-- list what's installed
+\dT
+
+drop extension cube; -- fail, earthdistance requires it
+
+drop extension earthdistance;
+
+drop type cube; -- fail, extension cube requires it
+
+-- list what's installed
+\dT
+
+create table foo (f1 cube, f2 int);
+
+drop extension cube; -- fail, foo.f1 requires it
+
+drop table foo;
+
+drop extension cube;
+
+-- list what's installed
+\dT
+\df
+\do
+
+create schema c;
+
+create extension cube with schema c;
+
+-- list what's installed
+\dT public.*
+\df public.*
+\do public.*
+\dT c.*
+
+create table foo (f1 c.cube, f2 int);
+
+drop extension cube; -- fail, foo.f1 requires it
+
+drop schema c; -- fail, cube requires it
+
+drop extension cube cascade;
+
+\d foo
+
+-- list what's installed
+\dT public.*
+\df public.*
+\do public.*
+\dT c.*
+\df c.*
+\do c.*
+
+drop schema c;