[OSM-dev] osm2pgsql / postgis precision

[Jon Burgess]

On Wed, 2009-07-01 at 13:51 +0200, Etienne Chové wrote:
> Etienne Chové a écrit :
> > The difference is not really significant but postgis says polygons
> are 
> > olvelapping each other while they have just a common boundary.
> 
> The errors doesn't appear when I delete lines "#define FIXED_POINT"
> in 
> middle_ram.c and middle_pgsql.c
> 
> Is it danerous to delete them ?

I have come across this issue before, the 'slim' mode can fetch the node
positions from the DB or from a RAM cache. The DB stores the positions
in doubles (giving 53 bits of precision), the RAM cache uses fixed point
arithmetic giving around 32 bits of precision. 32 bits is perfectly
sufficient for OSM and halves the storage size.

Undefining the FIXED_POINT will make the RAM cache use doubles instead.
This will double the size of each RAM cache entry which will halve the
effective cache size. This may make the import & updates a bit slower
but should not cause any bad effects. 

I was tempted to actually move in the other direction and make the DB
use the same fixed point representation of the RAM cache. It is
generally a bad idea to rely on doubles maintaining the full precision
through lots of processing steps. This would also reduce the size of the
nodes table, currently the largest one in the Mapnik rendering DB[1]

	Jon


1: Here is a snapshot of the table and index sizes from the tile server:

gis=> SELECT relname, pg_size_pretty(relpages::bigint * 8 *1024) AS
size, CASE WHEN relkind = 't' THEN (SELECT pgd.relname FROM pg_class pgd
WHERE pgd.relfilenode::text = SUBSTRING(pg.relname FROM 10)) ELSE
(SELECT pgc.relname FROM pg_class pgc WHERE pg.reltoastrelid =
pgc.relfilenode) END AS refrelname, relfilenode, relkind,
reltuples::bigint, relpages FROM pg_class pg ORDER BY relpages DESC;


              relname              |    size    |       refrelname        | relfilenode | relkind | reltuples | relpages
-----------------------------------+------------+-------------------------+-------------+---------+-----------+----------
 planet_osm_nodes                  | 22 GB      | pg_toast_22035543       |    22035543 | r       | 375808800 |  2828690
 planet_osm_ways_nodes             | 18 GB      |                         |    22391133 | i       |  29690000 |  2363649
 planet_osm_nodes_pkey             | 8063 MB    |                         |    22035548 | i       | 375808800 |  1032045
 planet_osm_line                   | 7526 MB    | pg_toast_22391150       |    22391150 | r       |  25469100 |   963325
 planet_osm_ways                   | 6275 MB    | pg_toast_22035550       |    22035550 | r       |  29690000 |   803205
 pg_toast_22391150                 | 1617 MB    | planet_osm_line         |    22391152 | t       |    469010 |   206975
 planet_osm_line_index             | 1540 MB    |                         |    22694470 | i       |  25476070 |   197056
 planet_osm_polygon                | 1161 MB    | pg_toast_22391135       |    22391135 | r       |   3663485 |   148556
 planet_osm_ways_pkey              | 895 MB     |                         |    22035555 | i       |  29690000 |   114574
 planet_osm_roads                  | 884 MB     | pg_toast_22391137       |    22391137 | r       |   2323677 |   113189
 pg_toast_22391135                 | 694 MB     | planet_osm_polygon      |    22391141 | t       |    248401 |    88782
 planet_osm_point                  | 692 MB     | pg_toast_22391139       |    22391139 | r       |   7280096 |    88612
 pg_toast_22391137                 | 617 MB     | planet_osm_roads        |    22391143 | t       |    229944 |    78958
 planet_osm_line_pkey              | 559 MB     |                         |    22694471 | i       |  25469100 |    71529
 planet_osm_point_index            | 426 MB     |                         |    22391155 | i       |   7280096 |    54537
 planet_osm_polygon_index          | 216 MB     |                         |    22567491 | i       |   3663905 |    27594
 planet_osm_rels_parts             | 205 MB     |                         |    22391134 | i       |    136670 |    26198
 planet_osm_point_pkey             | 156 MB     |                         |    22585957 | i       |   7280096 |    19926
 planet_osm_roads_index            | 139 MB     |                         |    22570778 | i       |   2325167 |    17821
 pg_toast_22035550                 | 127 MB     | planet_osm_ways         |    22035552 | t       |     79459 |    16301
 planet_osm_rels                   | 105 MB     | pg_toast_22035558       |    22035558 | r       |    136670 |    13501
 planet_osm_ways_idx               | 94 MB      |                         |    22035557 | i       |         0 |    11999
 planet_osm_polygon_pkey           | 80 MB      |                         |    22597180 | i       |   3663485 |    10289
 planet_osm_roads_pkey             | 51 MB      |                         |    22590797 | i       |   2323677 |     6574
 pg_toast_22391150_index           | 24 MB      |                         |    22391154 | i       |    469010 |     3093
 pg_toast_22035558                 | 17 MB      | planet_osm_rels         |    22035560 | t       |      1741 |     2123
...