//Getting Average Tag Number Ex: 4.6 other tags for static public double getAverageNumberOfTags(String country, String key, String value) { String query_get_tag_count_for_particular_tag = @"select num as tagnumbers, count(num) as occurances from ( WITH unrolled_tags as ( SELECT t.osm_id, tags, UNNEST(k.keys) AS key FROM osm.tags t LEFT JOIN LATERAL (SELECT ARRAY(SELECT * FROM JSONB_OBJECT_KEYS(t.tags)) AS keys) k ON true ) select osm_id as id, count(1) as num FROM unrolled_tags where tags ->> '" + key + @"' = '" + value + @"' group by osm_id order by count(1) desc ) as summation group by num order by count(1) desc"; Double average = -1; using (country_qualityContext db = new country_qualityContext(country)) { var tagCount = db.AverageTags .FromSqlRaw(query_get_tag_count_for_particular_tag) .ToList(); long KeyValueSum = 0; long valueSum = 0; foreach (var count in tagCount) { KeyValueSum += (count.tagnumbers * count.occurances); valueSum += count.occurances; } average = (double)KeyValueSum / (double)valueSum; Console.WriteLine("The average tag count for " + country + " is " + Math.Round(average, 2)); } return(Math.Round(average, 2)); }
//Getting the top '5' tags we need to compare it with different regions static public List <TagAggregation> getAllTagsRespectively(String country, String key, String value, String totalCount) { String query_get_top_tags = @"WITH unrolled_tags AS ( SELECT t.osm_id, tags, UNNEST(k.keys) AS key FROM osm.tags t LEFT JOIN LATERAL (SELECT ARRAY(SELECT * FROM JSONB_OBJECT_KEYS(t.tags)) AS keys) k ON TRUE ) SELECT key as tag, COUNT(1) as count, (cast(COUNT(1) as float)/cast(" + totalCount + @" as float))*100 as percent FROM unrolled_tags WHERE tags ->> '" + key + "' = '" + value + @"' GROUP BY key ORDER BY 2 DESC"; List <TagAggregation> tagList = new List <TagAggregation>(); using (country_qualityContext db = new country_qualityContext(country)) { var top_tags = db.TagAggregation .FromSqlRaw(query_get_top_tags) .ToList(); int i = 0; foreach (var tag in top_tags) { tagList.Add(tag); //if (i > 0 && i <= _TagThreshold) //{ // tagList.Add(tag); //} //i++; } } return(tagList); }
//Get total count of a tag. Example for amenity=cafe, get the count of those tags static public long getTagCount(String country, String key, String value) { String get_tag_count = @"select count(t.osm_id) as tagnumbers, 0 as occurances from osm.tags as t where t.tags ->> '" + key + "' = '" + value + "'"; using (country_qualityContext db = new country_qualityContext(country)) { long count = -1; var tag_count = db.AverageTags .FromSqlRaw(get_tag_count) .ToList(); foreach (var first in tag_count) { Console.WriteLine("Count : " + first.tagnumbers); count = first.tagnumbers; } return(count); } }