Frederik Ramm <frederik <at> remote.org> writes: > > Hi, > > On 02/16/2012 07:25 PM, Graham Jones wrote: > > This reminded me of a question I have been meaning to ask for quite a > > while - is a database generated by osm2pgsql with an hstore expected to > > perform similarly to one without? > > I never ran one with hstore when I think of what this must mean for the > database engine, and storage space, then I shudder and would not be > surprised by the factor 5 you mentioned.
How about doing it with relations? Let the importer program create four tables osm_point osm_line osm_polygon osm_relation These tables would each have two attributes: Geometry and osm_is. Geometries in the osm_relation would be of type "geometry collection", that is, a collection of whatever, and it could hold for example the tranport route relation with the route and bus stops and everything in one PostGIS geometry object. Then there should be one or four tables for tags (everything in one table or split to suit the geometry tables). The three attributes would be osm_id, key, and value. Osm_id would be used as a foreing key for joining geometries and attributes. If osm_id can not be guaranteed to be unique then there should be point_id, line_id, polygon_id and relation_id added to suitable places. Every attribute would be indexed. For Mapnik use where all that is needed is to do simple SQL selects. I guess that this database would be faster to query than hstore even it also contains all the tags. It might be faster for Mapnik than the current tables with extra wide attribute schema because now attributes are not indexed at all (or are they?). SQL queries according to tags and values or even with a part of the tag and value strings would be easier to do than from hstore. People could enhance attributes for some special uses by converting max_speeds and other values which are actually measures from strings to integers or doubles etc. This option is so obvious that I believe that someone must have tried it already. It would be nice to hear about experiences. -Jukka Rahkonen- _______________________________________________ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk
