pgLatLon
view latlon--0.15.sql @ 81:b0e17a5a0258
Renamed README.mkd to README.md
| author | jbe |
|---|---|
| date | Thu Oct 23 15:15:56 2025 +0200 (9 days ago) |
| parents | 4eb23fd90088 |
| children |
line source
1 ----------------------------------------
2 -- forward declarations (shell types) --
3 ----------------------------------------
5 CREATE TYPE ekey_point;
6 CREATE TYPE ekey_area;
7 CREATE TYPE epoint;
8 CREATE TYPE epoint_with_sample_count;
9 CREATE TYPE ebox;
10 CREATE TYPE ecircle;
11 CREATE TYPE ecluster;
14 ------------------------------------------------------------
15 -- dummy input/output functions for dummy index key types --
16 ------------------------------------------------------------
18 CREATE FUNCTION ekey_point_in_dummy(cstring)
19 RETURNS ekey_point
20 LANGUAGE C IMMUTABLE STRICT
21 AS '$libdir/latlon-v0010', 'pgl_notimpl';
23 CREATE FUNCTION ekey_point_out_dummy(ekey_point)
24 RETURNS cstring
25 LANGUAGE C IMMUTABLE STRICT
26 AS '$libdir/latlon-v0010', 'pgl_notimpl';
28 CREATE FUNCTION ekey_area_in_dummy(cstring)
29 RETURNS ekey_area
30 LANGUAGE C IMMUTABLE STRICT
31 AS '$libdir/latlon-v0010', 'pgl_notimpl';
33 CREATE FUNCTION ekey_area_out_dummy(ekey_area)
34 RETURNS cstring
35 LANGUAGE C IMMUTABLE STRICT
36 AS '$libdir/latlon-v0010', 'pgl_notimpl';
39 --------------------------
40 -- text input functions --
41 --------------------------
43 CREATE FUNCTION epoint_in(cstring)
44 RETURNS epoint
45 LANGUAGE C IMMUTABLE STRICT
46 AS '$libdir/latlon-v0010', 'pgl_epoint_in';
48 CREATE FUNCTION epoint_with_sample_count_in(cstring)
49 RETURNS epoint_with_sample_count
50 LANGUAGE C IMMUTABLE STRICT
51 AS '$libdir/latlon-v0010', 'pgl_epoint_with_sample_count_in';
53 CREATE FUNCTION ebox_in(cstring)
54 RETURNS ebox
55 LANGUAGE C IMMUTABLE STRICT
56 AS '$libdir/latlon-v0010', 'pgl_ebox_in';
58 CREATE FUNCTION ecircle_in(cstring)
59 RETURNS ecircle
60 LANGUAGE C IMMUTABLE STRICT
61 AS '$libdir/latlon-v0010', 'pgl_ecircle_in';
63 CREATE FUNCTION ecluster_in(cstring)
64 RETURNS ecluster
65 LANGUAGE C IMMUTABLE STRICT
66 AS '$libdir/latlon-v0010', 'pgl_ecluster_in';
69 ---------------------------
70 -- text output functions --
71 ---------------------------
73 CREATE FUNCTION epoint_out(epoint)
74 RETURNS cstring
75 LANGUAGE C IMMUTABLE STRICT
76 AS '$libdir/latlon-v0010', 'pgl_epoint_out';
78 CREATE FUNCTION epoint_with_sample_count_out(epoint_with_sample_count)
79 RETURNS cstring
80 LANGUAGE C IMMUTABLE STRICT
81 AS '$libdir/latlon-v0010', 'pgl_epoint_with_sample_count_out';
83 CREATE FUNCTION ebox_out(ebox)
84 RETURNS cstring
85 LANGUAGE C IMMUTABLE STRICT
86 AS '$libdir/latlon-v0010', 'pgl_ebox_out';
88 CREATE FUNCTION ecircle_out(ecircle)
89 RETURNS cstring
90 LANGUAGE C IMMUTABLE STRICT
91 AS '$libdir/latlon-v0010', 'pgl_ecircle_out';
93 CREATE FUNCTION ecluster_out(ecluster)
94 RETURNS cstring
95 LANGUAGE C IMMUTABLE STRICT
96 AS '$libdir/latlon-v0010', 'pgl_ecluster_out';
99 --------------------------
100 -- binary I/O functions --
101 --------------------------
103 CREATE FUNCTION epoint_recv(internal)
104 RETURNS epoint
105 LANGUAGE C IMMUTABLE STRICT
106 AS '$libdir/latlon-v0010', 'pgl_epoint_recv';
108 CREATE FUNCTION ebox_recv(internal)
109 RETURNS ebox
110 LANGUAGE C IMMUTABLE STRICT
111 AS '$libdir/latlon-v0010', 'pgl_ebox_recv';
113 CREATE FUNCTION ecircle_recv(internal)
114 RETURNS ecircle
115 LANGUAGE C IMMUTABLE STRICT
116 AS '$libdir/latlon-v0010', 'pgl_ecircle_recv';
118 CREATE FUNCTION epoint_send(epoint)
119 RETURNS bytea
120 LANGUAGE C IMMUTABLE STRICT
121 AS '$libdir/latlon-v0010', 'pgl_epoint_send';
123 CREATE FUNCTION ebox_send(ebox)
124 RETURNS bytea
125 LANGUAGE C IMMUTABLE STRICT
126 AS '$libdir/latlon-v0010', 'pgl_ebox_send';
128 CREATE FUNCTION ecircle_send(ecircle)
129 RETURNS bytea
130 LANGUAGE C IMMUTABLE STRICT
131 AS '$libdir/latlon-v0010', 'pgl_ecircle_send';
134 -----------------------------------------------
135 -- type definitions of dummy index key types --
136 -----------------------------------------------
138 CREATE TYPE ekey_point (
139 internallength = 8,
140 input = ekey_point_in_dummy,
141 output = ekey_point_out_dummy,
142 alignment = char );
144 CREATE TYPE ekey_area (
145 internallength = 9,
146 input = ekey_area_in_dummy,
147 output = ekey_area_out_dummy,
148 alignment = char );
151 ------------------------------------------
152 -- definitions of geographic data types --
153 ------------------------------------------
155 CREATE TYPE epoint (
156 internallength = 16,
157 input = epoint_in,
158 output = epoint_out,
159 receive = epoint_recv,
160 send = epoint_send,
161 alignment = double );
163 CREATE TYPE epoint_with_sample_count (
164 internallength = 20,
165 input = epoint_with_sample_count_in,
166 output = epoint_with_sample_count_out,
167 alignment = double );
169 CREATE TYPE ebox (
170 internallength = 32,
171 input = ebox_in,
172 output = ebox_out,
173 receive = ebox_recv,
174 send = ebox_send,
175 alignment = double );
177 CREATE TYPE ecircle (
178 internallength = 24,
179 input = ecircle_in,
180 output = ecircle_out,
181 receive = ecircle_recv,
182 send = ecircle_send,
183 alignment = double );
185 CREATE TYPE ecluster (
186 internallength = VARIABLE,
187 input = ecluster_in,
188 output = ecluster_out,
189 alignment = double,
190 storage = external );
193 --------------------
194 -- B-tree support --
195 --------------------
197 -- begin of B-tree support for epoint
199 CREATE FUNCTION epoint_btree_lt(epoint, epoint)
200 RETURNS boolean
201 LANGUAGE C IMMUTABLE STRICT
202 AS '$libdir/latlon-v0010', 'pgl_btree_epoint_lt';
204 CREATE FUNCTION epoint_btree_le(epoint, epoint)
205 RETURNS boolean
206 LANGUAGE C IMMUTABLE STRICT
207 AS '$libdir/latlon-v0010', 'pgl_btree_epoint_le';
209 CREATE FUNCTION epoint_btree_eq(epoint, epoint)
210 RETURNS boolean
211 LANGUAGE C IMMUTABLE STRICT
212 AS '$libdir/latlon-v0010', 'pgl_btree_epoint_eq';
214 CREATE FUNCTION epoint_btree_ne(epoint, epoint)
215 RETURNS boolean
216 LANGUAGE C IMMUTABLE STRICT
217 AS '$libdir/latlon-v0010', 'pgl_btree_epoint_ne';
219 CREATE FUNCTION epoint_btree_ge(epoint, epoint)
220 RETURNS boolean
221 LANGUAGE C IMMUTABLE STRICT
222 AS '$libdir/latlon-v0010', 'pgl_btree_epoint_ge';
224 CREATE FUNCTION epoint_btree_gt(epoint, epoint)
225 RETURNS boolean
226 LANGUAGE C IMMUTABLE STRICT
227 AS '$libdir/latlon-v0010', 'pgl_btree_epoint_gt';
229 CREATE OPERATOR <<< (
230 leftarg = epoint,
231 rightarg = epoint,
232 procedure = epoint_btree_lt,
233 commutator = >>>,
234 negator = >>>=,
235 restrict = scalarltsel,
236 join = scalarltjoinsel
237 );
239 CREATE OPERATOR <<<= (
240 leftarg = epoint,
241 rightarg = epoint,
242 procedure = epoint_btree_le,
243 commutator = >>>=,
244 negator = >>>,
245 restrict = scalarltsel,
246 join = scalarltjoinsel
247 );
249 CREATE OPERATOR = (
250 leftarg = epoint,
251 rightarg = epoint,
252 procedure = epoint_btree_eq,
253 commutator = =,
254 negator = <>,
255 restrict = eqsel,
256 join = eqjoinsel,
257 merges
258 );
260 CREATE OPERATOR <> (
261 leftarg = epoint,
262 rightarg = epoint,
263 procedure = epoint_btree_ne,
264 commutator = <>,
265 negator = =,
266 restrict = neqsel,
267 join = neqjoinsel
268 );
270 CREATE OPERATOR >>>= (
271 leftarg = epoint,
272 rightarg = epoint,
273 procedure = epoint_btree_ge,
274 commutator = <<<=,
275 negator = <<<,
276 restrict = scalargtsel,
277 join = scalargtjoinsel
278 );
280 CREATE OPERATOR >>> (
281 leftarg = epoint,
282 rightarg = epoint,
283 procedure = epoint_btree_gt,
284 commutator = <<<,
285 negator = <<<=,
286 restrict = scalargtsel,
287 join = scalargtjoinsel
288 );
290 CREATE FUNCTION epoint_btree_cmp(epoint, epoint)
291 RETURNS int4
292 LANGUAGE C IMMUTABLE STRICT
293 AS '$libdir/latlon-v0010', 'pgl_btree_epoint_cmp';
295 CREATE OPERATOR CLASS epoint_btree_ops
296 DEFAULT FOR TYPE epoint USING btree AS
297 OPERATOR 1 <<< ,
298 OPERATOR 2 <<<= ,
299 OPERATOR 3 = ,
300 OPERATOR 4 >>>= ,
301 OPERATOR 5 >>> ,
302 FUNCTION 1 epoint_btree_cmp(epoint, epoint);
304 -- end of B-tree support for epoint
306 -- begin of B-tree support for ebox
308 CREATE FUNCTION ebox_btree_lt(ebox, ebox)
309 RETURNS boolean
310 LANGUAGE C IMMUTABLE STRICT
311 AS '$libdir/latlon-v0010', 'pgl_btree_ebox_lt';
313 CREATE FUNCTION ebox_btree_le(ebox, ebox)
314 RETURNS boolean
315 LANGUAGE C IMMUTABLE STRICT
316 AS '$libdir/latlon-v0010', 'pgl_btree_ebox_le';
318 CREATE FUNCTION ebox_btree_eq(ebox, ebox)
319 RETURNS boolean
320 LANGUAGE C IMMUTABLE STRICT
321 AS '$libdir/latlon-v0010', 'pgl_btree_ebox_eq';
323 CREATE FUNCTION ebox_btree_ne(ebox, ebox)
324 RETURNS boolean
325 LANGUAGE C IMMUTABLE STRICT
326 AS '$libdir/latlon-v0010', 'pgl_btree_ebox_ne';
328 CREATE FUNCTION ebox_btree_ge(ebox, ebox)
329 RETURNS boolean
330 LANGUAGE C IMMUTABLE STRICT
331 AS '$libdir/latlon-v0010', 'pgl_btree_ebox_ge';
333 CREATE FUNCTION ebox_btree_gt(ebox, ebox)
334 RETURNS boolean
335 LANGUAGE C IMMUTABLE STRICT
336 AS '$libdir/latlon-v0010', 'pgl_btree_ebox_gt';
338 CREATE OPERATOR <<< (
339 leftarg = ebox,
340 rightarg = ebox,
341 procedure = ebox_btree_lt,
342 commutator = >>>,
343 negator = >>>=,
344 restrict = scalarltsel,
345 join = scalarltjoinsel
346 );
348 CREATE OPERATOR <<<= (
349 leftarg = ebox,
350 rightarg = ebox,
351 procedure = ebox_btree_le,
352 commutator = >>>=,
353 negator = >>>,
354 restrict = scalarltsel,
355 join = scalarltjoinsel
356 );
358 CREATE OPERATOR = (
359 leftarg = ebox,
360 rightarg = ebox,
361 procedure = ebox_btree_eq,
362 commutator = =,
363 negator = <>,
364 restrict = eqsel,
365 join = eqjoinsel,
366 merges
367 );
369 CREATE OPERATOR <> (
370 leftarg = ebox,
371 rightarg = ebox,
372 procedure = ebox_btree_ne,
373 commutator = <>,
374 negator = =,
375 restrict = neqsel,
376 join = neqjoinsel
377 );
379 CREATE OPERATOR >>>= (
380 leftarg = ebox,
381 rightarg = ebox,
382 procedure = ebox_btree_ge,
383 commutator = <<<=,
384 negator = <<<,
385 restrict = scalargtsel,
386 join = scalargtjoinsel
387 );
389 CREATE OPERATOR >>> (
390 leftarg = ebox,
391 rightarg = ebox,
392 procedure = ebox_btree_gt,
393 commutator = <<<,
394 negator = <<<=,
395 restrict = scalargtsel,
396 join = scalargtjoinsel
397 );
399 CREATE FUNCTION ebox_btree_cmp(ebox, ebox)
400 RETURNS int4
401 LANGUAGE C IMMUTABLE STRICT
402 AS '$libdir/latlon-v0010', 'pgl_btree_ebox_cmp';
404 CREATE OPERATOR CLASS ebox_btree_ops
405 DEFAULT FOR TYPE ebox USING btree AS
406 OPERATOR 1 <<< ,
407 OPERATOR 2 <<<= ,
408 OPERATOR 3 = ,
409 OPERATOR 4 >>>= ,
410 OPERATOR 5 >>> ,
411 FUNCTION 1 ebox_btree_cmp(ebox, ebox);
413 -- end of B-tree support for ebox
415 -- begin of B-tree support for ecircle
417 CREATE FUNCTION ecircle_btree_lt(ecircle, ecircle)
418 RETURNS boolean
419 LANGUAGE C IMMUTABLE STRICT
420 AS '$libdir/latlon-v0010', 'pgl_btree_ecircle_lt';
422 CREATE FUNCTION ecircle_btree_le(ecircle, ecircle)
423 RETURNS boolean
424 LANGUAGE C IMMUTABLE STRICT
425 AS '$libdir/latlon-v0010', 'pgl_btree_ecircle_le';
427 CREATE FUNCTION ecircle_btree_eq(ecircle, ecircle)
428 RETURNS boolean
429 LANGUAGE C IMMUTABLE STRICT
430 AS '$libdir/latlon-v0010', 'pgl_btree_ecircle_eq';
432 CREATE FUNCTION ecircle_btree_ne(ecircle, ecircle)
433 RETURNS boolean
434 LANGUAGE C IMMUTABLE STRICT
435 AS '$libdir/latlon-v0010', 'pgl_btree_ecircle_ne';
437 CREATE FUNCTION ecircle_btree_ge(ecircle, ecircle)
438 RETURNS boolean
439 LANGUAGE C IMMUTABLE STRICT
440 AS '$libdir/latlon-v0010', 'pgl_btree_ecircle_ge';
442 CREATE FUNCTION ecircle_btree_gt(ecircle, ecircle)
443 RETURNS boolean
444 LANGUAGE C IMMUTABLE STRICT
445 AS '$libdir/latlon-v0010', 'pgl_btree_ecircle_gt';
447 CREATE OPERATOR <<< (
448 leftarg = ecircle,
449 rightarg = ecircle,
450 procedure = ecircle_btree_lt,
451 commutator = >>>,
452 negator = >>>=,
453 restrict = scalarltsel,
454 join = scalarltjoinsel
455 );
457 CREATE OPERATOR <<<= (
458 leftarg = ecircle,
459 rightarg = ecircle,
460 procedure = ecircle_btree_le,
461 commutator = >>>=,
462 negator = >>>,
463 restrict = scalarltsel,
464 join = scalarltjoinsel
465 );
467 CREATE OPERATOR = (
468 leftarg = ecircle,
469 rightarg = ecircle,
470 procedure = ecircle_btree_eq,
471 commutator = =,
472 negator = <>,
473 restrict = eqsel,
474 join = eqjoinsel,
475 merges
476 );
478 CREATE OPERATOR <> (
479 leftarg = ecircle,
480 rightarg = ecircle,
481 procedure = ecircle_btree_ne,
482 commutator = <>,
483 negator = =,
484 restrict = neqsel,
485 join = neqjoinsel
486 );
488 CREATE OPERATOR >>>= (
489 leftarg = ecircle,
490 rightarg = ecircle,
491 procedure = ecircle_btree_ge,
492 commutator = <<<=,
493 negator = <<<,
494 restrict = scalargtsel,
495 join = scalargtjoinsel
496 );
498 CREATE OPERATOR >>> (
499 leftarg = ecircle,
500 rightarg = ecircle,
501 procedure = ecircle_btree_gt,
502 commutator = <<<,
503 negator = <<<=,
504 restrict = scalargtsel,
505 join = scalargtjoinsel
506 );
508 CREATE FUNCTION ecircle_btree_cmp(ecircle, ecircle)
509 RETURNS int4
510 LANGUAGE C IMMUTABLE STRICT
511 AS '$libdir/latlon-v0010', 'pgl_btree_ecircle_cmp';
513 CREATE OPERATOR CLASS ecircle_btree_ops
514 DEFAULT FOR TYPE ecircle USING btree AS
515 OPERATOR 1 <<< ,
516 OPERATOR 2 <<<= ,
517 OPERATOR 3 = ,
518 OPERATOR 4 >>>= ,
519 OPERATOR 5 >>> ,
520 FUNCTION 1 ecircle_btree_cmp(ecircle, ecircle);
522 -- end of B-tree support for ecircle
525 ----------------
526 -- type casts --
527 ----------------
529 CREATE FUNCTION cast_epoint_to_ebox(epoint)
530 RETURNS ebox
531 LANGUAGE C IMMUTABLE STRICT
532 AS '$libdir/latlon-v0010', 'pgl_epoint_to_ebox';
534 CREATE CAST (epoint AS ebox) WITH FUNCTION cast_epoint_to_ebox(epoint);
536 CREATE FUNCTION cast_epoint_to_ecircle(epoint)
537 RETURNS ecircle
538 LANGUAGE C IMMUTABLE STRICT
539 AS '$libdir/latlon-v0010', 'pgl_epoint_to_ecircle';
541 CREATE CAST (epoint AS ecircle) WITH FUNCTION cast_epoint_to_ecircle(epoint);
543 CREATE FUNCTION cast_epoint_to_ecluster(epoint)
544 RETURNS ecluster
545 LANGUAGE C IMMUTABLE STRICT
546 AS '$libdir/latlon-v0010', 'pgl_epoint_to_ecluster';
548 CREATE CAST (epoint AS ecluster) WITH FUNCTION cast_epoint_to_ecluster(epoint);
550 CREATE FUNCTION cast_ebox_to_ecluster(ebox)
551 RETURNS ecluster
552 LANGUAGE C IMMUTABLE STRICT
553 AS '$libdir/latlon-v0010', 'pgl_ebox_to_ecluster';
555 CREATE CAST (ebox AS ecluster) WITH FUNCTION cast_ebox_to_ecluster(ebox);
558 ---------------------------
559 -- constructor functions --
560 ---------------------------
562 CREATE FUNCTION epoint(float8, float8)
563 RETURNS epoint
564 LANGUAGE C IMMUTABLE STRICT
565 AS '$libdir/latlon-v0010', 'pgl_create_epoint';
567 CREATE FUNCTION epoint_latlon(float8, float8)
568 RETURNS epoint
569 LANGUAGE SQL IMMUTABLE STRICT AS $$
570 SELECT @extschema@.epoint($1, $2)
571 $$;
573 CREATE FUNCTION epoint_lonlat(float8, float8)
574 RETURNS epoint
575 LANGUAGE SQL IMMUTABLE STRICT AS $$
576 SELECT @extschema@.epoint($2, $1)
577 $$;
579 CREATE FUNCTION epoint_with_sample_count(epoint, int4)
580 RETURNS epoint_with_sample_count
581 LANGUAGE C IMMUTABLE STRICT
582 AS '$libdir/latlon-v0010', 'pgl_create_epoint_with_sample_count';
584 CREATE FUNCTION empty_ebox()
585 RETURNS ebox
586 LANGUAGE C IMMUTABLE STRICT
587 AS '$libdir/latlon-v0010', 'pgl_create_empty_ebox';
589 CREATE FUNCTION ebox(float8, float8, float8, float8)
590 RETURNS ebox
591 LANGUAGE C IMMUTABLE STRICT
592 AS '$libdir/latlon-v0010', 'pgl_create_ebox';
594 CREATE FUNCTION ebox(epoint, epoint)
595 RETURNS ebox
596 LANGUAGE C IMMUTABLE STRICT
597 AS '$libdir/latlon-v0010', 'pgl_create_ebox_from_epoints';
599 CREATE FUNCTION ecircle(float8, float8, float8)
600 RETURNS ecircle
601 LANGUAGE C IMMUTABLE STRICT
602 AS '$libdir/latlon-v0010', 'pgl_create_ecircle';
604 CREATE FUNCTION ecircle(epoint, float8)
605 RETURNS ecircle
606 LANGUAGE C IMMUTABLE STRICT
607 AS '$libdir/latlon-v0010', 'pgl_create_ecircle_from_epoint';
609 CREATE FUNCTION ecluster_concat(ecluster[])
610 RETURNS ecluster
611 LANGUAGE sql IMMUTABLE STRICT AS $$
612 SELECT pg_catalog.array_to_string($1, ' ')::@extschema@.ecluster
613 $$;
615 CREATE FUNCTION ecluster_concat(ecluster, ecluster)
616 RETURNS ecluster
617 LANGUAGE sql IMMUTABLE STRICT AS $$
618 SELECT (
619 $1::pg_catalog.text OPERATOR(pg_catalog.||) ' ' OPERATOR(pg_catalog.||)
620 $2::pg_catalog.text
621 )::@extschema@.ecluster
622 $$;
624 CREATE FUNCTION ecluster_create_multipoint(epoint[])
625 RETURNS ecluster
626 LANGUAGE sql IMMUTABLE STRICT AS $$
627 SELECT
628 pg_catalog.array_to_string(
629 pg_catalog.array_agg(
630 'point (' OPERATOR(pg_catalog.||) unnest OPERATOR(pg_catalog.||) ')'
631 ),
632 ' '
633 )::@extschema@.ecluster
634 FROM pg_catalog.unnest($1)
635 $$;
637 CREATE FUNCTION ecluster_create_path(epoint[])
638 RETURNS ecluster
639 LANGUAGE sql IMMUTABLE STRICT AS $$
640 SELECT CASE WHEN "str" OPERATOR(pg_catalog.=) '' THEN
641 'empty'::@extschema@.ecluster
642 ELSE
643 (
644 'path (' OPERATOR(pg_catalog.||) "str" OPERATOR(pg_catalog.||) ')'
645 )::@extschema@.ecluster
646 END
647 FROM pg_catalog.array_to_string($1, ' ') AS "str"
648 $$;
650 CREATE FUNCTION ecluster_create_outline(epoint[])
651 RETURNS ecluster
652 LANGUAGE sql IMMUTABLE STRICT AS $$
653 SELECT CASE WHEN "str" OPERATOR(pg_catalog.=) '' THEN
654 'empty'::@extschema@.ecluster
655 ELSE
656 (
657 'outline (' OPERATOR(pg_catalog.||) "str" OPERATOR(pg_catalog.||) ')'
658 )::@extschema@.ecluster
659 END
660 FROM pg_catalog.array_to_string($1, ' ') AS "str"
661 $$;
663 CREATE FUNCTION ecluster_create_polygon(epoint[])
664 RETURNS ecluster
665 LANGUAGE sql IMMUTABLE STRICT AS $$
666 SELECT CASE WHEN "str" OPERATOR(pg_catalog.=) '' THEN
667 'empty'::@extschema@.ecluster
668 ELSE
669 (
670 'polygon (' OPERATOR(pg_catalog.||) pg_catalog.array_to_string($1, ' ')
671 OPERATOR(pg_catalog.||) ')'
672 )::@extschema@.ecluster
673 END
674 FROM pg_catalog.array_to_string($1, ' ') AS "str"
675 $$;
678 ----------------------
679 -- getter functions --
680 ----------------------
682 CREATE FUNCTION latitude(epoint)
683 RETURNS float8
684 LANGUAGE C IMMUTABLE STRICT
685 AS '$libdir/latlon-v0010', 'pgl_epoint_lat';
687 CREATE FUNCTION longitude(epoint)
688 RETURNS float8
689 LANGUAGE C IMMUTABLE STRICT
690 AS '$libdir/latlon-v0010', 'pgl_epoint_lon';
692 CREATE FUNCTION min_latitude(ebox)
693 RETURNS float8
694 LANGUAGE C IMMUTABLE STRICT
695 AS '$libdir/latlon-v0010', 'pgl_ebox_lat_min';
697 CREATE FUNCTION max_latitude(ebox)
698 RETURNS float8
699 LANGUAGE C IMMUTABLE STRICT
700 AS '$libdir/latlon-v0010', 'pgl_ebox_lat_max';
702 CREATE FUNCTION min_longitude(ebox)
703 RETURNS float8
704 LANGUAGE C IMMUTABLE STRICT
705 AS '$libdir/latlon-v0010', 'pgl_ebox_lon_min';
707 CREATE FUNCTION max_longitude(ebox)
708 RETURNS float8
709 LANGUAGE C IMMUTABLE STRICT
710 AS '$libdir/latlon-v0010', 'pgl_ebox_lon_max';
712 CREATE FUNCTION center(ecircle)
713 RETURNS epoint
714 LANGUAGE C IMMUTABLE STRICT
715 AS '$libdir/latlon-v0010', 'pgl_ecircle_center';
717 CREATE FUNCTION radius(ecircle)
718 RETURNS float8
719 LANGUAGE C IMMUTABLE STRICT
720 AS '$libdir/latlon-v0010', 'pgl_ecircle_radius';
722 CREATE FUNCTION ecluster_extract_points(ecluster)
723 RETURNS SETOF epoint
724 LANGUAGE sql IMMUTABLE STRICT AS $$
725 SELECT "match"[2]::@extschema@.epoint
726 FROM pg_catalog.regexp_matches(
727 $1::pg_catalog.text, e'(^| )point \\(([^)]+)\\)', 'g'
728 ) AS "match"
729 $$;
731 CREATE FUNCTION ecluster_extract_paths(ecluster)
732 RETURNS SETOF epoint[]
733 LANGUAGE sql IMMUTABLE STRICT AS $$
734 SELECT (
735 SELECT pg_catalog.array_agg("m2"[1]::@extschema@.epoint)
736 FROM pg_catalog.regexp_matches("m1"[2], e'[^ ]+ [^ ]+', 'g') AS "m2"
737 )
738 FROM pg_catalog.regexp_matches(
739 $1::pg_catalog.text, e'(^| )path \\(([^)]+)\\)', 'g'
740 ) AS "m1"
741 $$;
743 CREATE FUNCTION ecluster_extract_outlines(ecluster)
744 RETURNS SETOF epoint[]
745 LANGUAGE sql IMMUTABLE STRICT AS $$
746 SELECT (
747 SELECT pg_catalog.array_agg("m2"[1]::@extschema@.epoint)
748 FROM pg_catalog.regexp_matches("m1"[2], e'[^ ]+ [^ ]+', 'g') AS "m2"
749 )
750 FROM pg_catalog.regexp_matches(
751 $1::pg_catalog.text, e'(^| )outline \\(([^)]+)\\)', 'g'
752 ) AS "m1"
753 $$;
755 CREATE FUNCTION ecluster_extract_polygons(ecluster)
756 RETURNS SETOF epoint[]
757 LANGUAGE sql IMMUTABLE STRICT AS $$
758 SELECT (
759 SELECT pg_catalog.array_agg("m2"[1]::@extschema@.epoint)
760 FROM pg_catalog.regexp_matches("m1"[2], e'[^ ]+ [^ ]+', 'g') AS "m2"
761 )
762 FROM pg_catalog.regexp_matches(
763 $1::pg_catalog.text, e'(^| )polygon \\(([^)]+)\\)', 'g'
764 ) AS "m1"
765 $$;
768 ---------------
769 -- operators --
770 ---------------
772 CREATE FUNCTION epoint_ebox_overlap_proc(epoint, ebox)
773 RETURNS boolean
774 LANGUAGE C IMMUTABLE STRICT
775 AS '$libdir/latlon-v0010', 'pgl_epoint_ebox_overlap';
777 CREATE FUNCTION epoint_ecircle_overlap_proc(epoint, ecircle)
778 RETURNS boolean
779 LANGUAGE C IMMUTABLE STRICT
780 AS '$libdir/latlon-v0010', 'pgl_epoint_ecircle_overlap';
782 CREATE FUNCTION epoint_ecluster_overlap_proc(epoint, ecluster)
783 RETURNS boolean
784 LANGUAGE C IMMUTABLE STRICT
785 AS '$libdir/latlon-v0010', 'pgl_epoint_ecluster_overlap';
787 CREATE FUNCTION epoint_ecluster_may_overlap_proc(epoint, ecluster)
788 RETURNS boolean
789 LANGUAGE C IMMUTABLE STRICT
790 AS '$libdir/latlon-v0010', 'pgl_epoint_ecluster_may_overlap';
792 CREATE FUNCTION ebox_overlap_proc(ebox, ebox)
793 RETURNS boolean
794 LANGUAGE C IMMUTABLE STRICT
795 AS '$libdir/latlon-v0010', 'pgl_ebox_overlap';
797 CREATE FUNCTION ebox_ecircle_may_overlap_proc(ebox, ecircle)
798 RETURNS boolean
799 LANGUAGE C IMMUTABLE STRICT
800 AS '$libdir/latlon-v0010', 'pgl_ebox_ecircle_may_overlap';
802 CREATE FUNCTION ebox_ecluster_may_overlap_proc(ebox, ecluster)
803 RETURNS boolean
804 LANGUAGE C IMMUTABLE STRICT
805 AS '$libdir/latlon-v0010', 'pgl_ebox_ecluster_may_overlap';
807 CREATE FUNCTION ecircle_overlap_proc(ecircle, ecircle)
808 RETURNS boolean
809 LANGUAGE C IMMUTABLE STRICT
810 AS '$libdir/latlon-v0010', 'pgl_ecircle_overlap';
812 CREATE FUNCTION ecircle_ecluster_overlap_proc(ecircle, ecluster)
813 RETURNS boolean
814 LANGUAGE C IMMUTABLE STRICT
815 AS '$libdir/latlon-v0010', 'pgl_ecircle_ecluster_overlap';
817 CREATE FUNCTION ecircle_ecluster_may_overlap_proc(ecircle, ecluster)
818 RETURNS boolean
819 LANGUAGE C IMMUTABLE STRICT
820 AS '$libdir/latlon-v0010', 'pgl_ecircle_ecluster_may_overlap';
822 CREATE FUNCTION ecluster_overlap_proc(ecluster, ecluster)
823 RETURNS boolean
824 LANGUAGE C IMMUTABLE STRICT
825 AS '$libdir/latlon-v0010', 'pgl_ecluster_overlap';
827 CREATE FUNCTION ecluster_may_overlap_proc(ecluster, ecluster)
828 RETURNS boolean
829 LANGUAGE C IMMUTABLE STRICT
830 AS '$libdir/latlon-v0010', 'pgl_ecluster_may_overlap';
832 CREATE FUNCTION ecluster_contains_proc(ecluster, ecluster)
833 RETURNS boolean
834 LANGUAGE C IMMUTABLE STRICT
835 AS '$libdir/latlon-v0010', 'pgl_ecluster_contains';
837 CREATE FUNCTION epoint_distance_proc(epoint, epoint)
838 RETURNS float8
839 LANGUAGE C IMMUTABLE STRICT
840 AS '$libdir/latlon-v0010', 'pgl_epoint_distance';
842 CREATE FUNCTION epoint_ecircle_distance_proc(epoint, ecircle)
843 RETURNS float8
844 LANGUAGE C IMMUTABLE STRICT
845 AS '$libdir/latlon-v0010', 'pgl_epoint_ecircle_distance';
847 CREATE FUNCTION epoint_ecluster_distance_proc(epoint, ecluster)
848 RETURNS float8
849 LANGUAGE C IMMUTABLE STRICT
850 AS '$libdir/latlon-v0010', 'pgl_epoint_ecluster_distance';
852 CREATE FUNCTION ecircle_distance_proc(ecircle, ecircle)
853 RETURNS float8
854 LANGUAGE C IMMUTABLE STRICT
855 AS '$libdir/latlon-v0010', 'pgl_ecircle_distance';
857 CREATE FUNCTION ecircle_ecluster_distance_proc(ecircle, ecluster)
858 RETURNS float8
859 LANGUAGE C IMMUTABLE STRICT
860 AS '$libdir/latlon-v0010', 'pgl_ecircle_ecluster_distance';
862 CREATE FUNCTION ecluster_distance_proc(ecluster, ecluster)
863 RETURNS float8
864 LANGUAGE C IMMUTABLE STRICT
865 AS '$libdir/latlon-v0010', 'pgl_ecluster_distance';
867 CREATE FUNCTION fair_distance_operator_proc(ecluster, epoint_with_sample_count)
868 RETURNS float8
869 LANGUAGE C IMMUTABLE STRICT
870 AS '$libdir/latlon-v0010', 'pgl_ecluster_epoint_sc_fair_distance';
872 CREATE OPERATOR && (
873 leftarg = epoint,
874 rightarg = ebox,
875 procedure = epoint_ebox_overlap_proc,
876 commutator = &&,
877 restrict = areasel,
878 join = areajoinsel
879 );
881 CREATE FUNCTION epoint_ebox_overlap_commutator(ebox, epoint)
882 RETURNS boolean
883 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&) $1';
885 CREATE OPERATOR && (
886 leftarg = ebox,
887 rightarg = epoint,
888 procedure = epoint_ebox_overlap_commutator,
889 commutator = &&,
890 restrict = areasel,
891 join = areajoinsel
892 );
894 CREATE OPERATOR && (
895 leftarg = epoint,
896 rightarg = ecircle,
897 procedure = epoint_ecircle_overlap_proc,
898 commutator = &&,
899 restrict = areasel,
900 join = areajoinsel
901 );
903 CREATE FUNCTION epoint_ecircle_overlap_commutator(ecircle, epoint)
904 RETURNS boolean
905 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&) $1';
907 CREATE OPERATOR && (
908 leftarg = ecircle,
909 rightarg = epoint,
910 procedure = epoint_ecircle_overlap_commutator,
911 commutator = &&,
912 restrict = areasel,
913 join = areajoinsel
914 );
916 CREATE OPERATOR && (
917 leftarg = epoint,
918 rightarg = ecluster,
919 procedure = epoint_ecluster_overlap_proc,
920 commutator = &&,
921 restrict = areasel,
922 join = areajoinsel
923 );
925 CREATE FUNCTION epoint_ecluster_overlap_commutator(ecluster, epoint)
926 RETURNS boolean
927 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&) $1';
929 CREATE OPERATOR && (
930 leftarg = ecluster,
931 rightarg = epoint,
932 procedure = epoint_ecluster_overlap_commutator,
933 commutator = &&,
934 restrict = areasel,
935 join = areajoinsel
936 );
938 CREATE OPERATOR && (
939 leftarg = ebox,
940 rightarg = ebox,
941 procedure = ebox_overlap_proc,
942 commutator = &&,
943 restrict = areasel,
944 join = areajoinsel
945 );
947 CREATE OPERATOR && (
948 leftarg = ecircle,
949 rightarg = ecircle,
950 procedure = ecircle_overlap_proc,
951 commutator = &&,
952 restrict = areasel,
953 join = areajoinsel
954 );
956 CREATE OPERATOR && (
957 leftarg = ecircle,
958 rightarg = ecluster,
959 procedure = ecircle_ecluster_overlap_proc,
960 commutator = &&,
961 restrict = areasel,
962 join = areajoinsel
963 );
965 CREATE FUNCTION ecircle_ecluster_overlap_commutator(ecluster, ecircle)
966 RETURNS boolean
967 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&) $1';
969 CREATE OPERATOR && (
970 leftarg = ecluster,
971 rightarg = ecircle,
972 procedure = ecircle_ecluster_overlap_commutator,
973 commutator = &&,
974 restrict = areasel,
975 join = areajoinsel
976 );
978 CREATE OPERATOR && (
979 leftarg = ecluster,
980 rightarg = ecluster,
981 procedure = ecluster_overlap_proc,
982 commutator = &&,
983 restrict = areasel,
984 join = areajoinsel
985 );
987 CREATE FUNCTION ebox_ecircle_overlap_castwrap(ebox, ecircle)
988 RETURNS boolean
989 LANGUAGE sql IMMUTABLE
990 AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.&&) $2';
992 CREATE OPERATOR && (
993 leftarg = ebox,
994 rightarg = ecircle,
995 procedure = ebox_ecircle_overlap_castwrap,
996 commutator = &&,
997 restrict = areasel,
998 join = areajoinsel
999 );
1001 CREATE FUNCTION ebox_ecircle_overlap_castwrap(ecircle, ebox)
1002 RETURNS boolean
1003 LANGUAGE sql IMMUTABLE
1004 AS 'SELECT $1 OPERATOR(@extschema@.&&) $2::@extschema@.ecluster';
1006 CREATE OPERATOR && (
1007 leftarg = ecircle,
1008 rightarg = ebox,
1009 procedure = ebox_ecircle_overlap_castwrap,
1010 commutator = &&,
1011 restrict = areasel,
1012 join = areajoinsel
1013 );
1015 CREATE FUNCTION ebox_ecluster_overlap_castwrap(ebox, ecluster)
1016 RETURNS boolean
1017 LANGUAGE sql IMMUTABLE
1018 AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.&&) $2';
1020 CREATE OPERATOR && (
1021 leftarg = ebox,
1022 rightarg = ecluster,
1023 procedure = ebox_ecluster_overlap_castwrap,
1024 commutator = &&,
1025 restrict = areasel,
1026 join = areajoinsel
1027 );
1029 CREATE FUNCTION ebox_ecluster_overlap_castwrap(ecluster, ebox)
1030 RETURNS boolean
1031 LANGUAGE sql IMMUTABLE
1032 AS 'SELECT $1 OPERATOR(@extschema@.&&) $2::@extschema@.ecluster';
1034 CREATE OPERATOR && (
1035 leftarg = ecluster,
1036 rightarg = ebox,
1037 procedure = ebox_ecluster_overlap_castwrap,
1038 commutator = &&,
1039 restrict = areasel,
1040 join = areajoinsel
1041 );
1043 CREATE OPERATOR &&+ (
1044 leftarg = epoint,
1045 rightarg = ecluster,
1046 procedure = epoint_ecluster_may_overlap_proc,
1047 commutator = &&+,
1048 restrict = areasel,
1049 join = areajoinsel
1050 );
1052 CREATE FUNCTION epoint_ecluster_may_overlap_commutator(ecluster, epoint)
1053 RETURNS boolean
1054 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&+) $1';
1056 CREATE OPERATOR &&+ (
1057 leftarg = ecluster,
1058 rightarg = epoint,
1059 procedure = epoint_ecluster_may_overlap_commutator,
1060 commutator = &&+,
1061 restrict = areasel,
1062 join = areajoinsel
1063 );
1065 CREATE OPERATOR &&+ (
1066 leftarg = ebox,
1067 rightarg = ecircle,
1068 procedure = ebox_ecircle_may_overlap_proc,
1069 commutator = &&+,
1070 restrict = areasel,
1071 join = areajoinsel
1072 );
1074 CREATE FUNCTION ebox_ecircle_may_overlap_commutator(ecircle, ebox)
1075 RETURNS boolean
1076 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&+) $1';
1078 CREATE OPERATOR &&+ (
1079 leftarg = ecircle,
1080 rightarg = ebox,
1081 procedure = ebox_ecircle_may_overlap_commutator,
1082 commutator = &&+,
1083 restrict = areasel,
1084 join = areajoinsel
1085 );
1087 CREATE OPERATOR &&+ (
1088 leftarg = ebox,
1089 rightarg = ecluster,
1090 procedure = ebox_ecluster_may_overlap_proc,
1091 commutator = &&+,
1092 restrict = areasel,
1093 join = areajoinsel
1094 );
1096 CREATE FUNCTION ebox_ecluster_may_overlap_commutator(ecluster, ebox)
1097 RETURNS boolean
1098 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&+) $1';
1100 CREATE OPERATOR &&+ (
1101 leftarg = ecluster,
1102 rightarg = ebox,
1103 procedure = ebox_ecluster_may_overlap_commutator,
1104 commutator = &&+,
1105 restrict = areasel,
1106 join = areajoinsel
1107 );
1109 CREATE OPERATOR &&+ (
1110 leftarg = ecircle,
1111 rightarg = ecluster,
1112 procedure = ecircle_ecluster_may_overlap_proc,
1113 commutator = &&+,
1114 restrict = areasel,
1115 join = areajoinsel
1116 );
1118 CREATE FUNCTION ecircle_ecluster_may_overlap_commutator(ecluster, ecircle)
1119 RETURNS boolean
1120 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.&&+) $1';
1122 CREATE OPERATOR &&+ (
1123 leftarg = ecluster,
1124 rightarg = ecircle,
1125 procedure = ecircle_ecluster_may_overlap_commutator,
1126 commutator = &&+,
1127 restrict = areasel,
1128 join = areajoinsel
1129 );
1131 CREATE OPERATOR &&+ (
1132 leftarg = ecluster,
1133 rightarg = ecluster,
1134 procedure = ecluster_may_overlap_proc,
1135 commutator = &&+,
1136 restrict = areasel,
1137 join = areajoinsel
1138 );
1140 CREATE OPERATOR @> (
1141 leftarg = ebox,
1142 rightarg = epoint,
1143 procedure = epoint_ebox_overlap_commutator,
1144 commutator = <@,
1145 restrict = areasel,
1146 join = areajoinsel
1147 );
1149 CREATE OPERATOR <@ (
1150 leftarg = epoint,
1151 rightarg = ebox,
1152 procedure = epoint_ebox_overlap_proc,
1153 commutator = @>,
1154 restrict = areasel,
1155 join = areajoinsel
1156 );
1158 CREATE OPERATOR @> (
1159 leftarg = ecluster,
1160 rightarg = epoint,
1161 procedure = epoint_ecluster_overlap_commutator,
1162 commutator = <@,
1163 restrict = areasel,
1164 join = areajoinsel
1165 );
1167 CREATE OPERATOR <@ (
1168 leftarg = epoint,
1169 rightarg = ecluster,
1170 procedure = epoint_ecluster_overlap_proc,
1171 commutator = @>,
1172 restrict = areasel,
1173 join = areajoinsel
1174 );
1176 CREATE OPERATOR @> (
1177 leftarg = ecluster,
1178 rightarg = ecluster,
1179 procedure = ecluster_contains_proc,
1180 commutator = <@,
1181 restrict = areasel,
1182 join = areajoinsel
1183 );
1185 CREATE FUNCTION ecluster_contains_commutator(ecluster, ecluster)
1186 RETURNS boolean
1187 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.@>) $1';
1189 CREATE OPERATOR <@ (
1190 leftarg = ecluster,
1191 rightarg = ecluster,
1192 procedure = ecluster_contains_commutator,
1193 commutator = @>,
1194 restrict = areasel,
1195 join = areajoinsel
1196 );
1198 CREATE FUNCTION ebox_contains_castwrap(ebox, ebox)
1199 RETURNS boolean
1200 LANGUAGE sql IMMUTABLE AS $$
1201 SELECT
1202 $1::@extschema@.ecluster OPERATOR(@extschema@.@>) $2::@extschema@.ecluster
1203 $$;
1205 CREATE OPERATOR @> (
1206 leftarg = ebox,
1207 rightarg = ebox,
1208 procedure = ebox_contains_castwrap,
1209 commutator = <@,
1210 restrict = areasel,
1211 join = areajoinsel
1212 );
1214 CREATE FUNCTION ebox_contains_swapped_castwrap(ebox, ebox)
1215 RETURNS boolean
1216 LANGUAGE sql IMMUTABLE AS $$
1217 SELECT
1218 $2::@extschema@.ecluster OPERATOR(@extschema@.@>) $1::@extschema@.ecluster
1219 $$;
1221 CREATE OPERATOR <@ (
1222 leftarg = ebox,
1223 rightarg = ebox,
1224 procedure = ebox_contains_swapped_castwrap,
1225 commutator = @>,
1226 restrict = areasel,
1227 join = areajoinsel
1228 );
1230 CREATE FUNCTION ebox_ecluster_contains_castwrap(ebox, ecluster)
1231 RETURNS boolean
1232 LANGUAGE sql IMMUTABLE
1233 AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.@>) $2';
1235 CREATE OPERATOR @> (
1236 leftarg = ebox,
1237 rightarg = ecluster,
1238 procedure = ebox_ecluster_contains_castwrap,
1239 commutator = <@,
1240 restrict = areasel,
1241 join = areajoinsel
1242 );
1244 CREATE FUNCTION ebox_ecluster_contains_castwrap(ecluster, ebox)
1245 RETURNS boolean
1246 LANGUAGE sql IMMUTABLE
1247 AS 'SELECT $2::@extschema@.ecluster OPERATOR(@extschema@.@>) $1';
1249 CREATE OPERATOR <@ (
1250 leftarg = ecluster,
1251 rightarg = ebox,
1252 procedure = ebox_ecluster_contains_castwrap,
1253 commutator = @>,
1254 restrict = areasel,
1255 join = areajoinsel
1256 );
1258 CREATE FUNCTION ecluster_ebox_contains_castwrap(ecluster, ebox)
1259 RETURNS boolean
1260 LANGUAGE sql IMMUTABLE
1261 AS 'SELECT $1 OPERATOR(@extschema@.@>) $2::@extschema@.ecluster';
1263 CREATE OPERATOR @> (
1264 leftarg = ecluster,
1265 rightarg = ebox,
1266 procedure = ecluster_ebox_contains_castwrap,
1267 commutator = <@,
1268 restrict = areasel,
1269 join = areajoinsel
1270 );
1272 CREATE FUNCTION ecluster_ebox_contains_castwrap(ebox, ecluster)
1273 RETURNS boolean
1274 LANGUAGE sql IMMUTABLE
1275 AS 'SELECT $2 OPERATOR(@extschema@.@>) $1::@extschema@.ecluster';
1277 CREATE OPERATOR <@ (
1278 leftarg = ebox,
1279 rightarg = ecluster,
1280 procedure = ecluster_ebox_contains_castwrap,
1281 commutator = @>,
1282 restrict = areasel,
1283 join = areajoinsel
1284 );
1286 CREATE OPERATOR <-> (
1287 leftarg = epoint,
1288 rightarg = epoint,
1289 procedure = epoint_distance_proc,
1290 commutator = <->
1291 );
1293 CREATE OPERATOR <-> (
1294 leftarg = epoint,
1295 rightarg = ecircle,
1296 procedure = epoint_ecircle_distance_proc,
1297 commutator = <->
1298 );
1300 CREATE FUNCTION epoint_ecircle_distance_commutator(ecircle, epoint)
1301 RETURNS float8
1302 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.<->) $1';
1304 CREATE OPERATOR <-> (
1305 leftarg = ecircle,
1306 rightarg = epoint,
1307 procedure = epoint_ecircle_distance_commutator,
1308 commutator = <->
1309 );
1311 CREATE OPERATOR <-> (
1312 leftarg = epoint,
1313 rightarg = ecluster,
1314 procedure = epoint_ecluster_distance_proc,
1315 commutator = <->
1316 );
1318 CREATE FUNCTION epoint_ecluster_distance_commutator(ecluster, epoint)
1319 RETURNS float8
1320 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.<->) $1';
1322 CREATE OPERATOR <-> (
1323 leftarg = ecluster,
1324 rightarg = epoint,
1325 procedure = epoint_ecluster_distance_commutator,
1326 commutator = <->
1327 );
1329 CREATE OPERATOR <-> (
1330 leftarg = ecircle,
1331 rightarg = ecircle,
1332 procedure = ecircle_distance_proc,
1333 commutator = <->
1334 );
1336 CREATE OPERATOR <-> (
1337 leftarg = ecircle,
1338 rightarg = ecluster,
1339 procedure = ecircle_ecluster_distance_proc,
1340 commutator = <->
1341 );
1343 CREATE FUNCTION ecircle_ecluster_distance_commutator(ecluster, ecircle)
1344 RETURNS float8
1345 LANGUAGE sql IMMUTABLE AS 'SELECT $2 OPERATOR(@extschema@.<->) $1';
1347 CREATE OPERATOR <-> (
1348 leftarg = ecluster,
1349 rightarg = ecircle,
1350 procedure = ecircle_ecluster_distance_commutator,
1351 commutator = <->
1352 );
1354 CREATE OPERATOR <-> (
1355 leftarg = ecluster,
1356 rightarg = ecluster,
1357 procedure = ecluster_distance_proc,
1358 commutator = <->
1359 );
1361 CREATE FUNCTION epoint_ebox_distance_castwrap(epoint, ebox)
1362 RETURNS float8
1363 LANGUAGE sql IMMUTABLE
1364 AS 'SELECT $1 OPERATOR(@extschema@.<->) $2::@extschema@.ecluster';
1366 CREATE OPERATOR <-> (
1367 leftarg = epoint,
1368 rightarg = ebox,
1369 procedure = epoint_ebox_distance_castwrap,
1370 commutator = <->
1371 );
1373 CREATE FUNCTION epoint_ebox_distance_castwrap(ebox, epoint)
1374 RETURNS float8
1375 LANGUAGE sql IMMUTABLE
1376 AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.<->) $2';
1378 CREATE OPERATOR <-> (
1379 leftarg = ebox,
1380 rightarg = epoint,
1381 procedure = epoint_ebox_distance_castwrap,
1382 commutator = <->
1383 );
1385 CREATE FUNCTION ebox_distance_castwrap(ebox, ebox)
1386 RETURNS float8
1387 LANGUAGE sql IMMUTABLE AS $$
1388 SELECT
1389 $1::@extschema@.ecluster OPERATOR(@extschema@.<->) $2::@extschema@.ecluster
1390 $$;
1392 CREATE OPERATOR <-> (
1393 leftarg = ebox,
1394 rightarg = ebox,
1395 procedure = ebox_distance_castwrap,
1396 commutator = <->
1397 );
1399 CREATE FUNCTION ebox_ecircle_distance_castwrap(ebox, ecircle)
1400 RETURNS float8
1401 LANGUAGE sql IMMUTABLE
1402 AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.<->) $2';
1404 CREATE OPERATOR <-> (
1405 leftarg = ebox,
1406 rightarg = ecircle,
1407 procedure = ebox_ecircle_distance_castwrap,
1408 commutator = <->
1409 );
1411 CREATE FUNCTION ebox_ecircle_distance_castwrap(ecircle, ebox)
1412 RETURNS float8
1413 LANGUAGE sql IMMUTABLE
1414 AS 'SELECT $1 OPERATOR(@extschema@.<->) $2::@extschema@.ecluster';
1416 CREATE OPERATOR <-> (
1417 leftarg = ecircle,
1418 rightarg = ebox,
1419 procedure = ebox_ecircle_distance_castwrap,
1420 commutator = <->
1421 );
1423 CREATE FUNCTION ebox_ecluster_distance_castwrap(ebox, ecluster)
1424 RETURNS float8
1425 LANGUAGE sql IMMUTABLE
1426 AS 'SELECT $1::@extschema@.ecluster OPERATOR(@extschema@.<->) $2';
1428 CREATE OPERATOR <-> (
1429 leftarg = ebox,
1430 rightarg = ecluster,
1431 procedure = ebox_ecluster_distance_castwrap,
1432 commutator = <->
1433 );
1435 CREATE FUNCTION ebox_ecluster_distance_castwrap(ecluster, ebox)
1436 RETURNS float8
1437 LANGUAGE sql IMMUTABLE
1438 AS 'SELECT $1 OPERATOR(@extschema@.<->) $2::@extschema@.ecluster';
1440 CREATE OPERATOR <-> (
1441 leftarg = ecluster,
1442 rightarg = ebox,
1443 procedure = ebox_ecluster_distance_castwrap,
1444 commutator = <->
1445 );
1447 CREATE OPERATOR <=> (
1448 leftarg = ecluster,
1449 rightarg = epoint_with_sample_count,
1450 procedure = fair_distance_operator_proc
1451 );
1454 ----------------
1455 -- GiST index --
1456 ----------------
1458 CREATE FUNCTION pgl_gist_consistent(internal, internal, smallint, oid, internal)
1459 RETURNS boolean
1460 LANGUAGE C STRICT
1461 AS '$libdir/latlon-v0010', 'pgl_gist_consistent';
1463 CREATE FUNCTION pgl_gist_union(internal, internal)
1464 RETURNS internal
1465 LANGUAGE C STRICT
1466 AS '$libdir/latlon-v0010', 'pgl_gist_union';
1468 CREATE FUNCTION pgl_gist_compress_epoint(internal)
1469 RETURNS internal
1470 LANGUAGE C STRICT
1471 AS '$libdir/latlon-v0010', 'pgl_gist_compress_epoint';
1473 CREATE FUNCTION pgl_gist_compress_ecircle(internal)
1474 RETURNS internal
1475 LANGUAGE C STRICT
1476 AS '$libdir/latlon-v0010', 'pgl_gist_compress_ecircle';
1478 CREATE FUNCTION pgl_gist_compress_ecluster(internal)
1479 RETURNS internal
1480 LANGUAGE C STRICT
1481 AS '$libdir/latlon-v0010', 'pgl_gist_compress_ecluster';
1483 CREATE FUNCTION pgl_gist_decompress(internal)
1484 RETURNS internal
1485 LANGUAGE C STRICT
1486 AS '$libdir/latlon-v0010', 'pgl_gist_decompress';
1488 CREATE FUNCTION pgl_gist_penalty(internal, internal, internal)
1489 RETURNS internal
1490 LANGUAGE C STRICT
1491 AS '$libdir/latlon-v0010', 'pgl_gist_penalty';
1493 CREATE FUNCTION pgl_gist_picksplit(internal, internal)
1494 RETURNS internal
1495 LANGUAGE C STRICT
1496 AS '$libdir/latlon-v0010', 'pgl_gist_picksplit';
1498 CREATE FUNCTION pgl_gist_same(internal, internal, internal)
1499 RETURNS internal
1500 LANGUAGE C STRICT
1501 AS '$libdir/latlon-v0010', 'pgl_gist_same';
1503 CREATE FUNCTION pgl_gist_distance(internal, internal, smallint, oid)
1504 RETURNS internal
1505 LANGUAGE C STRICT
1506 AS '$libdir/latlon-v0010', 'pgl_gist_distance';
1508 CREATE OPERATOR CLASS epoint_ops
1509 DEFAULT FOR TYPE epoint USING gist AS
1510 OPERATOR 11 = ,
1511 OPERATOR 22 && (epoint, ebox),
1512 OPERATOR 222 <@ (epoint, ebox),
1513 OPERATOR 23 && (epoint, ecircle),
1514 OPERATOR 24 && (epoint, ecluster),
1515 OPERATOR 124 &&+ (epoint, ecluster),
1516 OPERATOR 224 <@ (epoint, ecluster),
1517 OPERATOR 31 <-> (epoint, epoint) FOR ORDER BY float_ops,
1518 OPERATOR 32 <-> (epoint, ebox) FOR ORDER BY float_ops,
1519 OPERATOR 33 <-> (epoint, ecircle) FOR ORDER BY float_ops,
1520 OPERATOR 34 <-> (epoint, ecluster) FOR ORDER BY float_ops,
1521 FUNCTION 1 pgl_gist_consistent(internal, internal, smallint, oid, internal),
1522 FUNCTION 2 pgl_gist_union(internal, internal),
1523 FUNCTION 3 pgl_gist_compress_epoint(internal),
1524 FUNCTION 4 pgl_gist_decompress(internal),
1525 FUNCTION 5 pgl_gist_penalty(internal, internal, internal),
1526 FUNCTION 6 pgl_gist_picksplit(internal, internal),
1527 FUNCTION 7 pgl_gist_same(internal, internal, internal),
1528 FUNCTION 8 pgl_gist_distance(internal, internal, smallint, oid),
1529 STORAGE ekey_point;
1531 CREATE OPERATOR CLASS ecircle_ops
1532 DEFAULT FOR TYPE ecircle USING gist AS
1533 OPERATOR 13 = ,
1534 OPERATOR 21 && (ecircle, epoint),
1535 OPERATOR 22 && (ecircle, ebox),
1536 OPERATOR 122 &&+ (ecircle, ebox),
1537 OPERATOR 23 && (ecircle, ecircle),
1538 OPERATOR 24 && (ecircle, ecluster),
1539 OPERATOR 124 &&+ (ecircle, ecluster),
1540 OPERATOR 31 <-> (ecircle, epoint) FOR ORDER BY float_ops,
1541 OPERATOR 32 <-> (ecircle, ebox) FOR ORDER BY float_ops,
1542 OPERATOR 33 <-> (ecircle, ecircle) FOR ORDER BY float_ops,
1543 OPERATOR 34 <-> (ecircle, ecluster) FOR ORDER BY float_ops,
1544 FUNCTION 1 pgl_gist_consistent(internal, internal, smallint, oid, internal),
1545 FUNCTION 2 pgl_gist_union(internal, internal),
1546 FUNCTION 3 pgl_gist_compress_ecircle(internal),
1547 FUNCTION 4 pgl_gist_decompress(internal),
1548 FUNCTION 5 pgl_gist_penalty(internal, internal, internal),
1549 FUNCTION 6 pgl_gist_picksplit(internal, internal),
1550 FUNCTION 7 pgl_gist_same(internal, internal, internal),
1551 FUNCTION 8 pgl_gist_distance(internal, internal, smallint, oid),
1552 STORAGE ekey_area;
1554 CREATE OPERATOR CLASS ecluster_ops
1555 DEFAULT FOR TYPE ecluster USING gist AS
1556 OPERATOR 21 && (ecluster, epoint),
1557 OPERATOR 121 &&+ (ecluster, epoint),
1558 OPERATOR 221 @> (ecluster, epoint),
1559 OPERATOR 22 && (ecluster, ebox),
1560 OPERATOR 122 &&+ (ecluster, ebox),
1561 OPERATOR 222 @> (ecluster, ebox),
1562 OPERATOR 322 <@ (ecluster, ebox),
1563 OPERATOR 23 && (ecluster, ecircle),
1564 OPERATOR 123 &&+ (ecluster, ecircle),
1565 OPERATOR 24 && (ecluster, ecluster),
1566 OPERATOR 124 &&+ (ecluster, ecluster),
1567 OPERATOR 224 @> (ecluster, ecluster),
1568 OPERATOR 324 <@ (ecluster, ecluster),
1569 OPERATOR 31 <-> (ecluster, epoint) FOR ORDER BY float_ops,
1570 OPERATOR 32 <-> (ecluster, ebox) FOR ORDER BY float_ops,
1571 OPERATOR 33 <-> (ecluster, ecircle) FOR ORDER BY float_ops,
1572 OPERATOR 34 <-> (ecluster, ecluster) FOR ORDER BY float_ops,
1573 OPERATOR 131 <=> (ecluster, epoint_with_sample_count) FOR ORDER BY float_ops,
1574 FUNCTION 1 pgl_gist_consistent(internal, internal, smallint, oid, internal),
1575 FUNCTION 2 pgl_gist_union(internal, internal),
1576 FUNCTION 3 pgl_gist_compress_ecluster(internal),
1577 FUNCTION 4 pgl_gist_decompress(internal),
1578 FUNCTION 5 pgl_gist_penalty(internal, internal, internal),
1579 FUNCTION 6 pgl_gist_picksplit(internal, internal),
1580 FUNCTION 7 pgl_gist_same(internal, internal, internal),
1581 FUNCTION 8 pgl_gist_distance(internal, internal, smallint, oid),
1582 STORAGE ekey_area;
1585 ---------------------
1586 -- alias functions --
1587 ---------------------
1589 CREATE FUNCTION distance(epoint, epoint)
1590 RETURNS float8
1591 LANGUAGE sql IMMUTABLE AS 'SELECT $1 OPERATOR(@extschema@.<->) $2';
1593 CREATE FUNCTION distance(ecluster, epoint)
1594 RETURNS float8
1595 LANGUAGE sql IMMUTABLE AS 'SELECT $1 OPERATOR(@extschema@.<->) $2';
1597 CREATE FUNCTION distance_within(epoint, epoint, float8)
1598 RETURNS boolean
1599 LANGUAGE sql IMMUTABLE
1600 AS 'SELECT $1 OPERATOR(@extschema@.&&) @extschema@.ecircle($2, $3)';
1602 CREATE FUNCTION distance_within(ecluster, epoint, float8)
1603 RETURNS boolean
1604 LANGUAGE sql IMMUTABLE
1605 AS 'SELECT $1 OPERATOR(@extschema@.&&) @extschema@.ecircle($2, $3)';
1607 CREATE FUNCTION fair_distance(ecluster, epoint, int4 = 10000)
1608 RETURNS float8
1609 LANGUAGE sql IMMUTABLE AS $$
1610 SELECT
1611 $1 OPERATOR(@extschema@.<=>) @extschema@.epoint_with_sample_count($2, $3)
1612 $$;
1615 --------------------------------
1616 -- other data storage formats --
1617 --------------------------------
1619 CREATE FUNCTION coords_to_epoint(float8, float8, text = 'epoint')
1620 RETURNS epoint
1621 LANGUAGE plpgsql IMMUTABLE STRICT AS $$
1622 DECLARE
1623 "result" @extschema@.epoint;
1624 BEGIN
1625 IF $3 OPERATOR(pg_catalog.=) 'epoint_lonlat' THEN
1626 -- avoid dynamic command execution for better performance
1627 RETURN @extschema@.epoint($2, $1);
1628 END IF;
1629 IF
1630 $3 OPERATOR(pg_catalog.=) 'epoint' OR
1631 $3 OPERATOR(pg_catalog.=) 'epoint_latlon'
1632 THEN
1633 -- avoid dynamic command execution for better performance
1634 RETURN @extschema@.epoint($1, $2);
1635 END IF;
1636 EXECUTE
1637 'SELECT ' OPERATOR(pg_catalog.||) $3 OPERATOR(pg_catalog.||) '($1, $2)' INTO STRICT "result" USING $1, $2;
1638 RETURN "result";
1639 END;
1640 $$;
1642 CREATE FUNCTION GeoJSON_LinearRing_vertices(jsonb, text = 'epoint_lonlat')
1643 RETURNS SETOF jsonb
1644 LANGUAGE sql IMMUTABLE STRICT AS $$
1645 SELECT "result" FROM
1646 ( SELECT pg_catalog.jsonb_array_length($1) - 1 )
1647 AS "lastindex_row" ("lastindex")
1648 CROSS JOIN LATERAL pg_catalog.jsonb_array_elements(
1649 CASE WHEN
1650 @extschema@.coords_to_epoint(
1651 ($1 OPERATOR(pg_catalog.->) 0 OPERATOR(pg_catalog.->>) 0)
1652 ::pg_catalog.float8,
1653 ($1 OPERATOR(pg_catalog.->) 0 OPERATOR(pg_catalog.->>) 1)
1654 ::pg_catalog.float8,
1655 $2
1656 ) OPERATOR(pg_catalog.=) @extschema@.coords_to_epoint(
1657 ($1 OPERATOR(pg_catalog.->) "lastindex" OPERATOR(pg_catalog.->>) 0)
1658 ::pg_catalog.float8,
1659 ($1 OPERATOR(pg_catalog.->) "lastindex" OPERATOR(pg_catalog.->>) 1)
1660 ::pg_catalog.float8,
1661 $2
1662 )
1663 THEN
1664 $1 - "lastindex"
1665 ELSE
1666 $1
1667 END
1668 ) AS "result_row" ("result")
1669 $$;
1671 CREATE FUNCTION GeoJSON_to_epoint(jsonb, text = 'epoint_lonlat')
1672 RETURNS epoint
1673 LANGUAGE sql IMMUTABLE STRICT AS $$
1674 SELECT CASE
1675 WHEN $1 OPERATOR(pg_catalog.->>) 'type' OPERATOR(pg_catalog.=) 'Point' THEN
1676 @extschema@.coords_to_epoint(
1677 ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 0)
1678 ::pg_catalog.float8,
1679 ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 1)
1680 ::pg_catalog.float8,
1681 $2
1682 )
1683 WHEN $1->>'type' = 'Feature' THEN
1684 @extschema@.GeoJSON_to_epoint($1 OPERATOR(pg_catalog.->) 'geometry', $2)
1685 ELSE
1686 NULL
1687 END
1688 $$;
1690 CREATE FUNCTION GeoJSON_to_ecluster(jsonb, text = 'epoint_lonlat')
1691 RETURNS ecluster
1692 LANGUAGE plpgsql IMMUTABLE STRICT AS $$
1693 DECLARE
1694 "tp" TEXT = $1 OPERATOR(pg_catalog.->>) 'type';
1695 BEGIN
1696 IF "tp" = 'Point' THEN RETURN
1697 @extschema@.coords_to_epoint(
1698 ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 0)
1699 ::pg_catalog.float8,
1700 ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 1)
1701 ::pg_catalog.float8,
1702 $2
1703 )::@extschema@.ecluster;
1704 END IF;
1705 raise notice 'DEBUG2';
1706 IF "tp" = 'MultiPoint' THEN RETURN
1707 ( SELECT @extschema@.ecluster_create_multipoint(pg_catalog.array_agg(
1708 @extschema@.coords_to_epoint(
1709 ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
1710 ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
1711 $2
1712 )
1713 ))
1714 FROM pg_catalog.jsonb_array_elements(
1715 $1 OPERATOR(pg_catalog.->) 'coordinates'
1716 ) AS "coord"
1717 );
1718 END IF;
1719 IF "tp" = 'LineString' THEN RETURN
1720 ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg(
1721 @extschema@.coords_to_epoint(
1722 ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
1723 ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
1724 $2
1725 )
1726 ))
1727 FROM pg_catalog.jsonb_array_elements(
1728 $1 OPERATOR(pg_catalog.->) 'coordinates'
1729 ) AS "coord"
1730 );
1731 END IF;
1732 IF "tp" = 'MultiLineString' THEN RETURN
1733 ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
1734 ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg(
1735 @extschema@.coords_to_epoint(
1736 ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
1737 ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
1738 $2
1739 )
1740 ))
1741 FROM pg_catalog.jsonb_array_elements("coord_array") AS "coord"
1742 )
1743 ))
1744 FROM pg_catalog.jsonb_array_elements(
1745 $1 OPERATOR(pg_catalog.->) 'coordinates'
1746 ) AS "coord_array"
1747 );
1748 END IF;
1749 IF "tp" = 'Polygon' THEN RETURN
1750 ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
1751 ( SELECT @extschema@.ecluster_create_polygon(pg_catalog.array_agg(
1752 @extschema@.coords_to_epoint(
1753 ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
1754 ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
1755 $2
1756 )
1757 ))
1758 FROM @extschema@.GeoJSON_LinearRing_vertices("coord_array", $2)
1759 AS "coord"
1760 )
1761 ))
1762 FROM pg_catalog.jsonb_array_elements(
1763 $1 OPERATOR(pg_catalog.->) 'coordinates'
1764 ) AS "coord_array"
1765 );
1766 END IF;
1767 IF "tp" = 'MultiPolygon' THEN RETURN
1768 ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
1769 ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
1770 ( SELECT @extschema@.ecluster_create_polygon(
1771 pg_catalog.array_agg(
1772 @extschema@.coords_to_epoint(
1773 ("coord" OPERATOR(pg_catalog.->>) 0)
1774 ::pg_catalog.float8,
1775 ("coord" OPERATOR(pg_catalog.->>) 1)
1776 ::pg_catalog.float8,
1777 $2
1778 )
1779 )
1780 )
1781 FROM @extschema@.GeoJSON_LinearRing_vertices(
1782 "coord_array", $2
1783 ) AS "coord"
1784 )
1785 ))
1786 FROM pg_catalog.jsonb_array_elements("coord_array_array")
1787 AS "coord_array"
1788 )
1789 ))
1790 FROM jsonb_array_elements(
1791 $1 OPERATOR(pg_catalog.->) 'coordinates'
1792 ) AS "coord_array_array"
1793 );
1794 END IF;
1795 IF "tp" = 'GeometryCollection' THEN RETURN
1796 ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
1797 @extschema@.GeoJSON_to_ecluster("geometry", $2)
1798 ))
1799 FROM pg_catalog.jsonb_array_elements(
1800 $1 OPERATOR(pg_catalog.->) 'geometries'
1801 ) AS "geometry"
1802 );
1803 END IF;
1804 IF "tp" = 'Feature' THEN RETURN
1805 @extschema@.GeoJSON_to_ecluster(
1806 $1 OPERATOR(pg_catalog.->) 'geometry', $2
1807 );
1808 END IF;
1809 IF "tp" = 'FeatureCollection' THEN RETURN
1810 ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
1811 @extschema@.GeoJSON_to_ecluster("feature", $2)
1812 ))
1813 FROM pg_catalog.jsonb_array_elements(
1814 $1 OPERATOR(pg_catalog.->) 'features'
1815 ) AS "feature"
1816 );
1817 END IF;
1818 RETURN NULL;
1819 END;
1820 $$;