Given two input GEOGRAPHY objects, returns a GEOGRAPHY object that represents the shape containing the set of points that are common to both input objects (i.e. the intersection of the two objects).
ST_INTERSECTION_AGG , ST_UNION , ST_DIFFERENCE , ST_SYMDIFFERENCE
ST_INTERSECTION( <geography_expression_1> , <geography_expression_2> )
Copy
Arguments¶geography_expression_1
A GEOGRAPHY object.
geography_expression_2
A GEOGRAPHY object.
The function returns a value of type GEOGRAPHY.
Usage notes¶If any vertex of one input object is on the boundary of the other input object (excluding the vertices), the output might or might not include that vertex point.
For example, suppose that geography_expression_1 is POINT(1 1) and geography_expression_2 is LINESTRING(1 0, 1 2). In this case, geography_expression_1 is on the boundary of geography_expression_2 but is not a vertex of it.
In this example, the expected output is POINT(1 1), but the actual output might be an empty geography (represented by NULL).
To help to detect and work around these cases, one potential idea is to use ST_DWITHIN to determine if the minimum distance between the two input objects is 0. For example, you can check if a point lies on top of a LineString by checking if the minimum distance between the two objects is zero:
SELECT TO_GEOGRAPHY('POLYGON((0 0, 1 0, 2 1, 1 2, 2 3, 1 4, 0 4, 0 0))') AS polygon, TO_GEOGRAPHY('POINT(0 2)') AS point, ST_DWITHIN(polygon, point, 0) AS point_is_on_top_of_polygon, ST_INTERSECTION(polygon, point);Copy
This statement produces the following output:
+--------------------------------------------+------------+----------------------------+---------------------------------+ | POLYGON | POINT | POINT_IS_ON_TOP_OF_POLYGON | ST_INTERSECTION(POLYGON, POINT) | |--------------------------------------------+------------+----------------------------+---------------------------------| | POLYGON((0 0,1 0,2 1,1 2,2 3,1 4,0 4,0 0)) | POINT(0 2) | True | NULL | +--------------------------------------------+------------+----------------------------+---------------------------------+Copy
The function is not guaranteed to produce normalized and/or minimal results. For example, an output could consist of a LineString containing several points that actually forms just one straight segment.
The following example returns a GEOGRAPHY object that represents the intersection of two input GEOGRAPHY objects:
ALTER SESSION SET GEOGRAPHY_OUTPUT_FORMAT = 'WKT'; SELECT ST_INTERSECTION( TO_GEOGRAPHY('POLYGON((0 0, 1 0, 2 1, 1 2, 2 3, 1 4, 0 4, 0 0))'), TO_GEOGRAPHY('POLYGON((3 0, 3 4, 2 4, 1 3, 2 2, 1 1, 2 0, 3 0))')) AS intersection_of_objects;Copy
This example produces the following output:
+-----------------------------------------------------------------------------------------------------------------------------------------+ | INTERSECTION_OF_OBJECTS | |-----------------------------------------------------------------------------------------------------------------------------------------| | MULTIPOLYGON(((1.5 0.5000571198,2 1,1.5 1.500171359,1 1,1.5 0.5000571198)),((1.5 2.500285599,2 3,1.5 3.500399839,1 3,1.5 2.500285599))) | +-----------------------------------------------------------------------------------------------------------------------------------------+Copy
The following images illustrate the differences in the areas that represent the input and output objects:
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4