## pgLatLon

### view README.mkd @ 10:684a78d2f9f0

Introduced lossy overlap operator (&&+) and fixed ecircle overlap searches on GiST-indexed ecluster columns

author | jbe |
---|---|

date | Fri Sep 02 14:08:01 2016 +0200 (2016-09-02) |

parents | 2b7aea022117 |

children | 4489b4d7d9c8 |

line source

1 pgLatLon v0.3 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 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.

15 pgLatLon is a lightweight solution as it only depends on PostgreSQL itself (and

16 a C compiler for building).

18 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.

23 Installation

24 ------------

26 ### Automatic installation

28 Prerequisites:

30 * Ensure that the `pg_config` binary is in your path (shipped with PostgreSQL).

31 * Ensure that GNU Make is available (either as `make` or `gmake`).

33 Then simply type:

35 make install

37 ### Manual installation

39 It is also possible to compile and install the extension without GNU Make as

40 follows:

42 cc -Wall -O2 -fPIC -shared -I `pg_config --includedir-server` -o latlon-v0002.so latlon-v0002.c

43 cp latlon-v0002.so `pg_config --pkglibdir`

44 cp latlon.control `pg_config --sharedir`/extension/

45 cp latlon--0.1--0.2.sql latlon--0.2.sql `pg_config --sharedir`/extension/

47 ### Loading the extension

49 After installation, you can create a database and load the extension as

50 follows:

52 % createdb test_database

53 % psql test_database

54 psql (9.5.4)

55 Type "help" for help.

57 test_database=# CREATE EXTENSION latlon;

60 Reference

61 ---------

63 ### 1. Types

65 pgLatLon provides four geographic types: `epoint`, `ebox`, `ecircle`, and

66 `ecluster`.

68 #### `epoint`

70 A point on the earth spheroid (WGS-84).

72 The text input format is `'[N|S]<float> [E|W]<float>'`, where each float is in

73 degrees. Note the required white space between the latitude and longitude

74 components. Each floating point number may have a sign, in which case `N`/`S`

75 or `E`/`W` are switched respectively (e.g. `E-5` is the same as `W5`).

77 An `epoint` may also be created from two floating point numbers by calling

78 `epoint(latitude, longitude)`, where positive latitudes are used for the

79 northern hemisphere, negative latitudes are used for the southern hemisphere,

80 positive longitudes indicate positions east of the prime meridian, and negative

81 longitudes indicate positions west of the prime meridian.

83 Latitudes exceeding -90 or +90 degrees are truncated to -90 or +90

84 respectively, in which case a warning will be issued. Longitudes exceeding -180

85 or +180 degrees will be converted to values between -180 and +180 (both

86 inclusive) by adding or substracting a multiple of 360 degrees, in which case a

87 notice will be issued.

89 If the latitude is -90 or +90 (south pole or north pole), a longitude value is

90 still stored in the datum, and if a point is on the prime meridian or the

91 180th meridian, the east/west bit is also stored in the datum. In case of the

92 prime meridian, this is done by storing a floating point value of -0 for

93 0 degrees west and a value of +0 for 0 degrees east. In case of the

94 180th meridian, this is done by storing -180 or +180 respectively. The equality

95 operator, however, returns true when the same points on earth are described,

96 i.e. the longitude is ignored for the poles, and 180 degrees west is considered

97 to be equal to 180 degrees east.

99 #### `ebox`

101 An area on earth demarcated by a southern and northern latitude, and a western

102 and eastern longitude (all given in WGS-84).

104 The text input format is

105 `'{N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float>'`, where each float is in

106 degrees. The ordering of the four white-space separated blocks is not

107 significant. To include the 180th meridian, one longitude boundary must be

108 equal to or exceed `W180` or `E180`, e.g. `'N10 N20 E170 E190'`.

110 A special value is the empty area, denoted by the text represenation `'empty'`.

111 Such an `ebox` does not contain any point.

113 An `ebox` may also be created from four floating point numbers by calling

114 `ebox(min_latitude, max_latitude, min_longitude, max_longitude)`, where

115 positive values are used for north and east, and negative values are used for

116 south and west. If `min_latitude` is strictly greater than `max_latitude`, an

117 empty `ebox` is created. If `min_longitude` is greater than `max_longitude` and

118 if both longitudes are between -180 and +180 degrees, then the area oriented in

119 such way that the 180th meridian is included.

121 If the longitude span is less than 120 degrees, an `ebox` may be alternatively

122 created from two `epoints` in the following way: `ebox(epoint(lat1, lon1),

123 epoint(lat2, lon2))`. In this case `lat1` and `lat2` as well as `lon1` and

124 `lon2` can be swapped without any impact.

126 #### `ecircle`

128 An area containing all points not farther away from a given center point

129 (WGS-84) than a given radius.

131 The text input format is `'{N|S}<float> {E|W}<float> <float>'`, where the first

132 two floats denote the center point in degrees and the third float denotes the

133 radius in meters. A radius equal to minus infinity denotes an empty circle

134 which contains no point at all (despite having a center), while a radius equal

135 to zero denotes a circle that includes a single point.

137 An `ecircle` may also be created by calling `ecircle(epoint(...), radius)` or

138 from three floating point numbers by calling `ecircle(latitude, longitude,

139 radius)`.

141 #### `ecluster`

143 A collection of points, paths, polygons, and outlines on the WGS-84 spheroid.

144 Each path, polygon, or outline must cover a longitude range of less than

145 180 degrees to avoid ambiguities.

147 The text input format is a white-space separated list of the following items:

149 * `point ({N|S}<float> {E|W}<float>)`

150 * `path ({N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> ...)`

151 * `outline ({N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> ...)`

152 * `polygon ({N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> {N|S}<float> {E|W}<float> ...)`

154 Paths are open by default (i.e. there is no connection from the last point in

155 the list to the first point in the list). Outlines and polygons, in contrast,

156 are automatically closed (i.e. there is a line segment from the last point in

157 the list to the first point in the list) which means the first point should not

158 be repeated as last point in the list. Polygons are filled, outlines are not.

160 ### 2. Indices

162 Two kinds of indices are supported: B-tree and GiST indices.

164 #### B-tree indices

166 A B-tree index can be used for simple equality searches and is supported by the

167 `epoint`, `ebox`, and `ecircle` data types. B-tree indices can not be used for

168 geographic searches.

170 #### GiST indices

172 For geographic searches, GiST indices must be used. The `epoint`, `ecircle`,

173 and `ecluster` data types support GiST indexing. A GiST index for geographic

174 searches can be created as follows:

176 CREATE TABLE tbl (

177 id serial4 PRIMARY KEY,

178 loc epoint NOT NULL );

180 CREATE INDEX name_of_index ON tbl USING gist (loc);

182 GiST indices also support nearest neighbor searches when using the distance

183 operator (`<->`) in the ORDER BY clause.

185 #### Indices on other data types (e.g. GeoJSON)

187 Note that further types can be indexed by using an index on an expression with

188 a conversion function. One conversion function provided by pgLatLon is the

189 `GeoJSON_to_ecluster(float8, float8, text)` function:

191 CREATE TABLE tbl (

192 id serial4 PRIMARY KEY,

193 loc jsonb NOT NULL );

195 CREATE INDEX name_of_index ON tbl USING gist((GeoJSON_to_ecluster("loc")));

197 When using the conversion function in an expression, the index will be used

198 automatically:

200 SELECT * FROM tbl WHERE GeoJSON_to_ecluster("loc") && 'N50 E10 10000'::ecircle;

202 ### 3. Operators

204 #### Equality operator `=`

206 Tests if two geographic objects are equal.

208 The longitude is ignored for the poles, and 180 degrees west is considered to

209 be equal to 180 degrees east.

211 For boxes and circles, two empty objects are considered equal. (Note that a

212 circle is not empty if the radius is zero but only if it is negative infinity,

213 i.e. smaller than zero.) Two circles with a positive infinite radius are also

214 considered equal.

216 Implemented for:

218 * `epoint = epoint`

219 * `ebox = ebox`

220 * `ecircle = ecircle`

222 The negation is the inequality operator (`<>` or `!=`).

224 #### Linear ordering operators `<<<`, `<<<=`, `>>>=`, `>>>`

226 These operators create an arbitrary (but well-defined) linear ordering of

227 geographic objects, which is used internally for B-tree indexing and merge

228 joins. These operators will usually not be used by an application programmer.

230 #### Overlap operator `&&`

232 Tests if two geographic objects have at least one point in common. Currently

233 implemented for:

235 * `epoint && ebox`

236 * `epoint && ecircle`

237 * `epoint && ecluster`

238 * `ebox && ebox`

239 * `ecircle && ecircle`

240 * `ecircle && ecluster`

242 The `&&` operator is commutative, i.e. `a && b` is the same as `b && a`. Each

243 commutation is supported as well.

245 #### Lossy overlap operator `&&+`

247 Tests if two geographic objects may have at least one point in common. Opposed

248 to the `&&` operator, the `&&+` operator may return false positives and is

249 currently implemented for:

251 * `epoint &&+ ecluster`

252 * `ebox &&+ ecircle`

253 * `ebox &&+ ecluster`

254 * `ecircle &&+ ecluster`

255 * `ecluster &&+ ecluster`

257 The `&&+` operator is commutative, i.e. `a &&+ b` is the same as `b &&+ a`. Each

258 commutation is supported as well.

260 Where two data types support both the `&&` and the `&&+` operator, the `&&+`

261 operator computes faster.

263 #### Distance operator `<->`

265 Calculates the shortest distance between two geographic objects in meters (zero

266 if the objects are overlapping). Currently implemented for:

268 * `epoint <-> epoint`

269 * `epoint <-> ecircle`

270 * `epoint <-> ecluster`

271 * `ecircle <-> ecircle`

272 * `ecircle <-> ecluster`

274 The `<->` operator is commutative, i.e. `a <-> b` is the same as `b <-> a`.

275 Each commutation is supported as well.

277 For short distances, the result is very accurate (i.e. respects the dimensions

278 of the WGS-84 spheroid). For longer distances in the order of magnitude of

279 earth's radius or greater, the value is only approximate (but the error is

280 still less than 0.2% as long as no polygons with very long edges are involved).

282 The functions `distance(epoint, epoint)` and `distance(ecluster, epoint)` can

283 be used as an alias for this operator.

285 Note: In case of radial searches with a fixed radius, this operator should

286 not be used. Instead, an `ecircle` should be created and used in combination

287 with the overlap operator (`&&`). Alternatively, the functions

288 `distance_within(epoint, epoint, float8)` or `distance_within(ecluster, epoint,

289 float8)` can be used for fixed-radius searches.

291 ### 4. Functions

293 #### `center(circle)`

295 Returns the center of an `ecircle` as an `epoint`.

297 #### `distance(epoint, epoint)`

299 Calculates the distance between two `epoint` datums in meters. This function is

300 an alias for the distance operator `<->`.

302 Note: In case of radial searches with a fixed radius, this function should not be

303 used. Use `distance_within(epoint, epoint, float8)` instead.

305 #### `distance(ecluster, epoint)`

307 Calculates the distance from an `ecluster` to an `epoint` in meters. This

308 function is an alias for the distance operator `<->`.

310 Note: In case of radial searches with a fixed radius, this function should not be

311 used. Use `distance_within(epoint, epoint, float8)` instead.

313 #### `distance_within(`variable `epoint,` fixed `epoint,` radius `float8)`

315 Checks if the distance between two `epoint` datums is not greater than a given

316 value (search radius).

318 Note: In case of radial searches with a fixed radius, the first argument must

319 be used for the table column, while the second argument must be used for the

320 search center. Otherwise an existing index cannot be used.

322 #### `distance_within(`variable `ecluster,` fixed `epoint,` radius `float8)`

324 Checks if the distance from an `ecluster` to an `epoint` is not greater than a

325 given value (search radius).

327 #### `ebox(`latmin `float8,` latmax `float8,` lonmin `float8,` lonmax `float8)`

329 Creates a new `ebox` with the given boundaries.

330 See "1. Types", subsection `ebox` for details.

332 #### `ebox(epoint, epoint)`

334 Creates a new `ebox`. This function may only be used if the longitude

335 difference is less than or equal to 120 degrees.

336 See "1. Types", subsection `ebox` for details.

338 #### `ecircle(epoint, float8)`

340 Creates an `ecircle` with the given center point and radius.

342 #### `ecircle(`latitude `float8,` longitude `float8,` radius `float8)`

344 Creates an `ecircle` with the given center point and radius.

346 #### `ecluster_concat(ecluster, ecluster)`

348 Combines two clusters to form a new `ecluster` by uniting all entries of both

349 clusters. Note that two overlapping areas of polygons annihilate each other

350 (which may be used to create polygons with holes).

352 #### `ecluster_concat(ecluster[])`

354 Creates a new `ecluster` that unites all entries of all clusters in the passed

355 array. Note that two overlapping areas of polygons annihilate each other (which

356 may be used to create polygons with holes).

358 #### `ecluster_create_multipoint(epoint[])`

360 Creates a new `ecluster` which contains multiple points.

362 #### `ecluster_create_outline(epoint[])`

364 Creates a new `ecluster` that is an outline given by the passed points.

366 #### `ecluster_create_path(epoint[])`

368 Creates a new `ecluster` that is a path given by the passed points.

370 #### `ecluster_create_polygon(epoint[])`

372 Creates a new `ecluster` that is a polygon given by the passed points.

374 #### `ecluster_extract_outlines(ecluster)`

376 Set-returning function that returns the outlines of an `ecluster` as `epoint[]`

377 rows.

379 #### `ecluster_extract_paths(ecluster)`

381 Set-returning function that returns the paths of an `ecluster` as `epoint[]`

382 rows.

384 #### `ecluster_extract_points(ecluster)`

386 Set-returning function that returns the points of an `ecluster` as `epoint`

387 rows.

389 #### `ecluster_extract_polygons(ecluster)`

391 Set-returning function that returns the polygons of an `ecluster` as `epoint[]`

392 rows.

394 #### `empty_ebox`()

396 Returns the empty `ebox`.

397 See "1. Types", subsection `ebox` for details.

399 #### `epoint(`latitude `float8,` longitude `float8)`

401 Returns an `epoint` with the given latitude and longitude.

403 #### `epoint_latlon(`latitude `float8,` longitude `float8)`

405 Alias for `epoint(float8, float8)`.

407 #### `epoint_lonlat(`longitude `float8,` latitude `float8)`

409 Same as `epoint(float8, float8)` but with arguments reversed.

411 #### `GeoJSON_to_epoint(jsonb, text)`

413 Maps a GeoJSON object of type "Point" or "Feature" (which contains a

414 "Point") to an `epoint` datum. For any other JSON objects, NULL is returned.

416 The second parameter (which defaults to `epoint_lonlat`) may be set to a name

417 of a conversion function that transforms two coordinates (two `float8`

418 parameters) to an `epoint`.

420 #### `GeoJSON_to_ecluster(jsonb, text)`

422 Maps a (valid) GeoJSON object to an `ecluster`. Note that this function

423 does not check whether the JSONB object is a valid GeoJSON object.

425 The second parameter (which defaults to `epoint_lonlat`) may be set to a name

426 of a conversion function that transforms two coordinates (two `float8`

427 parameters) to an `epoint`.

429 #### `max_latitude(ebox)`

431 Returns the northern boundary of a given `ebox` in degrees between -90 and +90.

433 #### `max_longitude(ebox)`

435 Returns the eastern boundary of a given `ebox` in degrees between -180 and +180

436 (both inclusive).

438 #### `min_latitude(ebox)`

440 Returns the southern boundary of a given `ebox` in degrees between -90 and +90.

442 #### `min_longitude(ebox)`

444 Returns the western boundary of a given `ebox` in degrees between -180 and +180

445 (both inclusive).

447 #### `latitude(epoint)`

449 Returns the latitude value of an `epoint` in degrees between -90 and +90.

451 #### `longitude(epoint)`

453 Returns the longitude value of an `epoint` in degrees between -180 and +180

454 (both inclusive).

456 #### `radius(ecircle)`

458 Returns the radius of an `ecircle` in meters.