Creating columns in PostGIS using osm2pgsql hstore tags

Question

While I feel this may come down to a database question, it feels GIS-related enough that I feel comfortable posting it here. I'll be happy to take it elsewhere if it belongs there, though!

I've started the arduous journey of mangling OSM data to work in an RDBMS format that is useful to me. One of the major problems I am running into is the hstore data type. While it is quite useful for being able to hold an extremely large dictionary of values, they are hard to "get at", in my experience. Specifically, the osm2pgsql tool creates addr:flats, addr:housenumber, and addr:interpolation fields, but leaves out some that are quite obvious to me, such as city, postcode, and street. I would like to parse this data to populate new columns with it. What methods would you recommend for accessing such data?

Thanks

Answer 1

You can transfer the desired hstore key/value data to new columns:
1-Create the desired columns (ex. addres, city, key1, key2, keyn)
2-Run:

UPDATE table 
SET 
address=hstorecolumn->'address',
city=hstorecolumn->'city',
key1=hstorecolumn->'key1',
key2=hstorecolumn->'key2',
keyn=hstorecolumn->'keyn';

Or you can simply get used to hstore, it works pretty well and I have so many good results with it that I can say IMO "hstore changed my life". lol

EDIT:
An example on how to use hstore:
SELECT
streetdatatable.hstoredatacolumn -> 'address',
streetdatatable.hstoredatacolumn -> 'city'
FROM someschema. streetdatatable

It will show you all the address and cities values.