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

Commit8394ae7

Browse files
authored
Fix output precision limit for double values (issue#118) (#119)
Fix output precision limit for double values (issue#118)pgSphere used its own setting (set_sphere_output_precision function)to limit the output precision of double values, that could not begreater than 15 significant digits (DBL_DIG). It introduced someproblems with dump/restore. PostgreSQL uses its own precision setting:extra_float_digits. The PostgreSQL setting allows to use more significantdigits.This patch changes the default pgSphere output behaviour to utilizePostgreSQL extra_float_digits. Now, extra_float_digits is used by default,until set_sphere_output_precision is called.The old behaviour is kept for compatibility purposes. Once,set_sphere_output_precision is called, pgSphere starts to use the oldbehaviour (read, please, issue#118 discussion).The patch introduces a new function - reset_sphere_output_precision.It is used to reset to the PostgreSQL behaviour after usingset_sphere_output_precision.* Update upgrade script (reset_sphere_output_precision function)* Add test for pgSphere output precision with different settingsexpected/output_precision.out - PG 10-11expected/output_precision_1.out - PG 12+* Add extra_float_digits = 2 for epochprop and bounding_box_gist tests
1 parent462fa03 commit8394ae7

13 files changed

+1283
-55
lines changed

‎Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -40,7 +40,8 @@ DATA_built = $(RELEASE_SQL) \
4040
DOCS = README.pg_sphere COPYRIGHT.pg_sphere
4141
TESTS = version tables points euler circle line ellipse poly path box\
4242
index contains_ops contains_ops_compat bounding_box_gist gnomo\
43-
epochprop contains overlaps spoint_brin sbox_brin selectivity knn
43+
epochprop contains overlaps spoint_brin sbox_brin selectivity\
44+
knn output_precision
4445
REGRESS = init$(TESTS)
4546

4647
PG_CFLAGS+= -DPGSPHERE_VERSION=$(PGSPHERE_VERSION)

‎expected/bounding_box_gist.out

Lines changed: 19 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,4 @@
1+
SET extra_float_digits = 2;
12
SET enable_seqscan=true;
23
CREATE TABLE bbox_ellipse (e sellipse not null);
34
INSERT INTO bbox_ellipse VALUES ('<{10d, 0.1d}, (0d,0d), 0d>');
@@ -20,19 +21,19 @@ SELECT COUNT(*) FROM bbox_ellipse WHERE spoint '(5d, 0d)' <@ e;
2021
(1 row)
2122

2223
EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM bbox_ellipse WHERE spoint '(5d, 0d)' @ e;
23-
QUERY PLAN
24-
----------------------------------------------------------
24+
QUERY PLAN
25+
------------------------------------------------------------
2526
Aggregate
2627
-> Seq Scan on bbox_ellipse
27-
Filter: ('(0.0872664625997165 , 0)'::spoint @ e)
28+
Filter: ('(0.087266462599716474 , 0)'::spoint @ e)
2829
(3 rows)
2930

3031
EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM bbox_ellipse WHERE spoint '(5d, 0d)' <@ e;
31-
QUERY PLAN
32-
-----------------------------------------------------------
32+
QUERY PLAN
33+
-------------------------------------------------------------
3334
Aggregate
3435
-> Seq Scan on bbox_ellipse
35-
Filter: ('(0.0872664625997165 , 0)'::spoint <@ e)
36+
Filter: ('(0.087266462599716474 , 0)'::spoint <@ e)
3637
(3 rows)
3738

3839
-- The ellipse has semi-major axis length of 10 degrees along the equator,
@@ -53,19 +54,19 @@ SELECT COUNT(*) FROM bbox_ellipse WHERE spoint '(5d, 0d)' <@ e;
5354
(1 row)
5455

5556
EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM bbox_ellipse WHERE spoint '(5d, 0d)' @ e;
56-
QUERY PLAN
57-
--------------------------------------------------------------
57+
QUERY PLAN
58+
----------------------------------------------------------------
5859
Aggregate
5960
-> Index Scan using idx_bbox_ellipse on bbox_ellipse
60-
Index Cond: ('(0.0872664625997165 , 0)'::spoint @ e)
61+
Index Cond: ('(0.087266462599716474 , 0)'::spoint @ e)
6162
(3 rows)
6263

6364
EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM bbox_ellipse WHERE spoint '(5d, 0d)' <@ e;
64-
QUERY PLAN
65-
---------------------------------------------------------------
65+
QUERY PLAN
66+
-----------------------------------------------------------------
6667
Aggregate
6768
-> Index Scan using idx_bbox_ellipse on bbox_ellipse
68-
Index Cond: ('(0.0872664625997165 , 0)'::spoint <@ e)
69+
Index Cond: ('(0.087266462599716474 , 0)'::spoint <@ e)
6970
(3 rows)
7071

7172
SET enable_seqscan=true;
@@ -170,11 +171,11 @@ SELECT COUNT(*) FROM bbox_path WHERE spoint '(0d, 0d)' <@ p;
170171
(1 row)
171172

172173
EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM bbox_path WHERE sline(spoint '(0d, -10d)', spoint '(0d, 10d)') && p;
173-
QUERY PLAN
174-
--------------------------------------------------------------------------------------------------
174+
QUERY PLAN
175+
---------------------------------------------------------------------------------------------------------
175176
Aggregate
176177
-> Seq Scan on bbox_path
177-
Filter: ('( 6.10865238198015, 1.5707963267949, 0, ZXZ ), 0.349065850398866'::sline && p)
178+
Filter: ('( 6.1086523819801535, 1.5707963267948966, 0, ZXZ ), 0.34906585039886584'::sline && p)
178179
(3 rows)
179180

180181
EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM bbox_path WHERE spoint '(0d, 0d)' @ p;
@@ -215,11 +216,11 @@ SELECT COUNT(*) FROM bbox_path WHERE spoint '(0d, 0d)' <@ p;
215216
(1 row)
216217

217218
EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM bbox_path WHERE sline(spoint '(0d, -10d)', spoint '(0d, 10d)') && p;
218-
QUERY PLAN
219-
------------------------------------------------------------------------------------------------------
219+
QUERY PLAN
220+
-------------------------------------------------------------------------------------------------------------
220221
Aggregate
221222
-> Index Scan using idx_bbox_path on bbox_path
222-
Index Cond: ('( 6.10865238198015, 1.5707963267949, 0, ZXZ ), 0.349065850398866'::sline && p)
223+
Index Cond: ('( 6.1086523819801535, 1.5707963267948966, 0, ZXZ ), 0.34906585039886584'::sline && p)
223224
(3 rows)
224225

225226
EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM bbox_path WHERE spoint '(0d, 0d)' @ p;

‎expected/bounding_box_gist_1.out

Lines changed: 19 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,4 @@
1+
SET extra_float_digits = 2;
12
SET enable_seqscan=true;
23
CREATE TABLE bbox_ellipse (e sellipse not null);
34
INSERT INTO bbox_ellipse VALUES ('<{10d, 0.1d}, (0d,0d), 0d>');
@@ -20,19 +21,19 @@ SELECT COUNT(*) FROM bbox_ellipse WHERE spoint '(5d, 0d)' <@ e;
2021
(1 row)
2122

2223
EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM bbox_ellipse WHERE spoint '(5d, 0d)' @ e;
23-
QUERY PLAN
24-
----------------------------------------------------------
24+
QUERY PLAN
25+
-----------------------------------------------------------
2526
Aggregate
2627
-> Seq Scan on bbox_ellipse
27-
Filter: ('(0.0872664625997165 , 0)'::spoint @ e)
28+
Filter: ('(0.08726646259971647 , 0)'::spoint @ e)
2829
(3 rows)
2930

3031
EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM bbox_ellipse WHERE spoint '(5d, 0d)' <@ e;
31-
QUERY PLAN
32-
-----------------------------------------------------------
32+
QUERY PLAN
33+
------------------------------------------------------------
3334
Aggregate
3435
-> Seq Scan on bbox_ellipse
35-
Filter: ('(0.0872664625997165 , 0)'::spoint <@ e)
36+
Filter: ('(0.08726646259971647 , 0)'::spoint <@ e)
3637
(3 rows)
3738

3839
-- The ellipse has semi-major axis length of 10 degrees along the equator,
@@ -53,19 +54,19 @@ SELECT COUNT(*) FROM bbox_ellipse WHERE spoint '(5d, 0d)' <@ e;
5354
(1 row)
5455

5556
EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM bbox_ellipse WHERE spoint '(5d, 0d)' @ e;
56-
QUERY PLAN
57-
--------------------------------------------------------------
57+
QUERY PLAN
58+
---------------------------------------------------------------
5859
Aggregate
5960
-> Index Scan using idx_bbox_ellipse on bbox_ellipse
60-
Index Cond: (e ~ '(0.0872664625997165 , 0)'::spoint)
61+
Index Cond: (e ~ '(0.08726646259971647 , 0)'::spoint)
6162
(3 rows)
6263

6364
EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM bbox_ellipse WHERE spoint '(5d, 0d)' <@ e;
64-
QUERY PLAN
65-
---------------------------------------------------------------
65+
QUERY PLAN
66+
----------------------------------------------------------------
6667
Aggregate
6768
-> Index Scan using idx_bbox_ellipse on bbox_ellipse
68-
Index Cond: (e @> '(0.0872664625997165 , 0)'::spoint)
69+
Index Cond: (e @> '(0.08726646259971647 , 0)'::spoint)
6970
(3 rows)
7071

7172
SET enable_seqscan=true;
@@ -170,11 +171,11 @@ SELECT COUNT(*) FROM bbox_path WHERE spoint '(0d, 0d)' <@ p;
170171
(1 row)
171172

172173
EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM bbox_path WHERE sline(spoint '(0d, -10d)', spoint '(0d, 10d)') && p;
173-
QUERY PLAN
174-
--------------------------------------------------------------------------------------------------
174+
QUERY PLAN
175+
---------------------------------------------------------------------------------------------------------
175176
Aggregate
176177
-> Seq Scan on bbox_path
177-
Filter: ('( 6.10865238198015, 1.5707963267949, 0, ZXZ ), 0.349065850398866'::sline && p)
178+
Filter: ('( 6.1086523819801535, 1.5707963267948966, 0, ZXZ ), 0.34906585039886584'::sline && p)
178179
(3 rows)
179180

180181
EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM bbox_path WHERE spoint '(0d, 0d)' @ p;
@@ -215,11 +216,11 @@ SELECT COUNT(*) FROM bbox_path WHERE spoint '(0d, 0d)' <@ p;
215216
(1 row)
216217

217218
EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM bbox_path WHERE sline(spoint '(0d, -10d)', spoint '(0d, 10d)') && p;
218-
QUERY PLAN
219-
------------------------------------------------------------------------------------------------------
219+
QUERY PLAN
220+
-------------------------------------------------------------------------------------------------------------
220221
Aggregate
221222
-> Index Scan using idx_bbox_path on bbox_path
222-
Index Cond: (p && '( 6.10865238198015, 1.5707963267949, 0, ZXZ ), 0.349065850398866'::sline)
223+
Index Cond: (p && '( 6.1086523819801535, 1.5707963267948966, 0, ZXZ ), 0.34906585039886584'::sline)
223224
(3 rows)
224225

225226
EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM bbox_path WHERE spoint '(0d, 0d)' @ p;

‎expected/epochprop.out

Lines changed: 7 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,4 @@
1+
SET extra_float_digits = 2;
12
SELECT
23
to_char(DEGREES(tp[1]), '999D9999999999'),
34
to_char(DEGREES(tp[2]), '999D9999999999'),
@@ -92,16 +93,16 @@ SELECT epoch_prop_pos(spoint(radians(269.45207695), radians(4.693364966)),
9293
23,
9394
RADIANS(-801.551/3.6e6), RADIANS(10362/3.6e6), -110,
9495
20) AS tp;
95-
tp
96-
-----------------------------------------
97-
(4.70274792658313 , 0.0829194509345993)
96+
tp
97+
---------------------------------------------
98+
(4.7027479265831289 , 0.082919450934599334)
9899
(1 row)
99100

100101
SELECT epoch_prop_pos(spoint(radians(269.45207695), radians(4.693364966)),
101102
RADIANS(-801.551/3.6e6), RADIANS(10362/3.6e6),
102103
20) AS tp;
103-
tp
104-
-----------------------------------------
105-
(4.70274793061952 , 0.0829193989380876)
104+
tp
105+
---------------------------------------------
106+
(4.7027479306195161 , 0.082919398938087627)
106107
(1 row)
107108

‎expected/epochprop_1.out

Lines changed: 108 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,108 @@
1+
SET extra_float_digits = 2;
2+
SELECT
3+
to_char(DEGREES(tp[1]), '999D9999999999'),
4+
to_char(DEGREES(tp[2]), '999D9999999999'),
5+
to_char(tp[3], '999D999'),
6+
to_char(DEGREES(tp[4])*3.6e6, '999D999'),
7+
to_char(DEGREES(tp[5])*3.6e6, '99999D999'),
8+
to_char(tp[6], '999D999')
9+
FROM (
10+
SELECT epoch_prop(spoint(radians(269.45207695), radians(4.693364966)),
11+
546.9759,
12+
RADIANS(-801.551/3.6e6), RADIANS(10362/3.6e6), -110,
13+
-100) AS tp) AS q;
14+
to_char | to_char | to_char | to_char | to_char | to_char
15+
-----------------+-----------------+----------+----------+------------+----------
16+
269.4742714391 | 4.4072939987 | 543.624 | -791.442 | 10235.412 | -110.450
17+
(1 row)
18+
19+
SELECT
20+
to_char(DEGREES(tp[1]), '999D9999999999'),
21+
to_char(DEGREES(tp[2]), '999D9999999999'),
22+
to_char(tp[3], '999D999'),
23+
to_char(DEGREES(tp[4])*3.6e6, '999D999'),
24+
to_char(DEGREES(tp[5])*3.6e6, '99999D999'),
25+
to_char(tp[6], '999D999')
26+
FROM (
27+
SELECT epoch_prop(spoint(radians(269.45207695), radians(4.693364966)),
28+
0,
29+
RADIANS(-801.551/3.6e6), RADIANS(10362/3.6e6), -110,
30+
-100) AS tp) AS q;
31+
to_char | to_char | to_char | to_char | to_char | to_char
32+
-----------------+-----------------+---------+----------+------------+---------
33+
269.4744079540 | 4.4055337210 | | -801.210 | 10361.762 |
34+
(1 row)
35+
36+
SELECT
37+
to_char(DEGREES(tp[1]), '999D9999999999'),
38+
to_char(DEGREES(tp[2]), '999D9999999999'),
39+
to_char(tp[3], '999D999'),
40+
to_char(DEGREES(tp[4])*3.6e6, '999D999'),
41+
to_char(DEGREES(tp[5])*3.6e6, '99999D999'),
42+
to_char(tp[6], '999D999')
43+
FROM (
44+
SELECT epoch_prop(spoint(radians(269.45207695), radians(4.693364966)),
45+
NULL,
46+
RADIANS(-801.551/3.6e6), RADIANS(10362/3.6e6), -110,
47+
-100) AS tp) AS q;
48+
to_char | to_char | to_char | to_char | to_char | to_char
49+
-----------------+-----------------+---------+----------+------------+---------
50+
269.4744079540 | 4.4055337210 | | -801.210 | 10361.762 |
51+
(1 row)
52+
53+
SELECT
54+
to_char(DEGREES(tp[1]), '999D9999999999'),
55+
to_char(DEGREES(tp[2]), '999D9999999999'),
56+
to_char(tp[3], '999D999'),
57+
to_char(DEGREES(tp[4])*3.6e6, '999D999'),
58+
to_char(DEGREES(tp[5])*3.6e6, '99999D999'),
59+
to_char(tp[6], '999D999')
60+
FROM (
61+
SELECT epoch_prop(spoint(radians(269.45207695), radians(4.693364966)),
62+
23,
63+
RADIANS(-801.551/3.6e6), RADIANS(10362/3.6e6), NULL,
64+
20) AS tp) AS q;
65+
to_char | to_char | to_char | to_char | to_char | to_char
66+
-----------------+-----------------+----------+----------+------------+----------
67+
269.4476085384 | 4.7509315989 | 23.000 | -801.617 | 10361.984 | 2.159
68+
(1 row)
69+
70+
SELECT
71+
to_char(DEGREES(tp[1]), '999D9999999999'),
72+
to_char(DEGREES(tp[2]), '999D9999999999'),
73+
to_char(tp[3], '999D999'),
74+
to_char(DEGREES(tp[4])*3.6e6, '999D999'),
75+
to_char(DEGREES(tp[5])*3.6e6, '99999D999'),
76+
to_char(tp[6], '999D999')
77+
FROM (
78+
SELECT epoch_prop(spoint(radians(269.45207695), radians(4.693364966)),
79+
23,
80+
NULL, RADIANS(10362/3.6e6), -110,
81+
120) AS tp) AS q;
82+
to_char | to_char | to_char | to_char | to_char | to_char
83+
-----------------+-----------------+----------+----------+------------+----------
84+
269.4520769500 | 5.0388680565 | 23.007 | -.000 | 10368.061 | -97.120
85+
(1 row)
86+
87+
SELECT epoch_prop(NULL,
88+
23,
89+
0.01 , RADIANS(10362/3.6e6), -110,
90+
120);
91+
ERROR: NULL position not supported in epoch propagation
92+
SELECT epoch_prop_pos(spoint(radians(269.45207695), radians(4.693364966)),
93+
23,
94+
RADIANS(-801.551/3.6e6), RADIANS(10362/3.6e6), -110,
95+
20) AS tp;
96+
tp
97+
-------------------------------------------
98+
(4.702747926583129 , 0.08291945093459933)
99+
(1 row)
100+
101+
SELECT epoch_prop_pos(spoint(radians(269.45207695), radians(4.693364966)),
102+
RADIANS(-801.551/3.6e6), RADIANS(10362/3.6e6),
103+
20) AS tp;
104+
tp
105+
-------------------------------------------
106+
(4.702747930619516 , 0.08291939893808763)
107+
(1 row)
108+

0 commit comments

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