当前位置: 首页 > article >正文

ES 使用geo point 查询离目标地址最近的数据

        需求描述:项目中需要通过经纬度坐标查询目标地所在的行政区。

        解决思路有两种,使用mysql和es分别查询。

        1、使用mysql进行查询

        将带有经纬度坐标的省市区数据存入mysql中,使用mysql直接计算,表结构及查询sql如下。

        表结构:

CREATE TABLE `sys_district` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
	`parent_id` INT(10) UNSIGNED NOT NULL COMMENT '父栏目',
	`name` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
	`zipcode` INT(10) UNSIGNED NOT NULL DEFAULT '0',
	`pinyin` VARCHAR(100) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
	`lng` VARCHAR(20) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
	`lat` VARCHAR(20) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
	`level` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
	`sort` TINYINT(3) UNSIGNED NOT NULL DEFAULT '50' COMMENT '排序',
	`location` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
	PRIMARY KEY (`id`) USING BTREE
)
COMMENT='(公共)区域数据'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

        查询sql: 

SELECT * FROM sys_district WHERE ABS(lat - 34.4328) + ABS(lng - 115.88) = (SELECT MIN(ABS(lng - 115.88) + ABS(lat - 34.4328)) FROM sys_district ) LIMIT 1;

        使用mysql计算可优化的地方在于,新版本mysql提供了空间几何字段类型POINT,优化后新表结构如下。

CREATE TABLE `sys_district` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
	`parent_id` INT(10) UNSIGNED NOT NULL COMMENT '父栏目',
	`name` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8mb3_general_ci',
	`zipcode` INT(10) UNSIGNED NOT NULL DEFAULT '0',
	`pinyin` VARCHAR(100) NOT NULL DEFAULT '' COLLATE 'utf8mb3_general_ci',
	`lng` VARCHAR(20) NOT NULL DEFAULT '' COLLATE 'utf8mb3_general_ci',
	`lat` VARCHAR(20) NOT NULL DEFAULT '' COLLATE 'utf8mb3_general_ci',
	`geom` POINT NOT NULL COMMENT 'geo',
	`level` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
	`sort` TINYINT(3) UNSIGNED NOT NULL DEFAULT '50' COMMENT '排序',
	`location` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8mb3_general_ci',
	PRIMARY KEY (`id`) USING BTREE,
	SPATIAL INDEX `geom` (`geom`)
)
COMMENT='(公共)区域数据'
COLLATE='utf8mb3_general_ci'
ENGINE=InnoDB
;

        字段设置:



ALTER TABLE `sys_district`
	ADD COLUMN `geom` POINT NULL AFTER `lat`;


UPDATE sys_district SET geom = ST_PointFromText(CONCAT('POINT(', lng, ' ', lat, ')')) ;


ALTER TABLE sys_district ADD SPATIAL INDEX(geom);

        查询sql如下:

        ST_PointFromText(CONCAT('POINT(', lng, ' ', lat, ')')) 将表中的经度和纬度转换为几何点。

  ST_Distance_Sphere(geom, ST_PointFromText(CONCAT('POINT(', 120.15, ' ', 30.28, ')'))) 计算每个点与目标点之间的距离(单位为米)。

  ORDER BY distance 按距离从小到大排序

SELECT id, name, lng, lat,
       ST_Distance_Sphere(geom, ST_PointFromText(CONCAT('POINT(', 120.15, ' ', 30.28, ')'))) AS distance
FROM sys_district
ORDER BY distance
LIMIT 3;

        2、使用es进行查询

        将带有经纬度坐标的省市区数据存入es中,mappings字段使用geo point类型,索引及查询dsl如下。

        geo point文档地址:
                Geo-distance query | Elasticsearch Guide [8.6] | Elastic

                Sort search results | Elasticsearch Guide [8.6] | Elastic

        mappings结构:

PUT /sys_district
{
  "settings": {
    "index": {
      "number_of_shards": 1,
      "number_of_replicas": 1
    }
  },
  "mappings": {
    "properties": {
      "id": {
        "type": "long"
      },
      "parent_id": {
        "type": "long"
      },
      "name": {
        "type": "keyword"
      },
      "zipcode": {
        "type": "integer"
      },
      "pinyin": {
        "type": "keyword"
      },
      "location": {
        "type": "geo_point" // 如果用于地理坐标,可以考虑使用 geo_point 类型
      },
      "level": {
        "type": "byte" 
      },
      "sort": {
        "type": "byte"
      }
    }
  }
}

        dsl语句:

# 搜索坐标点附近的数据
GET sys_district/_search
{
  "from": 0,
  "size": 3,
  "query": {
    "bool": {
      "must": {
        "match_all": {}
      },
      "filter": [
        {
          "geo_distance": {
            # 半径内距离限制
            "distance": "100km",
            "location": {
                # 目的地坐标
              "lat": 34.4328,
              "lon": 115.88
            }
          }
        },
        {
          "term": {
            "level": "3"
          }
        }
      ]
    }
  },
# 排序
  "sort" : [
    {
      "_geo_distance" : {
        "location" : {
          "lat" :  34.4328,
          "lon" :115.88
        },
        "order" : "asc",
        "unit" : "km"
      }
    }
  ]
}

        获取举例最近的排序不能漏了


http://www.kler.cn/a/583901.html

相关文章:

  • Flutter 按钮组件 TextButton 详解
  • UFW 配置 Ubuntu 防火墙并设置防火墙规则
  • Spring Boot中引入Redis,以及RedisUtils完整工具类
  • 基于STM32F407ZGT6的硬件平台,(可选CubeMX) + PlatformIO软件开发的FreeRTOS部署指南
  • 什么是OF
  • 深入理解JavaScript构造函数与原型链:从原理到最佳实践
  • 《论语别裁》第01章 学而(24)五字串通五经
  • UE5与U3D引擎对比分析
  • hadoop第3课(hdfs shell)
  • 麒麟系统如何安装Anaconda
  • Day15:二叉树的后续遍历序列
  • C#中类‌的核心定义
  • 【存储中间件】Redis核心技术与实战(一):Redis入门与应用(常用数据结构:字符串String、哈希Hash、列表List)
  • LLM:了解大语言模型
  • OBS推WebRTC流,并添加毫秒级时间显示
  • K8S中的etcd数据库备份与恢复
  • 树莓百度百科更新!宜宾园区新业务板块全解析
  • 建筑兔零基础自学记录45|获取高德/百度POI-1
  • AI理论基础
  • 重生之我在学Vue--第12天 Vue 3 性能优化实战指南