//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);
}
}
