liquid_feedback_core
view pgLatLon/README.mkd @ 529:96ee2db56bec
New PostgreSQL extension "pgLatLon" for geospatial operations and indexing in LiquidFeedback
| author | jbe |
|---|---|
| date | Thu Aug 18 20:19:58 2016 +0200 (2016-08-18) |
| parents | |
| children | 6bc81898fd3b |
line source
1 pgLatLon v0.1 documentation
2 ===========================
4 pgLatLon is a spatial database extension for the PostgreSQL object-relational
5 database management system providing geographic data types and spatial indexing
6 for the WGS-84 spheroid.
8 While many other spatial databases still use imprecise bounding boxes for many
9 operations, pgLatLon supports more precise geometric calculations for all
10 implemented operators. Efficient indexing of geometric objects is provided
11 using fractal indicies. Optimizations on bit level (including logarithmic
12 compression) allow for a highly memory-efficient non-overlapping index suitable
13 for huge datasets.
15 Unlike competing spatial extensions for PostgreSQL, pgLatLon is available under
16 the permissive MIT/X11 license to avoid problems with viral licenses like the
17 GPLv2/v3.
20 Installation
21 ------------
23 ### Automatic installation
25 Prerequisites:
27 * Ensure that the `pg_config` binary is in your path (shipped with PostgreSQL).
28 * Ensure that GNU Make is available (either as `make` or `gmake`).
30 Then simply type:
32 make install
34 ### Manual installation
36 It is also possible to compile and install the extension without GNU Make as
37 follows:
39 cc -Wall -O2 -fPIC -shared -I `pg_config --includedir-server` -o latlon-v0001.so latlon-v0001.c
40 cp latlon-v0001.so `pg_config --pkglibdir`
41 cp latlon.control `pg_config --sharedir`/extension/
42 cp latlon--0.1.sql `pg_config --sharedir`/extension/
44 ### Loading the extension
46 After installation, you can create a database and load the extension as
47 follows:
49 % createdb test_database
50 % psql test_database
51 psql (9.5.4)
52 Type "help" for help.
54 test_database=# CREATE EXTENSION latlon;
57 Reference
58 ---------
60 ### 1. Types
62 pgLatLon provides four geographic types: `epoint`, `ebox`, `ecircle`, and
63 `ecluster`.
65 #### `epoint`
67 A point on the earth spheroid (WGS-84).
69 The text input format is `'[N|S]<float> [E|W]<float>'`, where each float is in
70 degrees. Note the required white space between the latitude and longitude
71 components. Each floating point number may have a sign, in which case `N`/`S`
72 or `E`/`W` are switched respectively (e.g. `E-5` is the same as `W5`).
74 An `epoint` may also be created from two floating point numbers by calling
75 `epoint(latitude, longitude)`, where positive latitudes are used for the
76 northern hemisphere, negative latitudes are used for the southern hemisphere,
77 positive longitudes indicate positions east of the prime meridian, and negative
78 longitudes indicate positions west of the prime meridian.
80 Latitudes exceeding -90 or +90 degrees are truncated to -90 or +90
81 respectively, in which case a warning will be issued. Longitudes exceeding -180
82 or +180 degrees will be converted to values between -180 and +180 (both
83 inclusive) by adding or substracting a multiple of 360 degrees, in which case a
84 notice will be issued.
86 If the latitude is -90 or +90 (south pole or north pole), a longitude value is
87 still stored in the datum, and if a point is on the prime meridian or the
88 180th meridian, the east/west bit is also stored in the datum. In case of the
89 prime meridian, this is done by storing a floating point value of -0 for
90 0 degrees west and a value of +0 for 0 degrees east. In case of the
91 180th meridian, this is done by storing -180 or +180 respectively. The equality
92 operator, however, returns true when the same points on earth are described,
93 i.e. the longitude is ignored for the poles, and 180 degrees west is considered
94 to be equal to 180 degrees east.
96 #### `ebox`
98 An area on earth demarcated by a southern and northern latitude, and a western
99 and eastern longitude (all given in WGS-84).
101 The text input format is
102 `'{N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float>'`, where each float is in
103 degrees. The ordering of the four white-space separated blocks is not
104 significant. To include the 180th meridian, one longitude boundary must be
105 equal to or exceed `W180` or `E180`, e.g. `'N10 N20 E170 E190'`.
107 A special value is the empty area, denoted by the text represenation `'empty'`.
108 Such an `ebox` does not contain any point.
110 An `ebox` may also be created from four floating point numbers by calling
111 `ebox(min_latitude, max_latitude, min_longitude, max_longitude)`, where
112 positive values are used for north and east, and negative values are used for
113 south and west. If `min_latitude` is strictly greater than `max_latitude`, an
114 empty `ebox` is created. If `min_longitude` is greater than `max_longitude` and
115 if both longitudes are between -180 and +180 degrees, then the area oriented in
116 such way that the 180th meridian is included.
118 If the longitude span is less than 120 degrees, an `ebox` may be alternatively
119 created from two `epoints` in the following way: `ebox(epoint(lat1, lon1),
120 epoint(lat2, lon2))`. In this case `lat1` and `lat2` as well as `lon1` and
121 `lon2` can be swapped without any impact.
123 #### `ecircle`
125 An area containing all points not farther away from a given center point
126 (WGS-84) than a given radius.
128 The text input format is `'{N|S}<float> {E|W}<float> <float>'`, where the first
129 two floats denote the center point in degrees and the third float denotes the
130 radius in meters. A radius equal to minus infinity denotes an empty circle
131 which contains no point at all (despite having a center), while a radius equal
132 to zero denotes a circle that includes a single point.
134 An `ecircle` may also be created by calling `ecircle(epoint(...), radius)` or
135 from three floating point numbers by calling `ecircle(latitude, longitude,
136 radius)`.
138 #### `ecluster`
140 A collection of points, paths, polygons, and outlines on the WGS-84 spheroid.
141 Each path, polygon, or outline must cover a longitude range of less than
142 180 degrees to avoid ambiguities.
144 The text input format is a white-space separated list of the following items:
146 * `point ({N|S}<float> {E|W}<float>)`
147 * `path ({N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> ...)`
148 * `outline ({N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> ...)`
149 * `polygon ({N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> ...)`
151 Paths are open by default (i.e. there is no connection from the last point in
152 the list to the first point in the list). Outlines and polygons, in contrast,
153 are automatically closed (i.e. there is a line segment from the last point in
154 the list to the first point in the list) which means the first point should not
155 be repeated as last point in the list. Polygons are filled, outlines are not.
157 ### 2. Indices
159 Two kinds of indices are supported: B-tree and GiST indices.
161 #### B-tree indicies
163 A B-tree index can be used for simple equality searches and is supported by the
164 `epoint`, `ebox`, and `ecircle` data types. B-tree indices can not be used for
165 geographic searches.
167 #### GiST indicies
169 For geographic searches, GiST indices must be used. The `epoint`, `ecircle`,
170 and `ecluster` data types support GiST indexing. A GiST index for geographic
171 searches can be created as follows:
173 CREATE TABLE tbl (
174 id serial4 PRIMARY KEY,
175 loc epoint NOT NULL );
177 CREATE INDEX name_of_index ON tbl USING gist (loc);
179 GiST indicies also support nearest neighbor searches when using the distance
180 operator (`<->`) in the ORDER BY clause.
182 #### Indicies on other data types (e.g. GeoJSON)
184 Note that further types can be indexed by using an index on an expression with
185 a conversion function. One conversion function provided by pgLatLon is the
186 `GeoJSON_to_ecluster(float8, float8, text)` function:
188 CREATE TABLE tbl (
189 id serial4 PRIMARY KEY,
190 loc jsonb NOT NULL );
192 CREATE INDEX name_of_index ON tbl USING gist((GeoJSON_to_ecluster("loc")));
194 When using the conversion function in an expression, the index will be used
195 automatically:
197 SELECT * FROM tbl WHERE GeoJSON_to_ecluster("loc") && 'N50 E10 10000'::ecircle;
199 ### 3. Operators
201 #### Equality operator `=`
203 Tests if two geographic objects are equal.
205 The longitude is ignored for the poles, and 180 degrees west is considered to
206 be equal to 180 degrees east.
208 For boxes and circles, two empty objects are considered equal. (Note that a
209 circle is not empty if the radius is zero but only if it is negative infinity,
210 i.e. smaller than zero.) Two circles with a positive infinite radius are also
211 considered equal.
213 Implemented for:
215 * `epoint = epoint`
216 * `ebox = ebox`
217 * `ecircle = ecircle`
219 The negation is the inequality operator (`<>` or `!=`).
221 #### Linear ordering operators `<<<`, `<<<=`, `>>>=`, `>>>`
223 These operators create an arbitrary (but well-defined) linear ordering of
224 geographic objects, which is used internally for B-tree indexing and merge
225 joins. These operators will usually not be used by an application programmer.
227 #### Overlap operator `&&`
229 Tests if two geographic objects have at least one point in common. Currently
230 implemented for:
232 * `epoint && ebox`
233 * `epoint && ecircle`
234 * `epoint && ecluster`
235 * `ebox && ebox`
236 * `ecircle && ecircle`
237 * `ecircle && ecluster`
239 The `&&` operator is commutative, i.e. `a && b` is the same as `b && a`. Each
240 commutation is supported as well.
242 #### Distance operator `<->`
244 Calculates the shortest distance between two geographic objects in meters (zero
245 if the objects are overlapping). Currently implemented for:
247 * `epoint <-> epoint`
248 * `epoint <-> ecircle`
249 * `epoint <-> ecluster`
250 * `ecircle <-> ecircle`
251 * `ecircle <-> ecluster`
253 The `<->` operator is commutative, i.e. `a <-> b` is the same as `b <-> a`.
254 Each commutation is supported as well.
256 For short distances, the result is very accurate (i.e. respects the dimensions
257 of the WGS-84 spheroid). For longer distances in the order of magnitude of
258 earth's radius or greater, the value is only approximate (but the error is
259 still less than 0.2% as long as no polygons with very long edges are involved).
261 The functions `distance(epoint, epoint)` and `distance(ecluster, epoint)` can
262 be used as an alias for this operator.
264 Note: In case of radial searches with a fixed radius, this operator should
265 not be used. Instead, an `ecircle` should be created and used in combination
266 with the overlap operator (`&&`). Alternatively, the functions
267 `distance_within(epoint, epoint, float8)` or `distance_within(ecluster, epoint,
268 float8)` can be used for fixed-radius searches.
270 ### 4. Functions
272 #### `center(circle)`
274 Returns the center of an `ecircle` as an `epoint`.
276 #### `distance(epoint, epoint)`
278 Calculates the distance between two `epoint` datums in meters. This function is
279 an alias for the distance operator `<->`.
281 Note: In case of radial searches with a fixed radius, this function should not be
282 used. Use `distance_within(epoint, epoint, float8)` instead.
284 #### `distance(ecluster, epoint)`
286 Calculates the distance from an `ecluster` to an `epoint` in meters. This
287 function is an alias for the distance operator `<->`.
289 Note: In case of radial searches with a fixed radius, this function should not be
290 used. Use `distance_within(epoint, epoint, float8)` instead.
292 #### `distance_within(`variable `epoint,` fixed `epoint,` radius `float8)`
294 Checks if the distance between two `epoint` datums is not greater than a given
295 value (search radius).
297 Note: In case of radial searches with a fixed radius, the first argument must
298 be used for the table column, while the second argument must be used for the
299 search center. Otherwise an existing index cannot be used.
301 #### `distance_within(`variable `ecluster,` fixed `epoint,` radius `float8)`
303 Checks if the distance from an `ecluster` to an `epoint` is not greater than a
304 given value (search radius).
306 #### `ebox(`latmin `float8,` latmax `float8,` lonmin `float8,` lonmax `float8)`
308 Creates a new `ebox` with the given boundaries.
309 See "1. Types", subsection `ebox` for details.
311 #### `ebox(epoint, epoint)`
313 Creates a new `ebox`. This function may only be used if the longitude
314 difference is less than or equal to 120 degrees.
315 See "1. Types", subsection `ebox` for details.
317 #### `ecircle(epoint, float8)`
319 Creates an `ecircle` with the given center point and radius.
321 #### `ecircle(`latitude `float8,` longitude `float8,` radius `float8)`
323 Creates an `ecircle` with the given center point and radius.
325 #### `ecluster_concat(ecluster, ecluster)`
327 Combines two clusters to form a new `ecluster` by uniting all entries of both
328 clusters. Note that two overlapping areas of polygons annihilate each other
329 (which may be used to create polygons with holes).
331 #### `ecluster_concat(ecluster[])`
333 Creates a new `ecluster` that unites all entries of all clusters in the passed
334 array. Note that two overlapping areas of polygons annihilate each other (which
335 may be used to create polygons with holes).
337 #### `ecluster_create_multipoint(epoint[])`
339 Creates a new `ecluster` which contains multiple points.
341 #### `ecluster_create_outline(epoint[])`
343 Creates a new `ecluster` that is an outline given by the passed points.
345 #### `ecluster_create_path(epoint[])`
347 Creates a new `ecluster` that is a path given by the passed points.
349 #### `ecluster_create_polygon(epoint[])`
351 Creates a new `ecluster` that is a polygon given by the passed points.
353 #### `ecluster_extract_outlines(ecluster)`
355 Set-returning function that returns the outlines of an `ecluster` as `epoint[]`
356 rows.
358 #### `ecluster_extract_paths(ecluster)`
360 Set-returning function that returns the paths of an `ecluster` as `epoint[]`
361 rows.
363 #### `ecluster_extract_points(ecluster)`
365 Set-returning function that returns the points of an `ecluster` as `epoint`
366 rows.
368 #### `ecluster_extract_polygons(ecluster)`
370 Set-returning function that returns the polygons of an `ecluster` as `epoint[]`
371 rows.
373 #### `empty_ebox`()
375 Returns the empty `ebox`.
376 See "1. Types", subsection `ebox` for details.
378 #### `epoint(`latitude `float8,` longitude `float8)`
380 Returns an `epoint` with the given latitude and longitude.
382 #### `epoint_latlon(`latitude `float8,` longitude `float8)`
384 Alias for `epoint(float8, float8)`.
386 #### `epoint_lonlat(`longitude `float8,` latitude `float8)`
388 Same as `epoint(float8, float8)` but with arguments reversed.
390 #### `GeoJSON_to_epoint(jsonb, text)`
392 Maps a GeoJSON object of type "Point" or "Feature" (which contains a
393 "Point") to an `epoint` datum. For any other JSON objects, NULL is returned.
395 The second parameter (which defaults to `epoint_lonlat`) may be set to a name
396 of a conversion function that transforms two coordinates (two `float8`
397 parameters) to an `epoint`.
399 #### `GeoJSON_to_ecluster(jsonb, text)`
401 Maps a (valid) GeoJSON object to an `ecluster`. Note that this function
402 does not check whether the JSONB object is a valid GeoJSON object.
404 The second parameter (which defaults to `epoint_lonlat`) may be set to a name
405 of a conversion function that transforms two coordinates (two `float8`
406 parameters) to an `epoint`.
408 #### `max_latitude(ebox)`
410 Returns the northern boundary of a given `ebox` in degrees between -90 and +90.
412 #### `max_longitude(ebox)`
414 Returns the eastern boundary of a given `ebox` in degrees between -180 and +180
415 (both inclusive).
417 #### `min_latitude(ebox)`
419 Returns the southern boundary of a given `ebox` in degrees between -90 and +90.
421 #### `min_longitude(ebox)`
423 Returns the western boundary of a given `ebox` in degrees between -180 and +180
424 (both inclusive).
426 #### `latitude(epoint)`
428 Returns the latitude value of an `epoint` in degrees between -90 and +90.
430 #### `longitude(epoint)`
432 Returns the longitude value of an `epoint` in degrees between -180 and +180
433 (both inclusive).
435 #### `radius(ecircle)`
437 Returns the radius of an `ecircle` in meters.