diff options
Diffstat (limited to '')
| -rw-r--r-- | contrib/earthdistance/.gitignore | 4 | ||||
| -rw-r--r-- | contrib/earthdistance/Makefile | 23 | ||||
| -rw-r--r-- | contrib/earthdistance/earthdistance--1.0--1.1.sql | 14 | ||||
| -rw-r--r-- | contrib/earthdistance/earthdistance--1.1.sql | 98 | ||||
| -rw-r--r-- | contrib/earthdistance/earthdistance.c | 105 | ||||
| -rw-r--r-- | contrib/earthdistance/earthdistance.control | 6 | ||||
| -rw-r--r-- | contrib/earthdistance/expected/earthdistance.out | 1098 | ||||
| -rw-r--r-- | contrib/earthdistance/meson.build | 35 | ||||
| -rw-r--r-- | contrib/earthdistance/sql/earthdistance.sql | 359 |
9 files changed, 1742 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..ded048c --- /dev/null +++ b/contrib/earthdistance/earthdistance.c @@ -0,0 +1,105 @@ +/* contrib/earthdistance/earthdistance.c */ + +#include "postgres.h" + +#include <math.h> + +#include "utils/geo_decls.h" /* for Point */ + +/* X/Open (XSI) requires <math.h> to provide M_PI, but core POSIX does not */ +#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/meson.build b/contrib/earthdistance/meson.build new file mode 100644 index 0000000..05794b3 --- /dev/null +++ b/contrib/earthdistance/meson.build @@ -0,0 +1,35 @@ +# Copyright (c) 2022-2023, PostgreSQL Global Development Group + +earthdistance_sources = files( + 'earthdistance.c', +) + +if host_system == 'windows' + earthdistance_sources += rc_lib_gen.process(win32ver_rc, extra_args: [ + '--NAME', 'earthdistance', + '--FILEDESC', 'earthdistance - calculate distances on the surface of the Earth',]) +endif + +earthdistance = shared_module('earthdistance', + earthdistance_sources, + kwargs: contrib_mod_args, +) +contrib_targets += earthdistance + +install_data( + 'earthdistance.control', + 'earthdistance--1.0--1.1.sql', + 'earthdistance--1.1.sql', + kwargs: contrib_data_args, +) + +tests += { + 'name': 'earthdistance', + 'sd': meson.current_source_dir(), + 'bd': meson.current_build_dir(), + 'regress': { + 'sql': [ + 'earthdistance', + ], + }, +} 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; |