基于4km²网格的需求热力图及门店增删功能实现咨询

一、Python本地快速验证方案（适合个人快速测试） #

1. 预处理地理数据，生成4km网格 #

先把订单数据的经纬度映射到4km边长的网格单元格，统计每个网格的订单量：

import pandas as pd
import numpy as np

# 替换为你的现有dataframe
df = pd.read_csv("your_order_data.csv")

# 计算网格步长：纬度每度约111km，4km对应纬度差≈0.036度
lat_step = 4 / 111
df['grid_lat'] = np.floor(df['cust_lat'] / lat_step) * lat_step

# 经度步长随纬度变化，动态计算每个点的经度网格
df['lon_step'] = 4 / (111 * np.cos(df['cust_lat'] * np.pi / 180))
df['grid_lon'] = np.floor(df['cust_long'] / df['lon_step']) * df['lon_step']

# 生成唯一网格ID并统计订单量
df['grid_id'] = df['grid_lat'].astype(str) + "_" + df['grid_lon'].astype(str)
grid_order_counts = df.groupby(['grid_lat', 'grid_lon'])['order_id'].count().reset_index(name='order_count')

2. 绘制交互热力图+可编辑门店图层 #

用folium生成带瓦片底图的交互地图，支持添加/删除门店标记：

import folium
from folium.plugins import Draw

# 以数据中心经纬度初始化地图
map_center = [df['cust_lat'].mean(), df['cust_long'].mean()]
m = folium.Map(location=map_center, zoom_start=12)

# 添加网格热力图：用圆形标记模拟瓦片，颜色深浅对应订单量
max_order = grid_order_counts['order_count'].max()
for _, row in grid_order_counts.iterrows():
    # 取网格中心作为标记位置
    center_lat = row['grid_lat'] + lat_step/2
    center_lon = row['grid_lon'] + row['lon_step']/2
    folium.CircleMarker(
        location=[center_lat, center_lon],
        radius=12,  # 调整半径匹配4km网格视觉大小
        fill_color=f"#{int(255*(1 - row['order_count']/max_order)):02x}00{int(255*row['order_count']/max_order):02x}",
        fill_opacity=0.7,
        color=None,
        popup=f"网格订单量: {row['order_count']}"
    ).add_to(m)

# 添加初始门店图层（替换为你的门店数据）
store_data = pd.DataFrame({
    'store_id': ['ST001', 'ST002'],
    'lat': [map_center[0]+0.02, map_center[0]-0.02],
    'lon': [map_center[1]+0.02, map_center[1]-0.02]
})
store_layer = folium.FeatureGroup(name="门店")
for _, store in store_data.iterrows():
    folium.Marker(
        location=[store['lat'], store['lon']],
        popup=f"门店ID: {store['store_id']}",
        icon=folium.Icon(color='red')
    ).add_to(store_layer)
store_layer.add_to(m)

# 添加绘制工具，支持手动增删门店标记
Draw(export=False, draw_options={'marker': True}).add_to(m)

# 开启图层控制
folium.LayerControl().add_to(m)

# 保存为本地HTML文件，直接打开即可交互
m.save("store_location_map.html")

二、BigQuery+Looker协作方案（适合团队使用，支持持久化增删） #

1. 数据导入与网格预计算 #

• 将你的订单数据上传到BigQuery表（如your_project.your_dataset.order_data），同时创建门店表：

  CREATE TABLE `your_project.your_dataset.stores` (
      store_id STRING PRIMARY KEY,
      store_lat FLOAT64,
      store_lon FLOAT64,
      is_active BOOL DEFAULT TRUE
  );
  

• 写SQL预计算4km网格的订单聚合数据，作为Looker的数据源：

  WITH grid_settings AS (
      SELECT 4 AS grid_km, 111 AS km_per_degree_lat
  )
  SELECT
      FLOOR(cust_lat / (grid_km / km_per_degree_lat)) * (grid_km / km_per_degree_lat) AS grid_lat,
      FLOOR(cust_long / (grid_km / (km_per_degree_lat * COS(RADIANS(cust_lat))))) * (grid_km / (km_per_degree_lat * COS(RADIANS(cust_lat)))) AS grid_lon,
      COUNT(order_id) AS order_count,
      -- 计算网格中心坐标用于地图标记
      grid_lat + (grid_km / km_per_degree_lat)/2 AS center_lat,
      grid_lon + (grid_km / (km_per_degree_lat * COS(RADIANS(grid_lat))))/2 AS center_lon
  FROM `your_project.your_dataset.order_data`, grid_settings
  GROUP BY grid_lat, grid_lon, grid_km, km_per_degree_lat
  

2. Looker可视化与门店交互配置 #

• 连接BigQuery数据源，导入上述SQL作为视图，创建LookML模型。
• 制作热力图：选择「地图」类型，用center_lat/center_lon作为地理维度，order_count作为颜色度量，调整标记大小匹配4km网格。
• 添加门店图层：导入stores表，创建独立图层，用红色标记区分门店，过滤is_active = TRUE的门店。
• 配置门店增删：
  • 给需要操作的人员开放仪表板编辑权限。
  • 创建自定义动作：关联stores表，支持插入新门店（填写store_id、经纬度），以及通过更新is_active字段实现软删除。
  • 添加门店列表表格组件，配置「删除」按钮触发UPDATE语句，「新增门店」按钮弹出表单提交插入请求。

内容的提问来源于stack exchange，提问作者Sruthi