虚谷文档 v1.0.3

适用虚谷数据库版本

v12.9



适用虚谷数据库版本

v12.9


<-> ​

📄字数 2.6K
👁️阅读量 加载中...

功能描述 ​

返回两个几何图形之间的二维距离。

语法格式 ​

sql
DOUBLE <->( GEOMETRY A , GEOMETRY B );
DOUBLE <->( GEOGRAPHY A , GEOGRAPHY B );

参数说明 ​

  • A：目标 GEOMETRY/GEOGRAPHY 对象。
  • B：目标 GEOMETRY/GEOGRAPHY 对象。

函数返回类型 ​

DOUBLE 类型

使用说明 ​

  1. 地理的 KNN 是在球面上计算的，而不是在椭球体平面上计算的。
  2. 该操作符支持多面体曲面。

示例 ​

几何 ​

创建表 knn_recheck_geom，插入 POINT、LINESTRING 和 POLYGON 数据：

sql
CREATE TABLE series1(x INT);  
CREATE TABLE series2(y INT); 
CREATE TABLE series3(i INT); 
CREATE OR REPLACE PROCEDURE p_seris(sta1 INT,endv1 INT,inc1 INT, tbn1 VARCHAR) IS
sta INT;
inc INT;
BEGIN
	sta:=sta1;
	inc:=inc1;
	LOOP
		EXECUTE IMMEDIATE 'INSERT INTO '||tbn1||' VALUES('||sta||')';
		sta:=sta+inc;
		IF sta > endv1 THEN
			EXIT;
		END IF;
	END LOOP;
END;

EXEC p_seris(-100, 1000,7, 'series1');
EXEC p_seris(-300,1000,9,'series2');
EXEC p_seris(0,10,1,'series3');

CREATE TABLE knn_recheck_geom(gid int primary key, geom GEOMETRY);

INSERT INTO knn_recheck_geom(gid,geom)
SELECT ROW_NUMBER() OVER(ORDER BY x,y) AS gid, ST_Point(x*0.777,y*0.887) As geom
FROM series1 AS x CROSS JOIN series2 As y;

INSERT INTO knn_recheck_geom(gid, geom)
SELECT 500000 + i, ST_Translate('LINESTRING(-100 300, 500 700, 400 123, 500 10000, 1 1)'::geometry, i*2000,0)
FROM series3 i;

TRUNCATE TABLE series3;
EXEC p_seris(0,3,1,'series3');

INSERT INTO knn_recheck_geom(gid, geom)
SELECT 500100 + i, ST_Translate('POLYGON((100 800, 100 700, 400 123, 405 124, 100 800))'::GEOMETRY,0,i*2000)
FROM series3 i;

INSERT INTO knn_recheck_geom(gid,geom)
SELECT 600000 + ROW_NUMBER() OVER(ORDER BY gid) AS gid, ST_Translate(ST_Buffer(geom,8,15 ),100,300) As geom
FROM knn_recheck_geom
WHERE gid IN(1000, 10000, 2000,3000);

按距离排序，查询 POINT：

sql
SQL> SELECT gid, ST_Distance( 'POINT(-305 998.5)'::GEOMETRY, geom)::NUMERIC(10,2) AS distance
     FROM knn_recheck_geom
     ORDER BY 'POINT(-305 998.5)'::GEOMETRY <-> geom LIMIT 5;

+-----+----------+
| GID | DISTANCE |
+-----+----------+
| 145 | 254.76   |
| 144 | 258.46   |
| 290 | 259.62   |
| 143 | 262.35   |
| 289 | 263.26   |
+-----+----------+

(5 rows)

按距离排序，查询 LINESTRING：

sql
SQL> SELECT  gid, ST_Distance( 'MULTILINESTRING((-95 -300, 100 200, 100 323),(-50 2000, 30 6000))'::GEOMETRY, geom)::NUMERIC(12,4) AS distance
     FROM knn_recheck_geom
     ORDER BY 'MULTILINESTRING((-95 -300, 100 200, 100 323),(-50 2000, 30 6000))'::GEOMETRY <-> geom LIMIT 5;

+------+----------+
| GID  | DISTANCE |
+------+----------+
| 3084 | 0.0094   |
| 2497 | 0.0256   |
| 3671 | 0.0444   |
| 1910 | 0.0607   |
| 4258 | 0.0794   |
+------+----------+

(5 rows)

地理 ​

创建表 knn_recheck_geog，插入 POINT、LINESTRING 和 POLYGON 数据：

sql
CREATE TABLE series5(x INT);  
CREATE TABLE series6(y INT); 
EXEC p_seris(-100,100,1,'series5');
EXEC p_seris(-90,90,1,'series6');

CREATE TABLE knn_recheck_geog(gid int primary key, geog GEOGRAPHY);
INSERT INTO knn_recheck_geog(gid,geog)
SELECT ROW_NUMBER() OVER(ORDER BY x,y) AS gid, ST_Point(x*1.11,y*0.95)::GEOGRAPHY As geog
FROM series5 x CROSS JOIN series6 As y;

INSERT INTO knn_recheck_geog(gid, geog)
SELECT 500000, 'LINESTRING(-95 -10, -93 -10.5, -90 -10.6, -95 -10.5, -95 -10)'::GEOGRAPHY;

INSERT INTO knn_recheck_geog(gid, geog)
SELECT 500001, 'POLYGON((-95 10, -95.6 10.5, -95.9 10.75, -95 10))'::GEOGRAPHY;

INSERT INTO knn_recheck_geog(gid,geog)
SELECT 600000 + ROW_NUMBER() OVER(ORDER BY gid) AS gid, ST_Buffer(geog,1000) As geog
FROM knn_recheck_geog
WHERE gid IN(1000, 10000, 2000, 2614, 40000);

到点的距离查询：

sql
SQL> SELECT gid, ('POINT(-95 -10)'::GEOGRAPHY <-> geog )::NUMERIC(12,4)
     FROM knn_recheck_geog
     ORDER BY 'POINT(-95 -10)'::GEOGRAPHY <-> geog LIMIT 5;

+--------+------------+
|  GID   |   EXPR1    |
+--------+------------+
| 500000 | 0          |
| 600003 | 69974.6935 |
| 2614   | 70976.1794 |
| 2615   | 75048.8551 |
| 2795   | 86965.9298 |
+--------+------------+

(5 rows)

到线的距离查询：

sql
SQL> SELECT gid, ('LINESTRING(75 10, 75 12, 80 20)'::GEOGRAPHY <-> geog)::NUMERIC(12,4) As knn_dist
     FROM knn_recheck_geog
     ORDER BY 'LINESTRING(75 10, 75 12, 80 20)'::GEOGRAPHY <-> geog LIMIT 5;

+-------+------------+
|  GID  |  KNN_DIST  |
+-------+------------+
| 31244 | 4189.9122  |
| 31061 | 6756.6424  |
| 30878 | 15161.4706 |
| 30695 | 21264.3654 |
| 30512 | 25313.2118 |
+-------+------------+

(5 rows)
``