场景:知道所有门店的经纬度信息,知道所有的小区的经纬度信息,想要得到一个门店 最近的10个小区,原来是etl帮处理的算好了一张表,门店和小区距离的的数据关系表,例如:
城市 | m门店 | 距离(km) |
北京 | 门店a | 1.4 |
北京 | 门店b | 5.3 |
上海 | 门店a | 1231.8 |
虽然给你算好了 只要把城市 门店放到sql里按照距离 取最近的10条就可以了,但是数据量太大了,快要1.2亿了。存储太大了,讨论下来领导说把这个表删掉,改成pg gis来处理,然后就来看了gis的函数。
ST_Distance
可以得到2个坐标的距离
float ST_Distance(
geometry g1, geometry g2)
; (这个入参不建议因为算的平面距离会不准)
float ST_Distance(
geography gg1, geography gg2)
;(算的椭圆距离)。(单位m)
问题来了 我要最近的10个或者20个小区怎么办,上面的函数只能求2个固定的点,总不能循环查所有城市下所有的小区距离在排序吧,后来在pg的的文档里找 链接在这==》我是pg的GIS文档。找了一圈没有,而且大部分的函数都是单个的入参,MULTIPOINT((0 0),(1 2)),这个数据类型是多个点的,但是函数一般都是一对一。
解决方案:这个时候看到
ST_DWithin:
在彼此在指定的距离内,则返回 true
boolean ST_DWithin(
geometry g1, geometry g2, double precision distance_of_srid)
;
boolean ST_DWithin(
geography gg1, geography gg2, double precision distance_meters)
;
可以先过滤几公里内的所有小区,然后在根据距离排序取值。就关联表查询。
建表语句
-- 门店
CREATE TABLE test_gis (
id bigint not null, -- 门店编码
geom geography -- 地理信息
);
-- 小区
CREATE TABLE res_gis (
city_cd varchar not null, -- 城市编码
res_cd varchar not null, -- 小区编码
geom geography -- 地理信息
);
然后插入一些数据例如:使用`ST_GeomFromText`方法构造geometry类型信息。例:
|
注)请使用地球坐标系构建地理信息,即SRID=4326。
下面就是用sql了:
SELECT
ST_Distance(s.geom, r.geom),r.city_cd
FROM test_gis s -- 门店表
LEFT JOIN res_gis r -- 小区表
ON ST_DWithin(s.geom , r.geom, 100000) -- 过滤门店10km内的小区
where s.id ='2' and r.city_cd='021' -- 指定的门店 和 城市
ORDER BY ST_Distance(s.geom, r.geom) limit 3 --按照距离取前3
就得到了想要的数据,后续在根据小区关联其他的业务场景。这样只需要2张维表就可以了,一张门店的数据,一张小区的数据。2张表加起来也就100w吧,比1.2亿少了很少,操作都交给了pg,查询还是挺快的,是因为数据量少吗,回头把数据灌全一点再看看。
记录一下,针对具体的业务场景的问题解决。
pgsql的 GIS功能真的是强大!!!