虚谷文档 v1.0.3

适用虚谷数据库版本

v12.9



适用虚谷数据库版本

v12.9


<<->> ​

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

功能描述 ​

返回两个几何图之间的 n 维（欧几里德）距离。

语法格式 ​

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

参数说明 ​

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

函数返回类型 ​

DOUBLE 类型

使用说明 ​

  1. 对于进行最近邻近似距离排序很有用。

示例 ​

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

sql
SQL> CREATE TABLE seriesx(x INT);  
SQL> CREATE TABLE seriesy(y INT); 
SQL> CREATE TABLE seriesz(z INT); 
SQL> CREATE TABLE seriesi(i INT); 
SQL> 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;

SQL> EXEC p_seris(-100,1000,7,'seriesx');
SQL> EXEC p_seris(-300,1000,9,'seriesy');
SQL> EXEC p_seris(1005,10000,5555,'seriesz');
SQL> EXEC p_seris(0,10,1,'seriesi');

SQL> CREATE TABLE knn_recheck_geom_nd(gid INT primary key, geom GEOMETRY);

-- 3D POINTS
SQL> INSERT INTO knn_recheck_geom_nd(gid,geom)
	 SELECT ROW_NUMBER() OVER(ORDER BY x,y,z) AS gid, ST_MakePoint(x*0.777,y*0.887,z*1.05) As geom
	 FROM seriesx AS x, seriesy As y, seriesz As z ;

-- 3d LINES
SQL> INSERT INTO knn_recheck_geom_nd(gid, geom)
	 SELECT 500000 + i, ST_Translate('LINESTRING(-100 300 500, 500 700 600, 400 123 0, 500 10000 -1234, 1 1 5000)'::GEOMETRY, i*2000,0)
	 FROM seriesi i;

SQL> TRUNCATE TABLE seriesi;
SQL> EXEC p_seris(0,3,1,'seriesi');
-- 3d POLYGONS
SQL> INSERT INTO knn_recheck_geom_nd(gid, geom)
	 SELECT 500100 + i, ST_Translate('POLYGON((100 800 5678, 100 700 5678, 400 123 5678, 405 124 5678, 100 800 5678))'::GEOMETRY,0,i*2000)
	 FROM seriesi i;

-- polyhedral surface --
SQL> INSERT INTO knn_recheck_geom_nd(gid,geom)
	 SELECT 600000 + row_number() over(), ST_Translate(the_geom,100, 450,1000) As the_geom
	  FROM ( 
	  select ST_GeomFromText(
	 'POLYHEDRALSURFACE(
	 ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),
	 ((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)), ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)),  ((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),
	 ((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)),  ((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1))
	 )')  AS the_geom UNION
	 select ST_GeomFromText(
	 'POLYHEDRALSURFACE(
	 ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),
	 ((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)) )')  AS the_geom
	 ) As foo(the_geom) ;

按距离排序，查询 POINT：

sql
SQL> SELECT gid, ST_3DDistance( 'POINT(-305 998.5 1000)'::GEOMETRY, geom)::NUMERIC(12,4) As dist3d,
     ('POINT(-305 998.5 1000)'::GEOMETRY <<->> geom)::NUMERIC(12,4) As dist_knn
     FROM knn_recheck_geom_nd
     ORDER BY 'POINT(-305 998.5 1000)'::GEOMETRY <<->> geom LIMIT 5;

+-----+----------+----------+
| GID |  DIST3D  | DIST_KNN |
+-----+----------+----------+
| 289 | 260.6797 | 260.6797 |
| 287 | 264.3    | 264.3    |
| 579 | 265.4356 | 265.4356 |
| 285 | 268.1092 | 268.1092 |
| 577 | 268.9919 | 268.9919 |
+-----+----------+----------+

(5 rows)

按距离排序，查询 LINESTRING：

sql
SQL> SELECT gid, ST_3DDistance( 'MULTILINESTRING((-95 -300 5000, 105 451 1000, 100 323 200),(-50 2000 456, 30 6000 789))'::GEOMETRY, geom)::NUMERIC(12,4),
      ('MULTILINESTRING((-95 -300 5000, 105 451 1000, 100 323 200),(-50 2000 456, 30 6000 789))'::GEOMETRY <<->> geom)::NUMERIC(12,4) As knn_dist
     FROM knn_recheck_geom_nd
     ORDER BY 'MULTILINESTRING((-95 -300 5000, 105 451 1000, 100 323 200),(-50 2000 456, 30 6000 789))'::GEOMETRY <<->> geom LIMIT 5;

+-------+--------+----------+
|  GID  | EXPR1  | KNN_DIST |
+-------+--------+----------+
| 9749  | 3.7272 | 3.7272   |
| 9747  | 4.1707 | 4.1707   |
| 10039 | 6.1761 | 6.1761   |
| 10037 | 6.514  | 6.514    |
| 9459  | 6.9758 | 6.9758   |
+-------+--------+----------+

(5 rows)