jbe@16: pgLatLon v0.4 documentation
jbe@0: ===========================
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@10: operations, pgLatLon aims to support more precise geometric calculations for
jbe@10: 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@10: pgLatLon is a lightweight solution as it only depends on PostgreSQL itself (and
jbe@10: a C compiler for building).
jbe@10:
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:
jbe@0: Installation
jbe@0: ------------
jbe@0:
jbe@0: ### Automatic installation
jbe@0:
jbe@0: Prerequisites:
jbe@0:
jbe@0: * Ensure that the `pg_config` binary is in your path (shipped with PostgreSQL).
jbe@0: * Ensure that GNU Make is available (either as `make` or `gmake`).
jbe@0:
jbe@0: Then simply type:
jbe@0:
jbe@0: make install
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@13: cc -Wall -O2 -fPIC -shared -I `pg_config --includedir-server` -o latlon-v0003.so latlon-v0003.c
jbe@13: cp latlon-v0003.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: ### 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@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: 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:
jbe@0: Reference
jbe@0: ---------
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] [E|W]'`, 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} {E|W} {N|S} {E|W}'`, 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} {E|W} '`, 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: * `point ({N|S} {E|W})`
jbe@0: * `path ({N|S} {E|W} {N|S} {E|W} ...)`
jbe@0: * `outline ({N|S} {E|W} {N|S} {E|W} {N|S} {E|W} ...)`
jbe@0: * `polygon ({N|S} {E|W} {N|S} {E|W} {N|S} {E|W} ...)`
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: 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: 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: ### 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: * `epoint = epoint`
jbe@0: * `ebox = ebox`
jbe@0: * `ecircle = ecircle`
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: * `epoint && ebox`
jbe@0: * `epoint && ecircle`
jbe@0: * `epoint && ecluster`
jbe@0: * `ebox && ebox`
jbe@16: * `ebox && ecircle`
jbe@16: * `ebox && ecluster`
jbe@0: * `ecircle && ecircle`
jbe@0: * `ecircle && ecluster`
jbe@16: * `ecluster && ecluster`
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@10: #### Lossy overlap operator `&&+`
jbe@10:
jbe@10: Tests if two geographic objects may have at least one point in common. Opposed
jbe@10: to the `&&` operator, the `&&+` operator may return false positives and is
jbe@10: currently implemented for:
jbe@10:
jbe@10: * `epoint &&+ ecluster`
jbe@10: * `ebox &&+ ecircle`
jbe@10: * `ebox &&+ ecluster`
jbe@10: * `ecircle &&+ ecluster`
jbe@10: * `ecluster &&+ ecluster`
jbe@10:
jbe@16: The `&&+` operator is commutative, i.e. `a &&+ b` is the same as `b &&+ a`.
jbe@16: Each commutation is supported as well.
jbe@10:
jbe@10: Where two data types support both the `&&` and the `&&+` operator, the `&&+`
jbe@10: operator computes faster.
jbe@10:
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: * `ebox @> epoint` (alias for `&&`)
jbe@16: * `ebox @> ecluster`
jbe@16: * `ecluster @> epoint` (alias for `&&`)
jbe@16: * `ecluster @> ebox`
jbe@16: * `ecluster @> ecluster`
jbe@16:
jbe@16: The commutator of `@>` ("contains") is `<@` ("is contained in"), i.e. `a @> b`
jbe@16: is the same as `b <@ a`.
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: * `epoint <-> epoint`
jbe@16: * `epoint <-> ebox`
jbe@0: * `epoint <-> ecircle`
jbe@0: * `epoint <-> ecluster`
jbe@16: * `ebox <-> ebox`
jbe@16: * `ebox <-> ecircle`
jbe@16: * `ebox <-> ecluster`
jbe@0: * `ecircle <-> ecircle`
jbe@0: * `ecircle <-> ecluster`
jbe@16: * `ecluster <-> ecluster`
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: