[postgis-users] Using hstore to compose polygons from OSM data in Postgis (problem with key value with whitespace)

[Ralf Suhr]

Hi Guido,

it is only a syntax problem. Add a explicit cast to get it work
'"admin level" => 6'::hstore


Gr
Ralf

Am Dienstag 08 Juli 2014, 12:07:07 schrieb Guido Lemoine:
> Dear All,
> 
> 
> 
> Maybe slightly off-topic, but nonetheless of interest to some (hopefully).
> 
> 
> 
> I am trying to compose administrative boundaries as polygons from an OSMOSIS
> (openstreetmap) import into Postgis. The actual composition works fine, but
> I would like to include the administrative level as an attribute.
> Unfortunately, this is stored as an hstore tag with a key “admin level”
> (i.e. it has a white space). I can’t seem to find a way to use tags->’admin
> level’ to select the value for this tag, or use it in the selection (see
> below). It appears that hstore can store a key, value pair that contains
> whitespace (if doubled quoted at input), but not use a white spaced key in
> operators. I have tried any possible combination of (double) quotes,
> including $$ delineation.
> 
> 
> 
> Any suggestion welcome.
> 
> 
> 
> # This works OK (yields all administrative boundaries)
> 
> select rid, st_makepolygon(st_addpoint(foo.mline, st_startpoint(foo.mline)))
> from (
> 
> select relations.id as rid, st_linemerge(st_union(linestring)) as mline from
> ways, relation_members rm, relations
> 
> where ways.id = rm.member_id
> 
> and rm.relation_id = relations.id
> 
> and rm.member_type = 'W'
> 
> and relations.tags @> 'boundary => administrative'
> 
> group by relations.id
> 
> order by relations.id
> 
> ) as foo
> 
> 
> 
> # This does not work (due to the use of ‘admin level’ in the AND)
> 
> select rid, st_makepolygon(st_addpoint(foo.mline, st_startpoint(foo.mline)))
> from (
> 
> select relations.id as rid, st_linemerge(st_union(linestring)) as mline from
> ways, relation_members rm, relations
> 
> where ways.id = rm.member_id
> 
> and rm.relation_id = relations.id
> 
> and rm.member_type = 'W'
> 
> and relations.tags @> 'boundary => administrative'
> 
> and relations.tags @> 'admin level => 6'
> 
> group by relations.id
> 
> order by relations.id
> 
> ) as foo
> 
> 
> 
> # This works, but admlevel remains empty, because tags->’admin level’ does
> not give any result.
> 
> (select rid, admlevel, st_makepolygon(st_addpoint(foo.mline,
> st_startpoint(foo.mline))) from (
> 
> select relations.id as rid, relations.tags->'admin level' as admlevel,
> st_linemerge(st_union(linestring)) as mline from ways, relation_members rm,
> relations
> 
> where ways.id = rm.member_id
> 
> and rm.relation_id = relations.id
> 
> and rm.member_type = 'W'
> 
> and relations.tags @> 'boundary => administrative'
> 
> group by relations.id
> 
> order by relations.id
> 
> ) as foo
> 
> )
> 
> 
> 
> [Tests were run with the Geofabrik OSM.BZ2 for Ile de France]
> 
> 
> 
> Guido Lemoine