gis操作符¶
&&¶
| 操作符 | 参数类型 | 返回类型 | 描述 |
|---|---|---|---|
| && | ( geometry A , geometry B ) | boolean | 如果输入对象A的2D bounding box与输入对象B的2D bounding box有交集，则返回TRUE |
| && | ( geography A , geography B ) | boolean | 如果输入对象A的2D bounding box与输入对象B的2D bounding box有交集，则返回TRUE |
| && | ( geometry A , box2df B ) | boolean | 如果几何体二维边界框与二维浮点精度边界框相交，则返回TRUE , 这意味着，如果B是（双精度）box2d，它将在内部转换为浮点精度2D边界框（BOX2DF） |
| && | ( box2df A , box2df B ) | boolean | 如果两个二维浮点精度边界框（BOX2DF）相交，则返回TRUE。 |
示例¶
-- 1-1 boolean &&( geometry A , geometry B )
SELECT col1 && col2 As overlap FROM ( SELECT ST_GeomFromText('LINESTRING(0 0, 3 3)') As col1 , ST_GeomFromText('LINESTRING(0 0, 3 3)') As col2);
OVERLAP(boolean) |
----------------------
true |
总数目：1
-- 1-2 boolean &&( geography A , geography B )
SELECT col1 && col2 As overlap FROM ( SELECT ST_GeogFromText('LINESTRING(0 0, 3 3)') As col1 , ST_GeogFromText('LINESTRING(0 0, 3 3)') As col2);
OVERLAP(boolean) |
----------------------
true |
总数目：1
-- 2-1 boolean &&( geometry A , box2df B )
SELECT ST_MakePoint(1,1) && ST_MakeBox2D(ST_MakePoint(0,0), ST_MakePoint(2,2)) AS overlaps;
OVERLAPS(boolean) |
-----------------------
true |
总数目：1
-- 3-1 boolean &&( box2df A , geometry B )
SELECT ST_MakeBox2D(ST_MakePoint(0,0), ST_MakePoint(2,2)) && ST_MakePoint(1,1) AS overlaps;
OVERLAPS(boolean) |
-----------------------
true |
总数目：1
-- 4-1 boolean &&( box2df A , box2df B )
SELECT ST_MakeBox2D(ST_MakePoint(0,0), ST_MakePoint(2,2)) && ST_MakeBox2D(ST_MakePoint(1,0), ST_MakePoint(2,1)) AS overlaps;
OVERLAPS(boolean) |
-----------------------
true |
总数目：1
&&&¶
| 操作符 | 参数类型 | 返回类型 | 描述 |
|---|---|---|---|
| &&& | ( geometry A , geometry B ) | boolean | 如果输入对象A的n-D bounding box与输入对象B的n-D bounding box有交集，则返回TRUE |
| &&& | ( geometry A , gidx B ) | boolean | 如果几何体的（缓存的）n-D边界框与n-D浮点精度边界框（GIDX）相交，则返回TRUE。 |
| &&& | ( gidx A , geometry B ) | boolean | 如果n-D浮点精度边界框（GIDX）与几何体的（缓存的）n-D边界框相交，则返回TRUE。 这意味着，如果A是（双精度）box3d，它将在内部转换为浮点精度3D边界框（GIDX）。 |
| &&& | ( gidx A , gidx B ) | boolean | 如果两个n-D浮点精度边界框（GIDX）相交，则返回TRUE。 这意味着，如果A（或B）是（双精度）box3d，它将在内部转换为浮点精度3D边界框（GIDX） 该运算符旨在由BRIN索引内部使用，而不是由用户使用。 |
示例¶
-- 1-1 boolean &&&( geometry A , geometry B );
SELECT col1 &&& col2 As overlap FROM ( SELECT ST_GeomFromText('LINESTRING(0 0, 3 3)') As col1 , ST_GeomFromText('LINESTRING(0 0, 3 3)') As col2);
OVERLAP(boolean) |
----------------------
true |
总数目：1
-- 2-1 boolean &&&( geometry A , gidx B );
SELECT ST_MakePoint(1,1,1) &&& ST_3DMakeBox(ST_MakePoint(0,0,0), ST_MakePoint(2,2,2)) AS overlaps;
OVERLAPS(boolean) |
-----------------------
true |
总数目：1
-- 3-1 boolean &&&( gidx A , geometry B );
SELECT ST_3DMakeBox(ST_MakePoint(0,0,0), ST_MakePoint(2,2,2)) &&& ST_MakePoint(1,1,1) AS overlaps;
OVERLAPS(boolean) |
-----------------------
true |
总数目：1
-- 4-1 boolean &&&( gidx A , gidx B );
SELECT ST_3DMakeBox(ST_MakePoint(0,0,0), ST_MakePoint(2,2,2)) &&& ST_3DMakeBox(ST_MakePoint(1,1,1), ST_MakePoint(3,3,3)) AS overlaps;
OVERLAPS(boolean) |
-----------------------
true |
总数目：1
&<¶
| 操作符 | 参数类型 | 返回类型 | 描述 |
|---|---|---|---|
| &< | ( geometry A , geometry B ) | boolean | 如果输入几何对象A的bounding box与输入几何对象B的bounding box重合或者在后者的右边，则返回TRUE |
示例¶
SELECT COL1 &< COL2, COL1 &< COL3,COL1 &< COL4
FROM
( SELECT ST_GeomFromText('LINESTRING(1 2, 4 6)') As COL1,
ST_GeomFromText('LINESTRING(0 0, 3 3)') As COL2,
ST_GeomFromText('LINESTRING(0 1, 0 5)') As COL3,
ST_GeomFromText('LINESTRING(6 0, 6 1)') As COL4
);
?COLUMN?(boolean) |?COLUMN?(boolean) |?COLUMN?(boolean) |
---------------------------------------------------------------------
false |false |true |
总数目：1
&<|¶
| 操作符 | 参数类型 | 返回类型 | 描述 |
|---|---|---|---|
| &<| | ( geometry A , geometry B ) | boolean | 如果A的bounding box覆盖或在B的bounding box的下侧，则返回TRUE |
示例¶
SELECT COL1 &<| COL2, COL1 &<| COL3,COL1 &<| COL4
FROM
( SELECT ST_GeomFromText('LINESTRING(6 0, 6 4)') As COL1,
ST_GeomFromText('LINESTRING(0 0, 3 3)') As COL2,
ST_GeomFromText('LINESTRING(0 1, 0 5)') As COL3,
ST_GeomFromText('LINESTRING(1 2, 4 6)') As COL4
);
?COLUMN?(boolean) |?COLUMN?(boolean) |?COLUMN?(boolean) |
---------------------------------------------------------------------
false |true |true |
总数目：1
&>¶
| 操作符 | 参数类型 | 返回类型 | 描述 |
|---|---|---|---|
| &> | ( geometry A , geometry B ) | boolean | 如果A的bounding box覆盖或在B的bounding box的右侧，则返回TRUE |
示例¶
SELECT COL1 &> COL2, COL1 &> COL3,COL1 &> COL4
FROM
( SELECT ST_GeomFromText('LINESTRING(1 2, 4 6)') As COL1,
ST_GeomFromText('LINESTRING(0 0, 3 3)') As COL2,
ST_GeomFromText('LINESTRING(0 1, 0 5)') As COL3,
ST_GeomFromText('LINESTRING(6 0, 6 1)') As COL4
);
?COLUMN?(boolean) |?COLUMN?(boolean) |?COLUMN?(boolean) |
---------------------------------------------------------------------
true |true |false |
总数目：1
<<¶
| 操作符 | 参数类型 | 返回类型 | 描述 |
|---|---|---|---|
| << | ( geometry A , geometry B ) | boolean | 如果A的bounding box严格地在B的bounding box左侧，则返回TRUE |
示例¶
SELECT col1 << col2 As overlap FROM ( SELECT ST_GeomFromText('LINESTRING(0 0, 3 3)') As col1 , ST_GeomFromText('LINESTRING(0 0, 3 3)') As col2);
OVERLAP(boolean) |
----------------------
false |
总数目：1
SELECT col1 << col2 As overlap FROM ( SELECT ST_GeomFromText('LINESTRING(0 0, 3 3)') As col1 , ST_GeomFromText('LINESTRING(5 5, 6 6)') As col2);
OVERLAP(boolean) |
----------------------
true |
总数目：1
<<|¶
| 操作符 | 参数类型 | 返回类型 | 描述 |
|---|---|---|---|
| <<| | ( geometry A , geometry B ) | boolean | 如果A的bounding box严格地在B的bounding box下侧，则返回TRUE |
示例¶
SELECT col1 <<| col2 As overlap FROM ( SELECT ST_GeomFromText('LINESTRING(0 0, 1 1)') As col1 , ST_GeomFromText('LINESTRING(3 0, 5 5)') As col2);
OVERLAP(boolean) |
----------------------
false |
总数目：1
SELECT col1 <<| col2 As overlap FROM ( SELECT ST_GeomFromText('LINESTRING(0 0, 1 1)') As col1 , ST_GeomFromText('LINESTRING(3 3, 5 5)') As col2);
OVERLAP(boolean) |
----------------------
true |
总数目：1
=¶
| 操作符 | 参数类型 | 返回类型 | 描述 |
|---|---|---|---|
| = | ( geometry A , geometry B ) | boolean | 如果几何图形A的坐标和坐标顺序与几何图形B的坐标和坐标顺序相同，则返回TRUE。 |
| = | ( geography A , geography B ) | boolean | 如果地理图形A的坐标和坐标顺序与地理图形B的坐标和坐标顺序相同，则返回TRUE。 |
示例¶
-- 8-1 boolean =( geometry A , geometry B )
SELECT 'LINESTRING(0 0, 0 1, 1 0)'::geometry = 'LINESTRING(1 1, 0 0)'::geometry;
?COLUMN?(boolean) |
-----------------------
false |
总数目：1
SELECT ST_GeomFromText('POINT(1707296.37 4820536.77)') =
ST_GeomFromText('POINT(1707296.27 4820536.87)') As pt_intersect;
PT_INTERSECT(boolean) |
---------------------------
false |
总数目：1
-- 8-2 boolean =( geography A , geography B )
SELECT ST_GeogFromText('POINT(100 50)') = ST_GeogFromText('POINT(100.1 50.1)') As pt_intersect;
PT_INTERSECT(boolean) |
---------------------------
false |
总数目：1
>>¶
| 操作符 | 参数类型 | 返回类型 | 描述 |
|---|---|---|---|
| >> | ( geometry A , geometry B ) | boolean | 如果几何对象A的bounding box严格在几何对象B的右边，则返回true |
示例¶
SELECT col1 >> col2 As overlap FROM ( SELECT ST_GeomFromText('LINESTRING(0 0, 3 3)') As col1 , ST_GeomFromText('LINESTRING(5 5, 6 6)') As col2);
OVERLAP(boolean) |
----------------------
false |
总数目：1
SELECT col1 >> col2 As overlap FROM ( SELECT ST_GeomFromText('LINESTRING(5 5, 6 6)') As col1 , ST_GeomFromText('LINESTRING(0 0, 3 3)') As col2);
OVERLAP(boolean) |
----------------------
true |
总数目：1
@¶
| 操作符 | 参数类型 | 返回类型 | 描述 |
|---|---|---|---|
| @ | ( geometry A , geometry B ) | boolean | 如果几何对象A的bounding box被几何对象B的bounding box严格包含，则返回true |
| @ | ( geometry A , box2df B ) | boolean | 如果几何图形的二维边界框包含在二维浮点精度边界框（BOX2DF）中，则返回TRUE。 如果B是（双精度）box2d，它将在内部转换为浮点精度2D边界框（BOX2DF） |
| @ | ( box2df A , geometry B ) | boolean | 如果二维浮点精度边界框（BOX2DF）包含在几何体的二维边界框中，则返回TRUE。 |
| @ | ( geometry A , geometry B ) | boolean | 如果二维浮点精度边界框（BOX2DF）包含在另一个二维浮点精度边界框中，则返回TRUE。 这意味着，如果A（或B）是（双精度）box2d，它将在内部转换为浮点精度二维边界框（BOX2DF） |
示例¶
-- 10-1 boolean @( geometry A , geometry B )
SELECT COL1 @ COL2, COL1 @ COL3,COL1 @ COL4
FROM
( SELECT ST_GeomFromText('LINESTRING(1 1, 3 3)') As COL1,
ST_GeomFromText('LINESTRING(0 0, 4 4)') As COL2,
ST_GeomFromText('LINESTRING(2 2, 4 4)') As COL3,
ST_GeomFromText('LINESTRING(1 1, 3 3)') As COL4
);
?COLUMN?(boolean) |?COLUMN?(boolean) |?COLUMN?(boolean) |
---------------------------------------------------------------------
true |false |true |
总数目：1
-- 10-2 boolean @( geometry A , box2df B )
SELECT COL1 @ COL2
FROM
( SELECT ST_GeomFromText('LINESTRING(1 1, 3 3)') As COL1,
ST_MakeBox2D(ST_MakePoint(0,0),ST_MakePoint(5,5)) As COL2
);
?COLUMN?(boolean) |
-----------------------
true |
总数目：1
-- 10-3 boolean @( box2df A , geometry B )
SELECT COL2 @ COL1
FROM
( SELECT ST_GeomFromText('LINESTRING(1 1, 3 3)') As COL1,
ST_MakeBox2D(ST_MakePoint(0,0),ST_MakePoint(5,5)) As COL2
);
?COLUMN?(boolean) |
-----------------------
false |
总数目：1
-- 10-4 boolean @( box2df A , box2df B )
SELECT COL1 @ COL2
FROM
( SELECT ST_MakeBox2D(ST_MakePoint(1,2),ST_MakePoint(3,4)) As COL1,
ST_MakeBox2D(ST_MakePoint(0,0),ST_MakePoint(5,5)) As COL2
);
?COLUMN?(boolean) |
-----------------------
true |
总数目：1
|&>¶
| 操作符 | 参数类型 | 返回类型 | 描述 |
|---|---|---|---|
| |&> | ( geometry A , geometry B ) | boolean | 如果几何对象A的bounding box覆盖B的bounding box或在B的bounding box上面，则返回true |
示例¶
SELECT COL1 |&> COL2, COL1 |&> COL3,COL1 |&> COL4
FROM
( SELECT ST_GeomFromText('LINESTRING(6 0, 6 4)') As COL1,
ST_GeomFromText('LINESTRING(0 0, 3 3)') As COL2,
ST_GeomFromText('LINESTRING(0 1, 0 5)') As COL3,
ST_GeomFromText('LINESTRING(1 2, 4 6)') As COL4
);
?COLUMN?(boolean) |?COLUMN?(boolean) |?COLUMN?(boolean) |
---------------------------------------------------------------------
true |false |false |
总数目：1
|>>¶
| 操作符 | 参数类型 | 返回类型 | 描述 |
|---|---|---|---|
| |>> | ( geometry A , geometry B ) | boolean | 如果A的bounding box严格地在B的bounding box上面，则返回TRUE |
示例¶
SELECT col1 |>> col2 As overlap FROM ( SELECT ST_GeomFromText('LINESTRING(3 3, 5 5)') As col1 , ST_GeomFromText('LINESTRING(0 0, 1 1)') As col2);
OVERLAP(boolean) |
----------------------
true |
总数目：1
SELECT col1 |>> col2 As overlap FROM ( SELECT ST_GeomFromText('LINESTRING(0 0, 1 1)') As col1 , ST_GeomFromText('LINESTRING(3 3, 5 5)') As col2);
OVERLAP(boolean) |
----------------------
false |
总数目：1
~¶
| 操作符 | 参数类型 | 返回类型 | 描述 |
|---|---|---|---|
| ~ | ( geometry A , geometry B ) | boolean | 如果A的bounding box包含B的bounding box，则返回TRUE |
| ~ | ( geometry A , box2df B ) | boolean | 如果几何体的bounding box包含二维浮点精度边界框（GIDX），则返回TRUE。 这意味着，如果B是（双精度）box2d，它将在内部转换为浮点精度2D边界框（BOX2DF） |
| ~ | ( box2df A , geometry B ) | boolean | 如果二维浮点精度边界框（BOX2DF）包含几何体的二维bounding box，则返回TRUE。 这意味着，如果A是（双精度）box2d，它将在内部转换为浮点精度2D边界框（BOX2DF） |
| ~ | ( box2df A , box2df B ) | boolean | 如果二维浮点精度边界框（BOX2DF）包含其他二维浮点精度边界框（BOX2DF），则返回TRUE。 |
示例¶
-- 13-1 boolean ~( geometry A , geometry B )
SELECT COL1 ~ COL2, COL1 ~ COL3,COL1 ~ COL4
FROM
( SELECT ST_GeomFromText('LINESTRING(0 0, 3 3)') As COL1,
ST_GeomFromText('LINESTRING(0 0, 4 4)') As COL2,
ST_GeomFromText('LINESTRING(1 1, 2 2)') As COL3,
ST_GeomFromText('LINESTRING(0 0, 3 3)') As COL4
);
?COLUMN?(boolean) |?COLUMN?(boolean) |?COLUMN?(boolean) |
---------------------------------------------------------------------
false |true |true |
总数目：1
-- 13-2 boolean ~( geometry A , box2df B )
SELECT ST_Buffer(ST_GeomFromText('POINT(1 1)'), 10) ~ ST_MakeBox2D(ST_MakePoint(0,0), ST_MakePoint(2,2)) AS contains;
CONTAINS(boolean) |
-----------------------
true |
总数目：1
-- 13-3 boolean ~( box2df A , geometry B )
SELECT ST_MakeBox2D(ST_MakePoint(0,0), ST_MakePoint(2,2)) ~ ST_Buffer(ST_GeomFromText('POINT(1 1)'), 10) AS contains;
CONTAINS(boolean) |
-----------------------
false |
总数目：1
-- 13-4 boolean ~( box2df A , box2df B )
SELECT ST_MakeBox2D(ST_MakePoint(0,0), ST_MakePoint(5,5)) ~ ST_MakeBox2D(ST_MakePoint(2,2), ST_MakePoint(3,3)) AS contains;
CONTAINS(boolean) |
-----------------------
true |
总数目：1
~=¶
| 操作符 | 参数类型 | 返回类型 | 描述 |
|---|---|---|---|
| ~= | ( geometry A , geometry B ) | boolean | 如果A的bounding box和B的bounding box相同，则返回TRUE |
示例¶
SELECT col1 ~= col2 As overlap FROM ( SELECT ST_GeomFromText('LINESTRING(0 0, 3 3)') As col1 , ST_GeomFromText('LINESTRING(0 3, 3 0)') As col2);
OVERLAP(boolean) |
----------------------
true |
总数目：1
SELECT col1 ~= col2 As overlap FROM ( SELECT ST_GeomFromText('LINESTRING(0 0, 3 3)') As col1 , ST_GeomFromText('LINESTRING(0 3, 5 0)') As col2);
OVERLAP(boolean) |
----------------------
false |
总数目：1
<->¶
| 操作符 | 参数类型 | 返回类型 | 描述 |
|---|---|---|---|
| <-> | ( geometry A , geometry B ) | double | 返回A和B之间的二维距离。 |
| <-> | ( geography A , geography B ) | double | 返回A和B之间的二维距离。 |
对于point或point类型检查，该运算符会使用浮点精度（相对于基本的几点对象所使用的double 双精度）。对于其他几何类型，返回的是bounding box的中心点之间的双精度输出距离。
在距离排序和使用KNN 功能进行近邻限制输出对象个数时候很有用。
示例¶
-- 15-1 double precision <->( geometry A , geometry B )
SELECT col1 <-> col2 As distince FROM ( SELECT ST_GeomFromText('POINT(0 0)') As col1 , ST_GeomFromText('POINT(0 1)') As col2);
DISTINCE(double precision) |
--------------------------------
1 |
总数目：1
-- 15-2 double precision <->( geography A , geography B )
SELECT col1 <-> col2 As distince FROM ( SELECT ST_GeogFromText('POINT(0 0)') As col1 , ST_GeogFromText('POINT(0 1)') As col2);
DISTINCE(double precision) |
--------------------------------
111195.079734632 |
总数目：1
<<->>¶
| 操作符 | 参数类型 | 返回类型 | 描述 |
|---|---|---|---|
| <<->> | ( geometry A , geometry B ) | double | 返回A和B边界框质心之间的n-D距离。 用于最近邻近似距离排序。 |
它不同于使用空间索引的其他运算符，因为空间索引仅在运算符位于ORDER BY子句中时使用。
示例¶
SELECT 'POINT M (0 0 13)'::geometry <<->> 'LINESTRING M (0 0 5, 0 1 6)'::geometry;
?COLUMN?(double precision) |
--------------------------------
8 |
总数目：1
<#>¶
| 操作符 | 参数类型 | 返回类型 | 描述 |
|---|---|---|---|
| <#> | ( geometry A , geometry B ) | double | 返回两个几何对象的bounding box之间的距离 |
<#>运算符计算两个单精度的bounding box之间的距离，如果有空间索引，则使用空间索引。对于使用近邻计算后距离排序很有用。
示例¶
SELECT col1 <#> col2 As distince FROM (
SELECT ST_GeomFromText('LINESTRING(746149 2948672,745954 2948576,
745787 2948499,745740 2948468,745712 2948438,
745690 2948384,745677 2948319)',2249) As col1,
ST_GeomFromText('LINESTRING(746149 2948672,745954 2948576,
745787 2948499,745740 2948468,745712 2948438,
745690 2948384,745677 2948319)',2249) As col2);
DISTINCE(double precision) |
--------------------------------
0 |
总数目：1
select 'LINESTRING(0 0,0 10,10 10)'::geometry <#> 'LINESTRING(11 0,19 10)'::geometry;
?COLUMN?(double precision) |
--------------------------------
1 |
总数目：1