Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit3b6db1f

Browse files
committed
Add geometry/range functions to support BRIN inclusion
This commit adds the following functions: box(point) -> box bound_box(box, box) -> box inet_same_family(inet, inet) -> bool inet_merge(inet, inet) -> cidr range_merge(anyrange, anyrange) -> anyrangeThe first of these is also used to implement a new assignment cast frompoint to box.These functions are the first part of a base to implement an "inclusion"operator class for BRIN, for multidimensional data types.Author: Emre HasegeliReviewed by: Andreas Karlsson
1 parent456ff08 commit3b6db1f

File tree

18 files changed

+363
-16
lines changed

18 files changed

+363
-16
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 47 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8295,6 +8295,12 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
82958295
<entry>circle to box</entry>
82968296
<entry><literal>box(circle '((0,0),2.0)')</literal></entry>
82978297
</row>
8298+
<row>
8299+
<entry><literal><function>box(<type>point</type>)</function></literal></entry>
8300+
<entry><type>box</type></entry>
8301+
<entry>point to empty box</entry>
8302+
<entry><literal>box(point '(0,0)')</literal></entry>
8303+
</row>
82988304
<row>
82998305
<entry><literal><function>box(<type>point</type>, <type>point</type>)</function></literal></entry>
83008306
<entry><type>box</type></entry>
@@ -8307,6 +8313,12 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
83078313
<entry>polygon to box</entry>
83088314
<entry><literal>box(polygon '((0,0),(1,1),(2,0))')</literal></entry>
83098315
</row>
8316+
<row>
8317+
<entry><literal><function>bound_box(<type>box</type>, <type>box</type>)</function></literal></entry>
8318+
<entry><type>box</type></entry>
8319+
<entry>boxes to bounding box</entry>
8320+
<entry><literal>bound_box(box '((0,0),(1,1))', box '((3,3),(4,4))')</literal></entry>
8321+
</row>
83108322
<row>
83118323
<entry>
83128324
<indexterm>
@@ -8734,6 +8746,30 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
87348746
<entry><literal>text(inet '192.168.1.5')</literal></entry>
87358747
<entry><literal>192.168.1.5/32</literal></entry>
87368748
</row>
8749+
<row>
8750+
<entry>
8751+
<indexterm>
8752+
<primary>inet_same_family</primary>
8753+
</indexterm>
8754+
<literal><function>inet_same_family(<type>inet</type>, <type>inet</type>)</function></literal>
8755+
</entry>
8756+
<entry><type>boolean</type></entry>
8757+
<entry>are the addresses from the same family?</entry>
8758+
<entry><literal>inet_same_family('192.168.1.5/24', '::1')</literal></entry>
8759+
<entry><literal>false</literal></entry>
8760+
</row>
8761+
<row>
8762+
<entry>
8763+
<indexterm>
8764+
<primary>inet_merge</primary>
8765+
</indexterm>
8766+
<literal><function>inet_merge(<type>inet</type>, <type>inet</type>)</function></literal>
8767+
</entry>
8768+
<entry><type>cidr</type></entry>
8769+
<entry>the smallest network which includes both of the given networks</entry>
8770+
<entry><literal>inet_merge('192.168.1.5/24', '192.168.2.5/24')</literal></entry>
8771+
<entry><literal>192.168.0.0/22</literal></entry>
8772+
</row>
87378773
</tbody>
87388774
</tgroup>
87398775
</table>
@@ -12090,6 +12126,17 @@ NULL baz</literallayout>(3 rows)</entry>
1209012126
<entry><literal>upper_inf('(,)'::daterange)</literal></entry>
1209112127
<entry><literal>true</literal></entry>
1209212128
</row>
12129+
<row>
12130+
<entry>
12131+
<literal>
12132+
<function>range_merge</function>(<type>anyrange</type>, <type>anyrange</type>)
12133+
</literal>
12134+
</entry>
12135+
<entry><type>anyrange</type></entry>
12136+
<entry>the smallest range which includes both of the given ranges</entry>
12137+
<entry><literal>range_merge('[1,2)'::int4range, '[3,4)'::int4range)</literal></entry>
12138+
<entry><literal>[1,4)</literal></entry>
12139+
</row>
1209312140
</tbody>
1209412141
</tgroup>
1209512142
</table>

‎src/backend/utils/adt/geo_ops.c

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4227,6 +4227,45 @@ box_div(PG_FUNCTION_ARGS)
42274227
PG_RETURN_BOX_P(result);
42284228
}
42294229

4230+
/*
4231+
* Convert point to empty box
4232+
*/
4233+
Datum
4234+
point_box(PG_FUNCTION_ARGS)
4235+
{
4236+
Point*pt=PG_GETARG_POINT_P(0);
4237+
BOX*box;
4238+
4239+
box= (BOX*)palloc(sizeof(BOX));
4240+
4241+
box->high.x=pt->x;
4242+
box->low.x=pt->x;
4243+
box->high.y=pt->y;
4244+
box->low.y=pt->y;
4245+
4246+
PG_RETURN_BOX_P(box);
4247+
}
4248+
4249+
/*
4250+
* Smallest bounding box that includes both of the given boxes
4251+
*/
4252+
Datum
4253+
boxes_bound_box(PG_FUNCTION_ARGS)
4254+
{
4255+
BOX*box1=PG_GETARG_BOX_P(0),
4256+
*box2=PG_GETARG_BOX_P(1),
4257+
*container;
4258+
4259+
container= (BOX*)palloc(sizeof(BOX));
4260+
4261+
container->high.x=Max(box1->high.x,box2->high.x);
4262+
container->low.x=Min(box1->low.x,box2->low.x);
4263+
container->high.y=Max(box1->high.y,box2->high.y);
4264+
container->low.y=Min(box1->low.y,box2->low.y);
4265+
4266+
PG_RETURN_BOX_P(container);
4267+
}
4268+
42304269

42314270
/***********************************************************************
42324271
**

‎src/backend/utils/adt/network.c

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -887,6 +887,58 @@ network_hostmask(PG_FUNCTION_ARGS)
887887
PG_RETURN_INET_P(dst);
888888
}
889889

890+
/*
891+
* Returns true if the addresses are from the same family, or false. Used to
892+
* check that we can create a network which contains both of the networks.
893+
*/
894+
Datum
895+
inet_same_family(PG_FUNCTION_ARGS)
896+
{
897+
inet*a1=PG_GETARG_INET_PP(0);
898+
inet*a2=PG_GETARG_INET_PP(1);
899+
900+
PG_RETURN_BOOL(ip_family(a1)==ip_family(a2));
901+
}
902+
903+
/*
904+
* Returns the smallest CIDR which contains both of the inputs.
905+
*/
906+
Datum
907+
inet_merge(PG_FUNCTION_ARGS)
908+
{
909+
inet*a1=PG_GETARG_INET_PP(0),
910+
*a2=PG_GETARG_INET_PP(1),
911+
*result;
912+
intcommonbits;
913+
914+
if (ip_family(a1)!=ip_family(a2))
915+
ereport(ERROR,
916+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
917+
errmsg("cannot merge addresses from different families")));
918+
919+
commonbits=bitncommon(ip_addr(a1),ip_addr(a2),
920+
Min(ip_bits(a1),ip_bits(a2)));
921+
922+
/* Make sure any unused bits are zeroed. */
923+
result= (inet*)palloc0(sizeof(inet));
924+
925+
ip_family(result)=ip_family(a1);
926+
ip_bits(result)=commonbits;
927+
928+
/* Clone appropriate bytes of the address. */
929+
if (commonbits>0)
930+
memcpy(ip_addr(result),ip_addr(a1), (commonbits+7) /8);
931+
932+
/* Clean any unwanted bits in the last partial byte. */
933+
if (commonbits %8!=0)
934+
ip_addr(result)[commonbits /8] &= ~(0xFF >> (commonbits %8));
935+
936+
/* Set varlena header correctly. */
937+
SET_INET_VARSIZE(result);
938+
939+
PG_RETURN_INET_P(result);
940+
}
941+
890942
/*
891943
* Convert a value of a network datatype to an approximate scalar value.
892944
* This is used for estimating selectivities of inequality operators

‎src/backend/utils/adt/rangetypes.c

Lines changed: 41 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -1006,13 +1006,14 @@ range_minus(PG_FUNCTION_ARGS)
10061006
PG_RETURN_NULL();
10071007
}
10081008

1009-
/* set union */
1010-
Datum
1011-
range_union(PG_FUNCTION_ARGS)
1009+
/*
1010+
* Set union. If strict is true, it is an error that the two input ranges
1011+
* are not adjacent or overlapping.
1012+
*/
1013+
staticRangeType*
1014+
range_union_internal(TypeCacheEntry*typcache,RangeType*r1,RangeType*r2,
1015+
boolstrict)
10121016
{
1013-
RangeType*r1=PG_GETARG_RANGE(0);
1014-
RangeType*r2=PG_GETARG_RANGE(1);
1015-
TypeCacheEntry*typcache;
10161017
RangeBoundlower1,
10171018
lower2;
10181019
RangeBoundupper1,
@@ -1026,19 +1027,18 @@ range_union(PG_FUNCTION_ARGS)
10261027
if (RangeTypeGetOid(r1)!=RangeTypeGetOid(r2))
10271028
elog(ERROR,"range types do not match");
10281029

1029-
typcache=range_get_typcache(fcinfo,RangeTypeGetOid(r1));
1030-
10311030
range_deserialize(typcache,r1,&lower1,&upper1,&empty1);
10321031
range_deserialize(typcache,r2,&lower2,&upper2,&empty2);
10331032

10341033
/* if either is empty, the other is the correct answer */
10351034
if (empty1)
1036-
PG_RETURN_RANGE(r2);
1035+
returnr2;
10371036
if (empty2)
1038-
PG_RETURN_RANGE(r1);
1037+
returnr1;
10391038

1040-
if (!DatumGetBool(range_overlaps(fcinfo))&&
1041-
!DatumGetBool(range_adjacent(fcinfo)))
1039+
if (strict&&
1040+
!DatumGetBool(range_overlaps_internal(typcache,r1,r2))&&
1041+
!DatumGetBool(range_adjacent_internal(typcache,r1,r2)))
10421042
ereport(ERROR,
10431043
(errcode(ERRCODE_DATA_EXCEPTION),
10441044
errmsg("result of range union would not be contiguous")));
@@ -1053,7 +1053,35 @@ range_union(PG_FUNCTION_ARGS)
10531053
else
10541054
result_upper=&upper2;
10551055

1056-
PG_RETURN_RANGE(make_range(typcache,result_lower,result_upper, false));
1056+
returnmake_range(typcache,result_lower,result_upper, false);
1057+
}
1058+
1059+
Datum
1060+
range_union(PG_FUNCTION_ARGS)
1061+
{
1062+
RangeType*r1=PG_GETARG_RANGE(0);
1063+
RangeType*r2=PG_GETARG_RANGE(1);
1064+
TypeCacheEntry*typcache;
1065+
1066+
typcache=range_get_typcache(fcinfo,RangeTypeGetOid(r1));
1067+
1068+
PG_RETURN_RANGE(range_union_internal(typcache,r1,r2, true));
1069+
}
1070+
1071+
/*
1072+
* range merge: like set union, except also allow and account for non-adjacent
1073+
* input ranges.
1074+
*/
1075+
Datum
1076+
range_merge(PG_FUNCTION_ARGS)
1077+
{
1078+
RangeType*r1=PG_GETARG_RANGE(0);
1079+
RangeType*r2=PG_GETARG_RANGE(1);
1080+
TypeCacheEntry*typcache;
1081+
1082+
typcache=range_get_typcache(fcinfo,RangeTypeGetOid(r1));
1083+
1084+
PG_RETURN_RANGE(range_union_internal(typcache,r1,r2, false));
10571085
}
10581086

10591087
/* set intersection */

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO201504291
56+
#defineCATALOG_VERSION_NO201505051
5757

5858
#endif

‎src/include/catalog/pg_cast.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -273,6 +273,7 @@ DATA(insert ( 703 23 0 e b ));
273273
/*
274274
* Geometric category
275275
*/
276+
DATA(insert (6006034091af ));
276277
DATA(insert (6016001532ef ));
277278
DATA(insert (6026001533ef ));
278279
DATA(insert (6026041449af ));

‎src/include/catalog/pg_proc.h

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1140,6 +1140,8 @@ DATA(insert OID = 978 ( box_distance PGNSP PGUID 12 1 0 0 0 f f f f t f i 2
11401140
DATA(insert OID = 979 ( area PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 701 "602" _null_ _null_ _null_ _null_ _null_path_area _null_ _null_ _null_ ));
11411141
DESCR("area of a closed path");
11421142
DATA(insert OID = 980 ( box_intersect PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 603 "603 603" _null_ _null_ _null_ _null_ _null_box_intersect _null_ _null_ _null_ ));
1143+
DATA(insert OID = 4067 ( bound_box PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 603 "603 603" _null_ _null_ _null_ _null_ _null_boxes_bound_box _null_ _null_ _null_ ));
1144+
DESCR("bounding box of two boxes");
11431145
DATA(insert OID = 981 ( diagonal PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 601 "603" _null_ _null_ _null_ _null_ _null_box_diagonal _null_ _null_ _null_ ));
11441146
DESCR("box diagonal");
11451147
DATA(insert OID = 982 ( path_n_lt PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 16 "602 602" _null_ _null_ _null_ _null_ _null_ path_n_lt _null_ _null_ _null_ ));
@@ -1744,6 +1746,8 @@ DESCR("convert vertex count and circle to polygon");
17441746
DATA(insert OID = 1476 ( dist_pcPGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 701 "600 718" _null_ _null_ _null_ _null_ _null_ dist_pc _null_ _null_ _null_ ));
17451747
DATA(insert OID = 1477 ( circle_contain_pt PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 16 "718 600" _null_ _null_ _null_ _null_ _null_ circle_contain_pt _null_ _null_ _null_ ));
17461748
DATA(insert OID = 1478 ( pt_contained_circlePGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 16 "600 718" _null_ _null_ _null_ _null_ _null_ pt_contained_circle _null_ _null_ _null_ ));
1749+
DATA(insert OID = 4091 ( boxPGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 603 "600" _null_ _null_ _null_ _null_ _null_ point_box _null_ _null_ _null_ ));
1750+
DESCR("convert point to empty box");
17471751
DATA(insert OID = 1479 ( circlePGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 718 "603" _null_ _null_ _null_ _null_ _null_ box_circle _null_ _null_ _null_ ));
17481752
DESCR("convert box to circle");
17491753
DATA(insert OID = 1480 ( boxPGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 603 "718" _null_ _null_ _null_ _null_ _null_ circle_box _null_ _null_ _null_ ));
@@ -2232,6 +2236,10 @@ DATA(insert OID = 2630 ( inetplPGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 869
22322236
DATA(insert OID = 2631 ( int8pl_inetPGNSP PGUID 14 1 0 0 0 f f f f t f i 2 0 869 "20 869" _null_ _null_ _null_ _null_ _null_ "select $2 + $1" _null_ _null_ _null_ ));
22332237
DATA(insert OID = 2632 ( inetmi_int8PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 869 "869 20" _null_ _null_ _null_ _null_ _null_inetmi_int8 _null_ _null_ _null_ ));
22342238
DATA(insert OID = 2633 ( inetmiPGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 20 "869 869" _null_ _null_ _null_ _null_ _null_inetmi _null_ _null_ _null_ ));
2239+
DATA(insert OID = 4071 ( inet_same_familyPGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 16 "869 869" _null_ _null_ _null_ _null_ _null_ inet_same_family _null_ _null_ _null_ ));
2240+
DESCR("are the addresses from the same family?");
2241+
DATA(insert OID = 4063 ( inet_mergePGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 650 "869 869" _null_ _null_ _null_ _null_ _null_ inet_merge _null_ _null_ _null_ ));
2242+
DESCR("the smallest network which includes both of the given networks");
22352243

22362244
/* GiST support for inet and cidr */
22372245
DATA(insert OID = 3553 ( inet_gist_consistentPGNSP PGUID 12 1 0 0 0 f f f f t f i 5 0 16 "2281 869 23 26 2281" _null_ _null_ _null_ _null_ _null_ inet_gist_consistent _null_ _null_ _null_ ));
@@ -4937,6 +4945,8 @@ DATA(insert OID = 3866 ( range_overrightPGNSP PGUID 12 1 0 0 0 f f f f t f i 2
49374945
DESCR("implementation of &> operator");
49384946
DATA(insert OID = 3867 ( range_unionPGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3831 "3831 3831" _null_ _null_ _null_ _null_ _null_ range_union _null_ _null_ _null_ ));
49394947
DESCR("implementation of + operator");
4948+
DATA(insert OID = 4057 ( range_mergePGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3831 "3831 3831" _null_ _null_ _null_ _null_ _null_ range_merge _null_ _null_ _null_ ));
4949+
DESCR("the smallest range which includes both of the given ranges");
49404950
DATA(insert OID = 3868 ( range_intersectPGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3831 "3831 3831" _null_ _null_ _null_ _null_ _null_ range_intersect _null_ _null_ _null_ ));
49414951
DESCR("implementation of * operator");
49424952
DATA(insert OID = 3869 ( range_minusPGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 3831 "3831 3831" _null_ _null_ _null_ _null_ _null_ range_minus _null_ _null_ _null_ ));

‎src/include/utils/builtins.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -942,6 +942,8 @@ extern Datum inetpl(PG_FUNCTION_ARGS);
942942
externDatuminetmi_int8(PG_FUNCTION_ARGS);
943943
externDatuminetmi(PG_FUNCTION_ARGS);
944944
externvoidclean_ipv6_addr(intaddr_family,char*addr);
945+
externDatuminet_same_family(PG_FUNCTION_ARGS);
946+
externDatuminet_merge(PG_FUNCTION_ARGS);
945947

946948
/* mac.c */
947949
externDatummacaddr_in(PG_FUNCTION_ARGS);

‎src/include/utils/geo_decls.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -302,6 +302,8 @@ extern Datum box_add(PG_FUNCTION_ARGS);
302302
externDatumbox_sub(PG_FUNCTION_ARGS);
303303
externDatumbox_mul(PG_FUNCTION_ARGS);
304304
externDatumbox_div(PG_FUNCTION_ARGS);
305+
externDatumpoint_box(PG_FUNCTION_ARGS);
306+
externDatumboxes_bound_box(PG_FUNCTION_ARGS);
305307

306308
/* public path routines */
307309
externDatumpath_area(PG_FUNCTION_ARGS);

‎src/include/utils/rangetypes.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -211,6 +211,7 @@ extern Datum range_gist_compress(PG_FUNCTION_ARGS);
211211
externDatumrange_gist_decompress(PG_FUNCTION_ARGS);
212212
externDatumrange_gist_fetch(PG_FUNCTION_ARGS);
213213
externDatumrange_gist_union(PG_FUNCTION_ARGS);
214+
externDatumrange_merge(PG_FUNCTION_ARGS);
214215
externDatumrange_gist_penalty(PG_FUNCTION_ARGS);
215216
externDatumrange_gist_picksplit(PG_FUNCTION_ARGS);
216217
externDatumrange_gist_same(PG_FUNCTION_ARGS);

‎src/test/regress/expected/geometry.out

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -278,6 +278,40 @@ SELECT '' AS twenty, b.f1 / p.f1 AS rotation
278278
| (0.3,0),(0.3,0)
279279
(20 rows)
280280

281+
SELECT f1::box
282+
FROM POINT_TBL;
283+
f1
284+
-----------------------
285+
(0,0),(0,0)
286+
(-10,0),(-10,0)
287+
(-3,4),(-3,4)
288+
(5.1,34.5),(5.1,34.5)
289+
(-5,-12),(-5,-12)
290+
(10,10),(10,10)
291+
(6 rows)
292+
293+
SELECT bound_box(a.f1, b.f1)
294+
FROM BOX_TBL a, BOX_TBL b;
295+
bound_box
296+
---------------------
297+
(2,2),(0,0)
298+
(3,3),(0,0)
299+
(2.5,3.5),(0,0)
300+
(3,3),(0,0)
301+
(3,3),(0,0)
302+
(3,3),(1,1)
303+
(3,3.5),(1,1)
304+
(3,3),(1,1)
305+
(2.5,3.5),(0,0)
306+
(3,3.5),(1,1)
307+
(2.5,3.5),(2.5,2.5)
308+
(3,3.5),(2.5,2.5)
309+
(3,3),(0,0)
310+
(3,3),(1,1)
311+
(3,3.5),(2.5,2.5)
312+
(3,3),(3,3)
313+
(16 rows)
314+
281315
--
282316
-- Paths
283317
--

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp