Postgres空间地理类型POINT POLYGON实现附近的定位和电子围栏功能 (2)

计算距离，单位是m的方法

-- 921.37629155 select ST_Distance(ST_GeographyFromText('SRID=4326;POINT(114.017299 22.537126)'), ST_GeographyFromText('SRID=4326;POINT(114.025919 22.534866)') ); -- 921.37629155 SELECT ST_Distance(ST_GeomFromText('POINT(114.017299 22.537126)',4326):: geography, ST_GeomFromText('POINT(114.025919 22.534866)', 4326):: geography ); -- 920.28519 SELECT ST_DistanceSphere(ST_GeomFromText('POINT(114.017299 22.537126)',4326), ST_GeomFromText('POINT(114.025919 22.534866)', 4326) ); -- unit=m 26986 马萨诸塞州 投影平面坐标系 单位m result=972.989337453172 SELECT ST_Distance( ST_Transform(ST_GeomFromText('POINT(114.017299 22.537126)',4326 ),26986), ST_Transform(ST_GeomFromText('POINT(114.025919 22.534866)', 4326 ),26986) );

计算距离，单位是度

# unit=degrees result=0.00891134108875483 SELECT ST_Distance(ST_GeomFromText('POINT(114.017299 22.537126)',4326), ST_GeomFromText('POINT(114.025919 22.534866)', 4326) );

关于单位是m的, 前三种的计算结果是正确的。最后一种坐标转换的计算方法，
参考PostGIS 坐标转换(SRID)的边界问题引发的专业知识 - ST_Transform 建议国内不要使用马萨诸塞州的投影平面，会使得距离计算不够准确。

附近5公里内的点

使用函数ST_DWithin 可以计算两个点之间的距离是否在5公里内。

# 计算两个点是否在给定距离内 # 单位米m SELECT ST_DWithin( ST_GeographyFromText('SRID=4326;POINT(114.017299 22.537126)'), ST_GeographyFromText('SRID=4326;POINT(114.025919 22.534866)'), 1000); # 单位度degrees SELECT ST_DWithin( ST_GeomFromText('POINT(114.017299 22.537126)',4326), ST_GeomFromText('POINT(114.025919 22.534866)', 4326), 0.00811134108875483); -- 查找给定经纬度5km以内的点 SELECT uuid, longitude, latitude, ST_DistanceSphere ( geom_point, ST_GeomFromText ( 'POINT(121.248642 31.380415)', 4326 )) distance FROM s_poi_gaode WHERE ST_DWithin ( geom_point :: geography, ST_GeomFromText ( 'POINT(121.248642 31.380415)', 4326 ) :: geography, 5000 ) IS TRUE order by distance desc LIMIT 30;

通过指定类型geom_point :: geography，单位变成米, 否则默认距离单位是度。

最近的10个点 SELECT * FROM s_poi_gaode_gps ORDER BY geom_point <-> ST_GeomFromText ( 'POINT(121.248642 31.380415)', 4326 ) LIMIT 10;

速度极快。

面多边形'POLYGON' 添加字段类型 SELECT AddGeometryColumn ('basic_mall_v1', 'geom_fence', 4326, 'POLYGON', 2); 或者 ALTER TABLE basic_mall_v1 ADD COLUMN geom_fence_alter geometry(POLYGON,4326);

添加索引

CREATE INDEX idx_area_fence ON basic_mall_v1 USING gist(geom_fence); 插入值

使用函数 ST_GeomFromText

SELECT ST_GeomFromText ( 'POLYGON((118.902957 32.085437,118.9041 32.086069,118.904754 32.085219,118.903592 32.084564,118.902957 32.085437))', 4326 ); st_geomfromtext ----------------------------------------------------------------------- 0103000020E610000001000000050000006F2C280CCAB95D40266F8099EF0A404012143FC6DCB95D4087191A4F040B4040363B527DE7B95D40B9FFC874E80A4040583B8A73D4B95D40A0353FFED20A40406F2C280CCAB95D40266F8099EF0A4040 (1 row) -- 验证 SELECT ST_AsText( ST_GeomFromText ( 'POLYGON((118.902957 32.085437,118.9041 32.086069,118.904754 32.085219,118.903592 32.084564,118.902957 32.085437))', 4326 ) ) st_astext ------------------------------------------------------------------------------------------------------------------- POLYGON((118.902957 32.085437,118.9041 32.086069,118.904754 32.085219,118.903592 32.084564,118.902957 32.085437)) (1 row)

更新到数据库字段

UPDATE basic_mall_v1 SET geom_fence=ST_GeomFromText ( 'POLYGON((118.902957 32.085437,118.9041 32.086069,118.904754 32.085219,118.903592 32.084564,118.902957 32.085437))', 4326 ) WHERE id=1000001; -- 验证： SELECT ST_AsText(geom_fence) FROM basic_mall_v1 WHERE id=1000001; st_astext ------------------------------------------------------------------------------------------------------------------- POLYGON((118.902957 32.085437,118.9041 32.086069,118.904754 32.085219,118.903592 32.084564,118.902957 32.085437)) (1 row)

实际上，我们原始围栏数据可能是这样的

-- longitude,latitude; longitude,latitude; longitude,latitude; 119.306413,26.131464;119.307575,26.131739;119.30776,26.131224;119.307336,26.131114;119.307438,26.130791;119.306776,26.13059;119.306413,26.131464