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?


1 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)

UPDATE table 

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

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

It will show you all the address and cities values.

  • I love hstore as a data type (dictionaries are your best friend), but it does not play nice with ESRI, so normal old column data types will have to do.
    – Nathanus
    May 26, 2011 at 12:44
  • This seems like exactly what I'm looking for, but what would I replace hstorecolumn with? Also, in my DB, the key-value pairs are joined with =>, not ->. Does this matter?
    – Nathanus
    May 26, 2011 at 14:13
  • hi, hstorecolumn is the name of the column with hstore data. -> is the notation to call a value from a key in hstore, this way: column->'key' and it returns the value, oh, you need the single quotation. I'm editing the answer with another example.
    – Pablo
    May 26, 2011 at 14:28
  • That makes much much more sense. Thank you kindly for the clarification.
    – Nathanus
    May 26, 2011 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.