pgLatLon
view README.html @ 12:b4ec52e7e888
Add update script for updating from version 0.2 to 0.3
| author | jbe |
|---|---|
| date | Fri Sep 02 14:27:31 2016 +0200 (2016-09-02) |
| parents | 7c1c76e7d341 |
| children | 4489b4d7d9c8 |
line source
1 <html><head><title>pgLatLon v0.3 documentation</title></head><body>
2 <h1>pgLatLon v0.3 documentation</h1>
4 <p>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.</p>
8 <p>While many other spatial databases still use imprecise bounding boxes for many
9 operations, pgLatLon aims to support more precise geometric calculations for
10 all implemented operators. Efficient indexing of geometric objects is provided
11 using space-filling fractal curves. Optimizations on bit level (including
12 logarithmic compression) allow for a highly memory-efficient non-overlapping
13 index suitable for huge datasets.</p>
15 <p>pgLatLon is a lightweight solution as it only depends on PostgreSQL itself (and
16 a C compiler for building).</p>
18 <p>Unlike competing spatial extensions for PostgreSQL, pgLatLon is available under
19 the permissive MIT/X11 license to avoid problems with viral licenses like the
20 GPLv2/v3.</p>
22 <h2>Installation</h2>
24 <h3>Automatic installation</h3>
26 <p>Prerequisites:</p>
28 <ul>
29 <li>Ensure that the <code>pg_config</code> binary is in your path (shipped with PostgreSQL).</li>
30 <li>Ensure that GNU Make is available (either as <code>make</code> or <code>gmake</code>).</li>
31 </ul>
33 <p>Then simply type:</p>
35 <pre><code>make install
36 </code></pre>
38 <h3>Manual installation</h3>
40 <p>It is also possible to compile and install the extension without GNU Make as
41 follows:</p>
43 <pre><code>cc -Wall -O2 -fPIC -shared -I `pg_config --includedir-server` -o latlon-v0002.so latlon-v0002.c
44 cp latlon-v0002.so `pg_config --pkglibdir`
45 cp latlon.control `pg_config --sharedir`/extension/
46 cp latlon--0.1--0.2.sql latlon--0.2.sql `pg_config --sharedir`/extension/
47 </code></pre>
49 <h3>Loading the extension</h3>
51 <p>After installation, you can create a database and load the extension as
52 follows:</p>
54 <pre><code>% createdb test_database
55 % psql test_database
56 psql (9.5.4)
57 Type "help" for help.
59 test_database=# CREATE EXTENSION latlon;
60 </code></pre>
62 <h2>Reference</h2>
64 <h3>1. Types</h3>
66 <p>pgLatLon provides four geographic types: <code>epoint</code>, <code>ebox</code>, <code>ecircle</code>, and
67 <code>ecluster</code>.</p>
69 <h4><code>epoint</code></h4>
71 <p>A point on the earth spheroid (WGS-84).</p>
73 <p>The text input format is <code>'[N|S]<float> [E|W]<float>'</code>, where each float is in
74 degrees. Note the required white space between the latitude and longitude
75 components. Each floating point number may have a sign, in which case <code>N</code>/<code>S</code>
76 or <code>E</code>/<code>W</code> are switched respectively (e.g. <code>E-5</code> is the same as <code>W5</code>).</p>
78 <p>An <code>epoint</code> may also be created from two floating point numbers by calling
79 <code>epoint(latitude, longitude)</code>, where positive latitudes are used for the
80 northern hemisphere, negative latitudes are used for the southern hemisphere,
81 positive longitudes indicate positions east of the prime meridian, and negative
82 longitudes indicate positions west of the prime meridian.</p>
84 <p>Latitudes exceeding -90 or +90 degrees are truncated to -90 or +90
85 respectively, in which case a warning will be issued. Longitudes exceeding -180
86 or +180 degrees will be converted to values between -180 and +180 (both
87 inclusive) by adding or substracting a multiple of 360 degrees, in which case a
88 notice will be issued.</p>
90 <p>If the latitude is -90 or +90 (south pole or north pole), a longitude value is
91 still stored in the datum, and if a point is on the prime meridian or the
92 180th meridian, the east/west bit is also stored in the datum. In case of the
93 prime meridian, this is done by storing a floating point value of -0 for
94 0 degrees west and a value of +0 for 0 degrees east. In case of the
95 180th meridian, this is done by storing -180 or +180 respectively. The equality
96 operator, however, returns true when the same points on earth are described,
97 i.e. the longitude is ignored for the poles, and 180 degrees west is considered
98 to be equal to 180 degrees east.</p>
100 <h4><code>ebox</code></h4>
102 <p>An area on earth demarcated by a southern and northern latitude, and a western
103 and eastern longitude (all given in WGS-84).</p>
105 <p>The text input format is
106 <code>'{N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float>'</code>, where each float is in
107 degrees. The ordering of the four white-space separated blocks is not
108 significant. To include the 180th meridian, one longitude boundary must be
109 equal to or exceed <code>W180</code> or <code>E180</code>, e.g. <code>'N10 N20 E170 E190'</code>.</p>
111 <p>A special value is the empty area, denoted by the text represenation <code>'empty'</code>.
112 Such an <code>ebox</code> does not contain any point.</p>
114 <p>An <code>ebox</code> may also be created from four floating point numbers by calling
115 <code>ebox(min_latitude, max_latitude, min_longitude, max_longitude)</code>, where
116 positive values are used for north and east, and negative values are used for
117 south and west. If <code>min_latitude</code> is strictly greater than <code>max_latitude</code>, an
118 empty <code>ebox</code> is created. If <code>min_longitude</code> is greater than <code>max_longitude</code> and
119 if both longitudes are between -180 and +180 degrees, then the area oriented in
120 such way that the 180th meridian is included.</p>
122 <p>If the longitude span is less than 120 degrees, an <code>ebox</code> may be alternatively
123 created from two <code>epoints</code> in the following way: <code>ebox(epoint(lat1, lon1),
124 epoint(lat2, lon2))</code>. In this case <code>lat1</code> and <code>lat2</code> as well as <code>lon1</code> and
125 <code>lon2</code> can be swapped without any impact.</p>
127 <h4><code>ecircle</code></h4>
129 <p>An area containing all points not farther away from a given center point
130 (WGS-84) than a given radius.</p>
132 <p>The text input format is <code>'{N|S}<float> {E|W}<float> <float>'</code>, where the first
133 two floats denote the center point in degrees and the third float denotes the
134 radius in meters. A radius equal to minus infinity denotes an empty circle
135 which contains no point at all (despite having a center), while a radius equal
136 to zero denotes a circle that includes a single point.</p>
138 <p>An <code>ecircle</code> may also be created by calling <code>ecircle(epoint(...), radius)</code> or
139 from three floating point numbers by calling <code>ecircle(latitude, longitude,
140 radius)</code>.</p>
142 <h4><code>ecluster</code></h4>
144 <p>A collection of points, paths, polygons, and outlines on the WGS-84 spheroid.
145 Each path, polygon, or outline must cover a longitude range of less than
146 180 degrees to avoid ambiguities.</p>
148 <p>The text input format is a white-space separated list of the following items:</p>
150 <ul>
151 <li><code>point ({N|S}<float> {E|W}<float>)</code></li>
152 <li><code>path ({N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> ...)</code></li>
153 <li><code>outline ({N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> ...)</code></li>
154 <li><code>polygon ({N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> ...)</code></li>
155 </ul>
157 <p>Paths are open by default (i.e. there is no connection from the last point in
158 the list to the first point in the list). Outlines and polygons, in contrast,
159 are automatically closed (i.e. there is a line segment from the last point in
160 the list to the first point in the list) which means the first point should not
161 be repeated as last point in the list. Polygons are filled, outlines are not.</p>
163 <h3>2. Indices</h3>
165 <p>Two kinds of indices are supported: B-tree and GiST indices.</p>
167 <h4>B-tree indices</h4>
169 <p>A B-tree index can be used for simple equality searches and is supported by the
170 <code>epoint</code>, <code>ebox</code>, and <code>ecircle</code> data types. B-tree indices can not be used for
171 geographic searches.</p>
173 <h4>GiST indices</h4>
175 <p>For geographic searches, GiST indices must be used. The <code>epoint</code>, <code>ecircle</code>,
176 and <code>ecluster</code> data types support GiST indexing. A GiST index for geographic
177 searches can be created as follows:</p>
179 <pre><code>CREATE TABLE tbl (
180 id serial4 PRIMARY KEY,
181 loc epoint NOT NULL );
183 CREATE INDEX name_of_index ON tbl USING gist (loc);
184 </code></pre>
186 <p>GiST indices also support nearest neighbor searches when using the distance
187 operator (<code><-></code>) in the ORDER BY clause.</p>
189 <h4>Indices on other data types (e.g. GeoJSON)</h4>
191 <p>Note that further types can be indexed by using an index on an expression with
192 a conversion function. One conversion function provided by pgLatLon is the
193 <code>GeoJSON_to_ecluster(float8, float8, text)</code> function:</p>
195 <pre><code>CREATE TABLE tbl (
196 id serial4 PRIMARY KEY,
197 loc jsonb NOT NULL );
199 CREATE INDEX name_of_index ON tbl USING gist((GeoJSON_to_ecluster("loc")));
200 </code></pre>
202 <p>When using the conversion function in an expression, the index will be used
203 automatically:</p>
205 <pre><code>SELECT * FROM tbl WHERE GeoJSON_to_ecluster("loc") && 'N50 E10 10000'::ecircle;
206 </code></pre>
208 <h3>3. Operators</h3>
210 <h4>Equality operator <code>=</code></h4>
212 <p>Tests if two geographic objects are equal.</p>
214 <p>The longitude is ignored for the poles, and 180 degrees west is considered to
215 be equal to 180 degrees east.</p>
217 <p>For boxes and circles, two empty objects are considered equal. (Note that a
218 circle is not empty if the radius is zero but only if it is negative infinity,
219 i.e. smaller than zero.) Two circles with a positive infinite radius are also
220 considered equal.</p>
222 <p>Implemented for:</p>
224 <ul>
225 <li><code>epoint = epoint</code></li>
226 <li><code>ebox = ebox</code></li>
227 <li><code>ecircle = ecircle</code></li>
228 </ul>
230 <p>The negation is the inequality operator (<code><></code> or <code>!=</code>).</p>
232 <h4>Linear ordering operators <code><<<</code>, <code><<<=</code>, <code>>>>=</code>, <code>>>></code></h4>
234 <p>These operators create an arbitrary (but well-defined) linear ordering of
235 geographic objects, which is used internally for B-tree indexing and merge
236 joins. These operators will usually not be used by an application programmer.</p>
238 <h4>Overlap operator <code>&&</code></h4>
240 <p>Tests if two geographic objects have at least one point in common. Currently
241 implemented for:</p>
243 <ul>
244 <li><code>epoint && ebox</code></li>
245 <li><code>epoint && ecircle</code></li>
246 <li><code>epoint && ecluster</code></li>
247 <li><code>ebox && ebox</code></li>
248 <li><code>ecircle && ecircle</code></li>
249 <li><code>ecircle && ecluster</code></li>
250 </ul>
252 <p>The <code>&&</code> operator is commutative, i.e. <code>a && b</code> is the same as <code>b && a</code>. Each
253 commutation is supported as well.</p>
255 <h4>Lossy overlap operator <code>&&+</code></h4>
257 <p>Tests if two geographic objects may have at least one point in common. Opposed
258 to the <code>&&</code> operator, the <code>&&+</code> operator may return false positives and is
259 currently implemented for:</p>
261 <ul>
262 <li><code>epoint &&+ ecluster</code></li>
263 <li><code>ebox &&+ ecircle</code></li>
264 <li><code>ebox &&+ ecluster</code></li>
265 <li><code>ecircle &&+ ecluster</code></li>
266 <li><code>ecluster &&+ ecluster</code></li>
267 </ul>
269 <p>The <code>&&+</code> operator is commutative, i.e. <code>a &&+ b</code> is the same as <code>b &&+ a</code>. Each
270 commutation is supported as well.</p>
272 <p>Where two data types support both the <code>&&</code> and the <code>&&+</code> operator, the <code>&&+</code>
273 operator computes faster.</p>
275 <h4>Distance operator <code><-></code></h4>
277 <p>Calculates the shortest distance between two geographic objects in meters (zero
278 if the objects are overlapping). Currently implemented for:</p>
280 <ul>
281 <li><code>epoint <-> epoint</code></li>
282 <li><code>epoint <-> ecircle</code></li>
283 <li><code>epoint <-> ecluster</code></li>
284 <li><code>ecircle <-> ecircle</code></li>
285 <li><code>ecircle <-> ecluster</code></li>
286 </ul>
288 <p>The <code><-></code> operator is commutative, i.e. <code>a <-> b</code> is the same as <code>b <-> a</code>.
289 Each commutation is supported as well.</p>
291 <p>For short distances, the result is very accurate (i.e. respects the dimensions
292 of the WGS-84 spheroid). For longer distances in the order of magnitude of
293 earth's radius or greater, the value is only approximate (but the error is
294 still less than 0.2% as long as no polygons with very long edges are involved).</p>
296 <p>The functions <code>distance(epoint, epoint)</code> and <code>distance(ecluster, epoint)</code> can
297 be used as an alias for this operator.</p>
299 <p>Note: In case of radial searches with a fixed radius, this operator should
300 not be used. Instead, an <code>ecircle</code> should be created and used in combination
301 with the overlap operator (<code>&&</code>). Alternatively, the functions
302 <code>distance_within(epoint, epoint, float8)</code> or <code>distance_within(ecluster, epoint,
303 float8)</code> can be used for fixed-radius searches.</p>
305 <h3>4. Functions</h3>
307 <h4><code>center(circle)</code></h4>
309 <p>Returns the center of an <code>ecircle</code> as an <code>epoint</code>.</p>
311 <h4><code>distance(epoint, epoint)</code></h4>
313 <p>Calculates the distance between two <code>epoint</code> datums in meters. This function is
314 an alias for the distance operator <code><-></code>.</p>
316 <p>Note: In case of radial searches with a fixed radius, this function should not be
317 used. Use <code>distance_within(epoint, epoint, float8)</code> instead.</p>
319 <h4><code>distance(ecluster, epoint)</code></h4>
321 <p>Calculates the distance from an <code>ecluster</code> to an <code>epoint</code> in meters. This
322 function is an alias for the distance operator <code><-></code>.</p>
324 <p>Note: In case of radial searches with a fixed radius, this function should not be
325 used. Use <code>distance_within(epoint, epoint, float8)</code> instead.</p>
327 <h4><code>distance_within(</code>variable <code>epoint,</code> fixed <code>epoint,</code> radius <code>float8)</code></h4>
329 <p>Checks if the distance between two <code>epoint</code> datums is not greater than a given
330 value (search radius).</p>
332 <p>Note: In case of radial searches with a fixed radius, the first argument must
333 be used for the table column, while the second argument must be used for the
334 search center. Otherwise an existing index cannot be used.</p>
336 <h4><code>distance_within(</code>variable <code>ecluster,</code> fixed <code>epoint,</code> radius <code>float8)</code></h4>
338 <p>Checks if the distance from an <code>ecluster</code> to an <code>epoint</code> is not greater than a
339 given value (search radius).</p>
341 <h4><code>ebox(</code>latmin <code>float8,</code> latmax <code>float8,</code> lonmin <code>float8,</code> lonmax <code>float8)</code></h4>
343 <p>Creates a new <code>ebox</code> with the given boundaries.
344 See "1. Types", subsection <code>ebox</code> for details.</p>
346 <h4><code>ebox(epoint, epoint)</code></h4>
348 <p>Creates a new <code>ebox</code>. This function may only be used if the longitude
349 difference is less than or equal to 120 degrees.
350 See "1. Types", subsection <code>ebox</code> for details.</p>
352 <h4><code>ecircle(epoint, float8)</code></h4>
354 <p>Creates an <code>ecircle</code> with the given center point and radius.</p>
356 <h4><code>ecircle(</code>latitude <code>float8,</code> longitude <code>float8,</code> radius <code>float8)</code></h4>
358 <p>Creates an <code>ecircle</code> with the given center point and radius.</p>
360 <h4><code>ecluster_concat(ecluster, ecluster)</code></h4>
362 <p>Combines two clusters to form a new <code>ecluster</code> by uniting all entries of both
363 clusters. Note that two overlapping areas of polygons annihilate each other
364 (which may be used to create polygons with holes).</p>
366 <h4><code>ecluster_concat(ecluster[])</code></h4>
368 <p>Creates a new <code>ecluster</code> that unites all entries of all clusters in the passed
369 array. Note that two overlapping areas of polygons annihilate each other (which
370 may be used to create polygons with holes).</p>
372 <h4><code>ecluster_create_multipoint(epoint[])</code></h4>
374 <p>Creates a new <code>ecluster</code> which contains multiple points.</p>
376 <h4><code>ecluster_create_outline(epoint[])</code></h4>
378 <p>Creates a new <code>ecluster</code> that is an outline given by the passed points.</p>
380 <h4><code>ecluster_create_path(epoint[])</code></h4>
382 <p>Creates a new <code>ecluster</code> that is a path given by the passed points.</p>
384 <h4><code>ecluster_create_polygon(epoint[])</code></h4>
386 <p>Creates a new <code>ecluster</code> that is a polygon given by the passed points.</p>
388 <h4><code>ecluster_extract_outlines(ecluster)</code></h4>
390 <p>Set-returning function that returns the outlines of an <code>ecluster</code> as <code>epoint[]</code>
391 rows.</p>
393 <h4><code>ecluster_extract_paths(ecluster)</code></h4>
395 <p>Set-returning function that returns the paths of an <code>ecluster</code> as <code>epoint[]</code>
396 rows.</p>
398 <h4><code>ecluster_extract_points(ecluster)</code></h4>
400 <p>Set-returning function that returns the points of an <code>ecluster</code> as <code>epoint</code>
401 rows.</p>
403 <h4><code>ecluster_extract_polygons(ecluster)</code></h4>
405 <p>Set-returning function that returns the polygons of an <code>ecluster</code> as <code>epoint[]</code>
406 rows.</p>
408 <h4><code>empty_ebox</code>()</h4>
410 <p>Returns the empty <code>ebox</code>.
411 See "1. Types", subsection <code>ebox</code> for details.</p>
413 <h4><code>epoint(</code>latitude <code>float8,</code> longitude <code>float8)</code></h4>
415 <p>Returns an <code>epoint</code> with the given latitude and longitude.</p>
417 <h4><code>epoint_latlon(</code>latitude <code>float8,</code> longitude <code>float8)</code></h4>
419 <p>Alias for <code>epoint(float8, float8)</code>.</p>
421 <h4><code>epoint_lonlat(</code>longitude <code>float8,</code> latitude <code>float8)</code></h4>
423 <p>Same as <code>epoint(float8, float8)</code> but with arguments reversed.</p>
425 <h4><code>GeoJSON_to_epoint(jsonb, text)</code></h4>
427 <p>Maps a GeoJSON object of type "Point" or "Feature" (which contains a
428 "Point") to an <code>epoint</code> datum. For any other JSON objects, NULL is returned.</p>
430 <p>The second parameter (which defaults to <code>epoint_lonlat</code>) may be set to a name
431 of a conversion function that transforms two coordinates (two <code>float8</code>
432 parameters) to an <code>epoint</code>.</p>
434 <h4><code>GeoJSON_to_ecluster(jsonb, text)</code></h4>
436 <p>Maps a (valid) GeoJSON object to an <code>ecluster</code>. Note that this function
437 does not check whether the JSONB object is a valid GeoJSON object.</p>
439 <p>The second parameter (which defaults to <code>epoint_lonlat</code>) may be set to a name
440 of a conversion function that transforms two coordinates (two <code>float8</code>
441 parameters) to an <code>epoint</code>.</p>
443 <h4><code>max_latitude(ebox)</code></h4>
445 <p>Returns the northern boundary of a given <code>ebox</code> in degrees between -90 and +90.</p>
447 <h4><code>max_longitude(ebox)</code></h4>
449 <p>Returns the eastern boundary of a given <code>ebox</code> in degrees between -180 and +180
450 (both inclusive).</p>
452 <h4><code>min_latitude(ebox)</code></h4>
454 <p>Returns the southern boundary of a given <code>ebox</code> in degrees between -90 and +90.</p>
456 <h4><code>min_longitude(ebox)</code></h4>
458 <p>Returns the western boundary of a given <code>ebox</code> in degrees between -180 and +180
459 (both inclusive).</p>
461 <h4><code>latitude(epoint)</code></h4>
463 <p>Returns the latitude value of an <code>epoint</code> in degrees between -90 and +90.</p>
465 <h4><code>longitude(epoint)</code></h4>
467 <p>Returns the longitude value of an <code>epoint</code> in degrees between -180 and +180
468 (both inclusive).</p>
470 <h4><code>radius(ecircle)</code></h4>
472 <p>Returns the radius of an <code>ecircle</code> in meters.</p>
473 </body></html>