pgLatLon is a spatial database extension for the PostgreSQL object-relational jbe@529: database management system providing geographic data types and spatial indexing jbe@529: for the WGS-84 spheroid.
jbe@529: jbe@529:While many other spatial databases still use imprecise bounding boxes for many jbe@529: operations, pgLatLon supports more precise geometric calculations for all jbe@529: implemented operators. Efficient indexing of geometric objects is provided jbe@529: using fractal indicies. Optimizations on bit level (including logarithmic jbe@529: compression) allow for a highly memory-efficient non-overlapping index suitable jbe@529: for huge datasets.
jbe@529: jbe@529:Unlike competing spatial extensions for PostgreSQL, pgLatLon is available under jbe@529: the permissive MIT/X11 license to avoid problems with viral licenses like the jbe@529: GPLv2/v3.
jbe@529: jbe@529:Prerequisites:
jbe@529: jbe@529:pg_config binary is in your path (shipped with PostgreSQL).make or gmake).Then simply type:
jbe@529: jbe@529:make install
jbe@529: It is also possible to compile and install the extension without GNU Make as jbe@529: follows:
jbe@529: jbe@529:cc -Wall -O2 -fPIC -shared -I `pg_config --includedir-server` -o latlon-v0001.so latlon-v0001.c
jbe@529: cp latlon-v0001.so `pg_config --pkglibdir`
jbe@529: cp latlon.control `pg_config --sharedir`/extension/
jbe@529: cp latlon--0.1.sql `pg_config --sharedir`/extension/
jbe@529: After installation, you can create a database and load the extension as jbe@529: follows:
jbe@529: jbe@529:% createdb test_database
jbe@529: % psql test_database
jbe@529: psql (9.5.4)
jbe@529: Type "help" for help.
jbe@529:
jbe@529: test_database=# CREATE EXTENSION latlon;
jbe@529: pgLatLon provides four geographic types: epoint, ebox, ecircle, and
jbe@529: ecluster.
epointA point on the earth spheroid (WGS-84).
jbe@529: jbe@529:The text input format is '[N|S]<float> [E|W]<float>', where each float is in
jbe@529: degrees. Note the required white space between the latitude and longitude
jbe@529: components. Each floating point number may have a sign, in which case N/S
jbe@529: or E/W are switched respectively (e.g. E-5 is the same as W5).
An epoint may also be created from two floating point numbers by calling
jbe@529: epoint(latitude, longitude), where positive latitudes are used for the
jbe@529: northern hemisphere, negative latitudes are used for the southern hemisphere,
jbe@529: positive longitudes indicate positions east of the prime meridian, and negative
jbe@529: longitudes indicate positions west of the prime meridian.
Latitudes exceeding -90 or +90 degrees are truncated to -90 or +90 jbe@529: respectively, in which case a warning will be issued. Longitudes exceeding -180 jbe@529: or +180 degrees will be converted to values between -180 and +180 (both jbe@529: inclusive) by adding or substracting a multiple of 360 degrees, in which case a jbe@529: notice will be issued.
jbe@529: jbe@529:If the latitude is -90 or +90 (south pole or north pole), a longitude value is jbe@529: still stored in the datum, and if a point is on the prime meridian or the jbe@529: 180th meridian, the east/west bit is also stored in the datum. In case of the jbe@529: prime meridian, this is done by storing a floating point value of -0 for jbe@529: 0 degrees west and a value of +0 for 0 degrees east. In case of the jbe@529: 180th meridian, this is done by storing -180 or +180 respectively. The equality jbe@529: operator, however, returns true when the same points on earth are described, jbe@529: i.e. the longitude is ignored for the poles, and 180 degrees west is considered jbe@529: to be equal to 180 degrees east.
jbe@529: jbe@529:eboxAn area on earth demarcated by a southern and northern latitude, and a western jbe@529: and eastern longitude (all given in WGS-84).
jbe@529: jbe@529:The text input format is
jbe@529: '{N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float>', where each float is in
jbe@529: degrees. The ordering of the four white-space separated blocks is not
jbe@529: significant. To include the 180th meridian, one longitude boundary must be
jbe@529: equal to or exceed W180 or E180, e.g. 'N10 N20 E170 E190'.
A special value is the empty area, denoted by the text represenation 'empty'.
jbe@529: Such an ebox does not contain any point.
An ebox may also be created from four floating point numbers by calling
jbe@529: ebox(min_latitude, max_latitude, min_longitude, max_longitude), where
jbe@529: positive values are used for north and east, and negative values are used for
jbe@529: south and west. If min_latitude is strictly greater than max_latitude, an
jbe@529: empty ebox is created. If min_longitude is greater than max_longitude and
jbe@529: if both longitudes are between -180 and +180 degrees, then the area oriented in
jbe@529: such way that the 180th meridian is included.
If the longitude span is less than 120 degrees, an ebox may be alternatively
jbe@529: created from two epoints in the following way: ebox(epoint(lat1, lon1),
jbe@529: epoint(lat2, lon2)). In this case lat1 and lat2 as well as lon1 and
jbe@529: lon2 can be swapped without any impact.
ecircleAn area containing all points not farther away from a given center point jbe@529: (WGS-84) than a given radius.
jbe@529: jbe@529:The text input format is '{N|S}<float> {E|W}<float> <float>', where the first
jbe@529: two floats denote the center point in degrees and the third float denotes the
jbe@529: radius in meters. A radius equal to minus infinity denotes an empty circle
jbe@529: which contains no point at all (despite having a center), while a radius equal
jbe@529: to zero denotes a circle that includes a single point.
An ecircle may also be created by calling ecircle(epoint(...), radius) or
jbe@529: from three floating point numbers by calling ecircle(latitude, longitude,
jbe@529: radius).
eclusterA collection of points, paths, polygons, and outlines on the WGS-84 spheroid. jbe@529: Each path, polygon, or outline must cover a longitude range of less than jbe@529: 180 degrees to avoid ambiguities.
jbe@529: jbe@529:The text input format is a white-space separated list of the following items:
jbe@529: jbe@529:point ({N|S}<float> {E|W}<float>)path ({N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> ...)outline ({N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> ...)polygon ({N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> ...)Paths are open by default (i.e. there is no connection from the last point in jbe@529: the list to the first point in the list). Outlines and polygons, in contrast, jbe@529: are automatically closed (i.e. there is a line segment from the last point in jbe@529: the list to the first point in the list) which means the first point should not jbe@529: be repeated as last point in the list. Polygons are filled, outlines are not.
jbe@529: jbe@529:Two kinds of indices are supported: B-tree and GiST indices.
jbe@529: jbe@529:A B-tree index can be used for simple equality searches and is supported by the
jbe@529: epoint, ebox, and ecircle data types. B-tree indices can not be used for
jbe@529: geographic searches.
For geographic searches, GiST indices must be used. The epoint, ecircle,
jbe@529: and ecluster data types support GiST indexing. A GiST index for geographic
jbe@529: searches can be created as follows:
CREATE TABLE tbl (
jbe@529: id serial4 PRIMARY KEY,
jbe@529: loc epoint NOT NULL );
jbe@529:
jbe@529: CREATE INDEX name_of_index ON tbl USING gist (loc);
jbe@529: GiST indicies also support nearest neighbor searches when using the distance
jbe@529: operator (<->) in the ORDER BY clause.
Note that further types can be indexed by using an index on an expression with
jbe@529: a conversion function. One conversion function provided by pgLatLon is the
jbe@529: GeoJSON_to_ecluster(float8, float8, text) function:
CREATE TABLE tbl (
jbe@529: id serial4 PRIMARY KEY,
jbe@529: loc jsonb NOT NULL );
jbe@529:
jbe@529: CREATE INDEX name_of_index ON tbl USING gist((GeoJSON_to_ecluster("loc")));
jbe@529: When using the conversion function in an expression, the index will be used jbe@529: automatically:
jbe@529: jbe@529:SELECT * FROM tbl WHERE GeoJSON_to_ecluster("loc") && 'N50 E10 10000'::ecircle;
jbe@529: =Tests if two geographic objects are equal.
jbe@529: jbe@529:The longitude is ignored for the poles, and 180 degrees west is considered to jbe@529: be equal to 180 degrees east.
jbe@529: jbe@529:For boxes and circles, two empty objects are considered equal. (Note that a jbe@529: circle is not empty if the radius is zero but only if it is negative infinity, jbe@529: i.e. smaller than zero.) Two circles with a positive infinite radius are also jbe@529: considered equal.
jbe@529: jbe@529:Implemented for:
jbe@529: jbe@529:epoint = epointebox = eboxecircle = ecircleThe negation is the inequality operator (<> or !=).
<<<, <<<=, >>>=, >>>These operators create an arbitrary (but well-defined) linear ordering of jbe@529: geographic objects, which is used internally for B-tree indexing and merge jbe@529: joins. These operators will usually not be used by an application programmer.
jbe@529: jbe@529:&&Tests if two geographic objects have at least one point in common. Currently jbe@529: implemented for:
jbe@529: jbe@529:epoint && eboxepoint && ecircleepoint && eclusterebox && eboxecircle && ecircleecircle && eclusterThe && operator is commutative, i.e. a && b is the same as b && a. Each
jbe@529: commutation is supported as well.
<->Calculates the shortest distance between two geographic objects in meters (zero jbe@529: if the objects are overlapping). Currently implemented for:
jbe@529: jbe@529:epoint <-> epointepoint <-> ecircleepoint <-> eclusterecircle <-> ecircleecircle <-> eclusterThe <-> operator is commutative, i.e. a <-> b is the same as b <-> a.
jbe@529: Each commutation is supported as well.
For short distances, the result is very accurate (i.e. respects the dimensions jbe@529: of the WGS-84 spheroid). For longer distances in the order of magnitude of jbe@529: earth's radius or greater, the value is only approximate (but the error is jbe@529: still less than 0.2% as long as no polygons with very long edges are involved).
jbe@529: jbe@529:The functions distance(epoint, epoint) and distance(ecluster, epoint) can
jbe@529: be used as an alias for this operator.
Note: In case of radial searches with a fixed radius, this operator should
jbe@529: not be used. Instead, an ecircle should be created and used in combination
jbe@529: with the overlap operator (&&). Alternatively, the functions
jbe@529: distance_within(epoint, epoint, float8) or distance_within(ecluster, epoint,
jbe@529: float8) can be used for fixed-radius searches.
center(circle)Returns the center of an ecircle as an epoint.
distance(epoint, epoint)Calculates the distance between two epoint datums in meters. This function is
jbe@529: an alias for the distance operator <->.
Note: In case of radial searches with a fixed radius, this function should not be
jbe@529: used. Use distance_within(epoint, epoint, float8) instead.
distance(ecluster, epoint)Calculates the distance from an ecluster to an epoint in meters. This
jbe@529: function is an alias for the distance operator <->.
Note: In case of radial searches with a fixed radius, this function should not be
jbe@529: used. Use distance_within(epoint, epoint, float8) instead.
distance_within(variable epoint, fixed epoint, radius float8)Checks if the distance between two epoint datums is not greater than a given
jbe@529: value (search radius).
Note: In case of radial searches with a fixed radius, the first argument must jbe@529: be used for the table column, while the second argument must be used for the jbe@529: search center. Otherwise an existing index cannot be used.
jbe@529: jbe@529:distance_within(variable ecluster, fixed epoint, radius float8)Checks if the distance from an ecluster to an epoint is not greater than a
jbe@529: given value (search radius).
ebox(latmin float8, latmax float8, lonmin float8, lonmax float8)Creates a new ebox with the given boundaries.
jbe@529: See "1. Types", subsection ebox for details.
ebox(epoint, epoint)Creates a new ebox. This function may only be used if the longitude
jbe@529: difference is less than or equal to 120 degrees.
jbe@529: See "1. Types", subsection ebox for details.
ecircle(epoint, float8)Creates an ecircle with the given center point and radius.
ecircle(latitude float8, longitude float8, radius float8)Creates an ecircle with the given center point and radius.
ecluster_concat(ecluster, ecluster)Combines two clusters to form a new ecluster by uniting all entries of both
jbe@529: clusters. Note that two overlapping areas of polygons annihilate each other
jbe@529: (which may be used to create polygons with holes).
ecluster_concat(ecluster[])Creates a new ecluster that unites all entries of all clusters in the passed
jbe@529: array. Note that two overlapping areas of polygons annihilate each other (which
jbe@529: may be used to create polygons with holes).
ecluster_create_multipoint(epoint[])Creates a new ecluster which contains multiple points.
ecluster_create_outline(epoint[])Creates a new ecluster that is an outline given by the passed points.
ecluster_create_path(epoint[])Creates a new ecluster that is a path given by the passed points.
ecluster_create_polygon(epoint[])Creates a new ecluster that is a polygon given by the passed points.
ecluster_extract_outlines(ecluster)Set-returning function that returns the outlines of an ecluster as epoint[]
jbe@529: rows.
ecluster_extract_paths(ecluster)Set-returning function that returns the paths of an ecluster as epoint[]
jbe@529: rows.
ecluster_extract_points(ecluster)Set-returning function that returns the points of an ecluster as epoint
jbe@529: rows.
ecluster_extract_polygons(ecluster)Set-returning function that returns the polygons of an ecluster as epoint[]
jbe@529: rows.
empty_ebox()Returns the empty ebox.
jbe@529: See "1. Types", subsection ebox for details.
epoint(latitude float8, longitude float8)Returns an epoint with the given latitude and longitude.
epoint_latlon(latitude float8, longitude float8)Alias for epoint(float8, float8).
epoint_lonlat(longitude float8, latitude float8)Same as epoint(float8, float8) but with arguments reversed.
GeoJSON_to_epoint(jsonb, text)Maps a GeoJSON object of type "Point" or "Feature" (which contains a
jbe@529: "Point") to an epoint datum. For any other JSON objects, NULL is returned.
The second parameter (which defaults to epoint_lonlat) may be set to a name
jbe@529: of a conversion function that transforms two coordinates (two float8
jbe@529: parameters) to an epoint.
GeoJSON_to_ecluster(jsonb, text)Maps a (valid) GeoJSON object to an ecluster. Note that this function
jbe@529: does not check whether the JSONB object is a valid GeoJSON object.
The second parameter (which defaults to epoint_lonlat) may be set to a name
jbe@529: of a conversion function that transforms two coordinates (two float8
jbe@529: parameters) to an epoint.
max_latitude(ebox)Returns the northern boundary of a given ebox in degrees between -90 and +90.
max_longitude(ebox)Returns the eastern boundary of a given ebox in degrees between -180 and +180
jbe@529: (both inclusive).
min_latitude(ebox)Returns the southern boundary of a given ebox in degrees between -90 and +90.
min_longitude(ebox)Returns the western boundary of a given ebox in degrees between -180 and +180
jbe@529: (both inclusive).
latitude(epoint)Returns the latitude value of an epoint in degrees between -90 and +90.
longitude(epoint)Returns the longitude value of an epoint in degrees between -180 and +180
jbe@529: (both inclusive).
radius(ecircle)Returns the radius of an ecircle in meters.