PostGIS之路——操作符
1、&&
A && B： 当二维A范围覆盖二维B范围时，返回真。
boolean &&( geometry A , geometry B );
boolean &&( geography A , geography B );
注意事项：这个操作将会利用任何可用的索引的几何图形，2.0.0版本支持多面体、圆形、曲线。
实例：
SELECT tbl1.column1, tbl2.column1, tbl1.column2 && tbl2.column2 AS overlaps
FROM ( VALUES
(1, 'LINESTRING(0 0, 3 3)'::geometry),
(2, 'LINESTRING(0 1, 0 5)'::geometry)) AS tbl1,
( VALUES
(3, 'LINESTRING(1 2, 4 6)'::geometry)) AS tbl2;
2、&&&
A &&& B： 当三维A范围覆盖三维B范围时，返回真。
boolean &&&( geometry A , geometry B );
注意事项：这个操作将会利用任何可用的索引的几何图形，2.0.0版本支持多面体、圆形、曲线、三角网和TIN数据。这个函数支持三维，但不能没有Z轴。
实例：
--//3D线
SELECT tbl1.column1, tbl2.column1, tbl1.column2 &&& tbl2.column2 AS overlaps_3d,
tbl1.column2 && tbl2.column2 AS overlaps_2d
FROM ( VALUES
(1, 'LINESTRING Z(0 0 1, 3 3 2)'::geometry),
(2, 'LINESTRING Z(1 2 0, 0 5 -1)'::geometry)) AS tbl1,
( VALUES
(3, 'LINESTRING Z(1 2 1, 4 6 1)'::geometry)) AS tbl2;
--//3M线
SELECT tbl1.column1, tbl2.column1, tbl1.column2 &&& tbl2.column2 AS overlaps_3zm,
tbl1.column2 && tbl2.column2 AS overlaps_2d
FROM ( VALUES
(1, 'LINESTRING M(0 0 1, 3 3 2)'::geometry),
(2, 'LINESTRING M(1 2 0, 0 5 -1)'::geometry)) AS tbl1,
( VALUES
(3, 'LINESTRING M(1 2 1, 4 6 1)'::geometry)) AS tbl2;
3、&<
A&<B ： A范围覆盖B范围或A范围在B范围左侧时，返回真。
boolean &<( geometry A , geometry B );
实例：
SELECT tbl1.column1, tbl2.column1, tbl1.column2 &< tbl2.column2 AS overleft
FROM
( VALUES
(1, 'LINESTRING(1 2, 4 6)'::geometry)) AS tbl1,
( VALUES
(2, 'LINESTRING(0 0, 3 3)'::geometry),
(3, 'LINESTRING(0 1, 0 5)'::geometry),
(4, 'LINESTRING(6 0, 6 1)'::geometry)) AS tbl2;
4、&<|
A&<|B ： 当A范围覆盖B范围或A范围在B范围下方，返回真。
boolean &<|( geometry A , geometry B );
实例：
SELECT tbl1.column1, tbl2.column1, tbl1.column2 &<| tbl2.column2 AS overbelow
FROM
( VALUES
(1, 'LINESTRING(6 0, 6 4)'::geometry)) AS tbl1,
( VALUES
(2, 'LINESTRING(0 0, 3 3)'::geometry),
(3, 'LINESTRING(0 1, 0 5)'::geometry),
(4, 'LINESTRING(1 2, 4 6)'::geometry)) AS tbl2;
5、&>
A&>B ：A范围覆盖B范围或A范围在B范围右侧时，返回真。
boolean &>( geometry A , geometry B );
实例：
SELECT tbl1.column1, tbl2.column1, tbl1.column2 &> tbl2.column2 AS overright
FROM
( VALUES
(1, 'LINESTRING(1 2, 4 6)'::geometry)) AS tbl1,
( VALUES
(2, 'LINESTRING(0 0, 3 3)'::geometry),
(3, 'LINESTRING(0 1, 0 5)'::geometry),
(4, 'LINESTRING(6 0, 6 1)'::geometry)) AS tbl2;
6、<<
A<<B : 当A范围在B范围左侧时，返回真。
boolean <<( geometry A , geometry B );
实例：
SELECT tbl1.column1, tbl2.column1, tbl1.column2 << tbl2.column2 AS left
FROM
( VALUES
(1, 'LINESTRING (1 2, 1 5)'::geometry)) AS tbl1,
( VALUES
(2, 'LINESTRING (0 0, 4 3)'::geometry),
(3, 'LINESTRING (6 0, 6 5)'::geometry),
(4, 'LINESTRING (2 2, 5 6)'::geometry)) AS tbl2;
7、<<|
A<<|B : 当A范围在B范围下方时返回真。
boolean <<|( geometry A , geometry B );
实例：
SELECT tbl1.column1, tbl2.column1, tbl1.column2 <<| tbl2.column2 AS below
FROM
( VALUES
(1, 'LINESTRING (0 0, 4 3)'::geometry)) AS tbl1,
( VALUES
(2, 'LINESTRING (1 4, 1 7)'::geometry),
(3, 'LINESTRING (6 1, 6 5)'::geometry),
(4, 'LINESTRING (2 3, 5 6)'::geometry)) AS tbl2;
8、=
A=B : A范围=B范围时，返回真。
boolean =( geometry A , geometry B );
boolean =( geography A , geography B );
警告：当你用=比较A与B，有时返回真，但A和B不一定相同，只是边框相同，需要用ST_OrderingEquals或ST_Equals判断。这个操作将不会利用任何可用的索引的几何图形。
实例：
SELECT 'LINESTRING(0 0, 0 1, 1 0)'::geometry = 'LINESTRING(1 1, 0 0)'::geometry;
SELECT ST_AsText(column1)
FROM ( VALUES
('LINESTRING(0 0, 1 1)'::geometry),
('LINESTRING(1 1, 0 0)'::geometry)) AS foo;
SELECT ST_AsText(column1)
FROM ( VALUES
('LINESTRING(0 0, 1 1)'::geometry),
('LINESTRING(1 1, 0 0)'::geometry)) AS foo
GROUP BY column1;
SELECT ST_GeomFromText('POINT(1707296.37 4820536.77)') =
ST_GeomFromText('POINT(1707296.27 4820536.87)') As pt_intersect;
9、>>
A>>B : 当A范围在B范围右侧时，返回真。
boolean >>( geometry A , geometry B );
实例：
SELECT tbl1.column1, tbl2.column1, tbl1.column2 >> tbl2.column2 AS left
FROM
( VALUES
(1, 'LINESTRING (1 2, 1 5)'::geometry)) AS tbl1,
( VALUES
(2, 'LINESTRING (0 0, 4 3)'::geometry),
(3, 'LINESTRING (6 0, 6 5)'::geometry),
(4, 'LINESTRING (2 2, 5 6)'::geometry)) AS tbl2;
10、@
A@B ：A范围被B范围包含时，返回真。
boolean @( geometry A , geometry B );
实例：
SELECT tbl1.column1, tbl2.column1, tbl1.column2 @ tbl2.column2 AS contained
FROM
( VALUES
(1, 'LINESTRING (1 1, 3 3)'::geometry)) AS tbl1,
( VALUES
(2, 'LINESTRING (0 0, 4 4)'::geometry),
(3, 'LINESTRING (2 2, 4 4)'::geometry),
(4, 'LINESTRING (1 1, 3 3)'::geometry)) AS tbl2;
11、|&>
A|&>B ： A范围覆盖B范围或A范围在B范围上方时，返回真。
boolean |&>( geometry A , geometry B );
实例：
SELECT tbl1.column1, tbl2.column1, tbl1.column2 |&> tbl2.column2 AS overabove
FROM
( VALUES
(1, 'LINESTRING(6 0, 6 4)'::geometry)) AS tbl1,
( VALUES
(2, 'LINESTRING(0 0, 3 3)'::geometry),
(3, 'LINESTRING(0 1, 0 5)'::geometry),
(4, 'LINESTRING(1 2, 4 6)'::geometry)) AS tbl2;
12、|>>
A|>>B : 当A范围在B范围上方时返回真。
boolean |>>( geometry A , geometry B );
实例：
SELECT tbl1.column1, tbl2.column1, tbl1.column2 |>> tbl2.column2 AS below
FROM
( VALUES
(1, 'LINESTRING (0 0, 4 3)'::geometry)) AS tbl1,
( VALUES
(2, 'LINESTRING (1 4, 1 7)'::geometry),
(3, 'LINESTRING (6 1, 6 5)'::geometry),
(4, 'LINESTRING (2 3, 5 6)'::geometry)) AS tbl2;
13、~
A~B :A范围包含B范围时，返回真。
boolean ~( geometry A , geometry B );
实例：
SELECT tbl1.column1, tbl2.column1, tbl1.column2 ~ tbl2.column2 AS contains
FROM
( VALUES
(1, 'LINESTRING (0 0, 3 3)'::geometry)) AS tbl1,
( VALUES
(2, 'LINESTRING (0 0, 4 4)'::geometry),
(3, 'LINESTRING (1 1, 2 2)'::geometry),
(4, 'LINESTRING (0 0, 3 3)'::geometry)) AS tbl2;
14、~=
A~=B : A和B几何要素相同时，返回真。
boolean ~=( geometry A , geometry B );
警告：这个操作符在1.5版本做了修改，只能检查边框相等，不能确定几何要素是否相等，需要用ST_OrderingEquals或ST_Equals判断。
实例：
select 'LINESTRING(0 0, 1 1)'::geometry ~= 'LINESTRING(0 1, 1 0)'::geometry as equality;
15、<->
A<->B : 返回两点之间的距离。为了点或点检查它使用浮点精度(相对于双精度的基础几何点)。对于其他几何类型之间的距离返回浮点边界
框。用于做距离排序和最近邻法。
double precision <->( geometry A , geometry B );
实例：
SELECT st_distance(geom, 'SRID=3005;POINT(1011102 450541)'::geometry) as d,edabbr, vaabbr
FROM va2005
ORDER BY geom <-> 'SRID=3005;POINT(1011102 450541)'::geometry limit 10;
16、<#>
A<#>B ：返回2个几何图形边界框之间的距离。对于点/点检查它的几乎一样的距离(虽然可能不一样,因为这个边界框是浮点精度和几何图形是双精度)。用于做最短距离和查找邻接边距离等。
double precision <#>( geometry A , geometry B );
实例：
SELECT *
FROM (
SELECT b.tlid, b.mtfcc,
b.geom <#> ST_GeomFromText(’LINESTRING(746149 2948672,745954 2948576,
745787 2948499,745740 2948468,745712 2948438,
745690 2948384,745677 2948319)’,2249) As b_dist,
ST_Distance(b.geom, ST_GeomFromText(’LINESTRING(746149 2948672,745954 2948576,
745787 2948499,745740 2948468,745712 2948438,
745690 2948384,745677 2948319)’,2249)) As act_dist
FROM bos_roads As b
ORDER BY b_dist, b.tlid
LIMIT 100) As foo
ORDER BY act_dist, tlid LIMIT 10;
