J. T. K. J. T. K. - 4 months ago 9
SQL Question

postgresql get the per-row number of keys of hstore data if key is in List "foo", "bar"

i try to count per row, how keys are in hstore data column.

array_length(akeys(tags), 1) as num_keys


this works fine for all tags.

In table
nodes
are many different more tags - I will just number per row of tags in my SELECT.

"name"=>"Campus", "amenity"=>"restaurant", "wheelchair"=>"yes"


count only "name" -> result 1

SELECT
id,
st_x(ST_Transform(geom,4326)) AS lon,
st_y(ST_Transform(geom,4326)) AS lat,
array_length(akeys(tags), 1) as num_keys,
tags->'name' AS name,
tags->'amenity' AS amenity,
tags->'addr:street' AS street,
tags->'addr:housenumber' AS housenumber,
tags->'addr:postcode' AS postcode,
tags->'addr:city' AS city,
tags->'cuisine' AS cuisine,
tags->'beer_garden' AS beer_garden,
tags->'outdoor_seating' AS outdoor_seating,
tags->'smoking' AS smoking,
tags->'brewery' AS brewery,
tags->'website' AS website,
tags->'internet_access' AS wlan,
tags->'phone' AS phone,
tags->'email' AS email,
tags->'opening_hours' AS opening_hours
FROM
nodes
WHERE
tags->'amenity' IN ('pub','bar','nightclub','biergarten','cafe','restaurant')
AND
tags->'name' IS NOT NULL;

Answer

The solution is:

SELECT
 CASE WHEN 'email' = ANY (akeys(tags)) THEN 1 ELSE 0 END +
 CASE WHEN 'opening_hours' = ANY (akeys(tags)) THEN 1 ELSE 0 END
 AS count_tags
FROM
 nodes