PostgreSQL & PostGIS 附近的人

[cevincheung]

0.说点啥

一直使用MongoDB来做的线上产品LBS检索，随着数据量的增长慢慢的越来越慢，研究了一下PostgreSQL+PostGIS,经实际数据测试感觉还差不多。1000w数据排序检索耗时0.0019s。

1.PostGIS安装过程

下载： http://www.postgis.net
安装：自己Google

2.建表

-- 只给出gis字段
select AddGeometryColumn('表名','要添加的字段名',4326,'geometry',0);
-- 4326对应的是WGS84坐标系 中国使用的是该类型

有关WGS84坐标系介绍及深入了解请点这里......

3.插入数据

insert into table_name
    (name,geometry_column)
values
    ('用户1', ST_GeometryFromText('POINT(longitude latitude)',4326))

4.附近的人搜索

select
    *,
    ST_Distance_Sphere(
                      geometry_column,
                      ST_GeometryFromText('POINT(longitude latitude)',
                      4326
                      ) distance
from
    table_name
order by
    geometry_column <-> ST_GeometryFromText('POINT(longitude latitude)',4326)
LIMIT 10 offset 0

别忘了添加GiST索引

另外附上Geohash的方案：
https://www.cevin.me/archives/lbs-with-geohash-use-php.html
https://www.cevin.me/geohash/

[mywaiting]

GIS这个对于pg来说，是个挺好的实现。传闻中instagram就是因为这个而选择了pg

[pichina]

1000w数据是随机生成的么？

[markyangd]

gis专业的低头走过

[cevincheung]

@pichina Google地图POI数据

[cevincheung]

@markyangd 学校里会教postgresql么？

[cevincheung]

@mywaiting 又收集了一个案例，居然不知道instagram用pg……（捂脸）

[zado]

请问 "1000w数据排序检索耗时0.0019s " 是个什么概念？
就是有1000万个坐标数据，查找出某一个坐标相邻的 10个坐标需用时 0.0019秒 的意思吗？也就是大概每秒钟能查找500次的样子对吗？

[cevincheung]

@zado
不知道你咋算的。默认配置mongodb需要3s

[zado]

@cevincheung 1秒 除以 0.0019秒 等于 526.32次 ，我是这样算的。
“默认配置mongodb需要3s” mongodb需要3s才能查询到1个结果吗？
很抱歉，我对PostgreSQL 和 mongodb 都不熟悉，我只是对 查找附近的人 这个东西感兴趣。

[cevincheung]

@zado
数据库中存储了1000w个point（经纬度坐标点，x,y 比如：113.846686,34.615693）。
指定一个point，比如112.846686,31.615693，获取这个点附近N公里内的所有点，需要将这个点与数据库中已有的所有点进行距离计算，然后按照距离排序，然后取出范围内的，最后返回。0.0019s已经很快了。


*关于mongodb，可能是我的配置有误（实际上我没做任何配置，启动命令：mongod --fork --dbpath /data/mongo --logpath /dev/null），按照对mongodb的理解，没理由这么慢。

[ivanlw]

最近两个月正在做这个，我有了解过你的解决方案，很不错！不过我们后来选择了ElasticSearch，它有内置的geo_point类型，所以直接可以用geo distance（你的搜索需求）或者polygon filter来搜。我们60million的数据，在地图画一个多边形，找出其中包含的所有点，不超过一秒，具体多少没看

[zado]

@cevincheung 谢谢，了解了。

[cevincheung]

@ivanlw
主要是不想用多套解决方案，比如库里更新一下，es更新一下，一个异常，pg没更新，es反倒更新成功了……担心这个

[iloahz]

@cevincheung 如果真是这样算的，实在没法想象1000w的规模跑0.0019s。。。

[omegaga]

@iloahz 如果他本身做了一些预处理，比如分块之类的，实际上的数据规模不一定是1000w

[cevincheung]

@iloahz yeah

[cevincheung]

@omegaga
欢迎一起分析

[ivyshark]

我们一开始也是用的mongo很慢 后来也用postgis很专业 现在已经转向elasticsearch了

[cevincheung]

@ivyshark
我也去看看es哈哈。如果对距离不敏感其实geohash也是个不错的方案。

[SG0606]

用 PostGIS2.1.7 和 Mongo 3.0.6 测试结果来看 Mongo 的性能大概是 PostGIS 的两倍~

[cevincheung]

@SG0606 如果可以的话，能不能给出测试数据我测试一下，一直在研究这一块。