jbe@529: pgLatLon v0.1 documentation
jbe@529: ===========================
jbe@529: 
jbe@529: 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@530: using fractal indices. 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: 
jbe@529: Installation
jbe@529: ------------
jbe@529: 
jbe@529: ### Automatic installation
jbe@529: 
jbe@529: Prerequisites:
jbe@529: 
jbe@529: * Ensure that the `pg_config` binary is in your path (shipped with PostgreSQL).
jbe@529: * Ensure that GNU Make is available (either as `make` or `gmake`).
jbe@529: 
jbe@529: Then simply type:
jbe@529: 
jbe@529:     make install
jbe@529: 
jbe@529: ### Manual installation    
jbe@529: 
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: 
jbe@529: ### Loading the extension
jbe@529: 
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: 
jbe@529: 
jbe@529: Reference
jbe@529: ---------
jbe@529: 
jbe@529: ### 1. Types
jbe@529: 
jbe@529: pgLatLon provides four geographic types: `epoint`, `ebox`, `ecircle`, and
jbe@529: `ecluster`.
jbe@529: 
jbe@529: #### `epoint`
jbe@529: 
jbe@529: A 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`).
jbe@529: 
jbe@529: 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.
jbe@529: 
jbe@529: 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: #### `ebox`
jbe@529: 
jbe@529: An 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'`.
jbe@529: 
jbe@529: A special value is the empty area, denoted by the text represenation `'empty'`.
jbe@529: Such an `ebox` does not contain any point.
jbe@529: 
jbe@529: 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.
jbe@529: 
jbe@529: 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.
jbe@529: 
jbe@529: #### `ecircle`
jbe@529: 
jbe@529: An 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.
jbe@529: 
jbe@529: 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)`.
jbe@529: 
jbe@529: #### `ecluster`
jbe@529: 
jbe@529: A 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>)`
jbe@529: * `path    ({N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> ...)`
jbe@529: * `outline ({N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> ...)`
jbe@529: * `polygon ({N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> ...)`
jbe@529: 
jbe@529: 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: ### 2. Indices
jbe@529: 
jbe@529: Two kinds of indices are supported: B-tree and GiST indices.
jbe@529: 
jbe@530: #### B-tree 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.
jbe@529: 
jbe@530: #### GiST indices
jbe@529: 
jbe@529: 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:
jbe@529: 
jbe@529:     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: 
jbe@530: GiST indices also support nearest neighbor searches when using the distance
jbe@529: operator (`<->`) in the ORDER BY clause.
jbe@529: 
jbe@530: #### Indices on other data types (e.g. GeoJSON)
jbe@529: 
jbe@529: 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:
jbe@529: 
jbe@529:     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: 
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: 
jbe@529: ### 3. Operators
jbe@529: 
jbe@529: #### Equality operator `=`
jbe@529: 
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 = epoint`
jbe@529: * `ebox = ebox`
jbe@529: * `ecircle = ecircle`
jbe@529: 
jbe@529: The negation is the inequality operator (`<>` or `!=`).
jbe@529: 
jbe@529: #### Linear ordering operators `<<<`, `<<<=`, `>>>=`, `>>>`
jbe@529: 
jbe@529: 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: #### Overlap operator `&&`
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 && ebox`
jbe@529: * `epoint && ecircle`
jbe@529: * `epoint && ecluster`
jbe@529: * `ebox && ebox`
jbe@529: * `ecircle && ecircle`
jbe@529: * `ecircle && ecluster`
jbe@529: 
jbe@529: The `&&` operator is commutative, i.e. `a && b` is the same as `b && a`. Each
jbe@529: commutation is supported as well.
jbe@529: 
jbe@529: #### Distance operator `<->`
jbe@529: 
jbe@529: 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 <-> epoint`
jbe@529: * `epoint <-> ecircle`
jbe@529: * `epoint <-> ecluster`
jbe@529: * `ecircle <-> ecircle`
jbe@529: * `ecircle <-> ecluster`
jbe@529: 
jbe@529: The `<->` operator is commutative, i.e. `a <-> b` is the same as `b <-> a`.
jbe@529: Each commutation is supported as well.
jbe@529: 
jbe@529: 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.
jbe@529: 
jbe@529: 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.
jbe@529: 
jbe@529: ### 4. Functions
jbe@529: 
jbe@529: #### `center(circle)`
jbe@529: 
jbe@529: Returns the center of an `ecircle` as an `epoint`.
jbe@529: 
jbe@529: #### `distance(epoint, epoint)`
jbe@529: 
jbe@529: Calculates the distance between two `epoint` datums in meters. This function is
jbe@529: an alias for the distance operator `<->`.
jbe@529: 
jbe@529: 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.
jbe@529: 
jbe@529: #### `distance(ecluster, epoint)`
jbe@529: 
jbe@529: Calculates the distance from an `ecluster` to an `epoint` in meters. This
jbe@529: function is an alias for the distance operator `<->`.
jbe@529: 
jbe@529: 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.
jbe@529: 
jbe@529: #### `distance_within(`variable `epoint,` fixed `epoint,` radius `float8)`
jbe@529: 
jbe@529: Checks if the distance between two `epoint` datums is not greater than a given
jbe@529: value (search radius).
jbe@529: 
jbe@529: 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)`
jbe@529: 
jbe@529: Checks if the distance from an `ecluster` to an `epoint` is not greater than a
jbe@529: given value (search radius).
jbe@529: 
jbe@529: #### `ebox(`latmin `float8,` latmax `float8,` lonmin `float8,` lonmax `float8)`
jbe@529: 
jbe@529: Creates a new `ebox` with the given boundaries.
jbe@529: See "1. Types", subsection `ebox` for details.
jbe@529: 
jbe@529: #### `ebox(epoint, epoint)`
jbe@529: 
jbe@529: 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.
jbe@529: 
jbe@529: #### `ecircle(epoint, float8)`
jbe@529: 
jbe@529: Creates an `ecircle` with the given center point and radius.
jbe@529: 
jbe@529: #### `ecircle(`latitude `float8,` longitude `float8,` radius `float8)`
jbe@529: 
jbe@529: Creates an `ecircle` with the given center point and radius.
jbe@529: 
jbe@529: #### `ecluster_concat(ecluster, ecluster)`
jbe@529: 
jbe@529: 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).
jbe@529: 
jbe@529: #### `ecluster_concat(ecluster[])`
jbe@529: 
jbe@529: 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).
jbe@529: 
jbe@529: #### `ecluster_create_multipoint(epoint[])`
jbe@529: 
jbe@529: Creates a new `ecluster` which contains multiple points.
jbe@529: 
jbe@529: #### `ecluster_create_outline(epoint[])`
jbe@529: 
jbe@529: Creates a new `ecluster` that is an outline given by the passed points.
jbe@529: 
jbe@529: #### `ecluster_create_path(epoint[])`
jbe@529: 
jbe@529: Creates a new `ecluster` that is a path given by the passed points.
jbe@529: 
jbe@529: #### `ecluster_create_polygon(epoint[])`
jbe@529: 
jbe@529: Creates a new `ecluster` that is a polygon given by the passed points.
jbe@529: 
jbe@529: #### `ecluster_extract_outlines(ecluster)`
jbe@529: 
jbe@529: Set-returning function that returns the outlines of an `ecluster` as `epoint[]`
jbe@529: rows.
jbe@529: 
jbe@529: #### `ecluster_extract_paths(ecluster)`
jbe@529: 
jbe@529: Set-returning function that returns the paths of an `ecluster` as `epoint[]`
jbe@529: rows.
jbe@529: 
jbe@529: #### `ecluster_extract_points(ecluster)`
jbe@529: 
jbe@529: Set-returning function that returns the points of an `ecluster` as `epoint`
jbe@529: rows.
jbe@529: 
jbe@529: #### `ecluster_extract_polygons(ecluster)`
jbe@529: 
jbe@529: Set-returning function that returns the polygons of an `ecluster` as `epoint[]`
jbe@529: rows.
jbe@529: 
jbe@529: #### `empty_ebox`()
jbe@529: 
jbe@529: Returns the empty `ebox`.
jbe@529: See "1. Types", subsection `ebox` for details.
jbe@529: 
jbe@529: #### `epoint(`latitude `float8,` longitude `float8)`
jbe@529: 
jbe@529: Returns an `epoint` with the given latitude and longitude.
jbe@529: 
jbe@529: #### `epoint_latlon(`latitude `float8,` longitude `float8)`
jbe@529: 
jbe@529: Alias for `epoint(float8, float8)`.
jbe@529: 
jbe@529: #### `epoint_lonlat(`longitude `float8,` latitude `float8)`
jbe@529: 
jbe@529: Same as `epoint(float8, float8)` but with arguments reversed.
jbe@529: 
jbe@529: #### `GeoJSON_to_epoint(jsonb, text)`
jbe@529: 
jbe@529: 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.
jbe@529: 
jbe@529: 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`.
jbe@529: 
jbe@529: #### `GeoJSON_to_ecluster(jsonb, text)`
jbe@529: 
jbe@529: 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.
jbe@529: 
jbe@529: 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`.
jbe@529: 
jbe@529: #### `max_latitude(ebox)`
jbe@529: 
jbe@529: Returns the northern boundary of a given `ebox` in degrees between -90 and +90.
jbe@529: 
jbe@529: #### `max_longitude(ebox)`
jbe@529: 
jbe@529: Returns the eastern boundary of a given `ebox` in degrees between -180 and +180
jbe@529: (both inclusive).
jbe@529: 
jbe@529: #### `min_latitude(ebox)`
jbe@529: 
jbe@529: Returns the southern boundary of a given `ebox` in degrees between -90 and +90.
jbe@529: 
jbe@529: #### `min_longitude(ebox)`
jbe@529: 
jbe@529: Returns the western boundary of a given `ebox` in degrees between -180 and +180
jbe@529: (both inclusive).
jbe@529: 
jbe@529: #### `latitude(epoint)`
jbe@529: 
jbe@529: Returns the latitude value of an `epoint` in degrees between -90 and +90.
jbe@529: 
jbe@529: #### `longitude(epoint)`
jbe@529: 
jbe@529: Returns the longitude value of an `epoint` in degrees between -180 and +180
jbe@529: (both inclusive).
jbe@529: 
jbe@529: #### `radius(ecircle)`
jbe@529: 
jbe@529: Returns the radius of an `ecircle` in meters.
jbe@529: