pgLatLon

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

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

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

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

Acknowledgements

pgLatLon has been contributed by FlexiGuided GmbH as part of the WeGovNow project which has received funding from the European Union's Horizon 2020 research and innovation programme under grant agreement No 693514.

Installation and Documentation

pgLatLon uses PostgreSQL's extension building infrastructure PGXS to allow for an easy installation:

  1. Download the extension by clicking on one of the links in the “Download” section below.
  2. Extract the tar.gz file.
  3. Run “make install”.

Documentation can be found here or be downloaded as an HTML file.

Example usage

Create a table with a column that stores a geographic position (an epoint) in column "location":

CREATE TABLE tbl (id serial4 PRIMARY KEY, location epoint NOT NULL);

Create a spatial index for column "location":

CREATE INDEX tbl_location_key ON tbl USING gist (location);

Perform a nearest-neighbor search using the previously created index:

SELECT * FROM tbl ORDER BY location <-> 'N12.34 E56.78'::epoint LIMIT 10;

Download

Repository

Dependencies

Changes

  • 2020-11-30: Version 0.15
    • Fixed longitude wraparound for values equal to or greater than 360°
    • Fixed performance issues of GeoJSON_to_ecluster function
    • Allow non-superusers to install extension
    • Added proper database schema support
      • Properly support creating extension in schema
      • Forbid schema relocation (as it would break the extension)
  • 2020-02-11: Version 0.14
    • Fixed wrong commutator of "epoint <@ ecluster" (which led to an unused shell operator of "ecluster <@ epoint")
    • Clarification in documentation on corner-case behavior of @>, where @> is an alias for &&
    • Provided previously missing (empty) update script from version 0.12 to version 0.13
  • 2020-02-09: Version 0.13
    • Fixed compatibility with PostgreSQL 11 and 12
    • Minor fixes in documentation and comments
  • 2017-12-11: Version 0.12
    • Bugfix for (in)equality operators of epoint, ebox, and ecircle
  • 2017-11-19: Version 0.11
    • Added C include directive to support PostgreSQL version 10
    • Minor fix in documentation regarding GeoJSON_to_ecluster function for GeoJSON indexing
  • 2016-10-31: Version 0.10
    • Added safety margins for distance calculation on index lookups (fixes bug that caused nearest-neighbor search to fail in certain cases)
    • Improved fair_distance function (see README for properties)
  • 2016-10-25: Version 0.9
    • Bugfix in type casts from epoint and ebox to ecluster (missing bounding circle calculation, requires reimport of database to be fixed)
    • New fair_distance function (see README)
  • 2016-10-21: Version 0.8
    • Fixed broken functions GeoJSON_to_epoint and GeoJSON_to_ecluster (NOTE: after update, manually recreate any indices using these functions)
  • 2016-09-26: Version 0.7
    • Improved accuracy of vector projection for distance calculation by considering local approximation of WGS-84 spheroid instead of sphere
  • 2016-09-22: Version 0.6
    • Bugfix in distance calculation involving the ecluster data type
  • 2016-09-12: Version 0.5
    • Changed behavior of "contains?" operator (@>) which fixes wrong behavior regarding certain corner cases
    • Added "contains?" operator for ebox type
  • 2016-09-09: Version 0.4
    • Added overlap operator (&&) and distance operator (<->) for ebox and ecluster data types
    • Added "contains?" (@>) and "is contained in?" (<@) operators
    • Bug causing table scans instead of index usage for nearest-neighbor searches of clusters (including polygons) has been fixed
  • 2016-09-02: Version 0.3
    • Added lossy overlap operator (&&+) for faster selections (where false positives are tolerable)
    • Bug causing table scans instead of index usage when using the overlap operator (&&) with a ecircle query object on ecluster columns has been fixed
  • 2016-08-22: Version 0.2
    • Improved calculation speed of approximated distances on the WGS-84 ellipsoid
  • 2016-08-21: Initial release of version 0.1