elasticsearch存储经纬度且按照范围进行查询

elasticsearch存储经纬度且按照范围进行查询

背景: 我在客户那边有很多舆情事件数据，数据里面包含的是有经纬度的，项目需求是用户在系统中输入一个地址，系统就可以查询到该地址100米 500米 1000米范围内的事件信息，当然了还可以输入事件的关键信息做模糊查询，所以我选择了使用es来存储引擎。

第一步: 创建ES索引

以下索引是测试环境数据

{
    "settings": {
        "number_of_shards": 3,
        "number_of_replicas": 1
    },
    "mappings": {
        "properties": {
            "content": {
                "type": "text",
                "analyzer": "ik_max_word"
            },
            "location": {
                "type": "geo_point"
            },
            "id": {
                "type": "keyword"
            }
        }
    }
}

第一步: 数据同步

我使用flinkSQL搭配袋鼠云的chunjun来将数据库里面的事件数据写入到ES
下面是我写的flinkSQL脚本

-- 数据库表
CREATE TABLE `location_test` (
  `id` VARCHAR,
  `content` VARCHAR COMMENT '内容',
  `lon` VARCHAR COMMENT '经度',
  `lat` VARCHAR COMMENT '纬度'
) WITH (
  'connector' = 'mysql-x',
  'url' = 'jdbc:mysql://127.0.0.1:3306/xxx?useSSL=false&useInformationSchema=true&nullCatalogMeansCurrent=true&characterEncoding=UTF-8',
  'table-name' = 'location_test',
  'username' = 'xxx',
  'password' = 'xxx',
  'scan.fetch-size' = '1024'
);

-- es索引
CREATE TABLE `location_geo_test` (
  `id` VARCHAR COMMENT '主键',
  `content` VARCHAR COMMENT '内容',
  `location` ROW< `lon` VARCHAR, `lat` VARCHAR > COMMENT '经纬度',
  PRIMARY KEY (`id`) NOT ENFORCED
) WITH (
  'connector' = 'elasticsearch7-x',
  'hosts' = '127.0.0.1:15287',
  'index' = 'location_geo_test',
  'username' = 'xxx',
  'password' = 'xxx',
  'sink.bulk-flush.max-actions' = '1024',
  'sink.bulk-flush.interval' = '2000',
  'sink.bulk-flush.max-size' = '5mb'
);

INSERT INTO `location_geo_test`
(SELECT `id`,
    `content`,
      (`lat`, `lon`)
  FROM `location_test`);

flink任务执行完毕之后 查询es数据

{
    "took": 15,
    "timed_out": false,
    "_shards": {
        "total": 6,
        "successful": 6,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 3,
            "relation": "eq"
        },
        "max_score": 1.0,
        "hits": [
            {
                "_index": "location_geo_test",
                "_type": "_doc",
                "_id": "2",
                "_score": 1.0,
                "_source": {
                    "location": {
                        "lon": "120.165269",
                        "lat": "30.312568"
                    },
                    "id": "2",
                    "content": "杭州市xxx"
                }
            },
            {
                "_index": "location_geo_test",
                "_type": "_doc",
                "_id": "3",
                "_score": 1.0,
                "_source": {
                    "location": {
                        "lon": "120.163325",
                        "lat": "30.271001"
                    },
                    "id": "3",
                    "content": "杭州市xxx"
                }
            },
            {
                "_index": "location_geo_test",
                "_type": "_doc",
                "_id": "1",
                "_score": 1.0,
                "_source": {
                    "location": {
                        "lon": "120.165269",
                        "lat": "30.312568"
                    },
                    "id": "1",
                    "content": "杭州市xxx"
                }
            }
        ]
    }
}

第三步: 查询

{
    "query": {
        "geo_distance": {
            "location": {
                "lon": 120.165269,
                "lat": 30.312568
            },
            "distance": 1,
            "distance_type": "arc"
        }
    }
}

• location：确定一个点；
• distance：确定一个半径，单位米
• distance_type：确定一个图形的类型；一般是圆形，arc

结果

{
    "took": 5,
    "timed_out": false,
    "_shards": {
        "total": 6,
        "successful": 6,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 2,
            "relation": "eq"
        },
        "max_score": 1.0,
        "hits": [
            {
                "_index": "location_geo_test",
                "_type": "_doc",
                "_id": "2",
                "_score": 1.0,
                "_source": {
                    "location": {
                        "lon": "120.165269",
                        "lat": "30.312568"
                    },
                    "id": "2",
                    "content": "杭州市xxx"
                }
            },
            {
                "_index": "location_geo_test",
                "_type": "_doc",
                "_id": "1",
                "_score": 1.0,
                "_source": {
                    "location": {
                        "lon": "120.165269",
                        "lat": "30.312568"
                    },
                    "id": "1",
                    "content": "杭州市xxx"
                }
            }
        ]
    }
}