pgLatLon
view latlon--0.12.sql @ 59:a011d71ae717
Changed year in LICENSE file
| author | jbe |
|---|---|
| date | Sun Feb 09 17:43:04 2020 +0100 (2020-02-09) |
| parents | 253218d6662d |
| children |
line source
2 ----------------------------------------
3 -- forward declarations (shell types) --
4 ----------------------------------------
6 CREATE TYPE ekey_point;
7 CREATE TYPE ekey_area;
8 CREATE TYPE epoint;
9 CREATE TYPE epoint_with_sample_count;
10 CREATE TYPE ebox;
11 CREATE TYPE ecircle;
12 CREATE TYPE ecluster;
15 ------------------------------------------------------------
16 -- dummy input/output functions for dummy index key types --
17 ------------------------------------------------------------
19 CREATE FUNCTION ekey_point_in_dummy(cstring)
20 RETURNS ekey_point
21 LANGUAGE C IMMUTABLE STRICT
22 AS '$libdir/latlon-v0009', 'pgl_notimpl';
24 CREATE FUNCTION ekey_point_out_dummy(ekey_point)
25 RETURNS cstring
26 LANGUAGE C IMMUTABLE STRICT
27 AS '$libdir/latlon-v0009', 'pgl_notimpl';
29 CREATE FUNCTION ekey_area_in_dummy(cstring)
30 RETURNS ekey_area
31 LANGUAGE C IMMUTABLE STRICT
32 AS '$libdir/latlon-v0009', 'pgl_notimpl';
34 CREATE FUNCTION ekey_area_out_dummy(ekey_area)
35 RETURNS cstring
36 LANGUAGE C IMMUTABLE STRICT
37 AS '$libdir/latlon-v0009', 'pgl_notimpl';
40 --------------------------
41 -- text input functions --
42 --------------------------
44 CREATE FUNCTION epoint_in(cstring)
45 RETURNS epoint
46 LANGUAGE C IMMUTABLE STRICT
47 AS '$libdir/latlon-v0009', 'pgl_epoint_in';
49 CREATE FUNCTION epoint_with_sample_count_in(cstring)
50 RETURNS epoint_with_sample_count
51 LANGUAGE C IMMUTABLE STRICT
52 AS '$libdir/latlon-v0009', 'pgl_epoint_with_sample_count_in';
54 CREATE FUNCTION ebox_in(cstring)
55 RETURNS ebox
56 LANGUAGE C IMMUTABLE STRICT
57 AS '$libdir/latlon-v0009', 'pgl_ebox_in';
59 CREATE FUNCTION ecircle_in(cstring)
60 RETURNS ecircle
61 LANGUAGE C IMMUTABLE STRICT
62 AS '$libdir/latlon-v0009', 'pgl_ecircle_in';
64 CREATE FUNCTION ecluster_in(cstring)
65 RETURNS ecluster
66 LANGUAGE C IMMUTABLE STRICT
67 AS '$libdir/latlon-v0009', 'pgl_ecluster_in';
70 ---------------------------
71 -- text output functions --
72 ---------------------------
74 CREATE FUNCTION epoint_out(epoint)
75 RETURNS cstring
76 LANGUAGE C IMMUTABLE STRICT
77 AS '$libdir/latlon-v0009', 'pgl_epoint_out';
79 CREATE FUNCTION epoint_with_sample_count_out(epoint_with_sample_count)
80 RETURNS cstring
81 LANGUAGE C IMMUTABLE STRICT
82 AS '$libdir/latlon-v0009', 'pgl_epoint_with_sample_count_out';
84 CREATE FUNCTION ebox_out(ebox)
85 RETURNS cstring
86 LANGUAGE C IMMUTABLE STRICT
87 AS '$libdir/latlon-v0009', 'pgl_ebox_out';
89 CREATE FUNCTION ecircle_out(ecircle)
90 RETURNS cstring
91 LANGUAGE C IMMUTABLE STRICT
92 AS '$libdir/latlon-v0009', 'pgl_ecircle_out';
94 CREATE FUNCTION ecluster_out(ecluster)
95 RETURNS cstring
96 LANGUAGE C IMMUTABLE STRICT
97 AS '$libdir/latlon-v0009', 'pgl_ecluster_out';
100 --------------------------
101 -- binary I/O functions --
102 --------------------------
104 CREATE FUNCTION epoint_recv(internal)
105 RETURNS epoint
106 LANGUAGE C IMMUTABLE STRICT
107 AS '$libdir/latlon-v0009', 'pgl_epoint_recv';
109 CREATE FUNCTION ebox_recv(internal)
110 RETURNS ebox
111 LANGUAGE C IMMUTABLE STRICT
112 AS '$libdir/latlon-v0009', 'pgl_ebox_recv';
114 CREATE FUNCTION ecircle_recv(internal)
115 RETURNS ecircle
116 LANGUAGE C IMMUTABLE STRICT
117 AS '$libdir/latlon-v0009', 'pgl_ecircle_recv';
119 CREATE FUNCTION epoint_send(epoint)
120 RETURNS bytea
121 LANGUAGE C IMMUTABLE STRICT
122 AS '$libdir/latlon-v0009', 'pgl_epoint_send';
124 CREATE FUNCTION ebox_send(ebox)
125 RETURNS bytea
126 LANGUAGE C IMMUTABLE STRICT
127 AS '$libdir/latlon-v0009', 'pgl_ebox_send';
129 CREATE FUNCTION ecircle_send(ecircle)
130 RETURNS bytea
131 LANGUAGE C IMMUTABLE STRICT
132 AS '$libdir/latlon-v0009', 'pgl_ecircle_send';
135 -----------------------------------------------
136 -- type definitions of dummy index key types --
137 -----------------------------------------------
139 CREATE TYPE ekey_point (
140 internallength = 8,
141 input = ekey_point_in_dummy,
142 output = ekey_point_out_dummy,
143 alignment = char );
145 CREATE TYPE ekey_area (
146 internallength = 9,
147 input = ekey_area_in_dummy,
148 output = ekey_area_out_dummy,
149 alignment = char );
152 ------------------------------------------
153 -- definitions of geographic data types --
154 ------------------------------------------
156 CREATE TYPE epoint (
157 internallength = 16,
158 input = epoint_in,
159 output = epoint_out,
160 receive = epoint_recv,
161 send = epoint_send,
162 alignment = double );
164 CREATE TYPE epoint_with_sample_count (
165 internallength = 20,
166 input = epoint_with_sample_count_in,
167 output = epoint_with_sample_count_out,
168 alignment = double );
170 CREATE TYPE ebox (
171 internallength = 32,
172 input = ebox_in,
173 output = ebox_out,
174 receive = ebox_recv,
175 send = ebox_send,
176 alignment = double );
178 CREATE TYPE ecircle (
179 internallength = 24,
180 input = ecircle_in,
181 output = ecircle_out,
182 receive = ecircle_recv,
183 send = ecircle_send,
184 alignment = double );
186 CREATE TYPE ecluster (
187 internallength = VARIABLE,
188 input = ecluster_in,
189 output = ecluster_out,
190 alignment = double,
191 storage = external );
194 --------------------
195 -- B-tree support --
196 --------------------
198 -- begin of B-tree support for epoint
200 CREATE FUNCTION epoint_btree_lt(epoint, epoint)
201 RETURNS boolean
202 LANGUAGE C IMMUTABLE STRICT
203 AS '$libdir/latlon-v0009', 'pgl_btree_epoint_lt';
205 CREATE FUNCTION epoint_btree_le(epoint, epoint)
206 RETURNS boolean
207 LANGUAGE C IMMUTABLE STRICT
208 AS '$libdir/latlon-v0009', 'pgl_btree_epoint_le';
210 CREATE FUNCTION epoint_btree_eq(epoint, epoint)
211 RETURNS boolean
212 LANGUAGE C IMMUTABLE STRICT
213 AS '$libdir/latlon-v0009', 'pgl_btree_epoint_eq';
215 CREATE FUNCTION epoint_btree_ne(epoint, epoint)
216 RETURNS boolean
217 LANGUAGE C IMMUTABLE STRICT
218 AS '$libdir/latlon-v0009', 'pgl_btree_epoint_ne';
220 CREATE FUNCTION epoint_btree_ge(epoint, epoint)
221 RETURNS boolean
222 LANGUAGE C IMMUTABLE STRICT
223 AS '$libdir/latlon-v0009', 'pgl_btree_epoint_ge';
225 CREATE FUNCTION epoint_btree_gt(epoint, epoint)
226 RETURNS boolean
227 LANGUAGE C IMMUTABLE STRICT
228 AS '$libdir/latlon-v0009', 'pgl_btree_epoint_gt';
230 CREATE OPERATOR <<< (
231 leftarg = epoint,
232 rightarg = epoint,
233 procedure = epoint_btree_lt,
234 commutator = >>>,
235 negator = >>>=,
236 restrict = scalarltsel,
237 join = scalarltjoinsel
238 );
240 CREATE OPERATOR <<<= (
241 leftarg = epoint,
242 rightarg = epoint,
243 procedure = epoint_btree_le,
244 commutator = >>>=,
245 negator = >>>,
246 restrict = scalarltsel,
247 join = scalarltjoinsel
248 );
250 CREATE OPERATOR = (
251 leftarg = epoint,
252 rightarg = epoint,
253 procedure = epoint_btree_eq,
254 commutator = =,
255 negator = <>,
256 restrict = eqsel,
257 join = eqjoinsel,
258 merges
259 );
261 CREATE OPERATOR <> (
262 leftarg = epoint,
263 rightarg = epoint,
264 procedure = epoint_btree_ne,
265 commutator = <>,
266 negator = =,
267 restrict = neqsel,
268 join = neqjoinsel
269 );
271 CREATE OPERATOR >>>= (
272 leftarg = epoint,
273 rightarg = epoint,
274 procedure = epoint_btree_ge,
275 commutator = <<<=,
276 negator = <<<,
277 restrict = scalargtsel,
278 join = scalargtjoinsel
279 );
281 CREATE OPERATOR >>> (
282 leftarg = epoint,
283 rightarg = epoint,
284 procedure = epoint_btree_gt,
285 commutator = <<<,
286 negator = <<<=,
287 restrict = scalargtsel,
288 join = scalargtjoinsel
289 );
291 CREATE FUNCTION epoint_btree_cmp(epoint, epoint)
292 RETURNS int4
293 LANGUAGE C IMMUTABLE STRICT
294 AS '$libdir/latlon-v0009', 'pgl_btree_epoint_cmp';
296 CREATE OPERATOR CLASS epoint_btree_ops
297 DEFAULT FOR TYPE epoint USING btree AS
298 OPERATOR 1 <<< ,
299 OPERATOR 2 <<<= ,
300 OPERATOR 3 = ,
301 OPERATOR 4 >>>= ,
302 OPERATOR 5 >>> ,
303 FUNCTION 1 epoint_btree_cmp(epoint, epoint);
305 -- end of B-tree support for epoint
307 -- begin of B-tree support for ebox
309 CREATE FUNCTION ebox_btree_lt(ebox, ebox)
310 RETURNS boolean
311 LANGUAGE C IMMUTABLE STRICT
312 AS '$libdir/latlon-v0009', 'pgl_btree_ebox_lt';
314 CREATE FUNCTION ebox_btree_le(ebox, ebox)
315 RETURNS boolean
316 LANGUAGE C IMMUTABLE STRICT
317 AS '$libdir/latlon-v0009', 'pgl_btree_ebox_le';
319 CREATE FUNCTION ebox_btree_eq(ebox, ebox)
320 RETURNS boolean
321 LANGUAGE C IMMUTABLE STRICT
322 AS '$libdir/latlon-v0009', 'pgl_btree_ebox_eq';
324 CREATE FUNCTION ebox_btree_ne(ebox, ebox)
325 RETURNS boolean
326 LANGUAGE C IMMUTABLE STRICT
327 AS '$libdir/latlon-v0009', 'pgl_btree_ebox_ne';
329 CREATE FUNCTION ebox_btree_ge(ebox, ebox)
330 RETURNS boolean
331 LANGUAGE C IMMUTABLE STRICT
332 AS '$libdir/latlon-v0009', 'pgl_btree_ebox_ge';
334 CREATE FUNCTION ebox_btree_gt(ebox, ebox)
335 RETURNS boolean
336 LANGUAGE C IMMUTABLE STRICT
337 AS '$libdir/latlon-v0009', 'pgl_btree_ebox_gt';
339 CREATE OPERATOR <<< (
340 leftarg = ebox,
341 rightarg = ebox,
342 procedure = ebox_btree_lt,
343 commutator = >>>,
344 negator = >>>=,
345 restrict = scalarltsel,
346 join = scalarltjoinsel
347 );
349 CREATE OPERATOR <<<= (
350 leftarg = ebox,
351 rightarg = ebox,
352 procedure = ebox_btree_le,
353 commutator = >>>=,
354 negator = >>>,
355 restrict = scalarltsel,
356 join = scalarltjoinsel
357 );
359 CREATE OPERATOR = (
360 leftarg = ebox,
361 rightarg = ebox,
362 procedure = ebox_btree_eq,
363 commutator = =,
364 negator = <>,
365 restrict = eqsel,
366 join = eqjoinsel,
367 merges
368 );
370 CREATE OPERATOR <> (
371 leftarg = ebox,
372 rightarg = ebox,
373 procedure = ebox_btree_ne,
374 commutator = <>,
375 negator = =,
376 restrict = neqsel,
377 join = neqjoinsel
378 );
380 CREATE OPERATOR >>>= (
381 leftarg = ebox,
382 rightarg = ebox,
383 procedure = ebox_btree_ge,
384 commutator = <<<=,
385 negator = <<<,
386 restrict = scalargtsel,
387 join = scalargtjoinsel
388 );
390 CREATE OPERATOR >>> (
391 leftarg = ebox,
392 rightarg = ebox,
393 procedure = ebox_btree_gt,
394 commutator = <<<,
395 negator = <<<=,
396 restrict = scalargtsel,
397 join = scalargtjoinsel
398 );
400 CREATE FUNCTION ebox_btree_cmp(ebox, ebox)
401 RETURNS int4
402 LANGUAGE C IMMUTABLE STRICT
403 AS '$libdir/latlon-v0009', 'pgl_btree_ebox_cmp';
405 CREATE OPERATOR CLASS ebox_btree_ops
406 DEFAULT FOR TYPE ebox USING btree AS
407 OPERATOR 1 <<< ,
408 OPERATOR 2 <<<= ,
409 OPERATOR 3 = ,
410 OPERATOR 4 >>>= ,
411 OPERATOR 5 >>> ,
412 FUNCTION 1 ebox_btree_cmp(ebox, ebox);
414 -- end of B-tree support for ebox
416 -- begin of B-tree support for ecircle
418 CREATE FUNCTION ecircle_btree_lt(ecircle, ecircle)
419 RETURNS boolean
420 LANGUAGE C IMMUTABLE STRICT
421 AS '$libdir/latlon-v0009', 'pgl_btree_ecircle_lt';
423 CREATE FUNCTION ecircle_btree_le(ecircle, ecircle)
424 RETURNS boolean
425 LANGUAGE C IMMUTABLE STRICT
426 AS '$libdir/latlon-v0009', 'pgl_btree_ecircle_le';
428 CREATE FUNCTION ecircle_btree_eq(ecircle, ecircle)
429 RETURNS boolean
430 LANGUAGE C IMMUTABLE STRICT
431 AS '$libdir/latlon-v0009', 'pgl_btree_ecircle_eq';
433 CREATE FUNCTION ecircle_btree_ne(ecircle, ecircle)
434 RETURNS boolean
435 LANGUAGE C IMMUTABLE STRICT
436 AS '$libdir/latlon-v0009', 'pgl_btree_ecircle_ne';
438 CREATE FUNCTION ecircle_btree_ge(ecircle, ecircle)
439 RETURNS boolean
440 LANGUAGE C IMMUTABLE STRICT
441 AS '$libdir/latlon-v0009', 'pgl_btree_ecircle_ge';
443 CREATE FUNCTION ecircle_btree_gt(ecircle, ecircle)
444 RETURNS boolean
445 LANGUAGE C IMMUTABLE STRICT
446 AS '$libdir/latlon-v0009', 'pgl_btree_ecircle_gt';
448 CREATE OPERATOR <<< (
449 leftarg = ecircle,
450 rightarg = ecircle,
451 procedure = ecircle_btree_lt,
452 commutator = >>>,
453 negator = >>>=,
454 restrict = scalarltsel,
455 join = scalarltjoinsel
456 );
458 CREATE OPERATOR <<<= (
459 leftarg = ecircle,
460 rightarg = ecircle,
461 procedure = ecircle_btree_le,
462 commutator = >>>=,
463 negator = >>>,
464 restrict = scalarltsel,
465 join = scalarltjoinsel
466 );
468 CREATE OPERATOR = (
469 leftarg = ecircle,
470 rightarg = ecircle,
471 procedure = ecircle_btree_eq,
472 commutator = =,
473 negator = <>,
474 restrict = eqsel,
475 join = eqjoinsel,
476 merges
477 );
479 CREATE OPERATOR <> (
480 leftarg = ecircle,
481 rightarg = ecircle,
482 procedure = ecircle_btree_ne,
483 commutator = <>,
484 negator = =,
485 restrict = neqsel,
486 join = neqjoinsel
487 );
489 CREATE OPERATOR >>>= (
490 leftarg = ecircle,
491 rightarg = ecircle,
492 procedure = ecircle_btree_ge,
493 commutator = <<<=,
494 negator = <<<,
495 restrict = scalargtsel,
496 join = scalargtjoinsel
497 );
499 CREATE OPERATOR >>> (
500 leftarg = ecircle,
501 rightarg = ecircle,
502 procedure = ecircle_btree_gt,
503 commutator = <<<,
504 negator = <<<=,
505 restrict = scalargtsel,
506 join = scalargtjoinsel
507 );
509 CREATE FUNCTION ecircle_btree_cmp(ecircle, ecircle)
510 RETURNS int4
511 LANGUAGE C IMMUTABLE STRICT
512 AS '$libdir/latlon-v0009', 'pgl_btree_ecircle_cmp';
514 CREATE OPERATOR CLASS ecircle_btree_ops
515 DEFAULT FOR TYPE ecircle USING btree AS
516 OPERATOR 1 <<< ,
517 OPERATOR 2 <<<= ,
518 OPERATOR 3 = ,
519 OPERATOR 4 >>>= ,
520 OPERATOR 5 >>> ,
521 FUNCTION 1 ecircle_btree_cmp(ecircle, ecircle);
523 -- end of B-tree support for ecircle
526 ----------------
527 -- type casts --
528 ----------------
530 CREATE FUNCTION cast_epoint_to_ebox(epoint)
531 RETURNS ebox
532 LANGUAGE C IMMUTABLE STRICT
533 AS '$libdir/latlon-v0009', 'pgl_epoint_to_ebox';
535 CREATE CAST (epoint AS ebox) WITH FUNCTION cast_epoint_to_ebox(epoint);
537 CREATE FUNCTION cast_epoint_to_ecircle(epoint)
538 RETURNS ecircle
539 LANGUAGE C IMMUTABLE STRICT
540 AS '$libdir/latlon-v0009', 'pgl_epoint_to_ecircle';
542 CREATE CAST (epoint AS ecircle) WITH FUNCTION cast_epoint_to_ecircle(epoint);
544 CREATE FUNCTION cast_epoint_to_ecluster(epoint)
545 RETURNS ecluster
546 LANGUAGE C IMMUTABLE STRICT
547 AS '$libdir/latlon-v0009', 'pgl_epoint_to_ecluster';
549 CREATE CAST (epoint AS ecluster) WITH FUNCTION cast_epoint_to_ecluster(epoint);
551 CREATE FUNCTION cast_ebox_to_ecluster(ebox)
552 RETURNS ecluster
553 LANGUAGE C IMMUTABLE STRICT
554 AS '$libdir/latlon-v0009', 'pgl_ebox_to_ecluster';
556 CREATE CAST (ebox AS ecluster) WITH FUNCTION cast_ebox_to_ecluster(ebox);
559 ---------------------------
560 -- constructor functions --
561 ---------------------------
563 CREATE FUNCTION epoint(float8, float8)
564 RETURNS epoint
565 LANGUAGE C IMMUTABLE STRICT
566 AS '$libdir/latlon-v0009', 'pgl_create_epoint';
568 CREATE FUNCTION epoint_latlon(float8, float8)
569 RETURNS epoint
570 LANGUAGE SQL IMMUTABLE STRICT AS $$
571 SELECT epoint($1, $2)
572 $$;
574 CREATE FUNCTION epoint_lonlat(float8, float8)
575 RETURNS epoint
576 LANGUAGE SQL IMMUTABLE STRICT AS $$
577 SELECT epoint($2, $1)
578 $$;
580 CREATE FUNCTION epoint_with_sample_count(epoint, int4)
581 RETURNS epoint_with_sample_count
582 LANGUAGE C IMMUTABLE STRICT
583 AS '$libdir/latlon-v0009', 'pgl_create_epoint_with_sample_count';
585 CREATE FUNCTION empty_ebox()
586 RETURNS ebox
587 LANGUAGE C IMMUTABLE STRICT
588 AS '$libdir/latlon-v0009', 'pgl_create_empty_ebox';
590 CREATE FUNCTION ebox(float8, float8, float8, float8)
591 RETURNS ebox
592 LANGUAGE C IMMUTABLE STRICT
593 AS '$libdir/latlon-v0009', 'pgl_create_ebox';
595 CREATE FUNCTION ebox(epoint, epoint)
596 RETURNS ebox
597 LANGUAGE C IMMUTABLE STRICT
598 AS '$libdir/latlon-v0009', 'pgl_create_ebox_from_epoints';
600 CREATE FUNCTION ecircle(float8, float8, float8)
601 RETURNS ecircle
602 LANGUAGE C IMMUTABLE STRICT
603 AS '$libdir/latlon-v0009', 'pgl_create_ecircle';
605 CREATE FUNCTION ecircle(epoint, float8)
606 RETURNS ecircle
607 LANGUAGE C IMMUTABLE STRICT
608 AS '$libdir/latlon-v0009', 'pgl_create_ecircle_from_epoint';
610 CREATE FUNCTION ecluster_concat(ecluster[])
611 RETURNS ecluster
612 LANGUAGE sql IMMUTABLE STRICT AS $$
613 SELECT array_to_string($1, ' ')::ecluster
614 $$;
616 CREATE FUNCTION ecluster_concat(ecluster, ecluster)
617 RETURNS ecluster
618 LANGUAGE sql IMMUTABLE STRICT AS $$
619 SELECT ($1::text || ' ' || $2::text)::ecluster
620 $$;
622 CREATE FUNCTION ecluster_create_multipoint(epoint[])
623 RETURNS ecluster
624 LANGUAGE sql IMMUTABLE STRICT AS $$
625 SELECT
626 array_to_string(array_agg('point (' || unnest || ')'), ' ')::ecluster
627 FROM unnest($1)
628 $$;
630 CREATE FUNCTION ecluster_create_path(epoint[])
631 RETURNS ecluster
632 LANGUAGE sql IMMUTABLE STRICT AS $$
633 SELECT CASE WHEN "str" = '' THEN 'empty'::ecluster ELSE
634 ('path (' || array_to_string($1, ' ') || ')')::ecluster
635 END
636 FROM array_to_string($1, ' ') AS "str"
637 $$;
639 CREATE FUNCTION ecluster_create_outline(epoint[])
640 RETURNS ecluster
641 LANGUAGE sql IMMUTABLE STRICT AS $$
642 SELECT CASE WHEN "str" = '' THEN 'empty'::ecluster ELSE
643 ('outline (' || array_to_string($1, ' ') || ')')::ecluster
644 END
645 FROM array_to_string($1, ' ') AS "str"
646 $$;
648 CREATE FUNCTION ecluster_create_polygon(epoint[])
649 RETURNS ecluster
650 LANGUAGE sql IMMUTABLE STRICT AS $$
651 SELECT CASE WHEN "str" = '' THEN 'empty'::ecluster ELSE
652 ('polygon (' || array_to_string($1, ' ') || ')')::ecluster
653 END
654 FROM array_to_string($1, ' ') AS "str"
655 $$;
658 ----------------------
659 -- getter functions --
660 ----------------------
662 CREATE FUNCTION latitude(epoint)
663 RETURNS float8
664 LANGUAGE C IMMUTABLE STRICT
665 AS '$libdir/latlon-v0009', 'pgl_epoint_lat';
667 CREATE FUNCTION longitude(epoint)
668 RETURNS float8
669 LANGUAGE C IMMUTABLE STRICT
670 AS '$libdir/latlon-v0009', 'pgl_epoint_lon';
672 CREATE FUNCTION min_latitude(ebox)
673 RETURNS float8
674 LANGUAGE C IMMUTABLE STRICT
675 AS '$libdir/latlon-v0009', 'pgl_ebox_lat_min';
677 CREATE FUNCTION max_latitude(ebox)
678 RETURNS float8
679 LANGUAGE C IMMUTABLE STRICT
680 AS '$libdir/latlon-v0009', 'pgl_ebox_lat_max';
682 CREATE FUNCTION min_longitude(ebox)
683 RETURNS float8
684 LANGUAGE C IMMUTABLE STRICT
685 AS '$libdir/latlon-v0009', 'pgl_ebox_lon_min';
687 CREATE FUNCTION max_longitude(ebox)
688 RETURNS float8
689 LANGUAGE C IMMUTABLE STRICT
690 AS '$libdir/latlon-v0009', 'pgl_ebox_lon_max';
692 CREATE FUNCTION center(ecircle)
693 RETURNS epoint
694 LANGUAGE C IMMUTABLE STRICT
695 AS '$libdir/latlon-v0009', 'pgl_ecircle_center';
697 CREATE FUNCTION radius(ecircle)
698 RETURNS float8
699 LANGUAGE C IMMUTABLE STRICT
700 AS '$libdir/latlon-v0009', 'pgl_ecircle_radius';
702 CREATE FUNCTION ecluster_extract_points(ecluster)
703 RETURNS SETOF epoint
704 LANGUAGE sql IMMUTABLE STRICT AS $$
705 SELECT "match"[2]::epoint
706 FROM regexp_matches($1::text, e'(^| )point \\(([^)]+)\\)', 'g') AS "match"
707 $$;
709 CREATE FUNCTION ecluster_extract_paths(ecluster)
710 RETURNS SETOF epoint[]
711 LANGUAGE sql IMMUTABLE STRICT AS $$
712 SELECT (
713 SELECT array_agg("m2"[1]::epoint)
714 FROM regexp_matches("m1"[2], e'[^ ]+ [^ ]+', 'g') AS "m2"
715 )
716 FROM regexp_matches($1::text, e'(^| )path \\(([^)]+)\\)', 'g') AS "m1"
717 $$;
719 CREATE FUNCTION ecluster_extract_outlines(ecluster)
720 RETURNS SETOF epoint[]
721 LANGUAGE sql IMMUTABLE STRICT AS $$
722 SELECT (
723 SELECT array_agg("m2"[1]::epoint)
724 FROM regexp_matches("m1"[2], e'[^ ]+ [^ ]+', 'g') AS "m2"
725 )
726 FROM regexp_matches($1::text, e'(^| )outline \\(([^)]+)\\)', 'g') AS "m1"
727 $$;
729 CREATE FUNCTION ecluster_extract_polygons(ecluster)
730 RETURNS SETOF epoint[]
731 LANGUAGE sql IMMUTABLE STRICT AS $$
732 SELECT (
733 SELECT array_agg("m2"[1]::epoint)
734 FROM regexp_matches("m1"[2], e'[^ ]+ [^ ]+', 'g') AS "m2"
735 )
736 FROM regexp_matches($1::text, e'(^| )polygon \\(([^)]+)\\)', 'g') AS "m1"
737 $$;
740 ---------------
741 -- operators --
742 ---------------
744 CREATE FUNCTION epoint_ebox_overlap_proc(epoint, ebox)
745 RETURNS boolean
746 LANGUAGE C IMMUTABLE STRICT
747 AS '$libdir/latlon-v0009', 'pgl_epoint_ebox_overlap';
749 CREATE FUNCTION epoint_ecircle_overlap_proc(epoint, ecircle)
750 RETURNS boolean
751 LANGUAGE C IMMUTABLE STRICT
752 AS '$libdir/latlon-v0009', 'pgl_epoint_ecircle_overlap';
754 CREATE FUNCTION epoint_ecluster_overlap_proc(epoint, ecluster)
755 RETURNS boolean
756 LANGUAGE C IMMUTABLE STRICT
757 AS '$libdir/latlon-v0009', 'pgl_epoint_ecluster_overlap';
759 CREATE FUNCTION epoint_ecluster_may_overlap_proc(epoint, ecluster)
760 RETURNS boolean
761 LANGUAGE C IMMUTABLE STRICT
762 AS '$libdir/latlon-v0009', 'pgl_epoint_ecluster_may_overlap';
764 CREATE FUNCTION ebox_overlap_proc(ebox, ebox)
765 RETURNS boolean
766 LANGUAGE C IMMUTABLE STRICT
767 AS '$libdir/latlon-v0009', 'pgl_ebox_overlap';
769 CREATE FUNCTION ebox_ecircle_may_overlap_proc(ebox, ecircle)
770 RETURNS boolean
771 LANGUAGE C IMMUTABLE STRICT
772 AS '$libdir/latlon-v0009', 'pgl_ebox_ecircle_may_overlap';
774 CREATE FUNCTION ebox_ecluster_may_overlap_proc(ebox, ecluster)
775 RETURNS boolean
776 LANGUAGE C IMMUTABLE STRICT
777 AS '$libdir/latlon-v0009', 'pgl_ebox_ecluster_may_overlap';
779 CREATE FUNCTION ecircle_overlap_proc(ecircle, ecircle)
780 RETURNS boolean
781 LANGUAGE C IMMUTABLE STRICT
782 AS '$libdir/latlon-v0009', 'pgl_ecircle_overlap';
784 CREATE FUNCTION ecircle_ecluster_overlap_proc(ecircle, ecluster)
785 RETURNS boolean
786 LANGUAGE C IMMUTABLE STRICT
787 AS '$libdir/latlon-v0009', 'pgl_ecircle_ecluster_overlap';
789 CREATE FUNCTION ecircle_ecluster_may_overlap_proc(ecircle, ecluster)
790 RETURNS boolean
791 LANGUAGE C IMMUTABLE STRICT
792 AS '$libdir/latlon-v0009', 'pgl_ecircle_ecluster_may_overlap';
794 CREATE FUNCTION ecluster_overlap_proc(ecluster, ecluster)
795 RETURNS boolean
796 LANGUAGE C IMMUTABLE STRICT
797 AS '$libdir/latlon-v0009', 'pgl_ecluster_overlap';
799 CREATE FUNCTION ecluster_may_overlap_proc(ecluster, ecluster)
800 RETURNS boolean
801 LANGUAGE C IMMUTABLE STRICT
802 AS '$libdir/latlon-v0009', 'pgl_ecluster_may_overlap';
804 CREATE FUNCTION ecluster_contains_proc(ecluster, ecluster)
805 RETURNS boolean
806 LANGUAGE C IMMUTABLE STRICT
807 AS '$libdir/latlon-v0009', 'pgl_ecluster_contains';
809 CREATE FUNCTION epoint_distance_proc(epoint, epoint)
810 RETURNS float8
811 LANGUAGE C IMMUTABLE STRICT
812 AS '$libdir/latlon-v0009', 'pgl_epoint_distance';
814 CREATE FUNCTION epoint_ecircle_distance_proc(epoint, ecircle)
815 RETURNS float8
816 LANGUAGE C IMMUTABLE STRICT
817 AS '$libdir/latlon-v0009', 'pgl_epoint_ecircle_distance';
819 CREATE FUNCTION epoint_ecluster_distance_proc(epoint, ecluster)
820 RETURNS float8
821 LANGUAGE C IMMUTABLE STRICT
822 AS '$libdir/latlon-v0009', 'pgl_epoint_ecluster_distance';
824 CREATE FUNCTION ecircle_distance_proc(ecircle, ecircle)
825 RETURNS float8
826 LANGUAGE C IMMUTABLE STRICT
827 AS '$libdir/latlon-v0009', 'pgl_ecircle_distance';
829 CREATE FUNCTION ecircle_ecluster_distance_proc(ecircle, ecluster)
830 RETURNS float8
831 LANGUAGE C IMMUTABLE STRICT
832 AS '$libdir/latlon-v0009', 'pgl_ecircle_ecluster_distance';
834 CREATE FUNCTION ecluster_distance_proc(ecluster, ecluster)
835 RETURNS float8
836 LANGUAGE C IMMUTABLE STRICT
837 AS '$libdir/latlon-v0009', 'pgl_ecluster_distance';
839 CREATE FUNCTION fair_distance_operator_proc(ecluster, epoint_with_sample_count)
840 RETURNS float8
841 LANGUAGE C IMMUTABLE STRICT
842 AS '$libdir/latlon-v0009', 'pgl_ecluster_epoint_sc_fair_distance';
844 CREATE OPERATOR && (
845 leftarg = epoint,
846 rightarg = ebox,
847 procedure = epoint_ebox_overlap_proc,
848 commutator = &&,
849 restrict = areasel,
850 join = areajoinsel
851 );
853 CREATE FUNCTION epoint_ebox_overlap_commutator(ebox, epoint)
854 RETURNS boolean
855 LANGUAGE sql IMMUTABLE AS 'SELECT $2 && $1';
857 CREATE OPERATOR && (
858 leftarg = ebox,
859 rightarg = epoint,
860 procedure = epoint_ebox_overlap_commutator,
861 commutator = &&,
862 restrict = areasel,
863 join = areajoinsel
864 );
866 CREATE OPERATOR && (
867 leftarg = epoint,
868 rightarg = ecircle,
869 procedure = epoint_ecircle_overlap_proc,
870 commutator = &&,
871 restrict = areasel,
872 join = areajoinsel
873 );
875 CREATE FUNCTION epoint_ecircle_overlap_commutator(ecircle, epoint)
876 RETURNS boolean
877 LANGUAGE sql IMMUTABLE AS 'SELECT $2 && $1';
879 CREATE OPERATOR && (
880 leftarg = ecircle,
881 rightarg = epoint,
882 procedure = epoint_ecircle_overlap_commutator,
883 commutator = &&,
884 restrict = areasel,
885 join = areajoinsel
886 );
888 CREATE OPERATOR && (
889 leftarg = epoint,
890 rightarg = ecluster,
891 procedure = epoint_ecluster_overlap_proc,
892 commutator = &&,
893 restrict = areasel,
894 join = areajoinsel
895 );
897 CREATE FUNCTION epoint_ecluster_overlap_commutator(ecluster, epoint)
898 RETURNS boolean
899 LANGUAGE sql IMMUTABLE AS 'SELECT $2 && $1';
901 CREATE OPERATOR && (
902 leftarg = ecluster,
903 rightarg = epoint,
904 procedure = epoint_ecluster_overlap_commutator,
905 commutator = &&,
906 restrict = areasel,
907 join = areajoinsel
908 );
910 CREATE OPERATOR && (
911 leftarg = ebox,
912 rightarg = ebox,
913 procedure = ebox_overlap_proc,
914 commutator = &&,
915 restrict = areasel,
916 join = areajoinsel
917 );
919 CREATE OPERATOR && (
920 leftarg = ecircle,
921 rightarg = ecircle,
922 procedure = ecircle_overlap_proc,
923 commutator = &&,
924 restrict = areasel,
925 join = areajoinsel
926 );
928 CREATE OPERATOR && (
929 leftarg = ecircle,
930 rightarg = ecluster,
931 procedure = ecircle_ecluster_overlap_proc,
932 commutator = &&,
933 restrict = areasel,
934 join = areajoinsel
935 );
937 CREATE FUNCTION ecircle_ecluster_overlap_commutator(ecluster, ecircle)
938 RETURNS boolean
939 LANGUAGE sql IMMUTABLE AS 'SELECT $2 && $1';
941 CREATE OPERATOR && (
942 leftarg = ecluster,
943 rightarg = ecircle,
944 procedure = ecircle_ecluster_overlap_commutator,
945 commutator = &&,
946 restrict = areasel,
947 join = areajoinsel
948 );
950 CREATE OPERATOR && (
951 leftarg = ecluster,
952 rightarg = ecluster,
953 procedure = ecluster_overlap_proc,
954 commutator = &&,
955 restrict = areasel,
956 join = areajoinsel
957 );
959 CREATE FUNCTION ebox_ecircle_overlap_castwrap(ebox, ecircle)
960 RETURNS boolean
961 LANGUAGE sql IMMUTABLE AS 'SELECT $1::ecluster && $2';
963 CREATE OPERATOR && (
964 leftarg = ebox,
965 rightarg = ecircle,
966 procedure = ebox_ecircle_overlap_castwrap,
967 commutator = &&,
968 restrict = areasel,
969 join = areajoinsel
970 );
972 CREATE FUNCTION ebox_ecircle_overlap_castwrap(ecircle, ebox)
973 RETURNS boolean
974 LANGUAGE sql IMMUTABLE AS 'SELECT $1 && $2::ecluster';
976 CREATE OPERATOR && (
977 leftarg = ecircle,
978 rightarg = ebox,
979 procedure = ebox_ecircle_overlap_castwrap,
980 commutator = &&,
981 restrict = areasel,
982 join = areajoinsel
983 );
985 CREATE FUNCTION ebox_ecluster_overlap_castwrap(ebox, ecluster)
986 RETURNS boolean
987 LANGUAGE sql IMMUTABLE AS 'SELECT $1::ecluster && $2';
989 CREATE OPERATOR && (
990 leftarg = ebox,
991 rightarg = ecluster,
992 procedure = ebox_ecluster_overlap_castwrap,
993 commutator = &&,
994 restrict = areasel,
995 join = areajoinsel
996 );
998 CREATE FUNCTION ebox_ecluster_overlap_castwrap(ecluster, ebox)
999 RETURNS boolean
1000 LANGUAGE sql IMMUTABLE AS 'SELECT $1 && $2::ecluster';
1002 CREATE OPERATOR && (
1003 leftarg = ecluster,
1004 rightarg = ebox,
1005 procedure = ebox_ecluster_overlap_castwrap,
1006 commutator = &&,
1007 restrict = areasel,
1008 join = areajoinsel
1009 );
1011 CREATE OPERATOR &&+ (
1012 leftarg = epoint,
1013 rightarg = ecluster,
1014 procedure = epoint_ecluster_may_overlap_proc,
1015 commutator = &&+,
1016 restrict = areasel,
1017 join = areajoinsel
1018 );
1020 CREATE FUNCTION epoint_ecluster_may_overlap_commutator(ecluster, epoint)
1021 RETURNS boolean
1022 LANGUAGE sql IMMUTABLE AS 'SELECT $2 &&+ $1';
1024 CREATE OPERATOR &&+ (
1025 leftarg = ecluster,
1026 rightarg = epoint,
1027 procedure = epoint_ecluster_may_overlap_commutator,
1028 commutator = &&+,
1029 restrict = areasel,
1030 join = areajoinsel
1031 );
1033 CREATE OPERATOR &&+ (
1034 leftarg = ebox,
1035 rightarg = ecircle,
1036 procedure = ebox_ecircle_may_overlap_proc,
1037 commutator = &&+,
1038 restrict = areasel,
1039 join = areajoinsel
1040 );
1042 CREATE FUNCTION ebox_ecircle_may_overlap_commutator(ecircle, ebox)
1043 RETURNS boolean
1044 LANGUAGE sql IMMUTABLE AS 'SELECT $2 &&+ $1';
1046 CREATE OPERATOR &&+ (
1047 leftarg = ecircle,
1048 rightarg = ebox,
1049 procedure = ebox_ecircle_may_overlap_commutator,
1050 commutator = &&+,
1051 restrict = areasel,
1052 join = areajoinsel
1053 );
1055 CREATE OPERATOR &&+ (
1056 leftarg = ebox,
1057 rightarg = ecluster,
1058 procedure = ebox_ecluster_may_overlap_proc,
1059 commutator = &&+,
1060 restrict = areasel,
1061 join = areajoinsel
1062 );
1064 CREATE FUNCTION ebox_ecluster_may_overlap_commutator(ecluster, ebox)
1065 RETURNS boolean
1066 LANGUAGE sql IMMUTABLE AS 'SELECT $2 &&+ $1';
1068 CREATE OPERATOR &&+ (
1069 leftarg = ecluster,
1070 rightarg = ebox,
1071 procedure = ebox_ecluster_may_overlap_commutator,
1072 commutator = &&+,
1073 restrict = areasel,
1074 join = areajoinsel
1075 );
1077 CREATE OPERATOR &&+ (
1078 leftarg = ecircle,
1079 rightarg = ecluster,
1080 procedure = ecircle_ecluster_may_overlap_proc,
1081 commutator = &&+,
1082 restrict = areasel,
1083 join = areajoinsel
1084 );
1086 CREATE FUNCTION ecircle_ecluster_may_overlap_commutator(ecluster, ecircle)
1087 RETURNS boolean
1088 LANGUAGE sql IMMUTABLE AS 'SELECT $2 &&+ $1';
1090 CREATE OPERATOR &&+ (
1091 leftarg = ecluster,
1092 rightarg = ecircle,
1093 procedure = ecircle_ecluster_may_overlap_commutator,
1094 commutator = &&+,
1095 restrict = areasel,
1096 join = areajoinsel
1097 );
1099 CREATE OPERATOR &&+ (
1100 leftarg = ecluster,
1101 rightarg = ecluster,
1102 procedure = ecluster_may_overlap_proc,
1103 commutator = &&+,
1104 restrict = areasel,
1105 join = areajoinsel
1106 );
1108 CREATE OPERATOR @> (
1109 leftarg = ebox,
1110 rightarg = epoint,
1111 procedure = epoint_ebox_overlap_commutator,
1112 commutator = <@,
1113 restrict = areasel,
1114 join = areajoinsel
1115 );
1117 CREATE OPERATOR <@ (
1118 leftarg = epoint,
1119 rightarg = ebox,
1120 procedure = epoint_ebox_overlap_proc,
1121 commutator = @>,
1122 restrict = areasel,
1123 join = areajoinsel
1124 );
1126 CREATE OPERATOR @> (
1127 leftarg = ecluster,
1128 rightarg = epoint,
1129 procedure = epoint_ecluster_overlap_commutator,
1130 commutator = <@,
1131 restrict = areasel,
1132 join = areajoinsel
1133 );
1135 CREATE OPERATOR <@ (
1136 leftarg = epoint,
1137 rightarg = ecluster,
1138 procedure = epoint_ecluster_overlap_proc,
1139 commutator = <@,
1140 restrict = areasel,
1141 join = areajoinsel
1142 );
1144 CREATE OPERATOR @> (
1145 leftarg = ecluster,
1146 rightarg = ecluster,
1147 procedure = ecluster_contains_proc,
1148 commutator = <@,
1149 restrict = areasel,
1150 join = areajoinsel
1151 );
1153 CREATE FUNCTION ecluster_contains_commutator(ecluster, ecluster)
1154 RETURNS boolean
1155 LANGUAGE sql IMMUTABLE AS 'SELECT $2 @> $1';
1157 CREATE OPERATOR <@ (
1158 leftarg = ecluster,
1159 rightarg = ecluster,
1160 procedure = ecluster_contains_commutator,
1161 commutator = @>,
1162 restrict = areasel,
1163 join = areajoinsel
1164 );
1166 CREATE FUNCTION ebox_contains_castwrap(ebox, ebox)
1167 RETURNS boolean
1168 LANGUAGE sql IMMUTABLE AS 'SELECT $1::ecluster @> $2::ecluster';
1170 CREATE OPERATOR @> (
1171 leftarg = ebox,
1172 rightarg = ebox,
1173 procedure = ebox_contains_castwrap,
1174 commutator = <@,
1175 restrict = areasel,
1176 join = areajoinsel
1177 );
1179 CREATE FUNCTION ebox_contains_swapped_castwrap(ebox, ebox)
1180 RETURNS boolean
1181 LANGUAGE sql IMMUTABLE AS 'SELECT $2::ecluster @> $1::ecluster';
1183 CREATE OPERATOR <@ (
1184 leftarg = ebox,
1185 rightarg = ebox,
1186 procedure = ebox_contains_swapped_castwrap,
1187 commutator = @>,
1188 restrict = areasel,
1189 join = areajoinsel
1190 );
1192 CREATE FUNCTION ebox_ecluster_contains_castwrap(ebox, ecluster)
1193 RETURNS boolean
1194 LANGUAGE sql IMMUTABLE AS 'SELECT $1::ecluster @> $2';
1196 CREATE OPERATOR @> (
1197 leftarg = ebox,
1198 rightarg = ecluster,
1199 procedure = ebox_ecluster_contains_castwrap,
1200 commutator = <@,
1201 restrict = areasel,
1202 join = areajoinsel
1203 );
1205 CREATE FUNCTION ebox_ecluster_contains_castwrap(ecluster, ebox)
1206 RETURNS boolean
1207 LANGUAGE sql IMMUTABLE AS 'SELECT $2::ecluster @> $1';
1209 CREATE OPERATOR <@ (
1210 leftarg = ecluster,
1211 rightarg = ebox,
1212 procedure = ebox_ecluster_contains_castwrap,
1213 commutator = @>,
1214 restrict = areasel,
1215 join = areajoinsel
1216 );
1218 CREATE FUNCTION ecluster_ebox_contains_castwrap(ecluster, ebox)
1219 RETURNS boolean
1220 LANGUAGE sql IMMUTABLE AS 'SELECT $1 @> $2::ecluster';
1222 CREATE OPERATOR @> (
1223 leftarg = ecluster,
1224 rightarg = ebox,
1225 procedure = ecluster_ebox_contains_castwrap,
1226 commutator = <@,
1227 restrict = areasel,
1228 join = areajoinsel
1229 );
1231 CREATE FUNCTION ecluster_ebox_contains_castwrap(ebox, ecluster)
1232 RETURNS boolean
1233 LANGUAGE sql IMMUTABLE AS 'SELECT $2 @> $1::ecluster';
1235 CREATE OPERATOR <@ (
1236 leftarg = ebox,
1237 rightarg = ecluster,
1238 procedure = ecluster_ebox_contains_castwrap,
1239 commutator = @>,
1240 restrict = areasel,
1241 join = areajoinsel
1242 );
1244 CREATE OPERATOR <-> (
1245 leftarg = epoint,
1246 rightarg = epoint,
1247 procedure = epoint_distance_proc,
1248 commutator = <->
1249 );
1251 CREATE OPERATOR <-> (
1252 leftarg = epoint,
1253 rightarg = ecircle,
1254 procedure = epoint_ecircle_distance_proc,
1255 commutator = <->
1256 );
1258 CREATE FUNCTION epoint_ecircle_distance_commutator(ecircle, epoint)
1259 RETURNS float8
1260 LANGUAGE sql IMMUTABLE AS 'SELECT $2 <-> $1';
1262 CREATE OPERATOR <-> (
1263 leftarg = ecircle,
1264 rightarg = epoint,
1265 procedure = epoint_ecircle_distance_commutator,
1266 commutator = <->
1267 );
1269 CREATE OPERATOR <-> (
1270 leftarg = epoint,
1271 rightarg = ecluster,
1272 procedure = epoint_ecluster_distance_proc,
1273 commutator = <->
1274 );
1276 CREATE FUNCTION epoint_ecluster_distance_commutator(ecluster, epoint)
1277 RETURNS float8
1278 LANGUAGE sql IMMUTABLE AS 'SELECT $2 <-> $1';
1280 CREATE OPERATOR <-> (
1281 leftarg = ecluster,
1282 rightarg = epoint,
1283 procedure = epoint_ecluster_distance_commutator,
1284 commutator = <->
1285 );
1287 CREATE OPERATOR <-> (
1288 leftarg = ecircle,
1289 rightarg = ecircle,
1290 procedure = ecircle_distance_proc,
1291 commutator = <->
1292 );
1294 CREATE OPERATOR <-> (
1295 leftarg = ecircle,
1296 rightarg = ecluster,
1297 procedure = ecircle_ecluster_distance_proc,
1298 commutator = <->
1299 );
1301 CREATE FUNCTION ecircle_ecluster_distance_commutator(ecluster, ecircle)
1302 RETURNS float8
1303 LANGUAGE sql IMMUTABLE AS 'SELECT $2 <-> $1';
1305 CREATE OPERATOR <-> (
1306 leftarg = ecluster,
1307 rightarg = ecircle,
1308 procedure = ecircle_ecluster_distance_commutator,
1309 commutator = <->
1310 );
1312 CREATE OPERATOR <-> (
1313 leftarg = ecluster,
1314 rightarg = ecluster,
1315 procedure = ecluster_distance_proc,
1316 commutator = <->
1317 );
1319 CREATE FUNCTION epoint_ebox_distance_castwrap(epoint, ebox)
1320 RETURNS float8
1321 LANGUAGE sql IMMUTABLE AS 'SELECT $1 <-> $2::ecluster';
1323 CREATE OPERATOR <-> (
1324 leftarg = epoint,
1325 rightarg = ebox,
1326 procedure = epoint_ebox_distance_castwrap,
1327 commutator = <->
1328 );
1330 CREATE FUNCTION epoint_ebox_distance_castwrap(ebox, epoint)
1331 RETURNS float8
1332 LANGUAGE sql IMMUTABLE AS 'SELECT $1::ecluster <-> $2';
1334 CREATE OPERATOR <-> (
1335 leftarg = ebox,
1336 rightarg = epoint,
1337 procedure = epoint_ebox_distance_castwrap,
1338 commutator = <->
1339 );
1341 CREATE FUNCTION ebox_distance_castwrap(ebox, ebox)
1342 RETURNS float8
1343 LANGUAGE sql IMMUTABLE AS 'SELECT $1::ecluster <-> $2::ecluster';
1345 CREATE OPERATOR <-> (
1346 leftarg = ebox,
1347 rightarg = ebox,
1348 procedure = ebox_distance_castwrap,
1349 commutator = <->
1350 );
1352 CREATE FUNCTION ebox_ecircle_distance_castwrap(ebox, ecircle)
1353 RETURNS float8
1354 LANGUAGE sql IMMUTABLE AS 'SELECT $1::ecluster <-> $2';
1356 CREATE OPERATOR <-> (
1357 leftarg = ebox,
1358 rightarg = ecircle,
1359 procedure = ebox_ecircle_distance_castwrap,
1360 commutator = <->
1361 );
1363 CREATE FUNCTION ebox_ecircle_distance_castwrap(ecircle, ebox)
1364 RETURNS float8
1365 LANGUAGE sql IMMUTABLE AS 'SELECT $1 <-> $2::ecluster';
1367 CREATE OPERATOR <-> (
1368 leftarg = ecircle,
1369 rightarg = ebox,
1370 procedure = ebox_ecircle_distance_castwrap,
1371 commutator = <->
1372 );
1374 CREATE FUNCTION ebox_ecluster_distance_castwrap(ebox, ecluster)
1375 RETURNS float8
1376 LANGUAGE sql IMMUTABLE AS 'SELECT $1::ecluster <-> $2';
1378 CREATE OPERATOR <-> (
1379 leftarg = ebox,
1380 rightarg = ecluster,
1381 procedure = ebox_ecluster_distance_castwrap,
1382 commutator = <->
1383 );
1385 CREATE FUNCTION ebox_ecluster_distance_castwrap(ecluster, ebox)
1386 RETURNS float8
1387 LANGUAGE sql IMMUTABLE AS 'SELECT $1 <-> $2::ecluster';
1389 CREATE OPERATOR <-> (
1390 leftarg = ecluster,
1391 rightarg = ebox,
1392 procedure = ebox_ecluster_distance_castwrap,
1393 commutator = <->
1394 );
1396 CREATE OPERATOR <=> (
1397 leftarg = ecluster,
1398 rightarg = epoint_with_sample_count,
1399 procedure = fair_distance_operator_proc
1400 );
1403 ----------------
1404 -- GiST index --
1405 ----------------
1407 CREATE FUNCTION pgl_gist_consistent(internal, internal, smallint, oid, internal)
1408 RETURNS boolean
1409 LANGUAGE C STRICT
1410 AS '$libdir/latlon-v0009', 'pgl_gist_consistent';
1412 CREATE FUNCTION pgl_gist_union(internal, internal)
1413 RETURNS internal
1414 LANGUAGE C STRICT
1415 AS '$libdir/latlon-v0009', 'pgl_gist_union';
1417 CREATE FUNCTION pgl_gist_compress_epoint(internal)
1418 RETURNS internal
1419 LANGUAGE C STRICT
1420 AS '$libdir/latlon-v0009', 'pgl_gist_compress_epoint';
1422 CREATE FUNCTION pgl_gist_compress_ecircle(internal)
1423 RETURNS internal
1424 LANGUAGE C STRICT
1425 AS '$libdir/latlon-v0009', 'pgl_gist_compress_ecircle';
1427 CREATE FUNCTION pgl_gist_compress_ecluster(internal)
1428 RETURNS internal
1429 LANGUAGE C STRICT
1430 AS '$libdir/latlon-v0009', 'pgl_gist_compress_ecluster';
1432 CREATE FUNCTION pgl_gist_decompress(internal)
1433 RETURNS internal
1434 LANGUAGE C STRICT
1435 AS '$libdir/latlon-v0009', 'pgl_gist_decompress';
1437 CREATE FUNCTION pgl_gist_penalty(internal, internal, internal)
1438 RETURNS internal
1439 LANGUAGE C STRICT
1440 AS '$libdir/latlon-v0009', 'pgl_gist_penalty';
1442 CREATE FUNCTION pgl_gist_picksplit(internal, internal)
1443 RETURNS internal
1444 LANGUAGE C STRICT
1445 AS '$libdir/latlon-v0009', 'pgl_gist_picksplit';
1447 CREATE FUNCTION pgl_gist_same(internal, internal, internal)
1448 RETURNS internal
1449 LANGUAGE C STRICT
1450 AS '$libdir/latlon-v0009', 'pgl_gist_same';
1452 CREATE FUNCTION pgl_gist_distance(internal, internal, smallint, oid)
1453 RETURNS internal
1454 LANGUAGE C STRICT
1455 AS '$libdir/latlon-v0009', 'pgl_gist_distance';
1457 CREATE OPERATOR CLASS epoint_ops
1458 DEFAULT FOR TYPE epoint USING gist AS
1459 OPERATOR 11 = ,
1460 OPERATOR 22 && (epoint, ebox),
1461 OPERATOR 222 <@ (epoint, ebox),
1462 OPERATOR 23 && (epoint, ecircle),
1463 OPERATOR 24 && (epoint, ecluster),
1464 OPERATOR 124 &&+ (epoint, ecluster),
1465 OPERATOR 224 <@ (epoint, ecluster),
1466 OPERATOR 31 <-> (epoint, epoint) FOR ORDER BY float_ops,
1467 OPERATOR 32 <-> (epoint, ebox) FOR ORDER BY float_ops,
1468 OPERATOR 33 <-> (epoint, ecircle) FOR ORDER BY float_ops,
1469 OPERATOR 34 <-> (epoint, ecluster) FOR ORDER BY float_ops,
1470 FUNCTION 1 pgl_gist_consistent(internal, internal, smallint, oid, internal),
1471 FUNCTION 2 pgl_gist_union(internal, internal),
1472 FUNCTION 3 pgl_gist_compress_epoint(internal),
1473 FUNCTION 4 pgl_gist_decompress(internal),
1474 FUNCTION 5 pgl_gist_penalty(internal, internal, internal),
1475 FUNCTION 6 pgl_gist_picksplit(internal, internal),
1476 FUNCTION 7 pgl_gist_same(internal, internal, internal),
1477 FUNCTION 8 pgl_gist_distance(internal, internal, smallint, oid),
1478 STORAGE ekey_point;
1480 CREATE OPERATOR CLASS ecircle_ops
1481 DEFAULT FOR TYPE ecircle USING gist AS
1482 OPERATOR 13 = ,
1483 OPERATOR 21 && (ecircle, epoint),
1484 OPERATOR 22 && (ecircle, ebox),
1485 OPERATOR 122 &&+ (ecircle, ebox),
1486 OPERATOR 23 && (ecircle, ecircle),
1487 OPERATOR 24 && (ecircle, ecluster),
1488 OPERATOR 124 &&+ (ecircle, ecluster),
1489 OPERATOR 31 <-> (ecircle, epoint) FOR ORDER BY float_ops,
1490 OPERATOR 32 <-> (ecircle, ebox) FOR ORDER BY float_ops,
1491 OPERATOR 33 <-> (ecircle, ecircle) FOR ORDER BY float_ops,
1492 OPERATOR 34 <-> (ecircle, ecluster) FOR ORDER BY float_ops,
1493 FUNCTION 1 pgl_gist_consistent(internal, internal, smallint, oid, internal),
1494 FUNCTION 2 pgl_gist_union(internal, internal),
1495 FUNCTION 3 pgl_gist_compress_ecircle(internal),
1496 FUNCTION 4 pgl_gist_decompress(internal),
1497 FUNCTION 5 pgl_gist_penalty(internal, internal, internal),
1498 FUNCTION 6 pgl_gist_picksplit(internal, internal),
1499 FUNCTION 7 pgl_gist_same(internal, internal, internal),
1500 FUNCTION 8 pgl_gist_distance(internal, internal, smallint, oid),
1501 STORAGE ekey_area;
1503 CREATE OPERATOR CLASS ecluster_ops
1504 DEFAULT FOR TYPE ecluster USING gist AS
1505 OPERATOR 21 && (ecluster, epoint),
1506 OPERATOR 121 &&+ (ecluster, epoint),
1507 OPERATOR 221 @> (ecluster, epoint),
1508 OPERATOR 22 && (ecluster, ebox),
1509 OPERATOR 122 &&+ (ecluster, ebox),
1510 OPERATOR 222 @> (ecluster, ebox),
1511 OPERATOR 322 <@ (ecluster, ebox),
1512 OPERATOR 23 && (ecluster, ecircle),
1513 OPERATOR 123 &&+ (ecluster, ecircle),
1514 OPERATOR 24 && (ecluster, ecluster),
1515 OPERATOR 124 &&+ (ecluster, ecluster),
1516 OPERATOR 224 @> (ecluster, ecluster),
1517 OPERATOR 324 <@ (ecluster, ecluster),
1518 OPERATOR 31 <-> (ecluster, epoint) FOR ORDER BY float_ops,
1519 OPERATOR 32 <-> (ecluster, ebox) FOR ORDER BY float_ops,
1520 OPERATOR 33 <-> (ecluster, ecircle) FOR ORDER BY float_ops,
1521 OPERATOR 34 <-> (ecluster, ecluster) FOR ORDER BY float_ops,
1522 OPERATOR 131 <=> (ecluster, epoint_with_sample_count) FOR ORDER BY float_ops,
1523 FUNCTION 1 pgl_gist_consistent(internal, internal, smallint, oid, internal),
1524 FUNCTION 2 pgl_gist_union(internal, internal),
1525 FUNCTION 3 pgl_gist_compress_ecluster(internal),
1526 FUNCTION 4 pgl_gist_decompress(internal),
1527 FUNCTION 5 pgl_gist_penalty(internal, internal, internal),
1528 FUNCTION 6 pgl_gist_picksplit(internal, internal),
1529 FUNCTION 7 pgl_gist_same(internal, internal, internal),
1530 FUNCTION 8 pgl_gist_distance(internal, internal, smallint, oid),
1531 STORAGE ekey_area;
1534 ---------------------
1535 -- alias functions --
1536 ---------------------
1538 CREATE FUNCTION distance(epoint, epoint)
1539 RETURNS float8
1540 LANGUAGE sql IMMUTABLE AS 'SELECT $1 <-> $2';
1542 CREATE FUNCTION distance(ecluster, epoint)
1543 RETURNS float8
1544 LANGUAGE sql IMMUTABLE AS 'SELECT $1 <-> $2';
1546 CREATE FUNCTION distance_within(epoint, epoint, float8)
1547 RETURNS boolean
1548 LANGUAGE sql IMMUTABLE AS 'SELECT $1 && ecircle($2, $3)';
1550 CREATE FUNCTION distance_within(ecluster, epoint, float8)
1551 RETURNS boolean
1552 LANGUAGE sql IMMUTABLE AS 'SELECT $1 && ecircle($2, $3)';
1554 CREATE FUNCTION fair_distance(ecluster, epoint, int4 = 10000)
1555 RETURNS float8
1556 LANGUAGE sql IMMUTABLE AS 'SELECT $1 <=> epoint_with_sample_count($2, $3)';
1559 --------------------------------
1560 -- other data storage formats --
1561 --------------------------------
1563 CREATE FUNCTION coords_to_epoint(float8, float8, text = 'epoint')
1564 RETURNS epoint
1565 LANGUAGE plpgsql IMMUTABLE STRICT AS $$
1566 DECLARE
1567 "result" epoint;
1568 BEGIN
1569 IF $3 = 'epoint_lonlat' THEN
1570 -- avoid dynamic command execution for better performance
1571 RETURN epoint($2, $1);
1572 END IF;
1573 IF $3 = 'epoint' OR $3 = 'epoint_latlon' THEN
1574 -- avoid dynamic command execution for better performance
1575 RETURN epoint($1, $2);
1576 END IF;
1577 EXECUTE 'SELECT ' || $3 || '($1, $2)' INTO STRICT "result" USING $1, $2;
1578 RETURN "result";
1579 END;
1580 $$;
1582 CREATE FUNCTION GeoJSON_LinearRing_vertices(jsonb, text = 'epoint_lonlat')
1583 RETURNS SETOF jsonb
1584 LANGUAGE sql IMMUTABLE STRICT AS $$
1585 SELECT "result" FROM
1586 ( SELECT jsonb_array_length($1) - 1 ) AS "lastindex_row" ("lastindex")
1587 CROSS JOIN LATERAL jsonb_array_elements(
1588 CASE WHEN
1589 coords_to_epoint(
1590 ($1->0->>0)::float8,
1591 ($1->0->>1)::float8,
1592 $2
1593 ) = coords_to_epoint(
1594 ($1->"lastindex"->>0)::float8,
1595 ($1->"lastindex"->>1)::float8,
1596 $2
1597 )
1598 THEN
1599 $1 - "lastindex"
1600 ELSE
1601 $1
1602 END
1603 ) AS "result_row" ("result")
1604 $$;
1606 CREATE FUNCTION GeoJSON_to_epoint(jsonb, text = 'epoint_lonlat')
1607 RETURNS epoint
1608 LANGUAGE sql IMMUTABLE STRICT AS $$
1609 SELECT CASE
1610 WHEN $1->>'type' = 'Point' THEN
1611 coords_to_epoint(
1612 ($1->'coordinates'->>0)::float8,
1613 ($1->'coordinates'->>1)::float8,
1614 $2
1615 )
1616 WHEN $1->>'type' = 'Feature' THEN
1617 GeoJSON_to_epoint($1->'geometry', $2)
1618 ELSE
1619 NULL
1620 END
1621 $$;
1623 CREATE FUNCTION GeoJSON_to_ecluster(jsonb, text = 'epoint_lonlat')
1624 RETURNS ecluster
1625 LANGUAGE sql IMMUTABLE STRICT AS $$
1626 SELECT CASE $1->>'type'
1627 WHEN 'Point' THEN
1628 coords_to_epoint(
1629 ($1->'coordinates'->>0)::float8,
1630 ($1->'coordinates'->>1)::float8,
1631 $2
1632 )::ecluster
1633 WHEN 'MultiPoint' THEN
1634 ( SELECT ecluster_create_multipoint(array_agg(
1635 coords_to_epoint(
1636 ("coord"->>0)::float8,
1637 ("coord"->>1)::float8,
1638 $2
1639 )
1640 ))
1641 FROM jsonb_array_elements($1->'coordinates') AS "coord"
1642 )
1643 WHEN 'LineString' THEN
1644 ( SELECT ecluster_create_path(array_agg(
1645 coords_to_epoint(
1646 ("coord"->>0)::float8,
1647 ("coord"->>1)::float8,
1648 $2
1649 )
1650 ))
1651 FROM jsonb_array_elements($1->'coordinates') AS "coord"
1652 )
1653 WHEN 'MultiLineString' THEN
1654 ( SELECT ecluster_concat(array_agg(
1655 ( SELECT ecluster_create_path(array_agg(
1656 coords_to_epoint(
1657 ("coord"->>0)::float8,
1658 ("coord"->>1)::float8,
1659 $2
1660 )
1661 ))
1662 FROM jsonb_array_elements("coord_array") AS "coord"
1663 )
1664 ))
1665 FROM jsonb_array_elements($1->'coordinates') AS "coord_array"
1666 )
1667 WHEN 'Polygon' THEN
1668 ( SELECT ecluster_concat(array_agg(
1669 ( SELECT ecluster_create_polygon(array_agg(
1670 coords_to_epoint(
1671 ("coord"->>0)::float8,
1672 ("coord"->>1)::float8,
1673 $2
1674 )
1675 ))
1676 FROM GeoJSON_LinearRing_vertices("coord_array", $2) AS "coord"
1677 )
1678 ))
1679 FROM jsonb_array_elements($1->'coordinates') AS "coord_array"
1680 )
1681 WHEN 'MultiPolygon' THEN
1682 ( SELECT ecluster_concat(array_agg(
1683 ( SELECT ecluster_concat(array_agg(
1684 ( SELECT ecluster_create_polygon(array_agg(
1685 coords_to_epoint(
1686 ("coord"->>0)::float8,
1687 ("coord"->>1)::float8,
1688 $2
1689 )
1690 ))
1691 FROM GeoJSON_LinearRing_vertices("coord_array", $2) AS "coord"
1692 )
1693 ))
1694 FROM jsonb_array_elements("coord_array_array") AS "coord_array"
1695 )
1696 ))
1697 FROM jsonb_array_elements($1->'coordinates') AS "coord_array_array"
1698 )
1699 WHEN 'GeometryCollection' THEN
1700 ( SELECT ecluster_concat(array_agg(
1701 GeoJSON_to_ecluster("geometry", $2)
1702 ))
1703 FROM jsonb_array_elements($1->'geometries') AS "geometry"
1704 )
1705 WHEN 'Feature' THEN
1706 GeoJSON_to_ecluster($1->'geometry', $2)
1707 WHEN 'FeatureCollection' THEN
1708 ( SELECT ecluster_concat(array_agg(
1709 GeoJSON_to_ecluster("feature", $2)
1710 ))
1711 FROM jsonb_array_elements($1->'features') AS "feature"
1712 )
1713 ELSE
1714 NULL
1715 END
1716 $$;