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

pgLatLon v0.3 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@0:

While many other spatial databases still use imprecise bounding boxes for many jbe@11: operations, pgLatLon aims to support more precise geometric calculations for jbe@11: all implemented operators. Efficient indexing of geometric objects is provided jbe@2: using space-filling fractal curves. Optimizations on bit level (including jbe@2: logarithmic compression) allow for a highly memory-efficient non-overlapping jbe@2: 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@6: 
cc -Wall -O2 -fPIC -shared -I `pg_config --includedir-server` -o latlon-v0002.so latlon-v0002.c
jbe@6: cp latlon-v0002.so `pg_config --pkglibdir`
jbe@0: cp latlon.control `pg_config --sharedir`/extension/
jbe@6: cp latlon--0.1--0.2.sql latlon--0.2.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@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@0:

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@0: 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@0:

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@0: GeoJSON_to_ecluster(float8, float8, 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@0:

The && operator is commutative, i.e. a && b is the same as b && a. Each jbe@0: 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@11:

The &&+ operator is commutative, i.e. a &&+ b is the same as b &&+ a. Each jbe@11: 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@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@0:

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@0: 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@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: