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

[Guido Lemoine]

Thanks, Ralf. However, it was not due to the cast but to my blindness... The tag is actually coded as admin_level (i.e. with an underscore).
This is very hard to decipher when using PGADMIN, as the underscore is hidden by the grid lines of the record grid. 
I only figured this out by checking the ASCII code for the 6th character of the tag. 

In fact, the following works fine, suggesting that there is nothing wrong with hstore.

select '"a b"=>"x"'::hstore -> 'a b'

Apologies to all. I hope the queries may be of use to someone. 

GL

-----Original Message-----
From: Ralf Suhr [mailto:Ralf.Suhr at itc-halle.de] 
Sent: 08 July 2014 13:17
To: postgis-users at lists.osgeo.org
Cc: Guido Lemoine
Subject: Re: [postgis-users] Using hstore to compose polygons from OSM data in Postgis (problem with key value with whitespace)

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