地理位置检索开源技术之串烧

最新推荐文章于 2024-01-23 01:29:01 发布

八步赶蟾

最新推荐文章于 2024-01-23 01:29:01 发布

阅读量498

点赞数

文章标签：地理位置空间索引 MongoDB ElasticSearch PostGIS

本文链接：https://blog.csdn.net/u013146060/article/details/100865138

版权

前言

在互联网时代，地理位置检索越来越重要，已经影响到了我们的衣食住行和对美好生活的向往，不管是滴滴打车，携程旅行,美团外卖，还是贝壳找房，都是必不可少的，这些互联网产品也正是因为有着高效的地理搜索功能，极大地提升了用户体验，牢牢的控制了互联网流量。今天我把所了解的几种开源技术实际操作了一下，包括MongoDB，ElasticSearch，PostGIS，留个笔记也做个分享。Redis好像也有支持，MySQL Spatiall也有比较弱的支持，但是不如PostGIS强大，PostgreSQL也自带的空间数据类型。

1 MongoDB

1.1 参考文档

1.2 操作

use geo_query;
插入测试数据)(莲花山)
db.geo_point.insert({"address":"莲花山","geo":{"type":"Point","coordinates":[114.0584092727,22.5540284045]}});
db.geo_point.insert({"address":"市民中心","geo":{"type":"Point","coordinates":[114.0619782593,22.5403207092]}});
db.geo_point.insert({"address":"南山地铁站","geo":{"type":"Point","coordinates":[113.9235542197,22.5239929516]}});

创建索引
db.geo_point.createIndex({"geo":"2dsphere"})		索引类型：2d(二维)，2dsphere(经纬度)

相交查询
db.geo_point.find({geo:{$geoIntersects:{$geometry:{type:"Polygon",coordinates:[[[114.0564603075,22.5590298277],[114.0634125933,22.5590298277],[114.0674473190,22.5549859872],[114.0677048110,22.5486445881],[114.0497681606,22.5480907024],[114.0489956844,22.5534809319],[114.0564603075,22.5590298277]]]}}}})
范围内查询
db.geo_point.find({geo:{$geoWithin:{$geometry:{type:"Polygon",coordinates:[[[114.0564603075,22.5590298277],[114.0634125933,22.5590298277],[114.0674473190,22.5549859872],[114.0677048110,22.5486445881],[114.0497681606,22.5480907024],[114.0489956844,22.5534809319],[114.0564603075,22.5590298277]]]}}}})
附近查询
db.geo_point.find({geo:{$nearSphere	:{$geometry:{type:"Point",coordinates:[114.0584092727,22.5540284045]}}}});

查看执行计划
db.geo_point.find({geo:{$nearSphere	:{$geometry:{type:"Point",coordinates:[114.0584092727,22.5540284045]}}}}).explain();

2 ElasticSearch

2.1参考文档

https://www.elastic.co/guide/en/elasticsearch/reference/current/geo-queries.html

2.2 操作

创建Index
PUT /geo_query
{
  "mappings": {
      "properties": {
        "geo" : {
          "type": "geo_point"
        }
      }
  }
}

插入数据
PUT /geo_query/_doc/1
{
  "address":"莲花山",
  "geo":"22.5540284045,114.0584092727" 
}

PUT /geo_query/_doc/2
{
  "address":"市民中心",
  "geo": { 
    "lat":22.5403207092,
    "lon":114.0619782593
  }
}

PUT /geo_query/_doc/3
{
  "address":"南山地铁站",
  "geo":[113.9235542197,22.5239929516] 
}

查询
1km范围内
GET /geo_query/_search
{
  "query": {
    "bool": {"must": [
      {"match_all": {}}
    ]
      , "filter": {
        "geo_distance": {
          "distance": "1km",
          "geo": {
            "lat": 22.5540284045,
            "lon": 114.0584092727
          }
        }
      }
    }
    
  }
}

多边形范围内
GET /geo_query/_search
{
  "query": {
    "bool": {"must": [
      {"match_all": {}}
    ]
      , "filter": {
        "geo_polygon": {
          "geo": {
            "points": [
              "22.5590298277,114.0564603075",
              "22.5590298277,114.0634125933",
              "22.5549859872,114.0674473190",
              "22.5486445881,114.0677048110",
              "22.5480907024,114.0497681606",
              "22.5534809319,114.0489956844",
              "22.5590298277,114.0564603075"
            ]
          }
        }
      }
    }
    
  }
}

矩形范围内
GET /geo_query/_search
{
  "query": {
    "bool": {"must": [
      {"match_all": {}}
    ]
      , "filter": {
        
        "geo_bounding_box": {
          "geo": {
            "top_left":"22.5590298277,114.0564603075",
            "bottom_right": "22.5534809319,114.0489956844"
          }
        }
      }
    }
  }
}

3 PostGIS

3.1 参考文档

版本(安装PostGIS用掉了大量时间)
proj-4.93,geos-3.51,gdal-1.92,postgis-2.53

3.2 操作

建表
CREATE TABLE point ( id int4, address varchar(50) );
增加空间列
SELECT AddGeometryColumn ('public','point','geo',4326,'POINT',2);
创建索引
CREATE INDEX shape_index_point ON public.point USING gist(geo);

或者
CREATE TABLE point(id serial PRIMARY KEY,address varchar(50), geo geography(POINT) );


插入数据
INSERT INTO point (address, geo) VALUES ('莲花山', ST_GeomFromText('POINT(22.55402840 114.05840927)',4326));
INSERT INTO point (address, geo) VALUES ('市民中心',ST_GeomFromText('POINT(22.54032070 114.06197825)',4326));
INSERT INTO point (address, geo) VALUES ('南山地铁站',ST_GeomFromText('POINT(22.52399295 113.92355421)',4326));

普通查询
SELECT address, ST_AsText(geo), ST_AsEwkt(geo), ST_X(geo), ST_Y(geo) FROM point;

查询距离
SELECT ST_Distance(ST_GeomFromText('POINT(22.55402840 114.05840927)',4326),geo)from point;
SELECT  address, ST_AsText(geo), ST_AsEwkt(geo), ST_X(geo), ST_Y(geo) from point where ST_Distance(ST_GeomFromText('POINT(22.55402840 114.05840927)',4326),geo)<0.01;
SELECT  address, ST_AsText(geo), ST_AsEwkt(geo), ST_X(geo), ST_Y(geo) from point where ST_DWithin(ST_GeomFromText('POINT(22.55402840 114.05840927)',4326),geo,0.01);

多边形包含
SELECT  address, ST_AsText(geo), ST_AsEwkt(geo), ST_X(geo), ST_Y(geo) from point where ST_Within(geo,ST_GeomFromText('POLYGON((22.5590298277 114.0564603075,22.5590298277 114.0634125933,22.5549859872 114.0674473190,22.5486445881 114.0677048110,22.5480907024 114.0497681606,22.5534809319 114.0489956844,22.5590298277 114.0564603075))'));