本文主要是介绍PostgreSQL 地图两点之间，经纬度距离计算， PostGIS方式计算， 电子围栏功能实现；PostgreSQL空间数据存储扩展 PostGIS，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
PostgreSQL 地图两点之间，经纬度距离计算， PostGIS方式计算， 电子围栏功能实现
- PostGIS介绍 - PostgreSQL空间数据存储扩展 PostGIS
- 安装插件PostGIS
- 在数据库中启用插件
- 坐标点相关函数使用
- 坐标系 AddGeometryColumn
- 文本转换为几何 ST_GeomFromText
- 转换坐标体系ST_Transform
- 将几何类型转换为文本描述ST_AsText
- 测试点保存
- 距离计算函数 ST_Distance
- 文本转换地理几何类型函数 ST_GeogFromText
- 两点距离计算
- 附近5公里内的前50个点
- 最近的30个点店
- 计算gps附近50m内的围栏
PostGIS在对象关系型数据库PostgreSQL上增加了存储管理空间数据的能力，相当于Oracle的spatial部分。PostGIS最大的特点是符合并且实现了OpenGIS的一些规范，是最著名的开源GIS数据库。
PostGIS的版权被纳入到GNU的GPL中，也就是说任何人可以自由得到PostGIS的源码并对其做研究和改进。正是由于这一点，PostGIS得到了迅速的发展，越来越多的爱好者和研究机构参与到PostGIS的应用开发和完善当中。
PostGIS是由Refractions Research
Inc开发的，Refractions是一家GIS和数据库咨询公司，Refraction公司最初是在PostgreSQL的基础上研究空间数据库的实
现，由于PostgreSQL所提供的空间数据类型和功能远远不能满足GIS的需求，研究工作经常陷入到进退维谷的境地，最终的结果往往是耗费了大量的人
力物力，而产品却极其复杂并且性能低下。这些原因直接或间接促成PostGIS项目的实施。
PostGIS介绍 - PostgreSQL空间数据存储扩展 PostGIS
PostGIS在对象关系型数据库PostgreSQL上增加了存储管理空间数据的能力，相当于Oracle的spatial部分。PostGIS最大的特点是符合并且实现了OpenGIS的一些规范，是最著名的开源GIS数据库。
PostGIS的版权被纳入到GNU的GPL中，也就是说任何人可以自由得到PostGIS的源码并对其做研究和改进。正是由于这一点，PostGIS得到了迅速的发展，越来越多的爱好者和研究机构参与到PostGIS的应用开发和完善当中。
PostGIS是由Refractions Research Inc开发的，Refractions是一家GIS和数据库咨询公司，Refraction公司最初是在PostgreSQL的基础上研究空间数据库的实 现，由于PostgreSQL所提供的空间数据类型和功能远远不能满足GIS的需求，研究工作经常陷入到进退维谷的境地，最终的结果往往是耗费了大量的人 力物力，而产品却极其复杂并且性能低下。这些原因直接或间接促成PostGIS项目的实施。
安装插件PostGIS
1、导入repo（导入过程省略）
2、安装pg-10（安装过程省略）
3、检查插件
yum search postgis
# yum search postgisLoaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
================================================ N/S matched: postgis =================================================
postgis-docs.x86_64 : Extra documentation for PostGIS
postgis-utils.x86_64 : The utils for PostGIS
postgis23_10-client.x86_64 : Client tools and their libraries of PostGIS
postgis23_10-devel.x86_64 : Development headers and libraries for PostGIS
postgis23_10-docs.x86_64 : Extra documentation for PostGIS
postgis23_10-utils.x86_64 : The utils for PostGIS
postgis24_10-client.x86_64 : Client tools and their libraries of PostGIS
postgis24_10-devel.x86_64 : Development headers and libraries for PostGIS
postgis24_10-docs.x86_64 : Extra documentation for PostGIS
postgis24_10-gui.x86_64 : GUI for PostGIS
postgis24_10-utils.x86_64 : The utils for PostGIS
postgis25_10-client.x86_64 : Client tools and their libraries of PostGIS
postgis25_10-devel.x86_64 : Development headers and libraries for PostGIS
postgis25_10-docs.x86_64 : Extra documentation for PostGIS
postgis25_10-gui.x86_64 : GUI for PostGIS
postgis25_10-utils.x86_64 : The utils for PostGIS
postgis25_12-client.x86_64 : Client tools and their libraries of PostGIS
postgis25_12-devel.x86_64 : Development headers and libraries for PostGIS
postgis25_12-docs.x86_64 : Extra documentation for PostGIS
postgis25_12-gui.x86_64 : GUI for PostGIS
postgis25_12-utils.x86_64 : The utils for PostGIS
postgis30_10-client.x86_64 : Client tools and their libraries of PostGIS
postgis30_10-devel.x86_64 : Development headers and libraries for PostGIS
postgis30_10-docs.x86_64 : Extra documentation for PostGIS
postgis30_10-gui.x86_64 : GUI for PostGIS
postgis30_10-utils.x86_64 : The utils for PostGIS
postgis30_12-client.x86_64 : Client tools and their libraries of PostGIS
postgis30_12-devel.x86_64 : Development headers and libraries for PostGIS
postgis30_12-docs.x86_64 : Extra documentation for PostGIS
postgis30_12-gui.x86_64 : GUI for PostGIS
postgis30_12-utils.x86_64 : The utils for PostGIS
SFCGAL.x86_64 : C++ wrapper library around CGAL for PostGIS
pgrouting_10.x86_64 : Routing functionality for PostGIS
pgrouting_12.x86_64 : Routing functionality for PostGIS
postgis.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis23_10.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis24_10.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis25_10.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis25_12.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis30_10.x86_64 : Geographic Information Systems Extensions to PostgreSQL
postgis30_12.x86_64 : Geographic Information Systems Extensions to PostgreSQLName and summary matches only, use "search all" for everything.4、安装插件
yum install postgis.x86_64 postgis24_10.x86_64 -y --skip-broken
等待插件安装完成即可
在数据库中启用插件
-- 添加空间插件
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
启用插件之后，public下会新增一个表spatial_ref_sys
坐标点相关函数使用
坐标系 AddGeometryColumn
POINT数据类型支持
AddGeometryColumn函数使用 https://postgis.net/docs/AddGeometryColumn.html
Synopsis
text AddGeometryColumn(varchar table_name, varchar column_name, integer srid, varchar type, integer dimension, boolean use_typmod=true);text AddGeometryColumn(varchar schema_name, varchar table_name, varchar column_name, integer srid, varchar type, integer dimension, boolean use_typmod=true);text AddGeometryColumn(varchar catalog_name, varchar schema_name, varchar table_name, varchar column_name, integer srid, varchar type, integer dimension, boolean use_typmod=true);
函数测试示例
-- Create schema to hold data
CREATE SCHEMA my_schema;
-- Create a new simple PostgreSQL table
CREATE TABLE my_schema.my_spatial_table (id serial);-- Describing the table shows a simple table with a single "id" column.
postgis=# \d my_schema.my_spatial_tableTable "my_schema.my_spatial_table"Column | Type | Modifiers
--------+---------+-------------------------------------------------------------------------id | integer | not null default nextval('my_schema.my_spatial_table_id_seq'::regclass)-- Add a spatial column to the table
SELECT AddGeometryColumn ('my_schema','my_spatial_table','geom',4326,'POINT',2);-- Add a point using the old constraint based behavior
SELECT AddGeometryColumn ('my_schema','my_spatial_table','geom_c',4326,'POINT',2, false);--Add a curvepolygon using old constraint behavior
SELECT AddGeometryColumn ('my_schema','my_spatial_table','geomcp_c',4326,'CURVEPOLYGON',2, false);-- Describe the table again reveals the addition of a new geometry columns.
\d my_schema.my_spatial_tableaddgeometrycolumn
-------------------------------------------------------------------------my_schema.my_spatial_table.geomcp_c SRID:4326 TYPE:CURVEPOLYGON DIMS:2
(1 row)Table "my_schema.my_spatial_table"Column | Type | Modifiers
----------+----------------------+-------------------------------------------------------------------------id | integer | not null default nextval('my_schema.my_spatial_table_id_seq'::regclass)geom | geometry(Point,4326) |geom_c | geometry |geomcp_c | geometry |
Check constraints:"enforce_dims_geom_c" CHECK (st_ndims(geom_c) = 2)"enforce_dims_geomcp_c" CHECK (st_ndims(geomcp_c) = 2)"enforce_geotype_geom_c" CHECK (geometrytype(geom_c) = 'POINT'::text OR geom_c IS NULL)"enforce_geotype_geomcp_c" CHECK (geometrytype(geomcp_c) = 'CURVEPOLYGON'::text OR geomcp_c IS NULL)"enforce_srid_geom_c" CHECK (st_srid(geom_c) = 4326)"enforce_srid_geomcp_c" CHECK (st_srid(geomcp_c) = 4326)-- geometry_columns view also registers the new columns --
SELECT f_geometry_column As col_name, type, srid, coord_dimension As ndimsFROM geometry_columnsWHERE f_table_name = 'my_spatial_table' AND f_table_schema = 'my_schema';col_name | type | srid | ndims
----------+--------------+------+-------geom | Point | 4326 | 2geom_c | Point | 4326 | 2geomcp_c | CurvePolygon | 4326 | 2
空间坐标系
SELECT AddGeometryColumn ('poi', 'geom_point', 4326, 'POINT', 2);
SELECT AddGeometryColumn ('poi', 'geom_point_26986', 26986, 'POINT', 2);
两个重要的坐标体系
4326 \ GCS_WGS_1984 \ World Geodetic System (WGS)
26986 \ 美国马萨诸塞州地方坐标系（区域坐标系）\ 投影坐标, 平面坐标
增加坐标列
ALTER TABLE kx_store ADD COLUMN address_geom_p_alter geometry(POINT,4326);
文本转换为几何 ST_GeomFromText
文本转换为几何类型的函数 ST_GeomFromText
函数说明 https://postgis.net/docs/ST_GeomFromText.html
Synopsis
geometry ST_GeomFromText(text WKT);geometry ST_GeomFromText(text WKT, integer srid);
SELECT ST_GeomFromText('POINT(123.259113 42.258729)', 4326);
测试数据示例
SELECT ST_GeomFromText('LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932)');
SELECT ST_GeomFromText('LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932)',4269);SELECT ST_GeomFromText('MULTILINESTRING((-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932))');SELECT ST_GeomFromText('POINT(-71.064544 42.28787)');SELECT ST_GeomFromText('POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239,
-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))');SELECT ST_GeomFromText('MULTIPOLYGON(((-71.1031880899493 42.3152774590236,
-71.1031627617667 42.3152960829043,-71.102923838298 42.3149156848307,
-71.1023097974109 42.3151969047397,-71.1019285062273 42.3147384934248,
-71.102505233663 42.3144722937587,-71.10277487471 42.3141658254797,
-71.103113945163 42.3142739188902,-71.10324876416 42.31402489987,
-71.1033002961013 42.3140393340215,-71.1033488797549 42.3139495090772,
-71.103396240451 42.3138632439557,-71.1041521907712 42.3141153348029,
-71.1041411411543 42.3141545014533,-71.1041287795912 42.3142114839058,
-71.1041188134329 42.3142693656241,-71.1041112482575 42.3143272556118,
-71.1041072845732 42.3143851580048,-71.1041057218871 42.3144430686681,
-71.1041065602059 42.3145009876017,-71.1041097995362 42.3145589148055,
-71.1041166403905 42.3146168544148,-71.1041258822717 42.3146748022936,
-71.1041375307579 42.3147318674446,-71.1041492906949 42.3147711126569,
-71.1041598612795 42.314808571739,-71.1042515013869 42.3151287620809,
-71.1041173835118 42.3150739481917,-71.1040809891419 42.3151344119048,
-71.1040438678912 42.3151191367447,-71.1040194562988 42.3151832057859,
-71.1038734225584 42.3151140942995,-71.1038446938243 42.3151006300338,
-71.1038315271889 42.315094347535,-71.1037393329282 42.315054824985,
-71.1035447555574 42.3152608696313,-71.1033436658644 42.3151648370544,
-71.1032580383161 42.3152269126061,-71.103223066939 42.3152517403219,
-71.1031880899493 42.3152774590236)),
((-71.1043632495873 42.315113108546,-71.1043583974082 42.3151211109857,
-71.1043443253471 42.3150676015829,-71.1043850704575 42.3150793250568,-71.1043632495873 42.315113108546)))',4326);SELECT ST_GeomFromText('CIRCULARSTRING(220268 150415,220227 150505,220227 150406)');转换坐标体系ST_Transform
函数说明 https://postgis.net/docs/ST_Transform.html
Synopsis
geometry ST_Transform(geometry g1, integer srid);geometry ST_Transform(geometry geom, text to_proj);geometry ST_Transform(geometry geom, text from_proj, text to_proj);geometry ST_Transform(geometry geom, text from_proj, integer to_srid);
SELECT ST_Transform(ST_GeomFromText('POINT(123.259113 42.258729)', 4326),26986)
测试数据示例
Change Massachusetts state plane US feet geometry to WGS 84 long latSELECT ST_AsText(ST_Transform(ST_GeomFromText('POLYGON((743238 2967416,743238 2967450,743265 2967450,743265.625 2967416,743238 2967416))',2249),4326)) As wgs_geom;wgs_geom
---------------------------POLYGON((-71.1776848522251 42.3902896512902,-71.1776843766326 42.3903829478009,
-71.1775844305465 42.3903826677917,-71.1775825927231 42.3902893647987,-71.177684
8522251 42.3902896512902));
(1 row)--3D Circular String example
SELECT ST_AsEWKT(ST_Transform(ST_GeomFromEWKT('SRID=2249;CIRCULARSTRING(743238 2967416 1,743238 2967450 2,743265 2967450 3,743265.625 2967416 3,743238 2967416 4)'),4326));st_asewkt
--------------------------------------------------------------------------------------SRID=4326;CIRCULARSTRING(-71.1776848522251 42.3902896512902 1,-71.1776843766326 42.3903829478009 2,-71.1775844305465 42.3903826677917 3,-71.1775825927231 42.3902893647987 3,-71.1776848522251 42.3902896512902 4)Example of creating a partial functional index. For tables where you are not sure all the geometries will be filled in, its best to use a partial index that leaves out null geometries which will both conserve space and make your index smaller and more efficient.CREATE INDEX idx_the_geom_26986_parcelsON parcelsUSING gist(ST_Transform(the_geom, 26986))WHERE the_geom IS NOT NULL;Examples of using PROJ.4 text to transform with custom spatial references.-- Find intersection of two polygons near the North pole, using a custom Gnomic projection
-- See http://boundlessgeo.com/2012/02/flattening-the-peel/WITH data AS (SELECTST_GeomFromText('POLYGON((170 50,170 72,-130 72,-130 50,170 50))', 4326) AS p1,ST_GeomFromText('POLYGON((-170 68,-170 90,-141 90,-141 68,-170 68))', 4326) AS p2,'+proj=gnom +ellps=WGS84 +lat_0=70 +lon_0=-160 +no_defs'::text AS gnom)SELECT ST_AsText(ST_Transform(ST_Intersection(ST_Transform(p1, gnom), ST_Transform(p2, gnom)),gnom, 4326))FROM data;st_astext--------------------------------------------------------------------------------POLYGON((-170 74.053793645338,-141 73.4268621378904,-141 68,-170 68,-170 74.053793645338))Configuring transformation behaviour
Sometimes coordinate transformation involving a grid-shift can fail, for example if PROJ.4 has not been built with grid-shift files or the coordinate does not lie within the range for which the grid shift is defined. By default, PostGIS will throw an error if a grid shift file is not present, but this behaviour can be configured on a per-SRID basis either by testing different to_proj values of PROJ.4 text, or altering the proj4text value within the spatial_ref_sys table.For example, the proj4text parameter +datum=NAD87 is a shorthand form for the following +nadgrids parameter:+nadgrids=@conus,@alaska,@ntv2_0.gsb,@ntv1_can.dat
The @ prefix means no error is reported if the files are not present, but if the end of the list is reached with no file having been appropriate (ie. found and overlapping) then an error is issued.If, conversely, you wanted to ensure that at least the standard files were present, but that if all files were scanned without a hit a null transformation is applied you could use:+nadgrids=@conus,@alaska,@ntv2_0.gsb,@ntv1_can.dat,null
The null grid shift file is a valid grid shift file covering the whole world and applying no shift. So for a complete example, if you wanted to alter PostGIS so that transformations to SRID 4267 that didn't lie within the correct range did not throw an ERROR, you would use the following:UPDATE spatial_ref_sys SET proj4text = '+proj=longlat +ellps=clrk66 +nadgrids=@conus,@alaska,@ntv2_0.gsb,@ntv1_can.dat,null +no_defs' WHERE srid = 4267;
将几何类型转换为文本描述ST_AsText
函数说明 https://postgis.net/docs/ST_AsText.html
Synopsis
text ST_AsText(geometry g1);text ST_AsText(geometry g1, integer maxdecimaldigits=15);text ST_AsText(geography g1);text ST_AsText(geography g1, integer maxdecimaldigits=15);
SELECT ST_AsText(ST_GeomFromText('POINT(123.259113 42.258729)', 4326));
测试示例数据
SELECT ST_AsText('01030000000100000005000000000000000000
000000000000000000000000000000000000000000000000
F03F000000000000F03F000000000000F03F000000000000F03
F000000000000000000000000000000000000000000000000');st_astext
--------------------------------POLYGON((0 0,0 1,1 1,1 0,0 0))
(1 row)
Providing the precision is optional.SELECT ST_AsText(GeomFromEWKT('SRID=4326;POINT(111.1111111 1.1111111)'))st_astext
------------------------------POINT(111.1111111 1.1111111)
(1 row)
SELECT ST_AsText(GeomFromEWKT('SRID=4326;POINT(111.1111111 1.1111111)'),2)
st_astext
--------------------
POINT(111.11 1.11)
(1 row)
测试点保存
--更新坐标点数据
update kx_store set
address_geom_point=ST_GeomFromText('POINT(123.259113 42.258729)', 4326),
address_geom_point_26986=ST_Transform(ST_GeomFromText('POINT(123.259113 42.258729)', 4326),26986),
address_geom_p_alter=ST_GeomFromText('POINT(123.259113 42.258729)', 4326)
WHERE usid='12698186578197259211269818657819725921';--查询原数据
SELECT address_geom_point,address_geom_point_26986,address_geom_p_alter from kx_store WHERE usid='12698186578197259211269818657819725921';--查询可视数据
SELECT ST_AsText(address_geom_point),ST_AsText(address_geom_point_26986),address_geom_p_alter from kx_store WHERE usid='12698186578197259211269818657819725921';
距离计算函数 ST_Distance
函数使用说明 https://postgis.net/docs/ST_Distance.html
Synopsis
float ST_Distance(geometry g1, geometry g2);float ST_Distance(geography geog1, geography geog2, >boolean use_spheroid=true);
Basic Geometry Examples
Geometry example - units in planar degrees 4326 is WGS 84 long lat, units are degrees.SELECT ST_Distance('SRID=4326;POINT(-72.1235 42.3521)'::geometry,'SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry);
st_distance
-----------------
0.00150567726382282
Geometry example - units in meters (SRID: 3857, proportional to pixels on popular web maps). Although the value is off, nearby ones can be compared correctly, which makes it a good choice for algorithms like KNN or KMeans.SELECT ST_Distance(ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 3857),ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 3857));
st_distance
-----------------
167.441410065196
Geometry example - units in meters (SRID: 3857 as above, but corrected by cos(lat) to account for distortion)SELECT ST_Distance(ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 3857),ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 3857)) * cosd(42.3521);
st_distance
-----------------
123.742351254151
Geometry example - units in meters (SRID: 26986 Massachusetts state plane meters) (most accurate for Massachusetts)SELECT ST_Distance(ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 26986),ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 26986));
st_distance
-----------------
123.797937878454
Geometry example - units in meters (SRID: 2163 US National Atlas Equal area) (least accurate)SELECT ST_Distance(ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 2163),ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 2163));st_distance
------------------
126.664256056812
Geography Examples
Same as geometry example but note units in meters - use sphere for slightly faster and less accurate computation.SELECT ST_Distance(gg1, gg2) As spheroid_dist, ST_Distance(gg1, gg2, false) As sphere_dist
FROM (SELECT'SRID=4326;POINT(-72.1235 42.3521)'::geography as gg1,'SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geography as gg2) As foo ;spheroid_dist | sphere_dist
------------------+------------------123.802076746848 | 123.475736916397
文本转换地理几何类型函数 ST_GeogFromText
函数使用说明 https://postgis.net/docs/ST_GeogFromText.html
Synopsis
geography ST_GeogFromText(text EWKT);
--- converting lon lat coords to geography
ALTER TABLE sometable ADD COLUMN geog geography(POINT,4326);
UPDATE sometable SET geog = ST_GeogFromText('SRID=4326;POINT(' || lon || ' ' || lat || ')');--- specify a geography point using EPSG:4267, NAD27
SELECT ST_AsEWKT(ST_GeogFromText('SRID=4267;POINT(-77.0092 38.889588)'));
两点距离计算
距离计算函数 ST_Distance
范围计算函数 ST_DWithin
文本转换地理几何类型函数 ST_GeogFromText
文本转换为地理几何类型函数 ST_GeographyFromText
计算距离，单位是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));
SELECT ST_Distance(ST_GeomFromText(‘POINT(114.017299 22.537126)’,4326),
ST_GeomFromText(‘POINT(114.025919 22.534866)’, 4326)
);
附近5公里内的前50个点
# 计算两个点是否在给定距离内# 单位米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 usid,longitude,latitude,ST_DistanceSphere (address_geom_point,ST_GeomFromText('POINT(116.281524,39.957202)',4326 )) distance
FROM kx_store WHERE ST_DWithin ( geom_point :: geography, ST_GeomFromText ( 'POINT(116.281524,39.957202)', 4326 ) :: geography, 5000 ) IS TRUE order by distance descLIMIT 50;
通过指定类型geom_point :: geography，单位变成米, 否则默认距离单位是度
最近的30个点店
SELECT * FROM kx_store ORDER BY address_geom_point <-> ST_GeomFromText ( 'POINT(116.281524,39.957202)', 4326 ) LIMIT 30;
计算gps附近50m内的围栏
使用函数ST_DWithin 判断一个几何对象是否在另一个的r距离以内：
SELECTST_Distance(ST_GeomFromText('POINT(120.731069 30.758984)',4326):: geography,geom_fence :: geography) AS distance, id, name
FROM kx_store
WHEREST_DWithin (geom_fence :: geography,ST_GeomFromText ( 'POINT( 120.731069 30.758984)', 4326 ) :: geography,50 ) ORDER BY distance LIMIT 30;
使用函数boolean ST_Within(geometry A, geometry B); 判断A是否完全在B内部
SELECTid, nameFROMbasic_mall_v1WHEREST_Within (ST_GeomFromText('POINT('|| #{longitude} ||' '|| #{latitude} ||')',4326),geom_fence)
这篇关于PostgreSQL 地图两点之间，经纬度距离计算， PostGIS方式计算， 电子围栏功能实现；PostgreSQL空间数据存储扩展 PostGIS的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
