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

KNN support for spoints#116

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to ourterms of service andprivacy statement. We’ll occasionally send you account related emails.

Already on GitHub?Sign in to your account

Merged
vitcpp merged 2 commits intopostgrespro:masterfromvitcpp:knn
Dec 22, 2023
Merged
Show file tree
Hide file tree
Changes fromall commits
Commits
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
8 changes: 6 additions & 2 deletionsMakefile
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -34,12 +34,13 @@ DATA_built = $(RELEASE_SQL) \
pg_sphere--1.3.0--1.3.1.sql \
pg_sphere--1.3.1--1.4.0.sql \
pg_sphere--1.4.0--1.4.1.sql \
pg_sphere--1.4.1--1.4.2.sql
pg_sphere--1.4.1--1.4.2.sql \
pg_sphere--1.4.2--1.5.0.sql

DOCS = README.pg_sphere COPYRIGHT.pg_sphere
TESTS = version tables points euler circle line ellipse poly path box \
index contains_ops contains_ops_compat bounding_box_gist gnomo \
epochprop contains overlaps spoint_brin sbox_brin selectivity
epochprop contains overlaps spoint_brin sbox_brin selectivity knn
REGRESS = init $(TESTS)

PG_CFLAGS+= -DPGSPHERE_VERSION=$(PGSPHERE_VERSION)
Expand DownExpand Up@@ -209,6 +210,9 @@ pg_sphere--1.3.1--1.4.0.sql: pgs_circle_sel.sql.in pgs_hash.sql.in
pg_sphere--1.4.0--1.4.1.sql pg_sphere--1.4.1--1.4.2.sql:
@echo "-- Nothing to upgrade in the schema" > $@

pg_sphere--1.4.2--1.5.0.sql:
cat upgrade_scripts/$@.in $^ > $@

# end of local stuff

src/sscan.o : src/sparse.c
Expand Down
2 changes: 1 addition & 1 deletionMakefile.common.mk
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -5,4 +5,4 @@
#----------------------------------------------------------------------------

EXTENSION := pg_sphere
PGSPHERE_VERSION := 1.4.2
PGSPHERE_VERSION := 1.5.0
12 changes: 12 additions & 0 deletionsdoc/indices.sgm
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -68,6 +68,11 @@
</para>
</listitem>
</itemizedlist>
<para>
A GiST index can be also used for quickly finding the points closest to the given one
when ordering by an expression with the <literal>&lt;-&gt;</literal> operator,
as shown in an example below.
</para>
<para>
BRIN indexing supports just spherical points (<type>spoint</type>)
and spherical coordinates range (<type>sbox</type>) at the moment.
Expand All@@ -82,6 +87,13 @@
<![CDATA[CREATE INDEX test_pos_idx ON test USING GIST (pos);]]>
<![CDATA[VACUUM ANALYZE test;]]>
</programlisting>
<para>
To find the points closest to a given spherical position, use the <literal>&lt;-&gt;</literal> operator:
</para>
<programlisting>
<![CDATA[SELECT * FROM test ORDER BY pos <-> spoint (0.2, 0.3) LIMIT 10 ]]>
</programlisting>

<para>
BRIN index can be created through the following syntax:
</para>
Expand Down
122 changes: 122 additions & 0 deletionsexpected/knn.out
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,122 @@
CREATE TABLE points (id int, p spoint, pos int);
INSERT INTO points (id, p) SELECT x, spoint(random()*6.28, (2*random()-1)*1.57) FROM generate_series(1,314159) x;
CREATE INDEX i ON points USING gist (p);
SET enable_indexscan = true;
EXPLAIN (costs off) SELECT p <-> spoint (0.2, 0.3) FROM points ORDER BY 1 LIMIT 100;
QUERY PLAN
-------------------------------------------------
Limit
-> Index Scan using i on points
Order By: (p <-> '(0.2 , 0.3)'::spoint)
(3 rows)

UPDATE points SET pos = n FROM
(SELECT id, row_number() OVER (ORDER BY p <-> spoint (0.2, 0.3)) n FROM points ORDER BY p <-> spoint (0.2, 0.3) LIMIT 100) sel
WHERE points.id = sel.id;
SET enable_indexscan = false;
SELECT pos, row_number() OVER (ORDER BY p <-> spoint (0.2, 0.3)) n FROM points ORDER BY p <-> spoint (0.2, 0.3) LIMIT 100;
pos | n
-----+-----
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 | 6
7 | 7
8 | 8
9 | 9
10 | 10
11 | 11
12 | 12
13 | 13
14 | 14
15 | 15
16 | 16
17 | 17
18 | 18
19 | 19
20 | 20
21 | 21
22 | 22
23 | 23
24 | 24
25 | 25
26 | 26
27 | 27
28 | 28
29 | 29
30 | 30
31 | 31
32 | 32
33 | 33
34 | 34
35 | 35
36 | 36
37 | 37
38 | 38
39 | 39
40 | 40
41 | 41
42 | 42
43 | 43
44 | 44
45 | 45
46 | 46
47 | 47
48 | 48
49 | 49
50 | 50
51 | 51
52 | 52
53 | 53
54 | 54
55 | 55
56 | 56
57 | 57
58 | 58
59 | 59
60 | 60
61 | 61
62 | 62
63 | 63
64 | 64
65 | 65
66 | 66
67 | 67
68 | 68
69 | 69
70 | 70
71 | 71
72 | 72
73 | 73
74 | 74
75 | 75
76 | 76
77 | 77
78 | 78
79 | 79
80 | 80
81 | 81
82 | 82
83 | 83
84 | 84
85 | 85
86 | 86
87 | 87
88 | 88
89 | 89
90 | 90
91 | 91
92 | 92
93 | 93
94 | 94
95 | 95
96 | 96
97 | 97
98 | 98
99 | 99
100 | 100
(100 rows)

DROP TABLE points;
2 changes: 1 addition & 1 deletionexpected/version.out
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -2,6 +2,6 @@
SELECT pg_sphere_version();
pg_sphere_version
-------------------
1.4.2
1.5.0
(1 row)

2 changes: 1 addition & 1 deletionpg_sphere.control
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
# pg_sphere extension
comment = 'spherical objects with useful functions, operators and index support'
default_version = '1.4.2'
default_version = '1.5.0'
module_pathname = '$libdir/pg_sphere'
relocatable = true
7 changes: 6 additions & 1 deletionpgs_gist.sql.in
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -98,12 +98,15 @@ CREATE FUNCTION g_spoint_compress(internal)
AS 'MODULE_PATHNAME', 'g_spoint_compress'
LANGUAGE 'c';


CREATE FUNCTION g_spoint_consistent(internal, internal, int4, oid, internal)
RETURNS internal
AS 'MODULE_PATHNAME', 'g_spoint_consistent'
LANGUAGE 'c';

CREATE FUNCTION g_spoint_distance(internal, spoint, smallint, oid, internal)
Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others.Learn more.

Create an upgrade script and add this function to it.

RETURNS internal
AS 'MODULE_PATHNAME', 'g_spoint_distance'
LANGUAGE 'c';

CREATE OPERATOR CLASS spoint
DEFAULT FOR TYPE spoint USING gist AS
Expand All@@ -114,6 +117,7 @@ CREATE OPERATOR CLASS spoint
OPERATOR 14 @ (spoint, spoly),
OPERATOR 15 @ (spoint, sellipse),
OPERATOR 16 @ (spoint, sbox),
OPERATOR 17 <-> (spoint, spoint) FOR ORDER BY float_ops,
OPERATOR 37 <@ (spoint, scircle),
OPERATOR 38 <@ (spoint, sline),
OPERATOR 39 <@ (spoint, spath),
Expand All@@ -127,6 +131,7 @@ CREATE OPERATOR CLASS spoint
FUNCTION 5 g_spherekey_penalty (internal, internal, internal),
FUNCTION 6 g_spherekey_picksplit (internal, internal),
FUNCTION 7 g_spherekey_same (spherekey, spherekey, internal),
FUNCTION 8 g_spoint_distance (internal, spoint, smallint, oid, internal),
STORAGE spherekey;


Expand Down
12 changes: 12 additions & 0 deletionssql/knn.sql
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
CREATE TABLE points (id int, p spoint, pos int);
INSERT INTO points (id, p) SELECT x, spoint(random()*6.28, (2*random()-1)*1.57) FROM generate_series(1,314159) x;
CREATE INDEX i ON points USING gist (p);
SET enable_indexscan = true;
EXPLAIN (costs off) SELECT p <-> spoint (0.2, 0.3) FROM points ORDER BY 1 LIMIT 100;
UPDATE points SET pos = n FROM
(SELECT id, row_number() OVER (ORDER BY p <-> spoint (0.2, 0.3)) n FROM points ORDER BY p <-> spoint (0.2, 0.3) LIMIT 100) sel
WHERE points.id = sel.id;
SET enable_indexscan = false;
SELECT pos, row_number() OVER (ORDER BY p <-> spoint (0.2, 0.3)) n FROM points ORDER BY p <-> spoint (0.2, 0.3) LIMIT 100;
DROP TABLE points;

Loading
[8]ページ先頭
©2009-2025 Movatter.jp