jbe@51: pgLatLon v0.11 documentation jbe@51:

pgLatLon v0.11 documentation

jbe@0: jbe@0:

pgLatLon is a spatial database extension for the PostgreSQL object-relational jbe@0: database management system providing geographic data types and spatial indexing jbe@0: for the WGS-84 spheroid.

jbe@0: jbe@32:

While many other spatial databases still use imprecise bounding boxes for jbe@32: many operations, pgLatLon aims to support more precise calculations for all jbe@32: implemented geographic operators. Efficient indexing of geographic objects jbe@32: is provided using space-filling fractal curves. Optimizations on bit level jbe@32: (including logarithmic compression) allow for a highly memory-efficient jbe@32: non-overlapping index suitable for huge datasets.

jbe@0: jbe@11:

pgLatLon is a lightweight solution as it only depends on PostgreSQL itself (and jbe@11: a C compiler for building).

jbe@11: jbe@0:

Unlike competing spatial extensions for PostgreSQL, pgLatLon is available under jbe@0: the permissive MIT/X11 license to avoid problems with viral licenses like the jbe@0: GPLv2/v3.

jbe@0: jbe@0:

Installation

jbe@0: jbe@0:

Automatic installation

jbe@0: jbe@0:

Prerequisites:

jbe@0: jbe@0: jbe@0: jbe@0:

Then simply type:

jbe@0: jbe@0:
make install
jbe@0: 
jbe@0: jbe@0:

Manual installation

jbe@0: jbe@0:

It is also possible to compile and install the extension without GNU Make as jbe@0: follows:

jbe@0: jbe@46:
cc -Wall -O2 -fPIC -shared -I `pg_config --includedir-server` -o latlon-v0009.so latlon-v0009.c
jbe@46: cp latlon-v0009.so `pg_config --pkglibdir`
jbe@0: cp latlon.control `pg_config --sharedir`/extension/
jbe@13: cp latlon--*.sql `pg_config --sharedir`/extension/
jbe@0: 
jbe@0: jbe@0:

Loading the extension

jbe@0: jbe@0:

After installation, you can create a database and load the extension as jbe@0: follows:

jbe@0: jbe@0:
% createdb test_database
jbe@0: % psql test_database
jbe@0: psql (9.5.4)
jbe@0: Type "help" for help.
jbe@0: 
jbe@0: test_database=# CREATE EXTENSION latlon;
jbe@0: 
jbe@0: jbe@16:

Updating

jbe@16: jbe@16:

Before updating your database cluster to a new version of pgLatLon, you may jbe@16: want to uninstall the old by calling "make uninstall" in the unpacked source jbe@16: code directory of your old pgLatLon version. You may also manually delete the jbe@16: latlon-v????.so files from your PostgreSQL library directory and the jbe@16: latlon.control and latlon--*.sql files from your PostgreSQL extension jbe@16: directory.

jbe@16: jbe@16:

The new version can be installed as described above. For altering an existing jbe@16: database to use the installed new version (mandatory if you removed the old jbe@16: version), execute the following SQL command in the respective databases:

jbe@16: jbe@16:
ALTER EXTENSION latlon UPDATE;
jbe@16: 
jbe@16: jbe@16:

If the update contains modifications to operator classes, it may be necessary jbe@16: to drop all indices on geographic data types first (you will get an error jbe@16: message in this case). These indices can be re-created after the update.

jbe@16: jbe@16:

Note that taking several update steps at once (e.g. updating from version 0.2 jbe@16: directly to version 0.4) requires the intermediate versions to be installed jbe@16: (i.e. in this example version 0.3 would need to be installed). Whenever you jbe@16: install or uninstall an intermediate or old version, make sure to afterwards jbe@16: re-install the latest pgLatLon version to ensure that the latlon.control file jbe@16: is available and points to the latest version.

jbe@16: jbe@16:

If the update contains modifications to the internal data representation jbe@16: format, an update path might not be available. In this case, create a dump of jbe@16: your database, delete your database, and restore it from your dump.

jbe@16: jbe@16:

Be sure to always keep backups of all your data before attempting to update.

jbe@16: jbe@0:

Reference

jbe@0: jbe@0:

1. Types

jbe@0: jbe@0:

pgLatLon provides four geographic types: epoint, ebox, ecircle, and jbe@0: ecluster.

jbe@0: jbe@0:

epoint

jbe@0: jbe@33:

A point on the Earth spheroid (WGS-84).

jbe@0: jbe@0:

The text input format is '[N|S]<float> [E|W]<float>', where each float is in jbe@0: degrees. Note the required white space between the latitude and longitude jbe@0: components. Each floating point number may have a sign, in which case N/S jbe@0: or E/W are switched respectively (e.g. E-5 is the same as W5).

jbe@0: jbe@0:

An epoint may also be created from two floating point numbers by calling jbe@0: epoint(latitude, longitude), where positive latitudes are used for the jbe@0: northern hemisphere, negative latitudes are used for the southern hemisphere, jbe@0: positive longitudes indicate positions east of the prime meridian, and negative jbe@0: longitudes indicate positions west of the prime meridian.

jbe@0: jbe@0:

Latitudes exceeding -90 or +90 degrees are truncated to -90 or +90 jbe@0: respectively, in which case a warning will be issued. Longitudes exceeding -180 jbe@0: or +180 degrees will be converted to values between -180 and +180 (both jbe@0: inclusive) by adding or substracting a multiple of 360 degrees, in which case a jbe@0: notice will be issued.

jbe@0: jbe@0:

If the latitude is -90 or +90 (south pole or north pole), a longitude value is jbe@0: still stored in the datum, and if a point is on the prime meridian or the jbe@0: 180th meridian, the east/west bit is also stored in the datum. In case of the jbe@0: prime meridian, this is done by storing a floating point value of -0 for jbe@0: 0 degrees west and a value of +0 for 0 degrees east. In case of the jbe@0: 180th meridian, this is done by storing -180 or +180 respectively. The equality jbe@33: operator, however, returns true when the same points on Earth are described, jbe@0: i.e. the longitude is ignored for the poles, and 180 degrees west is considered jbe@0: to be equal to 180 degrees east.

jbe@0: jbe@0:

ebox

jbe@0: jbe@33:

An area on Earth demarcated by a southern and northern latitude, and a western jbe@0: and eastern longitude (all given in WGS-84).

jbe@0: jbe@0:

The text input format is jbe@0: '{N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float>', where each float is in jbe@0: degrees. The ordering of the four white-space separated blocks is not jbe@0: significant. To include the 180th meridian, one longitude boundary must be jbe@0: equal to or exceed W180 or E180, e.g. 'N10 N20 E170 E190'.

jbe@0: jbe@0:

A special value is the empty area, denoted by the text represenation 'empty'. jbe@0: Such an ebox does not contain any point.

jbe@0: jbe@0:

An ebox may also be created from four floating point numbers by calling jbe@0: ebox(min_latitude, max_latitude, min_longitude, max_longitude), where jbe@0: positive values are used for north and east, and negative values are used for jbe@0: south and west. If min_latitude is strictly greater than max_latitude, an jbe@0: empty ebox is created. If min_longitude is greater than max_longitude and jbe@0: if both longitudes are between -180 and +180 degrees, then the area oriented in jbe@0: such way that the 180th meridian is included.

jbe@0: jbe@0:

If the longitude span is less than 120 degrees, an ebox may be alternatively jbe@0: created from two epoints in the following way: ebox(epoint(lat1, lon1), jbe@0: epoint(lat2, lon2)). In this case lat1 and lat2 as well as lon1 and jbe@0: lon2 can be swapped without any impact.

jbe@0: jbe@0:

ecircle

jbe@0: jbe@0:

An area containing all points not farther away from a given center point jbe@0: (WGS-84) than a given radius.

jbe@0: jbe@0:

The text input format is '{N|S}<float> {E|W}<float> <float>', where the first jbe@0: two floats denote the center point in degrees and the third float denotes the jbe@0: radius in meters. A radius equal to minus infinity denotes an empty circle jbe@0: which contains no point at all (despite having a center), while a radius equal jbe@0: to zero denotes a circle that includes a single point.

jbe@0: jbe@0:

An ecircle may also be created by calling ecircle(epoint(...), radius) or jbe@0: from three floating point numbers by calling ecircle(latitude, longitude, jbe@0: radius).

jbe@0: jbe@0:

ecluster

jbe@0: jbe@0:

A collection of points, paths, polygons, and outlines on the WGS-84 spheroid. jbe@0: Each path, polygon, or outline must cover a longitude range of less than jbe@0: 180 degrees to avoid ambiguities.

jbe@0: jbe@0:

The text input format is a white-space separated list of the following items:

jbe@0: jbe@0: jbe@0: jbe@0:

Paths are open by default (i.e. there is no connection from the last point in jbe@0: the list to the first point in the list). Outlines and polygons, in contrast, jbe@0: are automatically closed (i.e. there is a line segment from the last point in jbe@0: the list to the first point in the list) which means the first point should not jbe@0: be repeated as last point in the list. Polygons are filled, outlines are not.

jbe@0: jbe@0:

2. Indices

jbe@0: jbe@0:

Two kinds of indices are supported: B-tree and GiST indices.

jbe@0: jbe@0:

B-tree indices

jbe@0: jbe@0:

A B-tree index can be used for simple equality searches and is supported by the jbe@0: epoint, ebox, and ecircle data types. B-tree indices can not be used for jbe@0: geographic searches.

jbe@0: jbe@0:

GiST indices

jbe@0: jbe@0:

For geographic searches, GiST indices must be used. The epoint, ecircle, jbe@0: and ecluster data types support GiST indexing. A GiST index for geographic jbe@0: searches can be created as follows:

jbe@0: jbe@0:
CREATE TABLE tbl (
jbe@0:         id              serial4         PRIMARY KEY,
jbe@0:         loc             epoint          NOT NULL );
jbe@0: 
jbe@0: CREATE INDEX name_of_index ON tbl USING gist (loc);
jbe@0: 
jbe@0: jbe@0:

GiST indices also support nearest neighbor searches when using the distance jbe@0: operator (<->) in the ORDER BY clause.

jbe@0: jbe@0:

Indices on other data types (e.g. GeoJSON)

jbe@0: jbe@0:

Note that further types can be indexed by using an index on an expression with jbe@0: a conversion function. One conversion function provided by pgLatLon is the jbe@49: GeoJSON_to_ecluster(jsonb, text) function:

jbe@0: jbe@0:
CREATE TABLE tbl (
jbe@0:         id              serial4         PRIMARY KEY,
jbe@0:         loc             jsonb           NOT NULL );
jbe@0: 
jbe@0: CREATE INDEX name_of_index ON tbl USING gist((GeoJSON_to_ecluster("loc")));
jbe@0: 
jbe@0: jbe@0:

When using the conversion function in an expression, the index will be used jbe@0: automatically:

jbe@0: jbe@0:
SELECT * FROM tbl WHERE GeoJSON_to_ecluster("loc") && 'N50 E10 10000'::ecircle;
jbe@0: 
jbe@0: jbe@0:

3. Operators

jbe@0: jbe@0:

Equality operator =

jbe@0: jbe@0:

Tests if two geographic objects are equal.

jbe@0: jbe@0:

The longitude is ignored for the poles, and 180 degrees west is considered to jbe@0: be equal to 180 degrees east.

jbe@0: jbe@0:

For boxes and circles, two empty objects are considered equal. (Note that a jbe@0: circle is not empty if the radius is zero but only if it is negative infinity, jbe@0: i.e. smaller than zero.) Two circles with a positive infinite radius are also jbe@0: considered equal.

jbe@0: jbe@0:

Implemented for:

jbe@0: jbe@0: jbe@0: jbe@0:

The negation is the inequality operator (<> or !=).

jbe@0: jbe@0:

Linear ordering operators <<<, <<<=, >>>=, >>>

jbe@0: jbe@0:

These operators create an arbitrary (but well-defined) linear ordering of jbe@0: geographic objects, which is used internally for B-tree indexing and merge jbe@0: joins. These operators will usually not be used by an application programmer.

jbe@0: jbe@0:

Overlap operator &&

jbe@0: jbe@0:

Tests if two geographic objects have at least one point in common. Currently jbe@0: implemented for:

jbe@0: jbe@0: jbe@0: jbe@20:

The && operator is commutative, i.e. "a && b" is the same as "b && a". jbe@20: Each commutation is supported as well.

jbe@0: jbe@11:

Lossy overlap operator &&+

jbe@11: jbe@11:

Tests if two geographic objects may have at least one point in common. Opposed jbe@11: to the && operator, the &&+ operator may return false positives and is jbe@11: currently implemented for:

jbe@11: jbe@11: jbe@11: jbe@20:

The &&+ operator is commutative, i.e. "a &&+ b" is the same as "b &&+ a". jbe@16: Each commutation is supported as well.

jbe@11: jbe@11:

Where two data types support both the && and the &&+ operator, the &&+ jbe@11: operator computes faster.

jbe@11: jbe@16:

Contains operator @>

jbe@16: jbe@16:

Tests if the object right of the operator is contained in the object left of jbe@16: the operator. Currently implemented for:

jbe@16: jbe@16: jbe@16: jbe@20:

The commutator of @> ("contains") is <@ ("is contained in"), i.e. jbe@20: "a @> b" is the same as "b <@ a".

jbe@20: jbe@20:

Whether the perimeter of an object is taken into account is undefined and may jbe@20: differ between the left and the right hand side of the operator. The current jbe@20: implementation returns true only if an object is contained completely within jbe@20: the other object, not touching its perimeter, paths, outlines, or any singular jbe@20: points.

jbe@16: jbe@0:

Distance operator <->

jbe@0: jbe@0:

Calculates the shortest distance between two geographic objects in meters (zero jbe@0: if the objects are overlapping). Currently implemented for:

jbe@0: jbe@0: jbe@0: jbe@20:

The <-> operator is commutative, i.e. "a <-> b" is the same as "b <-> a". jbe@0: Each commutation is supported as well.

jbe@0: jbe@0:

For short distances, the result is very accurate (i.e. respects the dimensions jbe@0: of the WGS-84 spheroid). For longer distances in the order of magnitude of jbe@33: Earth's radius or greater, the value is only approximate (but the error is jbe@0: still less than 0.2% as long as no polygons with very long edges are involved).

jbe@0: jbe@0:

The functions distance(epoint, epoint) and distance(ecluster, epoint) can jbe@0: be used as an alias for this operator.

jbe@0: jbe@0:

Note: In case of radial searches with a fixed radius, this operator should jbe@0: not be used. Instead, an ecircle should be created and used in combination jbe@0: with the overlap operator (&&). Alternatively, the functions jbe@0: distance_within(epoint, epoint, float8) or distance_within(ecluster, epoint, jbe@0: float8) can be used for fixed-radius searches.

jbe@0: jbe@0:

4. Functions

jbe@0: jbe@0:

center(circle)

jbe@0: jbe@0:

Returns the center of an ecircle as an epoint.

jbe@0: jbe@0:

distance(epoint, epoint)

jbe@0: jbe@0:

Calculates the distance between two epoint datums in meters. This function is jbe@0: an alias for the distance operator <->.

jbe@0: jbe@0:

Note: In case of radial searches with a fixed radius, this function should not be jbe@0: used. Use distance_within(epoint, epoint, float8) instead.

jbe@0: jbe@0:

distance(ecluster, epoint)

jbe@0: jbe@0:

Calculates the distance from an ecluster to an epoint in meters. This jbe@0: function is an alias for the distance operator <->.

jbe@0: jbe@0:

Note: In case of radial searches with a fixed radius, this function should not be jbe@0: used. Use distance_within(epoint, epoint, float8) instead.

jbe@0: jbe@0:

distance_within(variable epoint, fixed epoint, radius float8)

jbe@0: jbe@0:

Checks if the distance between two epoint datums is not greater than a given jbe@0: value (search radius).

jbe@0: jbe@0:

Note: In case of radial searches with a fixed radius, the first argument must jbe@0: be used for the table column, while the second argument must be used for the jbe@0: search center. Otherwise an existing index cannot be used.

jbe@0: jbe@0:

distance_within(variable ecluster, fixed epoint, radius float8)

jbe@0: jbe@0:

Checks if the distance from an ecluster to an epoint is not greater than a jbe@0: given value (search radius).

jbe@0: jbe@0:

ebox(latmin float8, latmax float8, lonmin float8, lonmax float8)

jbe@0: jbe@0:

Creates a new ebox with the given boundaries. jbe@0: See "1. Types", subsection ebox for details.

jbe@0: jbe@0:

ebox(epoint, epoint)

jbe@0: jbe@0:

Creates a new ebox. This function may only be used if the longitude jbe@0: difference is less than or equal to 120 degrees. jbe@0: See "1. Types", subsection ebox for details.

jbe@0: jbe@0:

ecircle(epoint, float8)

jbe@0: jbe@0:

Creates an ecircle with the given center point and radius.

jbe@0: jbe@0:

ecircle(latitude float8, longitude float8, radius float8)

jbe@0: jbe@0:

Creates an ecircle with the given center point and radius.

jbe@0: jbe@0:

ecluster_concat(ecluster, ecluster)

jbe@0: jbe@0:

Combines two clusters to form a new ecluster by uniting all entries of both jbe@0: clusters. Note that two overlapping areas of polygons annihilate each other jbe@0: (which may be used to create polygons with holes).

jbe@0: jbe@0:

ecluster_concat(ecluster[])

jbe@0: jbe@0:

Creates a new ecluster that unites all entries of all clusters in the passed jbe@0: array. Note that two overlapping areas of polygons annihilate each other (which jbe@0: may be used to create polygons with holes).

jbe@0: jbe@0:

ecluster_create_multipoint(epoint[])

jbe@0: jbe@0:

Creates a new ecluster which contains multiple points.

jbe@0: jbe@0:

ecluster_create_outline(epoint[])

jbe@0: jbe@0:

Creates a new ecluster that is an outline given by the passed points.

jbe@0: jbe@0:

ecluster_create_path(epoint[])

jbe@0: jbe@0:

Creates a new ecluster that is a path given by the passed points.

jbe@0: jbe@0:

ecluster_create_polygon(epoint[])

jbe@0: jbe@0:

Creates a new ecluster that is a polygon given by the passed points.

jbe@0: jbe@0:

ecluster_extract_outlines(ecluster)

jbe@0: jbe@0:

Set-returning function that returns the outlines of an ecluster as epoint[] jbe@0: rows.

jbe@0: jbe@0:

ecluster_extract_paths(ecluster)

jbe@0: jbe@0:

Set-returning function that returns the paths of an ecluster as epoint[] jbe@0: rows.

jbe@0: jbe@0:

ecluster_extract_points(ecluster)

jbe@0: jbe@0:

Set-returning function that returns the points of an ecluster as epoint jbe@0: rows.

jbe@0: jbe@0:

ecluster_extract_polygons(ecluster)

jbe@0: jbe@0:

Set-returning function that returns the polygons of an ecluster as epoint[] jbe@0: rows.

jbe@0: jbe@0:

empty_ebox()

jbe@0: jbe@0:

Returns the empty ebox. jbe@0: See "1. Types", subsection ebox for details.

jbe@0: jbe@0:

epoint(latitude float8, longitude float8)

jbe@0: jbe@0:

Returns an epoint with the given latitude and longitude.

jbe@0: jbe@0:

epoint_latlon(latitude float8, longitude float8)

jbe@0: jbe@0:

Alias for epoint(float8, float8).

jbe@0: jbe@0:

epoint_lonlat(longitude float8, latitude float8)

jbe@0: jbe@0:

Same as epoint(float8, float8) but with arguments reversed.

jbe@0: jbe@42:

fair_distance(ecluster, epoint, samples int4 = 10000)

jbe@42: jbe@42:

When working with user-generated content, users may be tempted to create jbe@42: intentionally oversized objects in order to optimize search results in an jbe@42: unfair manner. The fair_distance function aims to handle this by returning an jbe@42: adjusted distance (i.e. distance increased by a penalty) if a geographic object jbe@42: (the ecluster) consists of more than one point.

jbe@42: jbe@42:

The first argument to this function is an ecluster, the second argument is a jbe@42: search point (epoint), and the third argument is an interger related to the jbe@42: precision (higher precision will require more computation time).

jbe@42: jbe@42:

The penalty by which the returned distance is increased fulfills (at least) the jbe@42: following properties:

jbe@42: jbe@42: jbe@42: jbe@46:

The function uses numerical integration to compute the result. The third jbe@46: parameter (which defaults to 10000) can be used to adjust the number of samples jbe@46: taken. A higher sample count increases precision as well as execution time of jbe@46: the function. Because this function internally uses a spherical model of earth jbe@46: for certain steps of the calculation, the precision cannot be increased jbe@46: unboundedly.

jbe@46: jbe@46:

Despite the limitations explained above, it is ensured that the penalty is jbe@46: always positive, i.e. results returned by the fair_distance function are jbe@46: always equal to or greater than the results returned by the distance jbe@46: function regardless of stochastic effects. Furthermore, all results are jbe@46: deterministic and reproducible with the same version of pgLatLon.

jbe@42: jbe@0:

GeoJSON_to_epoint(jsonb, text)

jbe@0: jbe@0:

Maps a GeoJSON object of type "Point" or "Feature" (which contains a jbe@0: "Point") to an epoint datum. For any other JSON objects, NULL is returned.

jbe@0: jbe@0:

The second parameter (which defaults to epoint_lonlat) may be set to a name jbe@0: of a conversion function that transforms two coordinates (two float8 jbe@0: parameters) to an epoint.

jbe@0: jbe@0:

GeoJSON_to_ecluster(jsonb, text)

jbe@0: jbe@0:

Maps a (valid) GeoJSON object to an ecluster. Note that this function jbe@0: does not check whether the JSONB object is a valid GeoJSON object.

jbe@0: jbe@0:

The second parameter (which defaults to epoint_lonlat) may be set to a name jbe@0: of a conversion function that transforms two coordinates (two float8 jbe@0: parameters) to an epoint.

jbe@0: jbe@0:

max_latitude(ebox)

jbe@0: jbe@0:

Returns the northern boundary of a given ebox in degrees between -90 and +90.

jbe@0: jbe@0:

max_longitude(ebox)

jbe@0: jbe@0:

Returns the eastern boundary of a given ebox in degrees between -180 and +180 jbe@0: (both inclusive).

jbe@0: jbe@0:

min_latitude(ebox)

jbe@0: jbe@0:

Returns the southern boundary of a given ebox in degrees between -90 and +90.

jbe@0: jbe@0:

min_longitude(ebox)

jbe@0: jbe@0:

Returns the western boundary of a given ebox in degrees between -180 and +180 jbe@0: (both inclusive).

jbe@0: jbe@0:

latitude(epoint)

jbe@0: jbe@0:

Returns the latitude value of an epoint in degrees between -90 and +90.

jbe@0: jbe@0:

longitude(epoint)

jbe@0: jbe@0:

Returns the longitude value of an epoint in degrees between -180 and +180 jbe@0: (both inclusive).

jbe@0: jbe@0:

radius(ecircle)

jbe@0: jbe@0:

Returns the radius of an ecircle in meters.

jbe@0: