Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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.

share|improve this answer
    
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 '11 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 '11 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 '11 at 14:28
    
That makes much much more sense. Thank you kindly for the clarification. –  Nathanus May 26 '11 at 14:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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