PostgreSQL has a module named hstore that implement the use of key/value pairs (it's something like the OpenStreetMap model, but in a single table).

What are the Pros and Cons of hstore regarding GIS compatibilities, data adding and retrieving?

  • 1
    After some testing: Cons: Qgis doesn't display hstore columns. One Solution, store key/value in varchar as comma delimited list: 'name=foo,type=bar,' And retrieve values with: SELECT * WHERE tags LIKE '%type=bar,%' – Pablo Feb 22 '11 at 16:56

We actually demonstrate this in our book (which supposedly will be going to print in the next week or so). In chapter 3. You can download the code from here: http://www.postgis.us/chapter_03

(and of course if you buy our book you get our thorough explanations of the pros and cons)

It uses the osm2pgsql hstore switch to import osm tags as hstore column.

What we do to compensate for the fact that Quantum and other things don't support hstore directly is to create a view around the key tags we need. For applications where you have complete control using a query that aliases the key->value as a column works fine too even if you database driver doesn't understand hstore type.

  • I have this book. I bought the MEAP edition. I'll take a look at it but I think it's outdated and I will have to wait for the final version release. By the way, it's a nice book. Thanks. – Pablo Mar 19 '11 at 13:02


It's a high performance and flexible key/value that can be indexed and queried.

I have a complex schemaless web GIS that uses an HStore column for storing the features properties.


Most WMS/WFS tools like Mapserver, Geoserver and similars can't understand and work with HStore columns, so you end up creating hard coded SQL Views (In Geoserver at least). You can still use it for working WMS/WFS but since you have to hardcode the keys you lose the schemaless capabilities.


I have no experience with it myself, but if you are interested in storing spatial data in such a database, you might want to search for spatial nosql. On post I found was eg: https://stackoverflow.com/questions/2041622/nosql-and-spatial-data

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.