业务场景：两个店铺的配送范围会有重合的地方，需要判定用户下单的地址是否在重复的区域内，若是，需计算出用户到两个店铺分别的距离。
数据基建：
（1）配送范围是用经纬度的形式存在系统里的，[（经度1，纬度1），（经度2，纬度2），…]；
（2）店铺的经纬度
（3）用户的经纬度
需求：要用sql来实现
谢谢！！
Select
Sum(If(`zone`='z0_0x1_0',`value`,0)) as `z0_0x1_0`,
Sum(If(`zone`='z0_0x1_1',`value`,0)) as `z0_0x1_1`,
Sum(If(`zone`='z0_0x1_2',`value`,0)) as `z0_0x1_2`,
Sum(If(`zone`='z0_0x1_3',`value`,0)) as `z0_0x1_3`,
Sum(If(`zone`='z0_1x1_0',`value`,0)) as `z0_1x1_0`,
Sum(If(`zone`='z0_1x1_1',`value`,0)) as `z0_1x1_1`,
Sum(If(`zone`='z0_1x1_2',`value`,0)) as `z0_1x1_2`,
Sum(If(`zone`='z0_2x1_0',`value`,0)) as `z0_2x1_0`,
Sum(If(`zone`='z0_2x1_1',`value`,0)) as `z0_2x1_1`,
Sum(If(`zone`='z0_3x1_0',`value`,0)) as `z0_3x1_0`,
Sum(If(`zone`='z0_3x1_1',`value`,0)) as `z0_3x1_1`,
Sum(If(`zone`='z0_0',`value`,0)) as `z0_0`,
Sum(If(`zone`='z0_1',`value`,0)) as `z0_1`,
Sum(If(`zone`='z0_2',`value`,0)) as `z0_2`,
Sum(If(`zone`='z0_3',`value`,0)) as `z0_3`,
Sum(If(`zone`='z1_0',`value`,0)) as `z1_0`,
Sum(If(`zone`='z1_1',`value`,0)) as `z1_1`,
Sum(If(`zone`='z1_2',`value`,0)) as `z1_2`,
Sum(If(`zone`='z1_3',`value`,0)) as `z1_3`
From
(Select `lat`, `lng`, `value`,
Case
When ((`dist_0` Between 2.8723597844095 And 4.3343662110324) And (`dist_1` Between 3.6260179152491 And 5.4681062617155)) Then 'z0_0x1_0'
When ((`dist_0` Between 2.8723597844095 And 4.3343662110324) And (`dist_1` Between 2.1278369006061 And 3.6260179152491)) Then 'z0_0x1_1'
When ((`dist_0` Between 2.8723597844095 And 4.3343662110324) And (`dist_1` Between 1.3333495959677 And 2.1278369006061)) Then 'z0_0x1_2'
When ((`dist_0` Between 2.8723597844095 And 4.3343662110324) And (`dist_1` Between 0 And 1.3333495959677)) Then 'z0_0x1_3'
When ((`dist_0` Between 1.68658498678 And 2.8723597844095) And (`dist_1` Between 3.6260179152491 And 5.4681062617155)) Then 'z0_1x1_0'
When ((`dist_0` Between 1.68658498678 And 2.8723597844095) And (`dist_1` Between 2.1278369006061 And 3.6260179152491)) Then 'z0_1x1_1'
When ((`dist_0` Between 1.68658498678 And 2.8723597844095) And (`dist_1` Between 1.3333495959677 And 2.1278369006061)) Then 'z0_1x1_2'
When ((`dist_0` Between 1.0573158612197 And 1.68658498678) And (`dist_1` Between 3.6260179152491 And 5.4681062617155)) Then 'z0_2x1_0'
When ((`dist_0` Between 1.0573158612197 And 1.68658498678) And (`dist_1` Between 2.1278369006061 And 3.6260179152491)) Then 'z0_2x1_1'
When ((`dist_0` Between 0 And 1.0573158612197) And (`dist_1` Between 3.6260179152491 And 5.4681062617155)) Then 'z0_3x1_0'
When ((`dist_0` Between 0 And 1.0573158612197) And (`dist_1` Between 2.1278369006061 And 3.6260179152491)) Then 'z0_3x1_1'
When ((`dist_0` Between 2.8723597844095 And 4.3343662110324)) Then 'z0_0'
When ((`dist_0` Between 1.68658498678 And 2.8723597844095)) Then 'z0_1'
When ((`dist_0` Between 1.0573158612197 And 1.68658498678)) Then 'z0_2'
When ((`dist_0` Between 0 And 1.0573158612197)) Then 'z0_3'
When ((`dist_1` Between 3.6260179152491 And 5.4681062617155)) Then 'z1_0'
When ((`dist_1` Between 2.1278369006061 And 3.6260179152491)) Then 'z1_1'
When ((`dist_1` Between 1.3333495959677 And 2.1278369006061)) Then 'z1_2'
When ((`dist_1` Between 0 And 1.3333495959677)) Then 'z1_3'
End As `zone`
From
(Select `lat`, `lng`, `value`,
(acos(0.65292272498833*sin(radians(`lat`)) + 0.75742452772129*cos(radians(`lat`))*cos(radians(`lng`)-(-1.2910922519714))) * 6371) as `dist_0`,
(acos(0.65251345816785*sin(radians(`lat`)) + 0.75777713538338*cos(radians(`lat`))*cos(radians(`lng`)-(-1.2916315412569))) * 6371) as `dist_1`
From `pop`
Where
((`lat` Between 40.714353892125 And 40.810300107875) And (`lng` Between -74.037474145971 And -73.910799854029)) Or
((`lat` Between 40.673205922895 And 40.789544077105) And (`lng` Between -74.081798776797 And -73.928273223203))
)
As FirstCut
)
As Zonecut
假设你的表是这样的
##tb1:用户，经度，纬度
##tb2：店家，经度1，经度2，纬度1，纬度2
select case when t1.经度>t2.经度1 and t1.经度<t2.经度2 and t1.纬度>t2.纬度1 and t1.纬度<t2.纬度2
and t1.经度>t3.经度1 and t1.经度<t3.经度2 and t1.纬度>t3.纬度1 and t1.纬度<t3.纬度2
then 1 else 0 end 是否重合,
6371 * acos(cos(radians(t1.纬度)) * cos(radians(t2.纬度)) * cos(
radians(t1.经度) - radians(t2.经度)) + sin(radians(t1.纬度)) * sin(radians(t2.纬度))) as 距离1,
6371 * acos(cos(radians(t1.纬度)) * cos(radians(t3.纬度)) * cos(
radians(t1.经度) - radians(t3.经度)) + sin(radians(t1.纬度)) * sin(radians(t3.纬度))) as 距离2
from tb1 t1
join tb2 t2 on 1=1
join tb2 t3 on 1=1