hstore from osm tags

[nextstopsun]

Is it possible to get all tags from osm driver as hstore column?

[pramsey]

I don't think so, the OGR data model doesn't really have a "map" or "dict" type. Best case would be an array of varchar via the OFTStringList OGR type.

[pramsey]

So, reading through the osm driver, it looks like this might work almost out of the box if, when you create your target table, you just set the type of the 'other_tags' column to hstore youself. OGR is populating that string column in an hstore-compatible format, and the fdw should just copy it across, so it hits the table definition and gets parsed.

[pramsey]

curl "https://api.openstreetmap.org/api/0.6/map?bbox=11.54,48.14,11.543,48.145" > /tmp/test.osm

Yeah, no, this is actually quite fiddly.

CREATE EXTENSION ogr_fdw;
CREATE EXTENSION postgis;
CREATE EXTENSION hstore;
CREATE SERVER osmserver 
  FOREIGN DATA WRAPPER ogr_fdw 
  OPTIONS (
    datasource '/tmp/test.osm', format 'OSM');
CREATE FOREIGN TABLE lines (
  fid bigint,
  geom Geometry(LineString,4326),
  osm_id varchar,
  name varchar,
  highway varchar,
  waterway varchar,
  aerialway varchar, 
  barrier varchar,
  man_made varchar,
  z_order integer,
  other_tags varchar) 
  SERVER osmserver 
  OPTIONS ( layer 'lines' )

I mapped a osm file into a table with an hstore column for the other_tags column, but the FDW is careful about type mapping.

psql: ERROR:  column "other_tags" of foreign table "lines" converts OGR "String" to "hstore"

And making it less careful or aware of the hstore type involves quite a bit of gymnastics that I'm not willing to do for one type. (The effort of handling hstore is compounded by the fact it's a dynamic type so the Oid has to be looked up dynamically too.) You can map other_tags into a varchar column in your FDW table, and then select that column out and cast it straight to hstore in the database, as the format is hstore text form:

select other_tags::hstore from lines limit 1;