# Spatial Relationships and Measurements of PostGIS: 3

## 17. ST_ClusterWithin

ST_ClusterWithin - aggregation. Returns an array of geometric sets, where each geometric set represents a set of geometric sets, and the distance between geometric sets does not exceed the specified distance.

### 17.1. Summary

```geometry[] ST_ClusterWithin(geometry set g, float8 distance);
```

### 17.2. Description

ST_ClusterWithin is an aggregate function that returns an array of geometric sets, where each geometric set represents a set of geometric sets sent no more than a specified distance( Distance is the Cartesian distance in SRID.)

### 17.3. Example

```WITH testdata AS
(SELECT unnest(ARRAY['LINESTRING (0 0, 1 1)'::geometry,
'LINESTRING (5 5, 4 4)'::geometry,
'LINESTRING (6 6, 7 7)'::geometry,
'LINESTRING (0 0, -1 -1)'::geometry,
'POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0))'::geometry]) AS geom)
SELECT ST_AsText(unnest(ST_ClusterWithin(geom, 1.4))) FROM testdata;
```
st_astext
GEOMETRYCOLLECTION(LINESTRING(0 0,1 1),LINESTRING(5 5,4 4),LINESTRING(0 0,-1 -1),POLYGON((0 0,4 0,4 4,0 4,0 0)))
GEOMETRYCOLLECTION(LINESTRING(6 6,7 7))

## 18. ST_Contains

ST_Contains - returns true if and only if B has no points outside A and at least one point inside B is inside A.

### 18.1. Summary

```boolean ST_Contains(geometry geomA, geometry geomB);
```

### 18.2. Description

Geometry A contains geometry B if and only if any point of B is not outside A and at least one point inside B is inside A. An important subtlety of this definition is that A does not contain its boundary, but A contains itself. With ST_Contains, in contrast, in St_ In contains, geometry A does not contain itself correctly.

Returns TRUE if geometry B is completely within geometry A. For this function to be meaningful, the source geometry must have the same coordinate projection and the same SRID. ST_Contains is an internal St_ The reciprocal of. Therefore, ST_Contains (A, b) indicates st_ in (B, A), The result is always false, whether defined or not, except in the case of invalid geometry. Performed by GEOS module

• Do not call GEOMETRYCOLLECTION as a parameter
• Do not use this function on invalid geometry. You will get unexpected results.

This function call will automatically include a bounding box comparison that will take advantage of any indexes available on the geometry. To avoid using indexes, use_ ST_Contains function.

• This method implements the OpenGIS simple function implementation specification of SQL 1.1. s2.1.1.2 / / s2.1.13.3 is the same as within(geometry B, geometry A)
• This method implements the SQL/MM specification. SQL-MM 3: 5.1.31

### 18.3. Example  ```SELECT ST_Contains(smallc, bigc) As smallcontainsbig,
ST_Contains(bigc,smallc) As bigcontainssmall,
ST_Contains(bigc, ST_Union(smallc, bigc)) as bigcontainsunion,
ST_Equals(bigc, ST_Union(smallc, bigc)) as bigisunion,
ST_Covers(bigc, ST_ExteriorRing(bigc)) As bigcoversexterior,
ST_Contains(bigc, ST_ExteriorRing(bigc)) As bigcontainsexterior
FROM (SELECT ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) As smallc,ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) As bigc) As foo;
```
smallcontainsbigbigcontainssmallbigcontainsunionbigisunionbigcoversexteriorbigcontainsexterior
fttttf
```SELECT ST_GeometryType(geomA) As geomtype, ST_Contains(geomA,geomA) AS acontainsa,
ST_ContainsProperly(geomA, geomA) AS acontainspropa,
ST_Contains(geomA, ST_Boundary(geomA)) As acontainsba, ST_ContainsProperly(geomA,ST_Boundary(geomA)) As acontainspropba
FROM (VALUES ( ST_Buffer(ST_Point(1,1), 5,1) ),( ST_MakeLine(ST_Point(1,1), ST_Point(-1,-1) ) ),( ST_Point(1,1) )) As foo(geomA);
```
geomtypeacontainsaacontainspropaacontainsbaacontainspropba
ST_Polygontfff
ST_LineStringtfff
ST_Pointttff

## 19. ST_ContainsProperly

ST_ContainsProperly - returns true if B intersects the inside of a instead of the boundary (or outside). A cannot properly contain itself, but it does contain itself.

### 19.1. Summary

```boolean ST_ContainsProperly(geometry geomA, geometry geomB);
```

### 19.2. Description

Returns true if B intersects the inside of A instead of the boundary (or outside).
A cannot properly contain itself, but it does contain itself.
Every point in other geometry is a point inside this geometry. ST_ Intersection matrix of two geometrically matched DE-9IM in relate [T**FFFF]

• Rewrite it a little from the JTS document: use this predicate relative to ST_Contains and St_ The advantage of intersects is that it can be calculated effectively without calculating the topology at a single point. An example use case for this predicate is to calculate the intersection of a set of geometry with a large polygon geometry. Since intersection is a rather slow operation, it can more effectively filter out the test geometry completely within the region using containsloper. In these cases, the intersection is known a priori and is accurate to the original test geometry.
• Do not call GEOMETRYCOLLECTION as a parameter
• Do not use this function on invalid geometry. You will get unexpected results.

This function call will automatically include a bounding box comparison that will take advantage of any indexes available on the geometry. To avoid using indexes, use_ st_ Containshopper function.

### 19.3. Example

```SELECT ST_ContainsProperly(smallc, bigc) As smallcontainspropbig,
ST_ContainsProperly(bigc,smallc) As bigcontainspropsmall,
ST_ContainsProperly(bigc, ST_Union(smallc, bigc)) as bigcontainspropunion,
ST_Equals(bigc, ST_Union(smallc, bigc)) as bigisunion,
ST_Covers(bigc, ST_ExteriorRing(bigc)) As bigcoversexterior,
ST_ContainsProperly(bigc, ST_ExteriorRing(bigc)) As bigcontainsexterior
FROM (SELECT ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) As smallc,ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) As bigc) As foo;
```
smallcontainspropbigbigcontainspropsmallbigcontainspropunionbigisunionbigcoversexteriorbigcontainsexterior
ftfttf
```SELECT ST_GeometryType(geomA) As geomtype,
ST_Contains(geomA,geomA) AS acontainsa,
ST_ContainsProperly(geomA, geomA) AS acontainspropa,
ST_Contains(geomA, ST_Boundary(geomA)) As acontainsba,
ST_ContainsProperly(geomA, ST_Boundary(geomA)) As acontainspropba
FROM (VALUES ( ST_Buffer(ST_Point(1,1), 5,1) ),( ST_MakeLine(ST_Point(1,1), ST_Point(-1,-1) ) ),( ST_Point(1,1) )) As foo(geomA);
```
geomtypeacontainsaacontainspropaacontainsbaacontainspropba
ST_Polygontfff
ST_LineStringtfff
ST_Pointttff

## 20. ST_Covers

ST_Covers - returns 1 (TRUE) if no point in geometry B is outside geometry A

### 20.1. Summary

```boolean ST_Covers(geometry geomA, geometry geomB);
boolean ST_Covers(geography geogpolyA, geography geogpointB);
```

### 20.2. Description

Returns 1 (TRUE) if no point in Geometry/Geography B is outside of Geometry/Geography A
Performed by GEOS module

• Do not call GEOMETRYCOLLECTION as a parameter
• Do not use this function on invalid geometry. You will get unexpected results.

This function call will automatically include a bounding box comparison that will take advantage of any indexes available on the geometry. To avoid using indexes, use_ ST_Covers function.

Note: This is the "allowed" version and returns Boolean values instead of integers.

Not OGC standard, but Oracle also has.

ST_Contains and St_ Some of the subtleties of within are not intuitively obvious. For details, please refer to OGC cover, contents and internal subtleties

### 20.3. Example

```SELECT ST_Covers(smallc,smallc) As smallinsmall,
ST_Covers(smallc, bigc) As smallcoversbig,
ST_Covers(bigc, ST_ExteriorRing(bigc)) As bigcoversexterior,
ST_Contains(bigc, ST_ExteriorRing(bigc)) As bigcontainsexterior
FROM (SELECT ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) As smallc,
ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) As bigc) As foo;

```
smallinsmallsmallcoversbigbigcoversexteriorbigcontainsexterior
tftf
```SELECT ST_Covers(geog_poly, geog_pt) As poly_covers_pt,
ST_Covers(ST_Buffer(geog_pt,10), geog_pt) As buff_10m_covers_cent
FROM (SELECT  ST_Buffer(ST_GeogFromText('SRID=4326;POINT(-99.327 31.4821)'), 300) As geog_poly,
ST_GeogFromText('SRID=4326;POINT(-99.33 31.483)') As geog_pt ) As foo;
```
poly_covers_ptbuff_10m_covers_cent
ft

## 21. ST_CoveredBy

ST_CoveredBy - returns 1 (TRUE) if no point in Geometry/Geography A is outside Geometry/Geography B

### 21.1. Summary

```boolean ST_CoveredBy(geometry geomA, geometry geomB);
boolean ST_CoveredBy(geography geogA, geography geogB);
```

### 21.2. Description

Returns 1 (TRUE) if no point in Geometry/Geography A is outside Geometry/Geography B
Performed by GEOS module

• Do not call GEOMETRYCOLLECTION as a parameter
• Do not use this function on invalid geometry. You will get unexpected results.

This function call will automatically include a bounding box comparison that will take advantage of any indexes available on the geometry. To avoid using indexes, use_ ST_CoveredBy function.
Note: This is the "allowed" version and returns Boolean values instead of integers.
Not OGC standard, but Oracle also has.
ST_Contains and St_ Some of the subtleties of within are not intuitively obvious. For details, see supplements of OGC covers, contains, within

### 21.3. Example

```SELECT ST_CoveredBy(smallc,smallc) As smallinsmall,
ST_CoveredBy(smallc, bigc) As smallcoveredbybig,
ST_CoveredBy(ST_ExteriorRing(bigc), bigc) As exteriorcoveredbybig,
ST_Within(ST_ExteriorRing(bigc),bigc) As exeriorwithinbig
FROM (SELECT  ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) As smallc,
ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) As bigc) As foo;

```
smallinsmallsmallcoveredbybigexteriorcoveredbybigexeriorwithinbig
tttf

## 22. ST_Crosses

ST_Crosses - returns TRUE if the geometry provided has some (but not all) internal common points.

### 22.1. Summary

```boolean ST_Crosses(geometry g1, geometry g2);
```

### 22.2. Description

ST_Crosses accepts two geometric objects. If their intersection is "spatial cross", that is, there are some but not all internal points in the geometry, it returns TRUE. The intersection within the geometry cannot be an empty set, and its dimension must be less than the maximum dimension of the two input geometry. In addition, the intersection of two geometries must not be equal to either source geometry. Otherwise, FALSE is returned.
In mathematical terms, this means:

a . C r o s s e s ( b ) = ( d i m ( I ( a ) ∩ I ( b ) ) < m a x ( d i m ( I ( a ) , I ( b ) ) ) ) a.Crosses(b)=(dim(I(a) \cap I(b)) < max(dim(I(a),I(b)))) a.Crosses(b)=(dim(I(a)∩I(b))<max(dim(I(a),I(b))))
[ a ∩ b ≠ a a \cap b \neq a a∩b​=a,
a ∩ b ≠ b a \cap b \neq b a∩b​=b]

The DE-9IM intersection matrix of the two geometries is:

```   T*T****** (Apply to point/Line and point/Areas and lines/Regional situation)
T*****T** (For line/Point and area/Points and areas/Line condition)
0******** (Line/Line condition)
```

This predicate returns false for any other dimension combination.
OpenGIS simple functional specification defines this predicate only for point / line, point / area, line / line and line / area. JTS / GEOS extends this definition to line / point, area / point and area / line. This makes the relationship symmetrical.

• Do not call GEOMETRYCOLLECTION as a parameter
• Do not use this function on invalid geometry. You will get unexpected results.

### 22.3. Example ```-- Consider a case where the user has two tables:One is the road table and the other is the highway table.

id serial NOT NULL,
the_geom geometry,
);

CREATE TABLE highways (
id serial NOT NULL,
the_gem geometry,
);

-- To determine the list of roads crossing the highway, you can use a query similar to the following:

```

## 23. ST_LineCrossingDirection

ST_LineCrossingDirection - given 2 line strings, a number between - 3 and 3 is returned to indicate which cross behavior. 0 is disjoint.

### 23.1. Summary

```integer ST_LineCrossingDirection(geometry linestringA, geometry linestringB);
```

### 23.2. Description

Given two line strings, a number between - 3 and 3 is returned to indicate which crossover behavior. 0 is disjoint. This only supports LINESTRING
Integer constants are defined as follows:

• 0: LINE NO CROSS no cross
• -1: LINE CROSS LEFT
• 1: LINE CROSS RIGHT
• -2: LINE MULTICROSS END LEFT
• 2: LINE MULTICROSS END RIGHT
• -3: LINE MULTICROSS END SAME FIRST LEFT
• 3: LINE MULTICROSS END SAME FIRST RIGHT

### 23.3. Example

• Line 1 (green), line 2 (blue), the ball is the starting point and the triangle is the end point. The following is the query.
```SELECT ST_LineCrossingDirection(foo.line1 , foo.line2) As l1_cross_l2 ,
ST_LineCrossingDirection(foo. line2, foo.line1) As l2_cross_l1
FROM (SELECT ST_GeomFromText('LINESTRING(25 169,89 114,40 70,86 43)') As line1,
ST_GeomFromText('LINESTRING(171 154,20 140,71 74,161 53)') As line2 ) As foo;
```
l1_cross_l2l2_cross_l1
3-3 • Line 1 (green), line 2 (blue), the ball is the starting point and the triangle is the end point. The following is the query.
```SELECT ST_LineCrossingDirection(foo.line1 , foo.line2) As l1_cross_l2 ,
ST_LineCrossingDirection(foo.line2, foo.line1) As l2_cross_l1
FROM (SELECT ST_GeomFromText('LINESTRING(25 169,89 114,40 70,86 43)') As line1,
ST_GeomFromText('LINESTRING (171 154, 20 140, 71 74, 2.99 90.16)') As line2) As foo;

```
l1_cross_l2l2_cross_l1
2-2 • Line 1 (green), line 2 (blue), the ball is the starting point and the triangle is the end point. The following is the query.
```SELECT ST_LineCrossingDirection(foo.line1, foo.line2) As l1_cross_l2 ,
ST_LineCrossingDirection(foo.line2, foo.line1) As l2_cross_l1
FROM (SELECT ST_GeomFromText('LINESTRING(25 169,89 114,40 70,86 43)') As line1,
ST_GeomFromText('LINESTRING (20 140, 71 74, 161 53)') As line2 ) As foo;

```
l1_cross_l2l2_cross_l1
-11 • Line 1 (green), line 2 (blue), the ball is the starting point and the triangle is the end point. The following is the query.
```SELECT ST_LineCrossingDirection(foo.line1 , foo.line2) As l1_cross_l2 ,
ST_LineCrossingDirection(foo.line2, foo.line1) As l2_cross_l1
FROM (SELECT ST_GeomFromText('LINESTRING(25 169,89 114,40 70,86 43)') As line1,
ST_GeomFromText('LINESTRING(2.99 90.16,71 74,20 140,171 154)') As line2) As foo;
```
l1_cross_l2l2_cross_l1
-22 ```SELECT s1.gid, s2.gid, ST_LineCrossingDirection(s1.the_geom, s2.the_geom)
FROM streets s1 CROSS JOIN streets s2 ON (s1.gid != s2.gid AND s1.the_geom && s2.the_geom )
WHERE ST_CrossingDirection(s1.the_geom, s2.the_geom) > 0;
```

## 24. ST_Disjoint

ST_Disjoint - returns TRUE if the geometry does not Spatially Intersect, if they do not share any space together.

### 24.1. Summary

```boolean ST_Disjoint(geometry A ,geometry B);
```

### 24.2. Description

Overlaps, Touches, Within all geometries are not disjoint in space. If any of the above returns true, the geometry is not spatially disjoint. Disjoint indicates that the space intersection is false.

• Do not call GEOMETRYCOLLECTION as a parameter

Performed by GEOS module

• This function call does not use an index
• Note: This is the "allowed" version and returns Boolean values instead of integers.

### 24.3. Example

```SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING (2 0, 0 2)'::geometry);
```
st_disjoint
t
```SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry);
```
st_disjoint
f

## 25. ST_Distance

ST_Distance - for geometry types, returns the 2D Cartesian distance between two geometries in projected units (based on the spatial reference system). For geographic types, the default is to return the minimum geodesic distance between two geographic locations, in meters.

### 25.1. Summary

```float ST_Distance(geometry g1, geometry g2);
float ST_Distance(geography gg1, geography gg2);
float ST_Distance(geography gg1, geography gg2, boolean use_spheroid);
```

### 25.2. Description

For geometry type, returns the minimum 2D Cartesian distance between two geometries in projected units (spatial reference units). For the geography type, the default is to return the minimum geodesic distance between two geographic locations, in meters. If use_ If spheroid is false, a faster sphere calculation is used instead of an ellipsoid.

• This method implements the OpenGIS simple function implementation specification of SQL 1.1.
• This method implements the SQL/MM specification. SQL-MM 3: 5.1.23
• This method supports circular strings and curves
• The SFCGAL backend also provides this method.

### 25.3. Example

```-- Coordinate conversion returns length units=degree

SELECT ST_Distance(
'SRID=4326;POINT(-72.1235 42.3521)'::geometry,
'SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry);
```
st_distance
0.00150567726382282
```-- Coordinate conversion returns length units=rice

SELECT ST_Distance(
ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 3857),
ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 3857));

```
st_distance
167.441410065196
```-- Use latitude to correct coordinate conversion errors
SELECT ST_Distance(
ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 3857),
ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 3857)) * cosd(42.3521);

```
st_distance
123.742351254151
```-- Geometric example-In meters(SRID: 26986 Massachusetts flat meter)(Most accurate)
SELECT ST_Distance(
ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 26986),
ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 26986));
```
st_distance
123.797937878454
```-- Geometry example-Unit: M(SRID: 2163 National Atlas of the United States)(Least accurate)
SELECT ST_Distance(
ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 2163),
ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 2163));
```
st_distance
126.664256056812
```-- Same as the geometric example, but pay attention to the unit of meter-Using the sphere is slightly faster and less accurate
SELECT ST_Distance(gg1, gg2) As spheroid_dist, ST_Distance(gg1, gg2, false) As sphere_dist
FROM (SELECT 'SRID=4326;POINT(-72.1235 42.3521)'::geography as gg1,
'SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geography as gg2
) As foo ;
```
spheroid_distsphere_dist
123.802076746848123.475736916397

Keywords: Database

Added by welshy123 on Sat, 04 Sep 2021 05:46:51 +0300