Spatial queries in PostGIS
Spatial queries are an essential aspect of Geographic Information Systems (GIS) and spatial data analysis. They enable us to extract valuable insights and information from spatial datasets by filtering, selecting, and analyzing spatial data. However, writing spatial queries can be challenging and time-consuming, especially for those who are new to GIS or spatial data analysis. A spatial queries cheat sheet can help to simplify this process and make it easier to write spatial queries by providing a quick reference guide to the most commonly used spatial SQL functions and their syntax. In this blog post, we will provide a comprehensive spatial queries cheat sheet that covers the most common spatial SQL functions in PostGIS, along with examples of their usage. Whether you’re a GIS professional or a data analyst, this cheat sheet will help you to write spatial queries more efficiently and accurately.
Want to stay ahead of the Geospatial curve? Listen to our podcast!
ST_Intersects in PostGIS
ST_Intersects returns true if two geometries intersect each other. It takes two geometry inputs and returns a boolean (true/false) value. The syntax for using ST_Intersects is as follows:
ST_Intersects(geometry1, geometry2)
Here, geometry1 and geometry2 are the two geometries being compared. They can be any type of PostGIS geometry, such as points, lines, polygons, or multi-geometries. The ST_Intersects function returns true if the two geometries have at least one point in common or if they share a common boundary.
For example, the following query returns all the buildings from a dataset that intersect with a given park:
SELECT *
FROM buildings
WHERE ST_Intersects(buildings.geometry, (SELECT geometry FROM parks WHERE name='Central Park'));
In this query, the ST_Intersects function is used to check if the geometry of each building in the dataset intersects with the geometry of Central Park. The WHERE clause filters the results to only include buildings that intersect with the park. This type of query can be used to perform spatial queries and analysis in PostGIS.
ST_Buffer in PostGIS
ST_Buffer creates a buffer around a given geometry with a specified distance. It is commonly used to create a buffer around a point, line, or polygon to represent an area of interest or proximity. The buffer is represented as a new geometry object, which can be used for further spatial analysis or visualization.
The syntax for using ST_Buffer is as follows:
ST_Buffer(geometry, radius, [options])
Here, geometry is the input geometry for which the buffer needs to be created, the radius is the distance in the units of the input geometry’s coordinate reference system, and options are optional parameters that can be used to control the number of segments used to represent the buffer’s boundary and the style of the buffer’s boundary.
For example, the following query creates a buffer around a point representing a city center with a radius of 500 meters and returns all the buildings in the buffer:
SELECT *
FROM buildings
WHERE ST_Intersects(buildings.geometry, ST_Buffer(ST_SetSRID(ST_MakePoint(-74.0060, 40.7128), 4326), 500));
In this query, ST_SetSRID is used to create a point geometry at (-74.0060, 40.7128) in the WGS 84 coordinate reference system (SRID 4326), and ST_Buffer is used to create a buffer around the point with a radius of 500 meters. The ST_Intersects function is then used to find all the buildings that intersect with the buffer. This type of query can be used to find all the features within a certain distance of a point, line, or polygon in a spatial dataset.
ST_Distance in PostGIS
ST_Distance calculates the distance between two geometries. It can be used to find the distance between two points, the length of a line, or the distance between two polygons. The distance is returned in the units of the input geometries’ coordinate reference system.
The syntax for using ST_Distance is as follows:
ST_Distance(geometry1, geometry2)
Here, geometry1 and geometry2 are the two geometries for which the distance needs to be calculated. They can be any type of PostGIS geometry, such as points, lines, polygons, or multi-geometries. The ST_Distance function returns the distance between the two geometries in the units of the coordinate reference system.
For example, the following query calculates the distance between two points in a dataset:
SELECT ST_Distance(ST_SetSRID(ST_MakePoint(-74.0060, 40.7128), 4326), ST_SetSRID(ST_MakePoint(-73.9712, 40.7831), 4326));
In this query, ST_SetSRID is used to create two-point geometries in the WGS 84 coordinate reference system (SRID 4326), and ST_Distance is used to calculate the distance between the two points. The result is returned in meters, which is the unit of the WGS 84 coordinate reference system. This type of query can be used to find the distance between any two geometries in a spatial dataset.
ST_Union in PostGIS
ST_Union merges several geometries into a single geometry. It can be used to merge several polygons into a single polygon or to merge several lines into a single line. The resulting geometry is represented as a new geometry object, which can be used for further spatial analysis or visualization.
The syntax for using ST_Union is as follows:
ST_Union(geometry1, geometry2, ...)
Here, geometry1, geometry2, and so on, are the input geometries that need to be merged. They can be any type of PostGIS geometry, such as points, lines, polygons, or multi-geometries. The ST_Union function returns a new geometry object that represents the merged geometries.
For example, the following query merges several polygons from a dataset into a single polygon:
SELECT ST_Union(geometry)
FROM neighborhoods
WHERE city = 'New York';
In this query, the ST_Union function is used to merge all the polygons in the neighborhoods dataset that belong to the city of New York. The resulting geometry represents the union of all the polygons and can be used to perform further spatial analysis or visualization. This type of query can be used to merge geometries in a spatial dataset and to simplify the representation of complex features.
ST_Transform in PostGIS
ST_Transform transforms a geometry from one spatial reference system (SRS) to another. It is often used to project geometries to a different SRS to perform spatial analysis or visualization. The function takes two arguments: the input geometry and the target SRS as a PostGIS Spatial Reference Identifier (SRID).
The syntax for using ST_Transform is as follows:
ST_Transform(geometry, srid)
Here, geometry is the input geometry that needs to be transformed, and srid is the target SRS as a PostGIS SRID. The function returns the transformed geometry object in the target SRS.
For example, the following query transforms a set of point geometries from the WGS 84 coordinate reference system (SRID 4326) to the Universal Transverse Mercator (UTM) projection for the United States (SRID 26918):
SELECT ST_Transform(geometry, 26918)
FROM points
WHERE state = 'New York';
In this query, the ST_Transform function is used to transform all the point geometries in the points dataset that belongs to the state of New York. The resulting geometries are represented in the UTM projection for the United States (SRID 26918) and can be used to perform further spatial analysis or visualization in that projection. This type of query can be used to transform geometries between different SRSs in a spatial dataset.
ST_Centroid in PostGIS
ST_Centroid returns the centroid of a geometry. The centroid is the geometric center of the geometry, calculated as the arithmetic mean of all the points in the geometry. It is often used to calculate the center of a polygon or to represent a point feature by the centroid of its geometry.
The syntax for using ST_Centroid is as follows:
ST_Centroid(geometry)
Here, geometry is the input geometry for which the centroid needs to be calculated. It can be any type of PostGIS geometry, such as points, lines, polygons, or multi-geometries. The ST_Centroid function returns a new point geometry object that represents the centroid of the input geometry.
For example, the following query calculates the centroid of a polygon in a dataset:
SELECT ST_Centroid(geometry)
FROM neighborhoods
WHERE name = 'Upper East Side';
In this query, the ST_Centroid function is used to calculate the centroid of the polygon in the neighborhoods dataset that represents the Upper East Side neighborhood. The resulting geometry represents the centroid of the polygon and can be used to represent the location of the neighborhood. This type of query can be used to calculate the centroids of polygons and to represent point features by the centroids of their geometries.
ST_GeomFromText in PostGIS
ST_GeomFromText creates a geometry from a well-known text (WKT) representation. WKT is a text-based format for representing geometric objects in a human-readable way. The function takes a WKT string as input and returns a new geometry object that represents the geometric object specified in the WKT string.
The syntax for using ST_GeomFromText is as follows:
ST_GeomFromText(wkt, srid)
Here, wkt is the WKT string that represents the geometry, and srid is the spatial reference identifier (SRID) for the geometry. The SRID is an optional argument and can be used to specify the coordinate reference system (CRS) of the geometry. If the SRID is not specified, the default SRID (4326) will be used.
For example, the following query creates a point geometry from a WKT string:
SELECT ST_GeomFromText('POINT(-74.0060 40.7128)', 4326);
In this query, the ST_GeomFromText function is used to create a point geometry from the WKT string ‘POINT(-74.0060 40.7128)’. The geometry represents a point at longitude -74.0060 and latitude 40.7128 in the WGS 84 coordinate reference system (SRID 4326). The resulting geometry object can be used for further spatial analysis or visualization. This type of query can be used to create geometry objects from text-based representations of geometric objects.
ST_Area in PostGIS
ST_Area calculates the area of a geometry. It can be used to calculate the area of a polygon or to filter geometries based on their area. The area is returned in the square units of the input geometry’s coordinate reference system.
The syntax for using ST_Area is as follows:
ST_Area(geometry)
Here, geometry is the input geometry for which the area needs to be calculated. It can be any type of PostGIS geometry, such as points, lines, polygons, or multi-geometries. The ST_Area function returns the area of the geometry in the square units of the coordinate reference system.
For example, the following query calculates the area of a polygon in a dataset:
SELECT ST_Area(geometry)
FROM neighborhoods
WHERE name = 'Upper East Side';
In this query, the ST_Area function is used to calculate the area of the polygon in the neighborhoods dataset that represents the Upper East Side neighborhood. The resulting value represents the area of the polygon and can be used to perform further spatial analysis or visualization. This type of query can be used to calculate the areas of polygons and to filter geometries based on their area.
ST_Length in PostGIS
ST_Length calculates the length of a geometry. It can be used to calculate the length of a line or to filter geometries based on their length. The length is returned in the units of the input geometry’s coordinate reference system.
The syntax for using ST_Length is as follows:
ST_Length(geometry)
Here, geometry is the input geometry for which the length needs to be calculated. It can be any type of PostGIS geometry, such as points, lines, polygons, or multi-geometries. The ST_Length function returns the length of the geometry in the units of the coordinate reference system.
For example, the following query calculates the length of a line in a dataset:
SELECT ST_Length(geometry)
FROM roads
WHERE name = 'Broadway';
In this query, the ST_Length function is used to calculate the length of the line in the roads dataset that represents Broadway. The resulting value represents the length of the line and can be used to perform further spatial analysis or visualization. This type of query can be used to calculate the lengths of lines and to filter geometries based on their length.
ST_Simplify in PostGIS
ST_Simplify simplifies the geometry by removing unnecessary vertices while preserving the overall shape of the geometry. It can be used to reduce the complexity of a polygon or line to improve performance or to make it easier to visualize. The function takes two arguments: the input geometry and a tolerance value, which specifies the maximum allowable deviation from the original geometry.
The syntax for using ST_Simplify is as follows:
ST_Simplify(geometry, tolerance)
Here, geometry is the input geometry that needs to be simplified, and tolerance is the maximum allowable deviation from the original geometry. The tolerance value is specified in the units of the input geometry’s coordinate reference system.
For example, the following query simplifies a polygon in a dataset with a tolerance of 0.01:
SELECT ST_Simplify(geometry, 0.01)
FROM neighborhoods
WHERE name = 'Upper East Side';
In this query, the ST_Simplify function is used to simplify the polygon in the neighborhoods dataset that represents the Upper East Side neighborhood. The tolerance value of 0.01 specifies the maximum allowable deviation from the original geometry. The resulting geometry object represents the simplified version of the original geometry and can be used for further spatial analysis or visualization. This type of query can be used to simplify the geometries in a spatial dataset to reduce complexity and improve performance.
ST_ConvexHull in PostGIS
ST_ConvexHull calculates the minimum convex polygon that contains a set of input geometries. It can be used to calculate the convex hull of a set of points or to generate a polygon that encompasses a set of polygons. The resulting geometry is represented as a new geometry object, which can be used for further spatial analysis or visualization.
The syntax for using ST_ConvexHull is as follows:
ST_ConvexHull(geometry)
Here, geometry is the input geometry or set of geometries for which the convex hull needs to be calculated. It can be any type of PostGIS geometry, such as points, lines, polygons, or multi-geometries. The ST_ConvexHull function returns a new geometry object that represents the minimum convex polygon that contains the input geometries.
For example, the following query calculates the convex hull of a set of points in a dataset:
SELECT ST_ConvexHull(ST_Collect(geometry))
FROM restaurants
WHERE cuisine = 'Italian';
In this query, the ST_Collect function is used to collect all the point geometries in the restaurant dataset that represent Italian restaurants. The resulting geometry represents the set of Italian restaurants and can be used to calculate the minimum convex polygon that contains all the restaurants using ST_ConvexHull. This type of query can be used to generate the minimum convex polygon that contains a set of geometries and to calculate the boundaries of a set of points or polygons.
ST_Collect in PostGIS
ST_Collect aggregates multiple geometries into a single geometry object. It can be used to merge multiple geometries of the same type into a single geometry, such as merging multiple points into multi-point or multiple polygons into a multi-polygon. The resulting geometry is represented as a new geometry object, which can be used for further spatial analysis or visualization.
The syntax for using ST_Collect is as follows:
ST_Collect(geometry1, geometry2, ...)
Here, geometry1, geometry2, and so on, are the input geometries that need to be merged. They can be any type of PostGIS geometry, such as points, lines, polygons, or multi-geometries. The ST_Collect function returns a new geometry object that represents the collection of the input geometries.
For example, the following query collects all the point geometries in a dataset into a single multi-point geometry:
SELECT ST_Collect(geometry)
FROM restaurants
WHERE cuisine = 'Italian';
In this query, the ST_Collect function is used to collect all the point geometries in the restaurants dataset that represent Italian restaurants. The resulting geometry represents the collection of all the Italian restaurants and can be used to perform further spatial analysis or visualization. This type of query can be used to aggregate geometries of the same type into a single geometry object and to simplify the representation of complex spatial features.
ST_Dump in PostGIS
ST_Dump takes a multi-geometry as input and returns a set of single geometries that make up the multi-geometry. It can be used to extract individual geometries from a multi-geometry for further analysis or visualization.
The syntax for using ST_Dump is as follows:
ST_Dump(geometry)
Here, geometry is the multi-geometry from which the individual geometries need to be extracted. The ST_Dump function returns a set of individual geometries that make up the input multi-geometry.
For example, the following query extracts all the polygons in a multi-polygon geometry:
SELECT (ST_Dump(geometry)).geom
FROM counties
WHERE state = 'New York';
In this query, the ST_Dump function is used to extract all the individual polygons in the counties dataset that belong to the state of New York. The resulting geometries are represented as a set of individual polygon geometries and can be used for further spatial analysis or visualization. This type of query can be used to extract individual geometries from a multi-geometry and to perform further analysis or visualization on the individual geometries.
ST_Covers in PostGIS
ST_Covers tests whether one geometry completely covers another geometry. It returns true if the first geometry completely covers the second geometry, and false otherwise. The function is typically used to check whether a polygon completely covers another polygon or a point lies inside a polygon.
The syntax for using ST_Covers is as follows:
ST_Covers(geometry1, geometry2)
Here, geometry1 is the first geometry that needs to be tested, and geometry2 is the second geometry that is being tested for coverage. Both geometries can be any type of PostGIS geometry, such as points, lines, polygons, or multi-geometries. The ST_Covers function returns true if the first geometry completely covers the second geometry and false otherwise.
For example, the following query checks whether a polygon covers a point in a dataset:
SELECT ST_Covers(polygon, point)
FROM counties
WHERE name = 'New York';
In this query, the ST_Covers function is used to check whether the polygon in the counties dataset that represents New York County completely covers the point geometry. The resulting value is true if the polygon completely covers the point and false otherwise. This type of query can be used to test whether one geometry completely covers another geometry and to perform spatial filtering based on coverage.
ST_Touches in PostGIS
ST_Touches tests whether two geometries touch at any point but do not overlap. It returns true if the two geometries touch at any point, such as a boundary point, but do not overlap anywhere, and false otherwise. The function is typically used to check whether a polygon touches another polygon or a point touches a polygon boundary.
The syntax for using ST_Touches is as follows:
ST_Touches(geometry1, geometry2)
Here, geometry1 and geometry2 are the two geometries that need to be tested. Both geometries can be any type of PostGIS geometry, such as points, lines, polygons, or multi-geometries. The ST_Touches function returns true if the two geometries touch at any point but do not overlap and false otherwise.
For example, the following query checks whether a point touches a polygon in a dataset:
SELECT ST_Touches(point, polygon)
FROM neighborhoods
WHERE name = 'Upper East Side';
In this query, the ST_Touches function is used to check whether the point geometry touches the polygon in the neighborhoods dataset that represents the Upper East Side neighborhood. The resulting value is true if the point touches the polygon at any point but does not overlap, and false otherwise. This type of query can be used to test whether two geometries touch at any point but do not overlap and to perform spatial filtering based on touch.
ST_MakeValid in PostGIS
ST_MakeValid takes an input geometry and attempts to create a valid geometry by removing or correcting any invalid components. It can be used to correct or repair invalid geometries, such as those with self-intersecting boundaries or incorrect topology. The function returns a new geometry object that represents the corrected or repaired version of the input geometry.
The syntax for using ST_MakeValid is as follows:
ST_MakeValid(geometry)
Here, geometry is the input geometry that needs to be corrected or repaired. It can be any type of PostGIS geometry, such as points, lines, polygons, or multi-geometries. The ST_MakeValid function returns a new geometry object that represents the corrected or repaired version of the input geometry.
For example, the following query attempts to create a valid polygon from an invalid polygon in a dataset:
SELECT ST_MakeValid(geometry)
FROM neighborhoods
WHERE name = 'Upper West Side';
In this query, the ST_MakeValid function is used to attempt to create a valid polygon from the polygon in the neighborhoods dataset that represents the Upper West Side neighborhood. The resulting geometry represents the corrected or repaired version of the original geometry and can be used for further spatial analysis or visualization. This type of query can be used to correct or repair invalid geometries and to ensure that spatial analysis and visualization functions work correctly.
ST_AsGeoJSON in PostGIS
ST_AsGeoJSON converts a geometry into a GeoJSON string. It can be used to output a geometry in GeoJSON format, which is a popular format for representing and exchanging geospatial data. The function returns a string that represents the input geometry in GeoJSON format.
The syntax for using ST_AsGeoJSON is as follows:
ST_AsGeoJSON(geometry)
Here, geometry is the input geometry that needs to be converted to GeoJSON format. It can be any type of PostGIS geometry, such as points, lines, polygons, or multi-geometries. The ST_AsGeoJSON function returns a string that represents the input geometry in GeoJSON format.
For example, the following query converts a polygon in a dataset to GeoJSON format:
SELECT ST_AsGeoJSON(geometry)
FROM neighborhoods
WHERE name = 'Chelsea';
In this query, the ST_AsGeoJSON function is used to convert the polygon in the neighborhoods dataset that represents the Chelsea neighborhood to GeoJSON format. The resulting string represents the polygon in GeoJSON format and can be used for further processing or visualization. This type of query can be used to output geometries in GeoJSON format and to exchange spatial data with other systems that use the GeoJSON format.
ST_Contains in PostGIS
ST_Contains tests whether one geometry contains another geometry. It returns true if the first geometry completely contains the second geometry, and false otherwise. The function is typically used to check whether a polygon contains a point or another polygon.
The syntax for using ST_Contains is as follows:
ST_Contains(geometry1, geometry2)
Here, geometry1 is the first geometry that needs to be tested, and geometry2 is the second geometry that is being tested for containment. Both geometries can be any type of PostGIS geometry, such as points, lines, polygons, or multi-geometries. The ST_Contains function returns true if the first geometry completely contains the second geometry and false otherwise.
For example, the following query checks whether a polygon contains a point in a dataset:
SELECT ST_Contains(polygon, point)
FROM counties
WHERE name = 'New York';
In this query, the ST_Contains function is used to check whether the polygon in the counties dataset that represents New York County contains the point geometry. The resulting value is true if the polygon contains the point completely and false otherwise. This type of query can be used to test whether one geometry contains another geometry and to perform spatial filtering based on containment.
ST_Disjoint in PostGIS
ST_Disjoint tests whether two geometries are completely disjoint, i.e., they do not share any common points. It returns true if the two geometries do not share any common points and false otherwise. The function is typically used to check whether two geometries do not overlap or touch.
The syntax for using ST_Disjoint is as follows:
ST_Disjoint(geometry1, geometry2)
Here, geometry1 and geometry2 are the two geometries that need to be tested. Both geometries can be any type of PostGIS geometry, such as points, lines, polygons, or multi-geometries. The ST_Disjoint function returns true if the two geometries do not share any common points and false otherwise.
For example, the following query checks whether a point is disjoint from a polygon in a dataset:
SELECT ST_Disjoint(point, polygon)
FROM neighborhoods
WHERE name = 'Upper East Side';
In this query, the ST_Disjoint function is used to check whether the point geometry is disjoint from the polygon in the neighborhoods dataset that represents the Upper East Side neighborhood. The resulting value is true if the point is disjoint from the polygon, i.e., they do not share any common points, and false otherwise. This type of query can be used to test whether two geometries are completely disjoint and to perform spatial filtering based on disjointness.
ST_Equals in PostGIS
ST_Equals tests whether two geometries are exactly equal. It returns true if the two geometries are exactly the same, i.e., they have the same geometry type, coordinates, and attributes, and false otherwise. The function is typically used to check whether two geometries are identical.
The syntax for using ST_Equals is as follows:
ST_Equals(geometry1, geometry2)
Here, geometry1 and geometry2 are the two geometries that need to be tested. Both geometries can be any type of PostGIS geometry, such as points, lines, polygons, or multi-geometries. The ST_Equals function returns true if the two geometries are exactly equal and false otherwise.
For example, the following query checks whether two polygons in a dataset are exactly equal:
SELECT ST_Equals(polygon1, polygon2)
FROM counties
WHERE name = 'New York';
In this query, the ST_Equals function is used to check whether two polygon geometries in the counties dataset that represent New York County are exactly equal. The resulting value is true if the polygons are exactly equal and false otherwise. This type of query can be used to test whether two geometries are exactly equal and to perform spatial filtering based on an exact equality.
ST_Within in PostGIS
ST_Within tests whether one geometry is completely within another geometry. It returns true if the first geometry is completely within the second geometry, and false otherwise. The function is typically used to check whether a point is within a polygon or another polygon.
The syntax for using ST_Within is as follows:
ST_Within(geometry1, geometry2)
Here, geometry1 is the first geometry that needs to be tested, and geometry2 is the second geometry that is being tested for inclusion. Both geometries can be any type of PostGIS geometry, such as points, lines, polygons, or multi-geometries. The ST_Within function returns true if the first geometry is completely within the second geometry and false otherwise.
For example, the following query checks whether a point is within a polygon in a dataset:
SELECT ST_Within(point, polygon)
FROM neighborhoods
WHERE name = 'Upper East Side';
In this query, the ST_Within function is used to check whether the point geometry is within the polygon in the neighborhoods dataset that represents the Upper East Side neighborhood. The resulting value is true if the point is within the polygon completely and false otherwise. This type of query can be used to test whether one geometry is within another geometry and to perform spatial filtering based on inclusion.
ST_GeomFromWKT in PostGIS
ST_GeomFromWKT creates a geometry from a Well-Known Text (WKT) string. It can be used to create a geometry from a text string that specifies the geometry type and coordinate values.
The syntax for using ST_GeomFromWKT is as follows:
ST_GeomFromWKT(WKT_string)
Here, WKT_string is the input string that specifies the geometry type and coordinate values. The WKT_string is a text string that follows the format of the Well-Known Text (WKT) specification. The ST_GeomFromWKT function returns a new geometry object that represents the geometry specified in the input string.
For example, the following query creates a point geometry from a WKT string:
SELECT ST_GeomFromWKT('POINT(-73.9873 40.7589)');
In this query, the ST_GeomFromWKT function is used to create a point geometry from a WKT string that specifies the coordinate values of the point. The resulting geometry represents a point with longitude -73.9873 and latitude 40.7589. This type of query can be used to create a geometry from a WKT string and to specify the spatial type and coordinate values of the geometry.
ST_IsEmpty in PostGIS
ST_IsEmpty is a Spatial SQL function in PostGIS that tests whether a geometry is empty, i.e., it has no points or vertices. It returns true if the input geometry is empty, and false otherwise. The function is typically used to check whether a geometry is valid or contains any data.
The syntax for using ST_IsEmpty is as follows:
ST_IsEmpty(geometry)
Here, geometry is the input geometry that needs to be tested for emptiness. It can be any type of PostGIS geometry, such as points, lines, polygons, or multi-geometries. The ST_IsEmpty function returns true if the input geometry is empty and false otherwise.
For example, the following query checks whether a polygon in a dataset is empty:
SELECT ST_IsEmpty(polygon)
FROM neighborhoods
WHERE name = 'Greenwich Village';
In this query, the ST_IsEmpty function is used to check whether the polygon in the neighborhoods dataset that represents the Greenwich Village neighborhood is empty. The resulting value is true if the polygon is empty, i.e., it has no points or vertices. This type of query can be used to check whether a geometry is empty and to perform spatial filtering based on emptiness.
ST_AsLatLonText in PostGIS
ST_AsLatLonText returns the latitude and longitude coordinates of a point geometry as a human-readable text string. It can be used to display the coordinates of a point geometry in a user-friendly format.
The syntax for using ST_AsLatLonText is as follows:
ST_AsLatLonText(geometry)
Here, geometry is the point geometry for which the latitude and longitude coordinates need to be returned as a text string. The ST_AsLatLonText function returns a text string that represents the latitude and longitude coordinates of the point geometry in a human-readable format.
For example, the following query returns the latitude and longitude coordinates of a point in a dataset as a text string:
SELECT ST_AsLatLonText(location)
FROM places
WHERE name = 'Central Park';
In this query, the ST_AsLatLonText function is used to return the latitude and longitude coordinates of the point geometry in the places dataset that represents Central Park. The resulting value is a text string that represents the latitude and longitude coordinates in a human-readable format. This type of query can be used to display the coordinates of a point geometry in a user-friendly format.
Apple Podcasts
Google Podcasts
Spotify
