pgLatLon
view latlon--0.15.sql @ 77:a707dc3e896c
Added tag v0.15 for changeset 4f11ccf36fb6
| author | jbe |
|---|---|
| date | Mon Nov 30 19:38:57 2020 +0100 (2020-11-30) |
| parents | 4f11ccf36fb6 |
| children |
line source
1 SET LOCAL search_path TO @extschema@;
4 ----------------------------------------
5 -- forward declarations (shell types) --
6 ----------------------------------------
8 CREATE TYPE ekey_point;
9 CREATE TYPE ekey_area;
10 CREATE TYPE epoint;
11 CREATE TYPE epoint_with_sample_count;
12 CREATE TYPE ebox;
13 CREATE TYPE ecircle;
14 CREATE TYPE ecluster;
17 ------------------------------------------------------------
18 -- dummy input/output functions for dummy index key types --
19 ------------------------------------------------------------
21 CREATE FUNCTION ekey_point_in_dummy(cstring)
22 RETURNS ekey_point
23 LANGUAGE C IMMUTABLE STRICT
24 AS '$libdir/latlon-v0010', 'pgl_notimpl';
26 CREATE FUNCTION ekey_point_out_dummy(ekey_point)
27 RETURNS cstring
28 LANGUAGE C IMMUTABLE STRICT
29 AS '$libdir/latlon-v0010', 'pgl_notimpl';
31 CREATE FUNCTION ekey_area_in_dummy(cstring)
32 RETURNS ekey_area
33 LANGUAGE C IMMUTABLE STRICT
34 AS '$libdir/latlon-v0010', 'pgl_notimpl';
36 CREATE FUNCTION ekey_area_out_dummy(ekey_area)
37 RETURNS cstring
38 LANGUAGE C IMMUTABLE STRICT
39 AS '$libdir/latlon-v0010', 'pgl_notimpl';
42 --------------------------
43 -- text input functions --
44 --------------------------
46 CREATE FUNCTION epoint_in(cstring)
47 RETURNS epoint
48 LANGUAGE C IMMUTABLE STRICT
49 AS '$libdir/latlon-v0010', 'pgl_epoint_in';
51 CREATE FUNCTION epoint_with_sample_count_in(cstring)
52 RETURNS epoint_with_sample_count
53 LANGUAGE C IMMUTABLE STRICT
54 AS '$libdir/latlon-v0010', 'pgl_epoint_with_sample_count_in';
56 CREATE FUNCTION ebox_in(cstring)
57 RETURNS ebox
58 LANGUAGE C IMMUTABLE STRICT
59 AS '$libdir/latlon-v0010', 'pgl_ebox_in';
61 CREATE FUNCTION ecircle_in(cstring)
62 RETURNS ecircle
63 LANGUAGE C IMMUTABLE STRICT
64 AS '$libdir/latlon-v0010', 'pgl_ecircle_in';
66 CREATE FUNCTION ecluster_in(cstring)
67 RETURNS ecluster
68 LANGUAGE C IMMUTABLE STRICT
69 AS '$libdir/latlon-v0010', 'pgl_ecluster_in';
72 ---------------------------
73 -- text output functions --
74 ---------------------------
76 CREATE FUNCTION epoint_out(epoint)
77 RETURNS cstring
78 LANGUAGE C IMMUTABLE STRICT
79 AS '$libdir/latlon-v0010', 'pgl_epoint_out';
81 CREATE FUNCTION epoint_with_sample_count_out(epoint_with_sample_count)
82 RETURNS cstring
83 LANGUAGE C IMMUTABLE STRICT
84 AS '$libdir/latlon-v0010', 'pgl_epoint_with_sample_count_out';
86 CREATE FUNCTION ebox_out(ebox)
87 RETURNS cstring
88 LANGUAGE C IMMUTABLE STRICT
89 AS '$libdir/latlon-v0010', 'pgl_ebox_out';
91 CREATE FUNCTION ecircle_out(ecircle)
92 RETURNS cstring
93 LANGUAGE C IMMUTABLE STRICT
94 AS '$libdir/latlon-v0010', 'pgl_ecircle_out';
96 CREATE FUNCTION ecluster_out(ecluster)
97 RETURNS cstring
98 LANGUAGE C IMMUTABLE STRICT
99 AS '$libdir/latlon-v0010', 'pgl_ecluster_out';
102 --------------------------
103 -- binary I/O functions --
104 --------------------------
106 CREATE FUNCTION epoint_recv(internal)
107 RETURNS epoint
108 LANGUAGE C IMMUTABLE STRICT
109 AS '$libdir/latlon-v0010', 'pgl_epoint_recv';
111 CREATE FUNCTION ebox_recv(internal)
112 RETURNS ebox
113 LANGUAGE C IMMUTABLE STRICT
114 AS '$libdir/latlon-v0010', 'pgl_ebox_recv';
116 CREATE FUNCTION ecircle_recv(internal)
117 RETURNS ecircle
118 LANGUAGE C IMMUTABLE STRICT
119 AS '$libdir/latlon-v0010', 'pgl_ecircle_recv';
121 CREATE FUNCTION epoint_send(epoint)
122 RETURNS bytea
123 LANGUAGE C IMMUTABLE STRICT
124 AS '$libdir/latlon-v0010', 'pgl_epoint_send';
126 CREATE FUNCTION ebox_send(ebox)
127 RETURNS bytea
128 LANGUAGE C IMMUTABLE STRICT
129 AS '$libdir/latlon-v0010', 'pgl_ebox_send';
131 CREATE FUNCTION ecircle_send(ecircle)
132 RETURNS bytea
133 LANGUAGE C IMMUTABLE STRICT
134 AS '$libdir/latlon-v0010', 'pgl_ecircle_send';
137 -----------------------------------------------
138 -- type definitions of dummy index key types --
139 -----------------------------------------------
141 CREATE TYPE ekey_point (
142 internallength = 8,
143 input = ekey_point_in_dummy,
144 output = ekey_point_out_dummy,
145 alignment = char );
147 CREATE TYPE ekey_area (
148 internallength = 9,
149 input = ekey_area_in_dummy,
150 output = ekey_area_out_dummy,
151 alignment = char );
154 ------------------------------------------
155 -- definitions of geographic data types --
156 ------------------------------------------
158 CREATE TYPE epoint (
159 internallength = 16,
160 input = epoint_in,
161 output = epoint_out,
162 receive = epoint_recv,
163 send = epoint_send,
164 alignment = double );
166 CREATE TYPE epoint_with_sample_count (
167 internallength = 20,
168 input = epoint_with_sample_count_in,
169 output = epoint_with_sample_count_out,
170 alignment = double );
172 CREATE TYPE ebox (
173 internallength = 32,
174 input = ebox_in,
175 output = ebox_out,
176 receive = ebox_recv,
177 send = ebox_send,
178 alignment = double );
180 CREATE TYPE ecircle (
181 internallength = 24,
182 input = ecircle_in,
183 output = ecircle_out,
184 receive = ecircle_recv,
185 send = ecircle_send,
186 alignment = double );
188 CREATE TYPE ecluster (
189 internallength = VARIABLE,
190 input = ecluster_in,
191 output = ecluster_out,
192 alignment = double,
193 storage = external );
196 --------------------
197 -- B-tree support --
198 --------------------
200 -- begin of B-tree support for epoint
202 CREATE FUNCTION epoint_btree_lt(epoint, epoint)
203 RETURNS boolean
204 LANGUAGE C IMMUTABLE STRICT
205 AS '$libdir/latlon-v0010', 'pgl_btree_epoint_lt';
207 CREATE FUNCTION epoint_btree_le(epoint, epoint)
208 RETURNS boolean
209 LANGUAGE C IMMUTABLE STRICT
210 AS '$libdir/latlon-v0010', 'pgl_btree_epoint_le';
212 CREATE FUNCTION epoint_btree_eq(epoint, epoint)
213 RETURNS boolean
214 LANGUAGE C IMMUTABLE STRICT
215 AS '$libdir/latlon-v0010', 'pgl_btree_epoint_eq';
217 CREATE FUNCTION epoint_btree_ne(epoint, epoint)
218 RETURNS boolean
219 LANGUAGE C IMMUTABLE STRICT
220 AS '$libdir/latlon-v0010', 'pgl_btree_epoint_ne';
222 CREATE FUNCTION epoint_btree_ge(epoint, epoint)
223 RETURNS boolean
224 LANGUAGE C IMMUTABLE STRICT
225 AS '$libdir/latlon-v0010', 'pgl_btree_epoint_ge';
227 CREATE FUNCTION epoint_btree_gt(epoint, epoint)
228 RETURNS boolean
229 LANGUAGE C IMMUTABLE STRICT
230 AS '$libdir/latlon-v0010', 'pgl_btree_epoint_gt';
232 CREATE OPERATOR <<< (
233 leftarg = epoint,
234 rightarg = epoint,
235 procedure = epoint_btree_lt,
236 commutator = >>>,
237 negator = >>>=,
238 restrict = scalarltsel,
239 join = scalarltjoinsel
240 );
242 CREATE OPERATOR <<<= (
243 leftarg = epoint,
244 rightarg = epoint,
245 procedure = epoint_btree_le,
246 commutator = >>>=,
247 negator = >>>,
248 restrict = scalarltsel,
249 join = scalarltjoinsel
250 );
252 CREATE OPERATOR = (
253 leftarg = epoint,
254 rightarg = epoint,
255 procedure = epoint_btree_eq,
256 commutator = =,
257 negator = <>,
258 restrict = eqsel,
259 join = eqjoinsel,
260 merges
261 );
263 CREATE OPERATOR <> (
264 leftarg = epoint,
265 rightarg = epoint,
266 procedure = epoint_btree_ne,
267 commutator = <>,
268 negator = =,
269 restrict = neqsel,
270 join = neqjoinsel
271 );
273 CREATE OPERATOR >>>= (
274 leftarg = epoint,
275 rightarg = epoint,
276 procedure = epoint_btree_ge,
277 commutator = <<<=,
278 negator = <<<,
279 restrict = scalargtsel,
280 join = scalargtjoinsel
281 );
283 CREATE OPERATOR >>> (
284 leftarg = epoint,
285 rightarg = epoint,
286 procedure = epoint_btree_gt,
287 commutator = <<<,
288 negator = <<<=,
289 restrict = scalargtsel,
290 join = scalargtjoinsel
291 );
293 CREATE FUNCTION epoint_btree_cmp(epoint, epoint)
294 RETURNS int4
295 LANGUAGE C IMMUTABLE STRICT
296 AS '$libdir/latlon-v0010', 'pgl_btree_epoint_cmp';
298 CREATE OPERATOR CLASS epoint_btree_ops
299 DEFAULT FOR TYPE epoint USING btree AS
300 OPERATOR 1 <<< ,
301 OPERATOR 2 <<<= ,
302 OPERATOR 3 = ,
303 OPERATOR 4 >>>= ,
304 OPERATOR 5 >>> ,
305 FUNCTION 1 epoint_btree_cmp(epoint, epoint);
307 -- end of B-tree support for epoint
309 -- begin of B-tree support for ebox
311 CREATE FUNCTION ebox_btree_lt(ebox, ebox)
312 RETURNS boolean
313 LANGUAGE C IMMUTABLE STRICT
314 AS '$libdir/latlon-v0010', 'pgl_btree_ebox_lt';
316 CREATE FUNCTION ebox_btree_le(ebox, ebox)
317 RETURNS boolean
318 LANGUAGE C IMMUTABLE STRICT
319 AS '$libdir/latlon-v0010', 'pgl_btree_ebox_le';
321 CREATE FUNCTION ebox_btree_eq(ebox, ebox)
322 RETURNS boolean
323 LANGUAGE C IMMUTABLE STRICT
324 AS '$libdir/latlon-v0010', 'pgl_btree_ebox_eq';
326 CREATE FUNCTION ebox_btree_ne(ebox, ebox)
327 RETURNS boolean
328 LANGUAGE C IMMUTABLE STRICT
329 AS '$libdir/latlon-v0010', 'pgl_btree_ebox_ne';
331 CREATE FUNCTION ebox_btree_ge(ebox, ebox)
332 RETURNS boolean
333 LANGUAGE C IMMUTABLE STRICT
334 AS '$libdir/latlon-v0010', 'pgl_btree_ebox_ge';
336 CREATE FUNCTION ebox_btree_gt(ebox, ebox)
337 RETURNS boolean
338 LANGUAGE C IMMUTABLE STRICT
339 AS '$libdir/latlon-v0010', 'pgl_btree_ebox_gt';
341 CREATE OPERATOR <<< (
342 leftarg = ebox,
343 rightarg = ebox,
344 procedure = ebox_btree_lt,
345 commutator = >>>,
346 negator = >>>=,
347 restrict = scalarltsel,
348 join = scalarltjoinsel
349 );
351 CREATE OPERATOR <<<= (
352 leftarg = ebox,
353 rightarg = ebox,
354 procedure = ebox_btree_le,
355 commutator = >>>=,
356 negator = >>>,
357 restrict = scalarltsel,
358 join = scalarltjoinsel
359 );
361 CREATE OPERATOR = (
362 leftarg = ebox,
363 rightarg = ebox,
364 procedure = ebox_btree_eq,
365 commutator = =,
366 negator = <>,
367 restrict = eqsel,
368 join = eqjoinsel,
369 merges
370 );
372 CREATE OPERATOR <> (
373 leftarg = ebox,
374 rightarg = ebox,
375 procedure = ebox_btree_ne,
376 commutator = <>,
377 negator = =,
378 restrict = neqsel,
379 join = neqjoinsel
380 );
382 CREATE OPERATOR >>>= (
383 leftarg = ebox,
384 rightarg = ebox,
385 procedure = ebox_btree_ge,
386 commutator = <<<=,
387 negator = <<<,
388 restrict = scalargtsel,
389 join = scalargtjoinsel
390 );
392 CREATE OPERATOR >>> (
393 leftarg = ebox,
394 rightarg = ebox,
395 procedure = ebox_btree_gt,
396 commutator = <<<,
397 negator = <<<=,
398 restrict = scalargtsel,
399 join = scalargtjoinsel
400 );
402 CREATE FUNCTION ebox_btree_cmp(ebox, ebox)
403 RETURNS int4
404 LANGUAGE C IMMUTABLE STRICT
405 AS '$libdir/latlon-v0010', 'pgl_btree_ebox_cmp';
407 CREATE OPERATOR CLASS ebox_btree_ops
408 DEFAULT FOR TYPE ebox USING btree AS
409 OPERATOR 1 <<< ,
410 OPERATOR 2 <<<= ,
411 OPERATOR 3 = ,
412 OPERATOR 4 >>>= ,
413 OPERATOR 5 >>> ,
414 FUNCTION 1 ebox_btree_cmp(ebox, ebox);
416 -- end of B-tree support for ebox
418 -- begin of B-tree support for ecircle
420 CREATE FUNCTION ecircle_btree_lt(ecircle, ecircle)
421 RETURNS boolean
422 LANGUAGE C IMMUTABLE STRICT
423 AS '$libdir/latlon-v0010', 'pgl_btree_ecircle_lt';
425 CREATE FUNCTION ecircle_btree_le(ecircle, ecircle)
426 RETURNS boolean
427 LANGUAGE C IMMUTABLE STRICT
428 AS '$libdir/latlon-v0010', 'pgl_btree_ecircle_le';
430 CREATE FUNCTION ecircle_btree_eq(ecircle, ecircle)
431 RETURNS boolean
432 LANGUAGE C IMMUTABLE STRICT
433 AS '$libdir/latlon-v0010', 'pgl_btree_ecircle_eq';
435 CREATE FUNCTION ecircle_btree_ne(ecircle, ecircle)
436 RETURNS boolean
437 LANGUAGE C IMMUTABLE STRICT
438 AS '$libdir/latlon-v0010', 'pgl_btree_ecircle_ne';
440 CREATE FUNCTION ecircle_btree_ge(ecircle, ecircle)
441 RETURNS boolean
442 LANGUAGE C IMMUTABLE STRICT
443 AS '$libdir/latlon-v0010', 'pgl_btree_ecircle_ge';
445 CREATE FUNCTION ecircle_btree_gt(ecircle, ecircle)
446 RETURNS boolean
447 LANGUAGE C IMMUTABLE STRICT
448 AS '$libdir/latlon-v0010', 'pgl_btree_ecircle_gt';
450 CREATE OPERATOR <<< (
451 leftarg = ecircle,
452 rightarg = ecircle,
453 procedure = ecircle_btree_lt,
454 commutator = >>>,
455 negator = >>>=,
456 restrict = scalarltsel,
457 join = scalarltjoinsel
458 );
460 CREATE OPERATOR <<<= (
461 leftarg = ecircle,
462 rightarg = ecircle,
463 procedure = ecircle_btree_le,
464 commutator = >>>=,
465 negator = >>>,
466 restrict = scalarltsel,
467 join = scalarltjoinsel
468 );
470 CREATE OPERATOR = (
471 leftarg = ecircle,
472 rightarg = ecircle,
473 procedure = ecircle_btree_eq,
474 commutator = =,
475 negator = <>,
476 restrict = eqsel,
477 join = eqjoinsel,
478 merges
479 );
481 CREATE OPERATOR <> (
482 leftarg = ecircle,
483 rightarg = ecircle,
484 procedure = ecircle_btree_ne,
485 commutator = <>,
486 negator = =,
487 restrict = neqsel,
488 join = neqjoinsel
489 );
491 CREATE OPERATOR >>>= (
492 leftarg = ecircle,
493 rightarg = ecircle,
494 procedure = ecircle_btree_ge,
495 commutator = <<<=,
496 negator = <<<,
497 restrict = scalargtsel,
498 join = scalargtjoinsel
499 );
501 CREATE OPERATOR >>> (
502 leftarg = ecircle,
503 rightarg = ecircle,
504 procedure = ecircle_btree_gt,
505 commutator = <<<,
506 negator = <<<=,
507 restrict = scalargtsel,
508 join = scalargtjoinsel
509 );
511 CREATE FUNCTION ecircle_btree_cmp(ecircle, ecircle)
512 RETURNS int4
513 LANGUAGE C IMMUTABLE STRICT
514 AS '$libdir/latlon-v0010', 'pgl_btree_ecircle_cmp';
516 CREATE OPERATOR CLASS ecircle_btree_ops
517 DEFAULT FOR TYPE ecircle USING btree AS
518 OPERATOR 1 <<< ,
519 OPERATOR 2 <<<= ,
520 OPERATOR 3 = ,
521 OPERATOR 4 >>>= ,
522 OPERATOR 5 >>> ,
523 FUNCTION 1 ecircle_btree_cmp(ecircle, ecircle);
525 -- end of B-tree support for ecircle
528 ----------------
529 -- type casts --
530 ----------------
532 CREATE FUNCTION cast_epoint_to_ebox(epoint)
533 RETURNS ebox
534 LANGUAGE C IMMUTABLE STRICT
535 AS '$libdir/latlon-v0010', 'pgl_epoint_to_ebox';
537 CREATE CAST (epoint AS ebox) WITH FUNCTION cast_epoint_to_ebox(epoint);
539 CREATE FUNCTION cast_epoint_to_ecircle(epoint)
540 RETURNS ecircle
541 LANGUAGE C IMMUTABLE STRICT
542 AS '$libdir/latlon-v0010', 'pgl_epoint_to_ecircle';
544 CREATE CAST (epoint AS ecircle) WITH FUNCTION cast_epoint_to_ecircle(epoint);
546 CREATE FUNCTION cast_epoint_to_ecluster(epoint)
547 RETURNS ecluster
548 LANGUAGE C IMMUTABLE STRICT
549 AS '$libdir/latlon-v0010', 'pgl_epoint_to_ecluster';
551 CREATE CAST (epoint AS ecluster) WITH FUNCTION cast_epoint_to_ecluster(epoint);
553 CREATE FUNCTION cast_ebox_to_ecluster(ebox)
554 RETURNS ecluster
555 LANGUAGE C IMMUTABLE STRICT
556 AS '$libdir/latlon-v0010', 'pgl_ebox_to_ecluster';
558 CREATE CAST (ebox AS ecluster) WITH FUNCTION cast_ebox_to_ecluster(ebox);
561 ---------------------------
562 -- constructor functions --
563 ---------------------------
565 CREATE FUNCTION epoint(float8, float8)
566 RETURNS epoint
567 LANGUAGE C IMMUTABLE STRICT
568 AS '$libdir/latlon-v0010', 'pgl_create_epoint';
570 CREATE FUNCTION epoint_latlon(float8, float8)
571 RETURNS epoint
572 LANGUAGE SQL IMMUTABLE STRICT AS $$
573 SELECT @extschema@.epoint($1, $2)
574 $$;
576 CREATE FUNCTION epoint_lonlat(float8, float8)
577 RETURNS epoint
578 LANGUAGE SQL IMMUTABLE STRICT AS $$
579 SELECT @extschema@.epoint($2, $1)
580 $$;
582 CREATE FUNCTION epoint_with_sample_count(epoint, int4)
583 RETURNS epoint_with_sample_count
584 LANGUAGE C IMMUTABLE STRICT
585 AS '$libdir/latlon-v0010', 'pgl_create_epoint_with_sample_count';
587 CREATE FUNCTION empty_ebox()
588 RETURNS ebox
589 LANGUAGE C IMMUTABLE STRICT
590 AS '$libdir/latlon-v0010', 'pgl_create_empty_ebox';
592 CREATE FUNCTION ebox(float8, float8, float8, float8)
593 RETURNS ebox
594 LANGUAGE C IMMUTABLE STRICT
595 AS '$libdir/latlon-v0010', 'pgl_create_ebox';
597 CREATE FUNCTION ebox(epoint, epoint)
598 RETURNS ebox
599 LANGUAGE C IMMUTABLE STRICT
600 AS '$libdir/latlon-v0010', 'pgl_create_ebox_from_epoints';
602 CREATE FUNCTION ecircle(float8, float8, float8)
603 RETURNS ecircle
604 LANGUAGE C IMMUTABLE STRICT
605 AS '$libdir/latlon-v0010', 'pgl_create_ecircle';
607 CREATE FUNCTION ecircle(epoint, float8)
608 RETURNS ecircle
609 LANGUAGE C IMMUTABLE STRICT
610 AS '$libdir/latlon-v0010', 'pgl_create_ecircle_from_epoint';
612 CREATE FUNCTION ecluster_concat(ecluster[])
613 RETURNS ecluster
614 LANGUAGE sql IMMUTABLE STRICT AS $$
615 SELECT pg_catalog.array_to_string($1, ' ')::@extschema@.ecluster
616 $$;
618 CREATE FUNCTION ecluster_concat(ecluster, ecluster)
619 RETURNS ecluster
620 LANGUAGE sql IMMUTABLE STRICT AS $$
621 SELECT (
622 $1::pg_catalog.text OPERATOR(pg_catalog.||) ' ' OPERATOR(pg_catalog.||)
623 $2::pg_catalog.text
624 )::@extschema@.ecluster
625 $$;
627 CREATE FUNCTION ecluster_create_multipoint(epoint[])
628 RETURNS ecluster
629 LANGUAGE sql IMMUTABLE STRICT AS $$
630 SELECT
631 pg_catalog.array_to_string(
632 pg_catalog.array_agg(
633 'point (' OPERATOR(pg_catalog.||) unnest OPERATOR(pg_catalog.||) ')'
634 ),
635 ' '
636 )::@extschema@.ecluster
637 FROM pg_catalog.unnest($1)
638 $$;
640 CREATE FUNCTION ecluster_create_path(epoint[])
641 RETURNS ecluster
642 LANGUAGE sql IMMUTABLE STRICT AS $$
643 SELECT CASE WHEN "str" OPERATOR(pg_catalog.=) '' THEN
644 'empty'::@extschema@.ecluster
645 ELSE
646 (
647 'path (' OPERATOR(pg_catalog.||) "str" OPERATOR(pg_catalog.||) ')'
648 )::@extschema@.ecluster
649 END
650 FROM pg_catalog.array_to_string($1, ' ') AS "str"
651 $$;
653 CREATE FUNCTION ecluster_create_outline(epoint[])
654 RETURNS ecluster
655 LANGUAGE sql IMMUTABLE STRICT AS $$
656 SELECT CASE WHEN "str" OPERATOR(pg_catalog.=) '' THEN
657 'empty'::@extschema@.ecluster
658 ELSE
659 (
660 'outline (' OPERATOR(pg_catalog.||) "str" OPERATOR(pg_catalog.||) ')'
661 )::@extschema@.ecluster
662 END
663 FROM pg_catalog.array_to_string($1, ' ') AS "str"
664 $$;
666 CREATE FUNCTION ecluster_create_polygon(epoint[])
667 RETURNS ecluster
668 LANGUAGE sql IMMUTABLE STRICT AS $$
669 SELECT CASE WHEN "str" OPERATOR(pg_catalog.=) '' THEN
670 'empty'::@extschema@.ecluster
671 ELSE
672 (
673 'polygon (' OPERATOR(pg_catalog.||) pg_catalog.array_to_string($1, ' ')
674 OPERATOR(pg_catalog.||) ')'
675 )::@extschema@.ecluster
676 END
677 FROM pg_catalog.array_to_string($1, ' ') AS "str"
678 $$;
681 ----------------------
682 -- getter functions --
683 ----------------------
685 CREATE FUNCTION latitude(epoint)
686 RETURNS float8
687 LANGUAGE C IMMUTABLE STRICT
688 AS '$libdir/latlon-v0010', 'pgl_epoint_lat';
690 CREATE FUNCTION longitude(epoint)
691 RETURNS float8
692 LANGUAGE C IMMUTABLE STRICT
693 AS '$libdir/latlon-v0010', 'pgl_epoint_lon';
695 CREATE FUNCTION min_latitude(ebox)
696 RETURNS float8
697 LANGUAGE C IMMUTABLE STRICT
698 AS '$libdir/latlon-v0010', 'pgl_ebox_lat_min';
700 CREATE FUNCTION max_latitude(ebox)
701 RETURNS float8
702 LANGUAGE C IMMUTABLE STRICT
703 AS '$libdir/latlon-v0010', 'pgl_ebox_lat_max';
705 CREATE FUNCTION min_longitude(ebox)
706 RETURNS float8
707 LANGUAGE C IMMUTABLE STRICT
708 AS '$libdir/latlon-v0010', 'pgl_ebox_lon_min';
710 CREATE FUNCTION max_longitude(ebox)
711 RETURNS float8
712 LANGUAGE C IMMUTABLE STRICT
713 AS '$libdir/latlon-v0010', 'pgl_ebox_lon_max';
715 CREATE FUNCTION center(ecircle)
716 RETURNS epoint
717 LANGUAGE C IMMUTABLE STRICT
718 AS '$libdir/latlon-v0010', 'pgl_ecircle_center';
720 CREATE FUNCTION radius(ecircle)
721 RETURNS float8
722 LANGUAGE C IMMUTABLE STRICT
723 AS '$libdir/latlon-v0010', 'pgl_ecircle_radius';
725 CREATE FUNCTION ecluster_extract_points(ecluster)
726 RETURNS SETOF epoint
727 LANGUAGE sql IMMUTABLE STRICT AS $$
728 SELECT "match"[2]::@extschema@.epoint
729 FROM pg_catalog.regexp_matches(
730 $1::pg_catalog.text, e'(^| )point \\(([^)]+)\\)', 'g'
731 ) AS "match"
732 $$;
734 CREATE FUNCTION ecluster_extract_paths(ecluster)
735 RETURNS SETOF epoint[]
736 LANGUAGE sql IMMUTABLE STRICT AS $$
737 SELECT (
738 SELECT pg_catalog.array_agg("m2"[1]::@extschema@.epoint)
739 FROM pg_catalog.regexp_matches("m1"[2], e'[^ ]+ [^ ]+', 'g') AS "m2"
740 )
741 FROM pg_catalog.regexp_matches(
742 $1::pg_catalog.text, e'(^| )path \\(([^)]+)\\)', 'g'
743 ) AS "m1"
744 $$;
746 CREATE FUNCTION ecluster_extract_outlines(ecluster)
747 RETURNS SETOF epoint[]
748 LANGUAGE sql IMMUTABLE STRICT AS $$
749 SELECT (
750 SELECT pg_catalog.array_agg("m2"[1]::@extschema@.epoint)
751 FROM pg_catalog.regexp_matches("m1"[2], e'[^ ]+ [^ ]+', 'g') AS "m2"
752 )
753 FROM pg_catalog.regexp_matches(
754 $1::pg_catalog.text, e'(^| )outline \\(([^)]+)\\)', 'g'
755 ) AS "m1"
756 $$;
758 CREATE FUNCTION ecluster_extract_polygons(ecluster)
759 RETURNS SETOF epoint[]
760 LANGUAGE sql IMMUTABLE STRICT AS $$
761 SELECT (
762 SELECT pg_catalog.array_agg("m2"[1]::@extschema@.epoint)
763 FROM pg_catalog.regexp_matches("m1"[2], e'[^ ]+ [^ ]+', 'g') AS "m2"
764 )
765 FROM pg_catalog.regexp_matches(
766 $1::pg_catalog.text, e'(^| )polygon \\(([^)]+)\\)', 'g'
767 ) AS "m1"
768 $$;
771 ---------------
772 -- operators --
773 ---------------
775 CREATE FUNCTION epoint_ebox_overlap_proc(epoint, ebox)
776 RETURNS boolean
777 LANGUAGE C IMMUTABLE STRICT
778 AS '$libdir/latlon-v0010', 'pgl_epoint_ebox_overlap';
780 CREATE FUNCTION epoint_ecircle_overlap_proc(epoint, ecircle)
781 RETURNS boolean
782 LANGUAGE C IMMUTABLE STRICT
783 AS '$libdir/latlon-v0010', 'pgl_epoint_ecircle_overlap';
785 CREATE FUNCTION epoint_ecluster_overlap_proc(epoint, ecluster)
786 RETURNS boolean
787 LANGUAGE C IMMUTABLE STRICT
788 AS '$libdir/latlon-v0010', 'pgl_epoint_ecluster_overlap';
790 CREATE FUNCTION epoint_ecluster_may_overlap_proc(epoint, ecluster)
791 RETURNS boolean
792 LANGUAGE C IMMUTABLE STRICT
793 AS '$libdir/latlon-v0010', 'pgl_epoint_ecluster_may_overlap';
795 CREATE FUNCTION ebox_overlap_proc(ebox, ebox)
796 RETURNS boolean
797 LANGUAGE C IMMUTABLE STRICT
798 AS '$libdir/latlon-v0010', 'pgl_ebox_overlap';
800 CREATE FUNCTION ebox_ecircle_may_overlap_proc(ebox, ecircle)
801 RETURNS boolean
802 LANGUAGE C IMMUTABLE STRICT
803 AS '$libdir/latlon-v0010', 'pgl_ebox_ecircle_may_overlap';
805 CREATE FUNCTION ebox_ecluster_may_overlap_proc(ebox, ecluster)
806 RETURNS boolean
807 LANGUAGE C IMMUTABLE STRICT
808 AS '$libdir/latlon-v0010', 'pgl_ebox_ecluster_may_overlap';
810 CREATE FUNCTION ecircle_overlap_proc(ecircle, ecircle)
811 RETURNS boolean
812 LANGUAGE C IMMUTABLE STRICT
813 AS '$libdir/latlon-v0010', 'pgl_ecircle_overlap';
815 CREATE FUNCTION ecircle_ecluster_overlap_proc(ecircle, ecluster)
816 RETURNS boolean
817 LANGUAGE C IMMUTABLE STRICT
818 AS '$libdir/latlon-v0010', 'pgl_ecircle_ecluster_overlap';
820 CREATE FUNCTION ecircle_ecluster_may_overlap_proc(ecircle, ecluster)
821 RETURNS boolean
822 LANGUAGE C IMMUTABLE STRICT
823 AS '$libdir/latlon-v0010', 'pgl_ecircle_ecluster_may_overlap';
825 CREATE FUNCTION ecluster_overlap_proc(ecluster, ecluster)
826 RETURNS boolean
827 LANGUAGE C IMMUTABLE STRICT
828 AS '$libdir/latlon-v0010', 'pgl_ecluster_overlap';
830 CREATE FUNCTION ecluster_may_overlap_proc(ecluster, ecluster)
831 RETURNS boolean
832 LANGUAGE C IMMUTABLE STRICT
833 AS '$libdir/latlon-v0010', 'pgl_ecluster_may_overlap';
835 CREATE FUNCTION ecluster_contains_proc(ecluster, ecluster)
836 RETURNS boolean
837 LANGUAGE C IMMUTABLE STRICT
838 AS '$libdir/latlon-v0010', 'pgl_ecluster_contains';
840 CREATE FUNCTION epoint_distance_proc(epoint, epoint)
841 RETURNS float8
842 LANGUAGE C IMMUTABLE STRICT
843 AS '$libdir/latlon-v0010', 'pgl_epoint_distance';
845 CREATE FUNCTION epoint_ecircle_distance_proc(epoint, ecircle)
846 RETURNS float8
847 LANGUAGE C IMMUTABLE STRICT
848 AS '$libdir/latlon-v0010', 'pgl_epoint_ecircle_distance';
850 CREATE FUNCTION epoint_ecluster_distance_proc(epoint, ecluster)
851 RETURNS float8
852 LANGUAGE C IMMUTABLE STRICT
853 AS '$libdir/latlon-v0010', 'pgl_epoint_ecluster_distance';
855 CREATE FUNCTION ecircle_distance_proc(ecircle, ecircle)
856 RETURNS float8
857 LANGUAGE C IMMUTABLE STRICT
858 AS '$libdir/latlon-v0010', 'pgl_ecircle_distance';
860 CREATE FUNCTION ecircle_ecluster_distance_proc(ecircle, ecluster)
861 RETURNS float8
862 LANGUAGE C IMMUTABLE STRICT
863 AS '$libdir/latlon-v0010', 'pgl_ecircle_ecluster_distance';
865 CREATE FUNCTION ecluster_distance_proc(ecluster, ecluster)
866 RETURNS float8
867 LANGUAGE C IMMUTABLE STRICT
868 AS '$libdir/latlon-v0010', 'pgl_ecluster_distance';
870 CREATE FUNCTION fair_distance_operator_proc(ecluster, epoint_with_sample_count)
871 RETURNS float8
872 LANGUAGE C IMMUTABLE STRICT
873 AS '$libdir/latlon-v0010', 'pgl_ecluster_epoint_sc_fair_distance';
875 CREATE OPERATOR && (
876 leftarg = epoint,
877 rightarg = ebox,
878 procedure = epoint_ebox_overlap_proc,
879 commutator = &&,
880 restrict = areasel,
881 join = areajoinsel
882 );
884 CREATE FUNCTION epoint_ebox_overlap_commutator(ebox, epoint)
885 RETURNS boolean
886 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&) $1';
888 CREATE OPERATOR && (
889 leftarg = ebox,
890 rightarg = epoint,
891 procedure = epoint_ebox_overlap_commutator,
892 commutator = &&,
893 restrict = areasel,
894 join = areajoinsel
895 );
897 CREATE OPERATOR && (
898 leftarg = epoint,
899 rightarg = ecircle,
900 procedure = epoint_ecircle_overlap_proc,
901 commutator = &&,
902 restrict = areasel,
903 join = areajoinsel
904 );
906 CREATE FUNCTION epoint_ecircle_overlap_commutator(ecircle, epoint)
907 RETURNS boolean
908 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&) $1';
910 CREATE OPERATOR && (
911 leftarg = ecircle,
912 rightarg = epoint,
913 procedure = epoint_ecircle_overlap_commutator,
914 commutator = &&,
915 restrict = areasel,
916 join = areajoinsel
917 );
919 CREATE OPERATOR && (
920 leftarg = epoint,
921 rightarg = ecluster,
922 procedure = epoint_ecluster_overlap_proc,
923 commutator = &&,
924 restrict = areasel,
925 join = areajoinsel
926 );
928 CREATE FUNCTION epoint_ecluster_overlap_commutator(ecluster, epoint)
929 RETURNS boolean
930 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&) $1';
932 CREATE OPERATOR && (
933 leftarg = ecluster,
934 rightarg = epoint,
935 procedure = epoint_ecluster_overlap_commutator,
936 commutator = &&,
937 restrict = areasel,
938 join = areajoinsel
939 );
941 CREATE OPERATOR && (
942 leftarg = ebox,
943 rightarg = ebox,
944 procedure = ebox_overlap_proc,
945 commutator = &&,
946 restrict = areasel,
947 join = areajoinsel
948 );
950 CREATE OPERATOR && (
951 leftarg = ecircle,
952 rightarg = ecircle,
953 procedure = ecircle_overlap_proc,
954 commutator = &&,
955 restrict = areasel,
956 join = areajoinsel
957 );
959 CREATE OPERATOR && (
960 leftarg = ecircle,
961 rightarg = ecluster,
962 procedure = ecircle_ecluster_overlap_proc,
963 commutator = &&,
964 restrict = areasel,
965 join = areajoinsel
966 );
968 CREATE FUNCTION ecircle_ecluster_overlap_commutator(ecluster, ecircle)
969 RETURNS boolean
970 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&) $1';
972 CREATE OPERATOR && (
973 leftarg = ecluster,
974 rightarg = ecircle,
975 procedure = ecircle_ecluster_overlap_commutator,
976 commutator = &&,
977 restrict = areasel,
978 join = areajoinsel
979 );
981 CREATE OPERATOR && (
982 leftarg = ecluster,
983 rightarg = ecluster,
984 procedure = ecluster_overlap_proc,
985 commutator = &&,
986 restrict = areasel,
987 join = areajoinsel
988 );
990 CREATE FUNCTION ebox_ecircle_overlap_castwrap(ebox, ecircle)
991 RETURNS boolean
992 LANGUAGE sql IMMUTABLE
993 AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.&&) $2';
995 CREATE OPERATOR && (
996 leftarg = ebox,
997 rightarg = ecircle,
998 procedure = ebox_ecircle_overlap_castwrap,
999 commutator = &&,
1000 restrict = areasel,
1001 join = areajoinsel
1002 );
1004 CREATE FUNCTION ebox_ecircle_overlap_castwrap(ecircle, ebox)
1005 RETURNS boolean
1006 LANGUAGE sql IMMUTABLE
1007 AS 'SELECT $1 OPERATOR(@extschema@.&&) $2::@extschema@.ecluster';
1009 CREATE OPERATOR && (
1010 leftarg = ecircle,
1011 rightarg = ebox,
1012 procedure = ebox_ecircle_overlap_castwrap,
1013 commutator = &&,
1014 restrict = areasel,
1015 join = areajoinsel
1016 );
1018 CREATE FUNCTION ebox_ecluster_overlap_castwrap(ebox, ecluster)
1019 RETURNS boolean
1020 LANGUAGE sql IMMUTABLE
1021 AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.&&) $2';
1023 CREATE OPERATOR && (
1024 leftarg = ebox,
1025 rightarg = ecluster,
1026 procedure = ebox_ecluster_overlap_castwrap,
1027 commutator = &&,
1028 restrict = areasel,
1029 join = areajoinsel
1030 );
1032 CREATE FUNCTION ebox_ecluster_overlap_castwrap(ecluster, ebox)
1033 RETURNS boolean
1034 LANGUAGE sql IMMUTABLE
1035 AS 'SELECT $1 OPERATOR(@extschema@.&&) $2::@extschema@.ecluster';
1037 CREATE OPERATOR && (
1038 leftarg = ecluster,
1039 rightarg = ebox,
1040 procedure = ebox_ecluster_overlap_castwrap,
1041 commutator = &&,
1042 restrict = areasel,
1043 join = areajoinsel
1044 );
1046 CREATE OPERATOR &&+ (
1047 leftarg = epoint,
1048 rightarg = ecluster,
1049 procedure = epoint_ecluster_may_overlap_proc,
1050 commutator = &&+,
1051 restrict = areasel,
1052 join = areajoinsel
1053 );
1055 CREATE FUNCTION epoint_ecluster_may_overlap_commutator(ecluster, epoint)
1056 RETURNS boolean
1057 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&+) $1';
1059 CREATE OPERATOR &&+ (
1060 leftarg = ecluster,
1061 rightarg = epoint,
1062 procedure = epoint_ecluster_may_overlap_commutator,
1063 commutator = &&+,
1064 restrict = areasel,
1065 join = areajoinsel
1066 );
1068 CREATE OPERATOR &&+ (
1069 leftarg = ebox,
1070 rightarg = ecircle,
1071 procedure = ebox_ecircle_may_overlap_proc,
1072 commutator = &&+,
1073 restrict = areasel,
1074 join = areajoinsel
1075 );
1077 CREATE FUNCTION ebox_ecircle_may_overlap_commutator(ecircle, ebox)
1078 RETURNS boolean
1079 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&+) $1';
1081 CREATE OPERATOR &&+ (
1082 leftarg = ecircle,
1083 rightarg = ebox,
1084 procedure = ebox_ecircle_may_overlap_commutator,
1085 commutator = &&+,
1086 restrict = areasel,
1087 join = areajoinsel
1088 );
1090 CREATE OPERATOR &&+ (
1091 leftarg = ebox,
1092 rightarg = ecluster,
1093 procedure = ebox_ecluster_may_overlap_proc,
1094 commutator = &&+,
1095 restrict = areasel,
1096 join = areajoinsel
1097 );
1099 CREATE FUNCTION ebox_ecluster_may_overlap_commutator(ecluster, ebox)
1100 RETURNS boolean
1101 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&+) $1';
1103 CREATE OPERATOR &&+ (
1104 leftarg = ecluster,
1105 rightarg = ebox,
1106 procedure = ebox_ecluster_may_overlap_commutator,
1107 commutator = &&+,
1108 restrict = areasel,
1109 join = areajoinsel
1110 );
1112 CREATE OPERATOR &&+ (
1113 leftarg = ecircle,
1114 rightarg = ecluster,
1115 procedure = ecircle_ecluster_may_overlap_proc,
1116 commutator = &&+,
1117 restrict = areasel,
1118 join = areajoinsel
1119 );
1121 CREATE FUNCTION ecircle_ecluster_may_overlap_commutator(ecluster, ecircle)
1122 RETURNS boolean
1123 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&+) $1';
1125 CREATE OPERATOR &&+ (
1126 leftarg = ecluster,
1127 rightarg = ecircle,
1128 procedure = ecircle_ecluster_may_overlap_commutator,
1129 commutator = &&+,
1130 restrict = areasel,
1131 join = areajoinsel
1132 );
1134 CREATE OPERATOR &&+ (
1135 leftarg = ecluster,
1136 rightarg = ecluster,
1137 procedure = ecluster_may_overlap_proc,
1138 commutator = &&+,
1139 restrict = areasel,
1140 join = areajoinsel
1141 );
1143 CREATE OPERATOR @> (
1144 leftarg = ebox,
1145 rightarg = epoint,
1146 procedure = epoint_ebox_overlap_commutator,
1147 commutator = <@,
1148 restrict = areasel,
1149 join = areajoinsel
1150 );
1152 CREATE OPERATOR <@ (
1153 leftarg = epoint,
1154 rightarg = ebox,
1155 procedure = epoint_ebox_overlap_proc,
1156 commutator = @>,
1157 restrict = areasel,
1158 join = areajoinsel
1159 );
1161 CREATE OPERATOR @> (
1162 leftarg = ecluster,
1163 rightarg = epoint,
1164 procedure = epoint_ecluster_overlap_commutator,
1165 commutator = <@,
1166 restrict = areasel,
1167 join = areajoinsel
1168 );
1170 CREATE OPERATOR <@ (
1171 leftarg = epoint,
1172 rightarg = ecluster,
1173 procedure = epoint_ecluster_overlap_proc,
1174 commutator = @>,
1175 restrict = areasel,
1176 join = areajoinsel
1177 );
1179 CREATE OPERATOR @> (
1180 leftarg = ecluster,
1181 rightarg = ecluster,
1182 procedure = ecluster_contains_proc,
1183 commutator = <@,
1184 restrict = areasel,
1185 join = areajoinsel
1186 );
1188 CREATE FUNCTION ecluster_contains_commutator(ecluster, ecluster)
1189 RETURNS boolean
1190 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.@>) $1';
1192 CREATE OPERATOR <@ (
1193 leftarg = ecluster,
1194 rightarg = ecluster,
1195 procedure = ecluster_contains_commutator,
1196 commutator = @>,
1197 restrict = areasel,
1198 join = areajoinsel
1199 );
1201 CREATE FUNCTION ebox_contains_castwrap(ebox, ebox)
1202 RETURNS boolean
1203 LANGUAGE sql IMMUTABLE AS $$
1204 SELECT
1205 $1::@extschema@.ecluster OPERATOR(@extschema@.@>) $2::@extschema@.ecluster
1206 $$;
1208 CREATE OPERATOR @> (
1209 leftarg = ebox,
1210 rightarg = ebox,
1211 procedure = ebox_contains_castwrap,
1212 commutator = <@,
1213 restrict = areasel,
1214 join = areajoinsel
1215 );
1217 CREATE FUNCTION ebox_contains_swapped_castwrap(ebox, ebox)
1218 RETURNS boolean
1219 LANGUAGE sql IMMUTABLE AS $$
1220 SELECT
1221 $2::@extschema@.ecluster OPERATOR(@extschema@.@>) $1::@extschema@.ecluster
1222 $$;
1224 CREATE OPERATOR <@ (
1225 leftarg = ebox,
1226 rightarg = ebox,
1227 procedure = ebox_contains_swapped_castwrap,
1228 commutator = @>,
1229 restrict = areasel,
1230 join = areajoinsel
1231 );
1233 CREATE FUNCTION ebox_ecluster_contains_castwrap(ebox, ecluster)
1234 RETURNS boolean
1235 LANGUAGE sql IMMUTABLE
1236 AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.@>) $2';
1238 CREATE OPERATOR @> (
1239 leftarg = ebox,
1240 rightarg = ecluster,
1241 procedure = ebox_ecluster_contains_castwrap,
1242 commutator = <@,
1243 restrict = areasel,
1244 join = areajoinsel
1245 );
1247 CREATE FUNCTION ebox_ecluster_contains_castwrap(ecluster, ebox)
1248 RETURNS boolean
1249 LANGUAGE sql IMMUTABLE
1250 AS 'SELECT $2::@extschema@.ecluster OPERATOR(@extschema@.@>) $1';
1252 CREATE OPERATOR <@ (
1253 leftarg = ecluster,
1254 rightarg = ebox,
1255 procedure = ebox_ecluster_contains_castwrap,
1256 commutator = @>,
1257 restrict = areasel,
1258 join = areajoinsel
1259 );
1261 CREATE FUNCTION ecluster_ebox_contains_castwrap(ecluster, ebox)
1262 RETURNS boolean
1263 LANGUAGE sql IMMUTABLE
1264 AS 'SELECT $1 OPERATOR(@extschema@.@>) $2::@extschema@.ecluster';
1266 CREATE OPERATOR @> (
1267 leftarg = ecluster,
1268 rightarg = ebox,
1269 procedure = ecluster_ebox_contains_castwrap,
1270 commutator = <@,
1271 restrict = areasel,
1272 join = areajoinsel
1273 );
1275 CREATE FUNCTION ecluster_ebox_contains_castwrap(ebox, ecluster)
1276 RETURNS boolean
1277 LANGUAGE sql IMMUTABLE
1278 AS 'SELECT $2 OPERATOR(@extschema@.@>) $1::@extschema@.ecluster';
1280 CREATE OPERATOR <@ (
1281 leftarg = ebox,
1282 rightarg = ecluster,
1283 procedure = ecluster_ebox_contains_castwrap,
1284 commutator = @>,
1285 restrict = areasel,
1286 join = areajoinsel
1287 );
1289 CREATE OPERATOR <-> (
1290 leftarg = epoint,
1291 rightarg = epoint,
1292 procedure = epoint_distance_proc,
1293 commutator = <->
1294 );
1296 CREATE OPERATOR <-> (
1297 leftarg = epoint,
1298 rightarg = ecircle,
1299 procedure = epoint_ecircle_distance_proc,
1300 commutator = <->
1301 );
1303 CREATE FUNCTION epoint_ecircle_distance_commutator(ecircle, epoint)
1304 RETURNS float8
1305 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.<->) $1';
1307 CREATE OPERATOR <-> (
1308 leftarg = ecircle,
1309 rightarg = epoint,
1310 procedure = epoint_ecircle_distance_commutator,
1311 commutator = <->
1312 );
1314 CREATE OPERATOR <-> (
1315 leftarg = epoint,
1316 rightarg = ecluster,
1317 procedure = epoint_ecluster_distance_proc,
1318 commutator = <->
1319 );
1321 CREATE FUNCTION epoint_ecluster_distance_commutator(ecluster, epoint)
1322 RETURNS float8
1323 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.<->) $1';
1325 CREATE OPERATOR <-> (
1326 leftarg = ecluster,
1327 rightarg = epoint,
1328 procedure = epoint_ecluster_distance_commutator,
1329 commutator = <->
1330 );
1332 CREATE OPERATOR <-> (
1333 leftarg = ecircle,
1334 rightarg = ecircle,
1335 procedure = ecircle_distance_proc,
1336 commutator = <->
1337 );
1339 CREATE OPERATOR <-> (
1340 leftarg = ecircle,
1341 rightarg = ecluster,
1342 procedure = ecircle_ecluster_distance_proc,
1343 commutator = <->
1344 );
1346 CREATE FUNCTION ecircle_ecluster_distance_commutator(ecluster, ecircle)
1347 RETURNS float8
1348 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.<->) $1';
1350 CREATE OPERATOR <-> (
1351 leftarg = ecluster,
1352 rightarg = ecircle,
1353 procedure = ecircle_ecluster_distance_commutator,
1354 commutator = <->
1355 );
1357 CREATE OPERATOR <-> (
1358 leftarg = ecluster,
1359 rightarg = ecluster,
1360 procedure = ecluster_distance_proc,
1361 commutator = <->
1362 );
1364 CREATE FUNCTION epoint_ebox_distance_castwrap(epoint, ebox)
1365 RETURNS float8
1366 LANGUAGE sql IMMUTABLE
1367 AS 'SELECT $1 OPERATOR(@extschema@.<->) $2::@extschema@.ecluster';
1369 CREATE OPERATOR <-> (
1370 leftarg = epoint,
1371 rightarg = ebox,
1372 procedure = epoint_ebox_distance_castwrap,
1373 commutator = <->
1374 );
1376 CREATE FUNCTION epoint_ebox_distance_castwrap(ebox, epoint)
1377 RETURNS float8
1378 LANGUAGE sql IMMUTABLE
1379 AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.<->) $2';
1381 CREATE OPERATOR <-> (
1382 leftarg = ebox,
1383 rightarg = epoint,
1384 procedure = epoint_ebox_distance_castwrap,
1385 commutator = <->
1386 );
1388 CREATE FUNCTION ebox_distance_castwrap(ebox, ebox)
1389 RETURNS float8
1390 LANGUAGE sql IMMUTABLE AS $$
1391 SELECT
1392 $1::@extschema@.ecluster OPERATOR(@extschema@.<->) $2::@extschema@.ecluster
1393 $$;
1395 CREATE OPERATOR <-> (
1396 leftarg = ebox,
1397 rightarg = ebox,
1398 procedure = ebox_distance_castwrap,
1399 commutator = <->
1400 );
1402 CREATE FUNCTION ebox_ecircle_distance_castwrap(ebox, ecircle)
1403 RETURNS float8
1404 LANGUAGE sql IMMUTABLE
1405 AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.<->) $2';
1407 CREATE OPERATOR <-> (
1408 leftarg = ebox,
1409 rightarg = ecircle,
1410 procedure = ebox_ecircle_distance_castwrap,
1411 commutator = <->
1412 );
1414 CREATE FUNCTION ebox_ecircle_distance_castwrap(ecircle, ebox)
1415 RETURNS float8
1416 LANGUAGE sql IMMUTABLE
1417 AS 'SELECT $1 OPERATOR(@extschema@.<->) $2::@extschema@.ecluster';
1419 CREATE OPERATOR <-> (
1420 leftarg = ecircle,
1421 rightarg = ebox,
1422 procedure = ebox_ecircle_distance_castwrap,
1423 commutator = <->
1424 );
1426 CREATE FUNCTION ebox_ecluster_distance_castwrap(ebox, ecluster)
1427 RETURNS float8
1428 LANGUAGE sql IMMUTABLE
1429 AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.<->) $2';
1431 CREATE OPERATOR <-> (
1432 leftarg = ebox,
1433 rightarg = ecluster,
1434 procedure = ebox_ecluster_distance_castwrap,
1435 commutator = <->
1436 );
1438 CREATE FUNCTION ebox_ecluster_distance_castwrap(ecluster, ebox)
1439 RETURNS float8
1440 LANGUAGE sql IMMUTABLE
1441 AS 'SELECT $1 OPERATOR(@extschema@.<->) $2::@extschema@.ecluster';
1443 CREATE OPERATOR <-> (
1444 leftarg = ecluster,
1445 rightarg = ebox,
1446 procedure = ebox_ecluster_distance_castwrap,
1447 commutator = <->
1448 );
1450 CREATE OPERATOR <=> (
1451 leftarg = ecluster,
1452 rightarg = epoint_with_sample_count,
1453 procedure = fair_distance_operator_proc
1454 );
1457 ----------------
1458 -- GiST index --
1459 ----------------
1461 CREATE FUNCTION pgl_gist_consistent(internal, internal, smallint, oid, internal)
1462 RETURNS boolean
1463 LANGUAGE C STRICT
1464 AS '$libdir/latlon-v0010', 'pgl_gist_consistent';
1466 CREATE FUNCTION pgl_gist_union(internal, internal)
1467 RETURNS internal
1468 LANGUAGE C STRICT
1469 AS '$libdir/latlon-v0010', 'pgl_gist_union';
1471 CREATE FUNCTION pgl_gist_compress_epoint(internal)
1472 RETURNS internal
1473 LANGUAGE C STRICT
1474 AS '$libdir/latlon-v0010', 'pgl_gist_compress_epoint';
1476 CREATE FUNCTION pgl_gist_compress_ecircle(internal)
1477 RETURNS internal
1478 LANGUAGE C STRICT
1479 AS '$libdir/latlon-v0010', 'pgl_gist_compress_ecircle';
1481 CREATE FUNCTION pgl_gist_compress_ecluster(internal)
1482 RETURNS internal
1483 LANGUAGE C STRICT
1484 AS '$libdir/latlon-v0010', 'pgl_gist_compress_ecluster';
1486 CREATE FUNCTION pgl_gist_decompress(internal)
1487 RETURNS internal
1488 LANGUAGE C STRICT
1489 AS '$libdir/latlon-v0010', 'pgl_gist_decompress';
1491 CREATE FUNCTION pgl_gist_penalty(internal, internal, internal)
1492 RETURNS internal
1493 LANGUAGE C STRICT
1494 AS '$libdir/latlon-v0010', 'pgl_gist_penalty';
1496 CREATE FUNCTION pgl_gist_picksplit(internal, internal)
1497 RETURNS internal
1498 LANGUAGE C STRICT
1499 AS '$libdir/latlon-v0010', 'pgl_gist_picksplit';
1501 CREATE FUNCTION pgl_gist_same(internal, internal, internal)
1502 RETURNS internal
1503 LANGUAGE C STRICT
1504 AS '$libdir/latlon-v0010', 'pgl_gist_same';
1506 CREATE FUNCTION pgl_gist_distance(internal, internal, smallint, oid)
1507 RETURNS internal
1508 LANGUAGE C STRICT
1509 AS '$libdir/latlon-v0010', 'pgl_gist_distance';
1511 CREATE OPERATOR CLASS epoint_ops
1512 DEFAULT FOR TYPE epoint USING gist AS
1513 OPERATOR 11 = ,
1514 OPERATOR 22 && (epoint, ebox),
1515 OPERATOR 222 <@ (epoint, ebox),
1516 OPERATOR 23 && (epoint, ecircle),
1517 OPERATOR 24 && (epoint, ecluster),
1518 OPERATOR 124 &&+ (epoint, ecluster),
1519 OPERATOR 224 <@ (epoint, ecluster),
1520 OPERATOR 31 <-> (epoint, epoint) FOR ORDER BY float_ops,
1521 OPERATOR 32 <-> (epoint, ebox) FOR ORDER BY float_ops,
1522 OPERATOR 33 <-> (epoint, ecircle) FOR ORDER BY float_ops,
1523 OPERATOR 34 <-> (epoint, ecluster) FOR ORDER BY float_ops,
1524 FUNCTION 1 pgl_gist_consistent(internal, internal, smallint, oid, internal),
1525 FUNCTION 2 pgl_gist_union(internal, internal),
1526 FUNCTION 3 pgl_gist_compress_epoint(internal),
1527 FUNCTION 4 pgl_gist_decompress(internal),
1528 FUNCTION 5 pgl_gist_penalty(internal, internal, internal),
1529 FUNCTION 6 pgl_gist_picksplit(internal, internal),
1530 FUNCTION 7 pgl_gist_same(internal, internal, internal),
1531 FUNCTION 8 pgl_gist_distance(internal, internal, smallint, oid),
1532 STORAGE ekey_point;
1534 CREATE OPERATOR CLASS ecircle_ops
1535 DEFAULT FOR TYPE ecircle USING gist AS
1536 OPERATOR 13 = ,
1537 OPERATOR 21 && (ecircle, epoint),
1538 OPERATOR 22 && (ecircle, ebox),
1539 OPERATOR 122 &&+ (ecircle, ebox),
1540 OPERATOR 23 && (ecircle, ecircle),
1541 OPERATOR 24 && (ecircle, ecluster),
1542 OPERATOR 124 &&+ (ecircle, ecluster),
1543 OPERATOR 31 <-> (ecircle, epoint) FOR ORDER BY float_ops,
1544 OPERATOR 32 <-> (ecircle, ebox) FOR ORDER BY float_ops,
1545 OPERATOR 33 <-> (ecircle, ecircle) FOR ORDER BY float_ops,
1546 OPERATOR 34 <-> (ecircle, ecluster) FOR ORDER BY float_ops,
1547 FUNCTION 1 pgl_gist_consistent(internal, internal, smallint, oid, internal),
1548 FUNCTION 2 pgl_gist_union(internal, internal),
1549 FUNCTION 3 pgl_gist_compress_ecircle(internal),
1550 FUNCTION 4 pgl_gist_decompress(internal),
1551 FUNCTION 5 pgl_gist_penalty(internal, internal, internal),
1552 FUNCTION 6 pgl_gist_picksplit(internal, internal),
1553 FUNCTION 7 pgl_gist_same(internal, internal, internal),
1554 FUNCTION 8 pgl_gist_distance(internal, internal, smallint, oid),
1555 STORAGE ekey_area;
1557 CREATE OPERATOR CLASS ecluster_ops
1558 DEFAULT FOR TYPE ecluster USING gist AS
1559 OPERATOR 21 && (ecluster, epoint),
1560 OPERATOR 121 &&+ (ecluster, epoint),
1561 OPERATOR 221 @> (ecluster, epoint),
1562 OPERATOR 22 && (ecluster, ebox),
1563 OPERATOR 122 &&+ (ecluster, ebox),
1564 OPERATOR 222 @> (ecluster, ebox),
1565 OPERATOR 322 <@ (ecluster, ebox),
1566 OPERATOR 23 && (ecluster, ecircle),
1567 OPERATOR 123 &&+ (ecluster, ecircle),
1568 OPERATOR 24 && (ecluster, ecluster),
1569 OPERATOR 124 &&+ (ecluster, ecluster),
1570 OPERATOR 224 @> (ecluster, ecluster),
1571 OPERATOR 324 <@ (ecluster, ecluster),
1572 OPERATOR 31 <-> (ecluster, epoint) FOR ORDER BY float_ops,
1573 OPERATOR 32 <-> (ecluster, ebox) FOR ORDER BY float_ops,
1574 OPERATOR 33 <-> (ecluster, ecircle) FOR ORDER BY float_ops,
1575 OPERATOR 34 <-> (ecluster, ecluster) FOR ORDER BY float_ops,
1576 OPERATOR 131 <=> (ecluster, epoint_with_sample_count) FOR ORDER BY float_ops,
1577 FUNCTION 1 pgl_gist_consistent(internal, internal, smallint, oid, internal),
1578 FUNCTION 2 pgl_gist_union(internal, internal),
1579 FUNCTION 3 pgl_gist_compress_ecluster(internal),
1580 FUNCTION 4 pgl_gist_decompress(internal),
1581 FUNCTION 5 pgl_gist_penalty(internal, internal, internal),
1582 FUNCTION 6 pgl_gist_picksplit(internal, internal),
1583 FUNCTION 7 pgl_gist_same(internal, internal, internal),
1584 FUNCTION 8 pgl_gist_distance(internal, internal, smallint, oid),
1585 STORAGE ekey_area;
1588 ---------------------
1589 -- alias functions --
1590 ---------------------
1592 CREATE FUNCTION distance(epoint, epoint)
1593 RETURNS float8
1594 LANGUAGE sql IMMUTABLE AS 'SELECT $1 OPERATOR(@extschema@.<->) $2';
1596 CREATE FUNCTION distance(ecluster, epoint)
1597 RETURNS float8
1598 LANGUAGE sql IMMUTABLE AS 'SELECT $1 OPERATOR(@extschema@.<->) $2';
1600 CREATE FUNCTION distance_within(epoint, epoint, float8)
1601 RETURNS boolean
1602 LANGUAGE sql IMMUTABLE
1603 AS 'SELECT $1 OPERATOR(@extschema@.&&) @extschema@.ecircle($2, $3)';
1605 CREATE FUNCTION distance_within(ecluster, epoint, float8)
1606 RETURNS boolean
1607 LANGUAGE sql IMMUTABLE
1608 AS 'SELECT $1 OPERATOR(@extschema@.&&) @extschema@.ecircle($2, $3)';
1610 CREATE FUNCTION fair_distance(ecluster, epoint, int4 = 10000)
1611 RETURNS float8
1612 LANGUAGE sql IMMUTABLE AS $$
1613 SELECT
1614 $1 OPERATOR(@extschema@.<=>) @extschema@.epoint_with_sample_count($2, $3)
1615 $$;
1618 --------------------------------
1619 -- other data storage formats --
1620 --------------------------------
1622 CREATE FUNCTION coords_to_epoint(float8, float8, text = 'epoint')
1623 RETURNS epoint
1624 LANGUAGE plpgsql IMMUTABLE STRICT AS $$
1625 DECLARE
1626 "result" @extschema@.epoint;
1627 BEGIN
1628 IF $3 OPERATOR(pg_catalog.=) 'epoint_lonlat' THEN
1629 -- avoid dynamic command execution for better performance
1630 RETURN @extschema@.epoint($2, $1);
1631 END IF;
1632 IF
1633 $3 OPERATOR(pg_catalog.=) 'epoint' OR
1634 $3 OPERATOR(pg_catalog.=) 'epoint_latlon'
1635 THEN
1636 -- avoid dynamic command execution for better performance
1637 RETURN @extschema@.epoint($1, $2);
1638 END IF;
1639 EXECUTE
1640 'SELECT ' OPERATOR(pg_catalog.||) $3 OPERATOR(pg_catalog.||) '($1, $2)' INTO STRICT "result" USING $1, $2;
1641 RETURN "result";
1642 END;
1643 $$;
1645 CREATE FUNCTION GeoJSON_LinearRing_vertices(jsonb, text = 'epoint_lonlat')
1646 RETURNS SETOF jsonb
1647 LANGUAGE sql IMMUTABLE STRICT AS $$
1648 SELECT "result" FROM
1649 ( SELECT pg_catalog.jsonb_array_length($1) - 1 )
1650 AS "lastindex_row" ("lastindex")
1651 CROSS JOIN LATERAL pg_catalog.jsonb_array_elements(
1652 CASE WHEN
1653 @extschema@.coords_to_epoint(
1654 ($1 OPERATOR(pg_catalog.->) 0 OPERATOR(pg_catalog.->>) 0)
1655 ::pg_catalog.float8,
1656 ($1 OPERATOR(pg_catalog.->) 0 OPERATOR(pg_catalog.->>) 1)
1657 ::pg_catalog.float8,
1658 $2
1659 ) OPERATOR(pg_catalog.=) @extschema@.coords_to_epoint(
1660 ($1 OPERATOR(pg_catalog.->) "lastindex" OPERATOR(pg_catalog.->>) 0)
1661 ::pg_catalog.float8,
1662 ($1 OPERATOR(pg_catalog.->) "lastindex" OPERATOR(pg_catalog.->>) 1)
1663 ::pg_catalog.float8,
1664 $2
1665 )
1666 THEN
1667 $1 - "lastindex"
1668 ELSE
1669 $1
1670 END
1671 ) AS "result_row" ("result")
1672 $$;
1674 CREATE FUNCTION GeoJSON_to_epoint(jsonb, text = 'epoint_lonlat')
1675 RETURNS epoint
1676 LANGUAGE sql IMMUTABLE STRICT AS $$
1677 SELECT CASE
1678 WHEN $1 OPERATOR(pg_catalog.->>) 'type' OPERATOR(pg_catalog.=) 'Point' THEN
1679 @extschema@.coords_to_epoint(
1680 ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 0)
1681 ::pg_catalog.float8,
1682 ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 1)
1683 ::pg_catalog.float8,
1684 $2
1685 )
1686 WHEN $1->>'type' = 'Feature' THEN
1687 @extschema@.GeoJSON_to_epoint($1 OPERATOR(pg_catalog.->) 'geometry', $2)
1688 ELSE
1689 NULL
1690 END
1691 $$;
1693 CREATE FUNCTION GeoJSON_to_ecluster(jsonb, text = 'epoint_lonlat')
1694 RETURNS ecluster
1695 LANGUAGE plpgsql IMMUTABLE STRICT AS $$
1696 DECLARE
1697 "tp" TEXT = $1 OPERATOR(pg_catalog.->>) 'type';
1698 BEGIN
1699 IF "tp" = 'Point' THEN RETURN
1700 @extschema@.coords_to_epoint(
1701 ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 0)
1702 ::pg_catalog.float8,
1703 ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 1)
1704 ::pg_catalog.float8,
1705 $2
1706 )::@extschema@.ecluster;
1707 END IF;
1708 raise notice 'DEBUG2';
1709 IF "tp" = 'MultiPoint' THEN RETURN
1710 ( SELECT @extschema@.ecluster_create_multipoint(pg_catalog.array_agg(
1711 @extschema@.coords_to_epoint(
1712 ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
1713 ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
1714 $2
1715 )
1716 ))
1717 FROM pg_catalog.jsonb_array_elements(
1718 $1 OPERATOR(pg_catalog.->) 'coordinates'
1719 ) AS "coord"
1720 );
1721 END IF;
1722 IF "tp" = 'LineString' THEN RETURN
1723 ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg(
1724 @extschema@.coords_to_epoint(
1725 ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
1726 ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
1727 $2
1728 )
1729 ))
1730 FROM pg_catalog.jsonb_array_elements(
1731 $1 OPERATOR(pg_catalog.->) 'coordinates'
1732 ) AS "coord"
1733 );
1734 END IF;
1735 IF "tp" = 'MultiLineString' THEN RETURN
1736 ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
1737 ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg(
1738 @extschema@.coords_to_epoint(
1739 ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
1740 ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
1741 $2
1742 )
1743 ))
1744 FROM pg_catalog.jsonb_array_elements("coord_array") AS "coord"
1745 )
1746 ))
1747 FROM pg_catalog.jsonb_array_elements(
1748 $1 OPERATOR(pg_catalog.->) 'coordinates'
1749 ) AS "coord_array"
1750 );
1751 END IF;
1752 IF "tp" = 'Polygon' THEN RETURN
1753 ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
1754 ( SELECT @extschema@.ecluster_create_polygon(pg_catalog.array_agg(
1755 @extschema@.coords_to_epoint(
1756 ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
1757 ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
1758 $2
1759 )
1760 ))
1761 FROM @extschema@.GeoJSON_LinearRing_vertices("coord_array", $2)
1762 AS "coord"
1763 )
1764 ))
1765 FROM pg_catalog.jsonb_array_elements(
1766 $1 OPERATOR(pg_catalog.->) 'coordinates'
1767 ) AS "coord_array"
1768 );
1769 END IF;
1770 IF "tp" = 'MultiPolygon' THEN RETURN
1771 ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
1772 ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
1773 ( SELECT @extschema@.ecluster_create_polygon(
1774 pg_catalog.array_agg(
1775 @extschema@.coords_to_epoint(
1776 ("coord" OPERATOR(pg_catalog.->>) 0)
1777 ::pg_catalog.float8,
1778 ("coord" OPERATOR(pg_catalog.->>) 1)
1779 ::pg_catalog.float8,
1780 $2
1781 )
1782 )
1783 )
1784 FROM @extschema@.GeoJSON_LinearRing_vertices(
1785 "coord_array", $2
1786 ) AS "coord"
1787 )
1788 ))
1789 FROM pg_catalog.jsonb_array_elements("coord_array_array")
1790 AS "coord_array"
1791 )
1792 ))
1793 FROM jsonb_array_elements(
1794 $1 OPERATOR(pg_catalog.->) 'coordinates'
1795 ) AS "coord_array_array"
1796 );
1797 END IF;
1798 IF "tp" = 'GeometryCollection' THEN RETURN
1799 ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
1800 @extschema@.GeoJSON_to_ecluster("geometry", $2)
1801 ))
1802 FROM pg_catalog.jsonb_array_elements(
1803 $1 OPERATOR(pg_catalog.->) 'geometries'
1804 ) AS "geometry"
1805 );
1806 END IF;
1807 IF "tp" = 'Feature' THEN RETURN
1808 @extschema@.GeoJSON_to_ecluster(
1809 $1 OPERATOR(pg_catalog.->) 'geometry', $2
1810 );
1811 END IF;
1812 IF "tp" = 'FeatureCollection' THEN RETURN
1813 ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
1814 @extschema@.GeoJSON_to_ecluster("feature", $2)
1815 ))
1816 FROM pg_catalog.jsonb_array_elements(
1817 $1 OPERATOR(pg_catalog.->) 'features'
1818 ) AS "feature"
1819 );
1820 END IF;
1821 RETURN NULL;
1822 END;
1823 $$;