Return to Question

Make sense, see data, a "S curve" for geohash_len X perc,

geohash_len|  avg_diffs   | n_diffs_not0 |  perc | avg_diffs_not0 
-----------|--------------|--------------|-------|--------
10         | 0.0000020377 |      1786453 | 97.4% |   0.0000020912
11         | 0.0000003716 |      1111317 | 60.6% |   0.0000006130
12         | 0.0000000756 |       267843 | 14.6% |   0.0000005173
13         | 0.0000000489 |       174916 | 9.5%  |   0.0000005127
14         | 0.0000000493 |       176324 | 9.6%  |   0.0000005128

... seems internal (floating point?) problems after Geohash 13 digits.

Make sense, see data, a "S curve" for geohash_len X perc, with transition in len=12

gh_len|  avg_diffs   | difs_not0 |  perc | avg_diffs_not0 
------|--------------|-----------|-------|---------------
9     | 0.0000107338 | 1733044   | 94.53%| 0.0000113549
10    | 0.0000020370 | 679864    | 37.08%| 0.0000054930
11    | 0.0000003749 | 134963    | 7.36% | 0.0000050926
12    | 0.0000000836 | 30480     | 1.66% | 0.0000050300
13    | 0.0000000563 | 20502     | 1.12% | 0.0000050332
14    | 0.0000000570 | 20792     | 1.13% | 0.0000050293

The transition (S curve) is in len=10 or len=11, as expected. The problems arises after Geohash 13 digits, seems internal (floating point?).

Decode/encode Geohashes are easy, and there are a "rule of the thumb" for estimat mininal Geohashe length for each LatLong precision.

Decode/encode Geohashes are easy, and there are a "rule of the thumb" for estimat mininal Geohashe length for each LatLong precision. The question is other: precision for revesibility,

1. How many digits of Geohash for a symetric GeoURI with P decimal digits of precision? There are a (statistically) reliable formula for this kind of estimation?

2. There are other tool for PostGIS to calculate with better precision the decode/encode of Gehashes?

CREATE or replace VIEW vw_point_geohash_diffs AS
SELECT *, abs(orig_x-x) diff_x, abs(orig_y-y) diff_y
FROM (
SELECT orig_x, round(st_x(pt),6) x, orig_y, round(st_y(pt),6) y 
  FROM (
    SELECT osm_id, st_centroid(ST_GeomFromGeoHash( st_geohash(way,14) )) pt, 
           round(st_x(way),6) orig_x, round(st_y(way),6) orig_y
    FROM planet_osm_point
 ) t1
) t2;
 
-- Precision checks:
SELECT n, sum_x, sum_y, round(avg_diffs/2.0,10) as avg_diffs, n_diffs_not0, 
       round(100.0*n_diffs_not0/n,2)||'%' as perc,
       round(avg_diffs_not0/2.0,10) as avg_diffs_not0
FROM ( 
  SELECT count(*) n, sum(diff_x) sum_x, sum(diff_y)  sum_y, 
       avg(diff_x+diff_y)  avg_diffs,
       count(*) FILTER (WHERE diff_x>0 OR diff_y>0) n_diffs_not0,
       avg(diff_x+diff_y) FILTER (WHERE diff_x>0 OR diff_y>0) avg_diffs_not0
  FROM vw_point_geohash_diffs
) t;

geohash_len|  avg_diffs   | n_diffs_not0 |   perc  | avg_diffs_not0 
-----------|--------------|--------------|---------|--------
10         | 0.0000020377 |      1786453 | 97.4%   |   0.0000020912
11         | 0.0000003716 |      1111317 | 60.6%   |   0.0000006130
12         | 0.0000000756 |       267843 | 14.6%   |   0.0000005173
13         | 0.0000000489 |       174916 | 9.5%    |   0.0000005127
14         | 0.0000000493 |       176324 | 9.6%    |   0.0000005128

CREATE or replace VIEW vw_point_geohash_diffs AS
SELECT *, abs(orig_x-x) diff_x, abs(orig_y-y) diff_y
FROM (
SELECT orig_x, round(st_x(pt),6) x, orig_y, round(st_y(pt),6) y
    -- the 6 is "6 decimal digits at LatLong coordinates" 
  FROM (
    SELECT osm_id, st_centroid(ST_GeomFromGeoHash( st_geohash(way,14)
                   -- here ...9, 10, 11, 12, 13, 14...  
                 )) pt, 
           round(st_x(way),6) orig_x, round(st_y(way),6) orig_y
    FROM planet_osm_point
 ) t1
) t2;
-- Precision checks:
SELECT n, sum_x, sum_y, round(avg_diffs/2.0,10) as avg_diffs, n_diffs_not0, 
       round(100.0*n_diffs_not0/n,2)||'%' as perc,
       round(avg_diffs_not0/2.0,10) as avg_diffs_not0
FROM ( 
  SELECT count(*) n, sum(diff_x) sum_x, sum(diff_y)  sum_y, 
       avg(diff_x+diff_y)  avg_diffs,
       count(*) FILTER (WHERE diff_x>0 OR diff_y>0) n_diffs_not0,
       avg(diff_x+diff_y) FILTER (WHERE diff_x>0 OR diff_y>0) avg_diffs_not0
  FROM vw_point_geohash_diffs
) t;

geohash_len|  avg_diffs   | n_diffs_not0 |   perc  | avg_diffs_not0 
-----------|--------------|--------------|---------|--------
10         | 0.0000020377 |      1786453 | 97.4%   |   0.0000020912
11         | 0.0000003716 |      1111317 | 60.6%   |   0.0000006130
12         | 0.0000000756 |       267843 | 14.6%   |   0.0000005173
13         | 0.0000000489 |       174916 | 9.5%    |   0.0000005127
14         | 0.0000000493 |       176324 | 9.6%    |   0.0000005128

Running all with n=1833326 points (OSM Brazil).

The main problem for "reversible precision" seems the float arithmetic error at Geohash-14 or more. Same problem when we change round from 6 decimal places of LatLong to 5 places,

geohash_len|  avg_diffs   | n_diffs_not0 |  perc | avg_diffs_not0 
-----------|--------------|--------------|-------|--------
10         | 0.0000020377 |      1786453 | 97.4% |   0.0000020912
11         | 0.0000003716 |      1111317 | 60.6% |   0.0000006130
12         | 0.0000000756 |       267843 | 14.6% |   0.0000005173
13         | 0.0000000489 |       174916 | 9.5%  |   0.0000005127
14         | 0.0000000493 |       176324 | 9.6%  |   0.0000005128

... seems internal (floating point?) problems after Geohash 13 digits.