Details of spatial SQL functions
ST_AREA
ST_AREA returns the Cartesian or geodesic area of a geometry.
The area is calculated according to the unit of measurement specified when defining the geometry.
Format:
<Area> = ST_AREA(<Geometry>)
Where:
- <Area>: Area calculated in the specified unit.
- <Geometry>: Geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
The unit (meters, feet, etc.) of the returned value depends on the Spatial Reference System (SRS) used in the definition of the geometry.
Example: This example returns the area of each plot in the "Parcel" table.
SELECT ST_AREA(GeoParcel) AS CityArea
FROM Parcels
ST_CONTAINS
ST_CONTAINS determines if a geometry contains another geometry (in 2D or geographic coordinates), i.e. if all points of the geometry to be compared lie inside the reference geometry.
Format:
<Status> = ST_CONTAINS(<Reference geometry>, <Geometry to compare>)
Where:
- <Status>: Return value (boolean):
- True if the geometry to be compared is contained in the reference geometry.
- False otherwise.
- <Reference geometry>: Reference geometry in the comparison. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
- <Geometry to compare>: Geometry to be compared with the reference geometry. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
Note: The two geometries must be of the same type (two geometries in 2D or geographic coordinates).
Example: This example returns the name of the city containing the parcel defined by the RefPark geometry (search for the city in which a parcel is located).
SELECT CityID, CityName FROM Cities
WHERE ST_CONTAINS(GeoCity, RefPark)
ST_COVEREDBY
ST_COVEREDBY determines whether every point in the first geometry lies inside the second geometry, or if at least one of the points in the first geometry is not inside the second geometry.
Note: This function is equivalent to ST_COVERS, but with the parameters in reverse order.
Format:
<Status> = ST_COVEREDBY(<Geometry 1>, <Geometry 2>)
where:
- <Status>: Return value (boolean):
- True if every point in the first geometry lies inside the second geometry.
- False if at least one of the points in the first geometry is not inside the second geometry.
- <Geometry 1>: First geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
- <Geometry 2>: Second geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
Note: The two geometries must be of the same type (two geometries in 2D or geographic coordinates).
Example: Find all the towns within or on the border of a state.
The town and the state are represented by the "GeoTown" and the "GeoRefState" geometries, respectively.
SELECT TownName FROM TownMap
WHERE ST_COVEREDBY(GeoTown, GeoRefState)
ST_COVERS
ST_COVERS determines whether every point in the second geometry lies inside the first geometry, or if at least one of the points in the second geometry is not inside the first geometry.
Note: This function is equivalent to ST_COVEREDBY, but with the parameters in reverse order.
Format:
<Status> = ST_COVERS(<Geometry 1>, <Geometry 2>)
where:
- <Status>: Return value (boolean):
- True if every point in the second geometry lies inside the first geometry.
- False if at least one of the points in the second geometry is not inside the first geometry.
- <Geometry 1>: First geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
- <Geometry 2>: Second geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
Note: The two geometries must be of the same type (two geometries in 2D or geographic coordinates).
Example: Find all the towns within or on the border of a state.
The town and the state are represented by the "GeoTown" and the "GeoRefState" geometries, respectively.
SELECT TownName FROM TownMap
WHERE ST_COVERS(GeoRefState, GeoTown)
ST_CROSSES
ST_CROSSES determines if two geometries have some, but not all their interior points in common.
The intersection represented by these points in common must meet two conditions:
- the dimension of this intersection must be smaller than the dimensions of the geometries being compared.
- this intersection must not be equal to one of the geometries being compared.
Format:
<Status> = ST_CROSSES(<Geometry 1>, <Geometry 2>)
Where:
- <Status>: Return value (boolean):
- True if one of the geometries has some of its interior points in common with the second geometry, and if the conditions are met.
- False otherwise. In this case, either the 2 conditions are not met, or there are no common interior points.
- <Geometry 1>: First geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
- <Geometry 2>: Second geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
Note: The two geometries must be of the same type (two geometries in 2D or geographic coordinates).
Example: Find all countries crossed by a river.
The river and the country are represented by the "GeoRefRiver" and "GeoCountry" geometries, respectively.
SELECT CountryName FROM WorldCountriesMap
WHERE ST_CROSSES(GeoRefRiver, GeoCountry)
ST_DIFFERENCE
ST_DIFFERENCE returns a geometry corresponding to the difference between the two geometries passed as parameters.
The smallest geometry is subtracted from the largest, regardless of the order in which they were passed.
Format:
<Resulting geometry> = ST_DIFFERENCE(<Geometry 1>, <Geometry 2>)
Where:
- <Resulting geometry>: Geometry that represents the difference between the two geometries.
- <Geometry 1>: First geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
- <Geometry 2>: Second geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
Note: The two geometries must be of the same type (two geometries in 2D or geographic coordinates).
Example: Find the geometry that represents the remaining area after selling a parcel:
SELECT ST_DIFFERENCE(Land1, ParcelSold)
AS RemainingArea
FROM ParcelsPlot
WHERE LandID=1
ST_DISTANCE
ST_DISTANCE calculates the minimum Cartesian or geodesic distance between two geometries. This distance is expressed in the unit of measurement specified when defining the geometry.
Format:
<Distance> = ST_DISTANCE(<Geometry 1>, <Geometry 2>)
Where:
- <Distance>: Distance calculated in the specified unit.
- <Geometry 1>: First geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
- <Geometry 2>: Second geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
The unit (meters, degrees, etc.) of the returned value depends on the Spatial Reference System (SRS) used in the definition of the geometry.
Example 1: This example returns the distances between all the cities in the "Maps" table and the "GeoRef" reference city.
SELECT ST_DISTANCE(GeoCity, GeoRef) AS Distance FROM Maps
: This example returns the distance between the 2 specified geometries. Geometries are defined using polygons.
SELECT
ST_DISTANCE(ST_GEOMFROMTEXT('POLYGON((-3 5,4 5.03,4 1.06,-3.03 0.97,-5.06 3.06,-3.09 5,-3.09 5,-3 5))'),
ST_GEOMFROMTEXT('POLYGON((4.03 9.03,7 9.11,6.94 7.06,4 7.97,3.97 9.06,4.03 9.03))'))
AS dist_2d
ST_EQUALS
ST_EQUALS determines if two geometries (in 2D or geographic coordinates) are equal, i.e. if they have the same dimensions and their points occupy the same space.
Remark: The coordinates of the geometries can be different (they may not be located at the same position).
Format:
<Status> = ST_EQUALS(<Geometry 1>, <Geometry 2>)
Where:
- <Status>: Return value (boolean):
- True if the two geometries are equal,
- False otherwise.
- <Geometry 1>: First geometry to be manipulated. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
- <Geometry 2>: Second geometry to be manipulated. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
Note: The two geometries must be of the same type (two geometries in 2D or geographic coordinates).
Example: This example returns the list of parcels that are the same size and occupy the same space as the reference parcel defined by the "RefPark" geometry.
SELECT ParcelID, ParcelLocation FROM Parcels
WHERE ST_EQUALS(DefParcel, RefPark)
ST_GEOMFROMTEXT
ST_GEOMFROMTEXT constructs a two-dimensional geometry from a text representation.
Format:
<Geometry> = ST_GEOMFROMTEXT(<String>)
Where:
- <Geometry>: Two-dimensional geometry.
- <String>: Text representation of a geometry.
Example: This example calculates the distance between two 2-dimensional geometries.
The 2 geometries are polygons represented by a series of points described in a string. The string describing the polygon is converted into a geometry using the SQL ST_GEOMFROMTEXT function.
SELECT
ST_DISTANCE(ST_GEOMFROMTEXT('POLYGON((-3 5,4 5.03,4 1.06,-3.03 0.97,-5.06 3.06,-3.09 5,-3.09 5,-3 5))'),
ST_GEOMFROMTEXT('POLYGON((4.03 9.03,7 9.11,6.94 7.06,4 7.97,3.97 9.06,4.03 9.03))'))
AS dist_2d
ST_INTERSECTS
ST_INTERSECTS determines if 2 geometries (in 2D or geographic coordinates) have points in common. This command calculates the common area (intersection) between the two geometries.
Format:
<Status> = ST_INTERSECTS(<Geometry 1>, <Geometry 2>)
Where:
- <Status>: Return value (boolean):
- True if the geometries have at least one point in common,
- False otherwise.
- <Geometry 1>: First geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
- <Geometry 2>: Second geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
Note: The two geometries must be of the same type (two geometries in 2D or geographic coordinates).
Example: This example returns a list of cities represented by the "GeoCity" geometry that have any point in common with the "GeoRef" reference geometry.
SELECT CityName FROM Maps WHERE ST_INTERSECTS(GeoCity, GeoRef)
ST_LENGTH
ST_LENGTH returns the length (in meters) of a linear geometry. A linear geometry (or linestring) is a geometry composed of several connected points. The geometry must be composed of at least two points. The set of points must not form a closed geometry, i.e. a polygon.
The length is obtained from the sum of each line making up the linestring.
Format:
<Length> = ST_LENGTH(<Geometry>)
Where:
- <Length>: Length calculated in meters.
- <Geometry>: Linestring to be manipulated. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
Note: If the geometry passed as parameter is a polygon or a single point, the function will return 0.
Example: Return the length of all rivers in France.
SELECT RiverName, ST_LENGTH(GeoRiver) AS RiverLength
FROM RiverMap WHERE Country='France'
ST_MAKEPOLYGON
ST_MAKEPOLYGON generates a geometry of type Polygon using the description of a geometry.
This command can take a second optional parameter describing the holes in the resulting geometry. These holes are defined using an array of linestrings (one hole corresponds to one linestring).
Format:
<Resulting geometry> = ST_MAKEPOLYGON(<Geometry to analyze>[, <Interior rings geometry] [, <SRID>])
Where:
- <Resulting geometry>: Geometry corresponding to the resulting polygon.
- <Geometry to analyze>: Geometry composed of lines without the optional holes. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
- <Interior rings geometry> (optional): Geometry describing the holes to be integrated into the geometry to be analyzed. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
- <SRID> (optional): In the case of a geometry in geographic coordinates, indicates the spatial reference system (SRID) defined in the description of the geometry.
Example: Build a rectangular polygon containing a hole.
SELECT ST_MAKEPOLYGON('LINESTRING(0 8, 6 8, 6 0, 0 0, 0 8)', 'LINESTRING(3 3, 3 5, 4 5,4 3, 3 3)');
ST_OVERLAPS
ST_OVERLAPS determines if two geometries overlap and if their intersection has the same dimension as the original geometries. None of the geometries should be a subset of the other.
Format:
<Status> = ST_OVERLAPS(<Geometry 1>, <Geometry 2>)
Where:
- <Status>: Return value (boolean):
- True if the resulting geometry overlaps the geometries passed as parameters.
- False otherwise.
- <Geometry 1>: First geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
- <Geometry 2>: Second geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
Note: The two geometries must be of the same type (two geometries in 2D or geographic coordinates).
Example: Return a list of overlapping geometries.
SELECT ListObjects.NameObject1, ListObjectsRef.NameObjectRef
FROM ListObjects, ListObjectsRef
WHERE ListObjectsRef.FamilyID = ListObjects.FamilyID AND
ST_OVERLAPS(ListObjectsRef.GeoRef, ListObjects.geoObject)
ST_PERIMETER
ST_PERIMETER returns the perimeter (in meters) of the specified geometry. The perimeter is calculated by adding each segment that makes up the boundary of the geometry.
Format:
<Perimeter> = ST_PERIMETER(<Geometry>)
Where
- <Perimeter>: Perimeter calculated in meters.
- <Geometry>: Geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
Example: Return the perimeter of each parcel in plot number 15.
SELECT ParcelID, ST_PERIMETER(GeoParcel) AS ParcelPerimeter FROM Parcels WHERE PlotID=15
ST_SIMPLIFY
ST_SIMPLIFY returns a 'simplified' version of the specified geometry. The new geometry is calculated using the Ramer-Douglas-Peucker algorithm, with the specified tolerance.
Format:
<Resulting geometry> = ST_SIMPLIFY(<Geometry>[, <Tolerance>)
Where:
- <Resulting geometry>: Simplified geometry.
- <Geometry>: Geometry used. This geometry can be composed of several geometries. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
- <Tolerance> (optional real): Represents the tolerance threshold in meters for the calculation of simplified geometry. Geometries that are part of the original geometry, but with a length less than the tolerance threshold, will not be included in the simplified version.
Example: This example returns a simplified representation of an object. Small geometries with a length of less than 0.05m will not be represented in the resulting geometry.
SELECT ObjectID, ST_SIMPLIFY(DetailedObject, 0.05) AS SimplifiedObject FROM ListObjects
ST_SYMDIFFERENCE
ST_SYMDIFFERENCE returns a geometry that represents the difference of two geometries passed as parameters.
In the resulting geometry, all points common to both geometries are excluded.
This operation corresponds to an "exclusive union".
Format:
<Resulting geometry> = ST_SYMDIFFERENCE(<Geometry 1>, <Geometry 2>)
Where:
- <Resulting geometry>: Geometry that represents the symmetric difference.
- <Geometry 1>: First geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
- <Geometry 2>: Second geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
Note: The two geometries must be of the same type (two geometries in 2D or geographic coordinates).
Example: Return the geometry corresponding to the exclusive union of the two geometries making up product number 1.
SELECT ST_SYMDIFFERENCE(GeometryObject1, GeometryObject2) AS ResultingObject
FROM Objects
WHERE ProductID=1
ST_TOUCHES
ST_TOUCHES determines if two geometries have at least one boundary point in common.
Please note that the geometries must not have any interior points in common.
Format:
<Status> = ST_TOUCHES(<Geometry 1>, <Geometry 2>)
Where:
- <Status>: Return value (boolean):
- True if the two geometries have at least one boundary point in common.
- False otherwise. In this case:
- there are not boundary points in common.
- one of the interior points of one of the geometries intersects the other geometry.
- <Geometry 1>: First geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
- <Geometry 2>: Second geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
Note: The two geometries must be of the same type (two geometries in 2D or geographic coordinates).
Example: Return a list of countries that share borders with a reference country.
"GeoCountryRef" represents the country for which we want to find border countries. "GeoCountry" represents the country to be compared against the reference.
SELECT CountryName FROM ListCountries
WHERE ST_TOUCHES(GeoCountryRef, GeoCountry)
ST_UNION
ST_UNION returns a geometry corresponding to the union between two geometries.
The resulting geometry corresponds to the merger of the two original geometries. No particular process is applied to common points.
Format:
<Resulting geometry> = ST_UNION(<Geometry 1>, <Geometry 2>)
Where:
- <Resulting geometry>: Geometry corresponding to the union of the two geometries.
- <Geometry 1>: First geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
- <Geometry 2>: Second geometry to be manipulated. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
Note: The two geometries must be of the same type (two geometries in 2D or geographic coordinates).
Example: Return a geometry that represents the resulting plot of land after purchasing and joining two contiguous parcels.
SELECT NewParcelID, ST_UNION(Parcel1, Parcel2) AS ResultingPlot
FROM ParcelsPlot
WHERE LandID=1
ST_WITHIN
ST_WITHIN determines if the first geometry is contained within the second geometry, i.e. if all the points in the first geometry lie inside the second geometry.
Format:
<Status> = ST_WITHIN(<Geometry 1>, <Geometry 2>)
Where
- <Status>: Return value (boolean):
- True if the first geometry is within the second geometry.
- False otherwise. This means that at least one of the points in the first geometry is not within in the second geometry.
- <Geometry 1>: First geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
- <Geometry 2>: Second geometry used. This geometry can be:
- an item of type "Geometric data" or "Geographic data".
- a string containing the geometry description.
Note: The two geometries must be of the same type (two geometries in 2D or geographic coordinates).
Example: Return the list of ships passing through the Panama Canal.
"PosGeoShip" represents the ship's real-time position, "GeoPanamaCanal" is a geometry that corresponds to a geographic representation of the Panama Canal.
SELECT ShipName FROM ListShips
WHERE ST_WITHIN(PosGeoShip, GeoPanamaCanal)