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

[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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140708/7f373868/attachment.html>