@TedZhou
2024-10-31T06:12:59.000000Z
字数 1212
阅读 36
地图
头像
经纬度坐标
某社交平台要求,把共享了位置的用户头像按经纬度坐标展示到地图上。
根据可视区域查询所在区域的用户,这是最基本的处理,满足需求1和2都不是问题。关键要考虑如何处理需求3。
主要思路是:把用户坐标按适当的精度分组,每组选取1个用户代表,即减少了数量,又保证了分散,且不会遗漏有用户的分区。
假设数据库存储了用户的位置坐标P(x,y),地图可视区为V(x1, y1, x2, y2)。
设选取的用户数量限30人左右(20~40)。
d := Distance(P(x1,y1),P(x2,y2));
设精度系数 c := 1000/a;
with u as (select max(id) as id, count(id) as all from user where P in V group by ROUND(p*c))
select count(id) as cnt, sum(all) as all from u
;select * from user where P in V;
select * from user where id in (select id from u);
c *= cnt<30 ? 30/(cnt+30) : Math.sqrt(30/(cnt+30));
回到第2步分组不多时(c<0.2)直接分组返回
d := Distance(P(x1,y1),P(x2,y2));
设精度系数 c := 1000/d;
select * from user where P in V;
with u as (select max(id) as id from user where P in V group by ROUND(p*c))
select * from user where id in (select id from u);
select count(id) as cnt, sum(all) as all from u
;select * from user where P in V;
select count(id) as cnt from u;
select * from user where id in (select id from u);
c *= cnt<30 ? 2.0*30/(cnt+30) : Math.sqrt(1.0*30/(cnt+30));
回到第5步