I have a table of OSM data that includes "other_tags" column in the format like' "maxspeed"=>"90","name:en"=>"Highway H-35","ref"=>"H-35","ref:en"=>"H-35","smoothness"=>"good","surface"=>"asphalt" '. The length and k-v pairs are varied, for example sometimes it is just like '"ref"=>"12"'. How can I explode the column and create a column for each key? If a given row's pairs doesn't have a key, then the resulting column's value will contain NaN. I want to do this using a postgres query.
2
-
(gis.stackexchange.com/users/93656/geozelot?tab=profile) do you have any idea?– Fardin EsmaeiliCommented Jun 30, 2022 at 11:07
-
...unfortunately, users that do not participate in posts are not notified when tagged. There currently is no simpler way than Erwin Brandstetters answer in @bugmenot's linked post within PostgreSQL.You can specify to dump tags into columns during OSM import in most import tools, if that helps.– geozelotCommented Jul 2, 2022 at 14:00
Add a comment
|
1 Answer
First of all, this is a hstore data type but in text form. You can turn it into a real hstore type by casting it.
SELECT CAST(other_tags AS hstore) ...
or
SELECT other_tags::hstore ...
Converting the data into actual columns is hard but here is a link to someone else's similar question and some answers on this network: https://dba.stackexchange.com/questions/94717/dynamically-convert-hstore-keys-into-columns-for-an-unknown-set-of-keys
answered Jun 30, 2022 at 6:47
-
it is like hstore but the type of column is "text" and I can't use the arrow operator to extract the value of a specific key. Commented Jun 30, 2022 at 12:42
-
You can cast the text to hstore, I added it to the post. Commented Jun 30, 2022 at 18:29
-
I tried that but I got: ERROR: Unexpected end of string SQL state: XX000 Commented Jul 1, 2022 at 7:01
-
Even for
SELECT '"maxspeed"=>"90","name:en"=>"Highway H-35","ref"=>"H-35","ref:en"=>"H-35","smoothness"=>"good","surface"=>"asphalt"'::hstore? Commented Jul 1, 2022 at 7:37 -
'SELECT '"maxspeed"=>"90","name:en"=>"Highway H-35","ref"=>"H-35","ref:en"=>"H-35","smoothness"=>"good","surface"=>"asphalt"'::hstore' works but 'SELECT other_tags::hstore ...' does not. Commented Jul 1, 2022 at 9:14