帮助

ST_Distance

定义

ST_Distance 用于返回两个几何之间的距离。这一距离是两个几何的最近折点之间的距离。

语法

Oracle 和 PostgreSQL

sde.st_distance (geometry1 sde.st_geometry, geometry2 sde.st_geometry)

SQLite

st_distance (geometry1 geometryblob, geometry2 geometryblob)

st_distance (geometry1 geometryblob, geometry2 geometryblob, unit_name text)

有效单位名称如下：

毫米

英寸

码

链接

厘米

Inch_US

Yard_US

Link_US

分米

英尺

Yard_Clarke

Link_Clarke

米

Foot_US

Yard_Sears

Link_Sears

Meter_German

Foot_Clarke

Yard_Sears_1922_Truncated

Link_Sears_1922_Truncated

千米

Foot_Sears

Yard_Benoit_1895_A

Link_Benoit_1895_B

50_Kilometers

Foot_Sears_1922_Truncated

Yard_Indian

链接

150_Kilometers

Foot_Benoit_1895_A

Yard_Indian_1937

Chain_US

Vara_US

Foot_1865

Yard_Indian_1962

Chain_Clarke

Smoot

Foot_Indian

Yard_Indian_1975

Chain_Sears

Foot_Indian_1937

英寻

Chain_Sears_1922_Truncated

Foot_Indian_1962

Mile_US

Chain_Benoit_1895_A

Foot_Indian_1975

Statute_Mile

杆

Foot_Gold_Coast

Nautical_Mile

Rod_US

Foot_British_1936

Nautical_Mile_US

Nautical_Mile_UK

返回类型

双精度型

示例

会创建并填充两个表 study1 和 zones。然后，ST_Distance 函数会用于在使用代码 400 的 study1 区域表中确定各分区和面的边界之间的距离。由于在此形状上有三个区域，因此应返回三个记录。

在 Oracle 和 PostgreSQL 中，单位在使用的投影系统中定义。在这些示例中，单位为十进制度。在 SQLite 中，可指定单位。在 SQLite 示例中，指定的单位为千米；因此返回的距离以千米为单位。

Oracle

--Create tables and insert values.
CREATE TABLE zones (
 sa_id integer,
 usecode integer,
 shape sde.st_geometry
);

CREATE TABLE study1 (
 code integer unique,
 shape sde.st_geometry
);

INSERT INTO zones (sa_id, usecode, shape) VALUES (
 1,
 400,
 sde.st_polygon ('polygon ((0 0, 0 10, 10 10, 10 0, 0 0))', 4326)
);

INSERT INTO zones (sa_id, usecode, shape) VALUES (
 2,
 400,
 sde.st_polygon ('polygon ((12 3, 12 6, 15 6, 15 3, 12 3))', 4326)
); 

INSERT INTO zones (sa_id, usecode, shape) VALUES (
 3,
 400,
 sde.st_polygon ('polygon ((20 0, 20 10, 30 10, 30 0, 20 0))', 4326)
);

INSERT INTO zones (sa_id, usecode, shape) VALUES (
 4,
 402,
 sde.st_polygon ('polygon ((40 0, 40 10, 50 10, 50 0, 40 0))', 4326)
);

INSERT INTO study1 (code, shape) VALUES (
 400,
 sde.st_polygon ('polygon ((-1 -1, -1 11, 11 11, 19 11, 31 11, 31 -1, 19 -1, 11 -1, -1 -1))', 4326)
);

INSERT INTO study1 (code, shape) VALUES (
 402,
 sde.st_polygon ('polygon ((39 -1, 39 11, 51 11, 51 -1, 39 -1))', 4326)
);

SELECT UNIQUE s.code, z.sa_id, sde.st_distance(z.shape, sde.st_boundary(s.shape)) DISTANCE
 FROM zones z, study1 s
 WHERE z.usecode = s.code AND s.code = 400
 ORDER BY DISTANCE;

code		sa_id      DISTANCE

400		       1					    1
400   			   3					    1
400    			  3					    4

PostgreSQL

--Create tables and insert values.
CREATE TABLE zones (
 sa_id integer,
 usecode integer,
 shape sde.st_geometry
);

CREATE TABLE study1 (
 code integer unique,
 shape sde.st_geometry
);

INSERT INTO zones (sa_id, usecode, shape) VALUES (
 1,
 400,
 sde.st_polygon ('polygon ((0 0, 0 10, 10 10, 10 0, 0 0))', 4326)
);

INSERT INTO zones (sa_id, usecode, shape) VALUES (
 2,
 400,
 sde.st_polygon ('polygon ((12 3, 12 6, 15 6, 15 3, 12 3))', 4326)
); 

INSERT INTO zones (sa_id, usecode, shape) VALUES (
 3,
 400,
 sde.st_polygon ('polygon ((20 0, 20 10, 30 10, 30 0, 20 0))', 4326)
);

INSERT INTO zones (sa_id, usecode, shape) VALUES (
 4,
 402,
 sde.st_polygon ('polygon ((40 0, 40 10, 50 10, 50 0, 40 0))', 4326)
);

INSERT INTO study1 (code, shape) VALUES (
 400,
 sde.st_polygon ('polygon ((-1 -1, -1 11, 11 11, 19 11, 31 11, 31 -1, 19 -1, 11 -1, -1 -1))', 4326)
);

INSERT INTO study1 (code, shape) VALUES (
 402,
 sde.st_polygon ('polygon ((39 -1, 39 11, 51 11, 51 -1, 39 -1))', 4326)
);

--
SELECT DISTINCT s.code, z.sa_id, sde.st_distance(z.shape, sde.st_boundary(s.shape))
 AS Distance
 FROM zones z, study1 s
 WHERE z.usecode = s.code AND s.code = 400
 ORDER BY Distance;

code	     sa_id		distance

400            1				  1
400            3			  	1
400     	      2			  	4

SQLite

--Create tables, add geometry columns, and insert values.
CREATE TABLE zones (
 sa_id integer primary key autoincrement not null,
 usecode integer
);

SELECT AddGeometryColumn (
 NULL,
 'zones',
 'shape',
 4326,
 'polygon',
 'xy',
 'null'
);

CREATE TABLE study1 (
 code integer unique
);

SELECT AddGeometryColumn (
 NULL,
 'study1',
 'shape',
 4326,
 'polygon',
 'xy',
 'null'
);

INSERT INTO zones (usecode, shape) VALUES (
 400,
 st_polygon ('polygon ((0 0, 0 10, 10 10, 10 0, 0 0))', 4326)
);

INSERT INTO zones (usecode, shape) VALUES (
 400,
 st_polygon ('polygon ((12 3, 12 6, 15 6, 15 3, 12 3))', 4326)
); 

INSERT INTO zones (usecode, shape) VALUES (
 400,
 st_polygon ('polygon ((20 0, 20 10, 30 10, 30 0, 20 0))', 4326)
);

INSERT INTO zones (usecode, shape) VALUES (
 402,
 st_polygon ('polygon ((40 0, 40 10, 50 10, 50 0, 40 0))', 4326)
);

INSERT INTO study1 (code, shape) VALUES (
 400,
 st_polygon ('polygon ((-1 -1, -1 11, 11 11, 19 11, 31 11, 31 -1, 19 -1, 11 -1, -1 -1))', 4326)
);

INSERT INTO study1 (code, shape) VALUES (
 402,
 st_polygon ('polygon ((39 -1, 39 11, 51 11, 51 -1, 39 -1))', 4326)
);

SELECT DISTINCT s.code, z.sa_id, st_distance(z.shape, st_boundary(s.shape), "kilometer") 
 AS "Distance(km)"
 FROM zones z, study1 s
 WHERE z.usecode = s.code AND s.code = 400
 ORDER BY "Distance(km)";

code  		sa_id      Distance(km)

400		       1					 109.63919620267
400   			   3					 109.63919620267
400    			  2					 442.30025845408

相关主题