Spatial Measurement

{{ocientgeo}} spatial measurement functions can perform basic calculations on geospatial data, such as measuring the distance between two point objects or the area of a polygon object st area returns the area of the specified geospatial object in the specified unit of measurement syntax st area(geo \[, units] \[, use spheroid ] ) argument data type description geo point , linestring , or polygon a geospatial object to be measured in the specified measurement unit units string optional supported units are 'feet' , 'kilometers' , 'miles' , and 'meters' the default value is 'meters' use spheroid boolean optional if you set this value to true , this function uses a spheroid model instead of a spherical model defaults to false example select st area( st polygon(st linestring('linestring(2 3, 2 4, 4 2, 2 3)')), 'miles'); output 196932665 2369081 st angle calculates the angle between two lines the lines are represented by three point values, including one point for each line (a, b), and a third point (c) representing the vertex where they intersect the function calculates the spherical angle as formed by the two great circles defined by (a, c) and (b, c) st angle returns the value in radians, in the range \[0, 2pi] , excluding the value 2pi if either a or b is the same or exactly antipodal to c, the function returns null if any argument is null, the function returns null syntax st angle(endpoint a, vertex c, endpoint b) argument data type description endpoint a point a point that represents an endpoint for a line between its position and the vertex c value vertex c point a point that represents the intersection between two lines the lines comprise the endpoints between endpoint a and vertex c , and endpoint b and vertex c endpoint b point a point that represents an endpoint for a line between its position and the vertex c value example select st angle(st point(1,0), st point(0,0), st point(1,1)); output 5 49778714378213 st azimuth returns the azimuth of the line from point1 to point2 in radians returns null if either of the points are empty returns 0 if both of the points are the same syntax st azimuth(point1, point2) argument data type description point1 point an endpoint for a line to be calculated for its azimuth value point2 point an endpoint for a line to be calculated for its azimuth value example select st azimuth(st point(1,1), st point(2,2)); output 0 785398163397448 st distance returns the minimum distance between the specified arguments the st distance function returns the minimum distance in the specified unit of measurement the use spheroid optional argument enables the computation to use a spheroid model rather than a spherical one syntax st distance(geo1, geo2 \[, units] \[, use spheroid] ) argument data type description geo1 point , linestring , or polygon a geospatial object to be measured for its minimum distance to geo2 geo2 point , linestring , or polygon a geospatial object to be measured for its minimum distance to geo1 units string optional supported units include 'feet' , 'kilometers' , 'miles' , and 'meters' if you do not specify this argument, the value defaults to 'meters' use spheroid boolean optional if you set this value to true , this function uses a spheroid model instead of a spherical model defaults to false example select st distance( st linestring('linestring(2 3, 2 4)'), st linestring('linestring(1 2, 1 4, 1 5)'), 'meters', true); output 111050 10339481 to use st distance as a spatiotemporal function to calculate the minimum simultaneous distance between two linestring timestamp array pairs, see the spatiotemporal measurement docid\ absjkm232p3oz0u4ex m1 function in spatiotemporal measurement docid\ absjkm232p3oz0u4ex m1 st distancesphere returns the minimum distance between the specified arguments using a spherical computation this function is identical to st distance with the use spheroid argument set to false st distancesphere(geo1, geo2 \[, units] ) see spatial measurement docid\ afqtjckbrk6j8olx4pqby for usage example st distancespheroid returns the minimum distance between the specified arguments using a spheroid computation this function is identical to st distance with the use spheroid argument set to true st distancespheroid(geo1, geo2 \[, units] ) see spatial measurement docid\ afqtjckbrk6j8olx4pqby for usage example st euclideandistance3d returns the minimum distance between two geospatial points with altitude values the distance represents the length of a euclidean line between the points syntax st euclideandistance3d(point1, altitude1, point2, altitude2 \[, units] ) argument data type description point1 point a geospatial point that represents a euclidean line with point2 altitude1 double distance from the surface of the earth for point1 this argument uses meters, unless you specify a different unit of measurement with the units argument point2 point a geospatial point that represents a euclidean line with point1 altitude2 double distance from the surface of the earth for point2 this argument uses meters, unless you specify a different unit of measurement with the units argument units varchar optional this argument specifies the unit of measurement for the distance input arguments and the function output supported units are 'feet' , 'kilometers' , 'miles' , and 'meters' the default value is 'meters' example select st euclideandistance3d( st point(0,0),1000 0, 	st point(0,1),1000 0, 	'meters'); output 111211 09372321835 st hausdorffdistance returns the hausdorff distance between two geographies in a specified measurement if any argument is null or empty, the function returns null the two geographies can be point , linestring , or polygon values the system promotes these geographies to filled polygons behavior is undefined if the resulting polygons are not convex syntax st hausdorffdistance(geo1, geo2 \[, units] \[, use spheroid] ) argument data type description geo1 point , linestring , or polygon a geospatial object to be measured for its hausdorff distance to geo2 geo2 point , linestring , or polygon a geospatial object to be measured for its hausdorff distance to geo1 units string optional supported units are 'feet' , 'kilometers' , 'miles' , and 'meters' the default value is 'meters' use spheroid boolean optional if you set this argument to true , this function uses a spheroid model instead of a spherical model defaults to false example select st hausdorffdistance( st polygon('polygon((1 1, 2 2, 1 2, 1 1))'), st polygon('polygon((0 0, 2 1, 2 0, 0 0))'), 'meters', true); output 156899 5136831999 st length returns the length of the specified line in the specified measurement unit returns 0 for all geography types except for linestring to return the total length of a polygon , use spatial measurement docid\ afqtjckbrk6j8olx4pqby syntax st length(geo \[, units] \[, use spheroid] ) argument data type description geo linestring a line that is to be measured for its length the function accepts point and polygon data types, but returns 0 units string optional supported units are 'feet' , 'kilometers' , 'miles' , and 'meters' the default value is 'meters' use spheroid boolean optional if you set this argument to true , this function uses a spheroid model instead of a spherical model defaults to false example select st length( st linestring('linestring(1 2, 1 4)'), 'meters', false); output 221154 8586057658 st length2d alias for spatial measurement docid\ afqtjckbrk6j8olx4pqby st minimumdistancetosurface calculates the shortest distance between any point along a euclidean line segment in three dimensional space and the surface of the earth two input points and their altitudes define this line segment the line can go above or below the surface of the earth the function takes the difference of both points from the center of the earth to calculate the distance between the nearest point of the line and the surface of the earth the function returns the distance in meters or another unit of measurement if specified if the returned distance is positive, the shortest distance is above the surface if the result is negative, the shortest distance is below the surface syntax st minimumdistancetosurface(point1, altitude1, point2, altitude2 \[, units] ) argument data type description point1 point a geospatial point that represents a euclidean line with point2 altitude1 double distance from the surface of the earth for point1 this argument uses meters, unless you specify a different unit of measurement with the units argument point2 point a geospatial point that represents a euclidean line with point1 altitude2 double distance from the surface of the earth for point2 this argument uses meters, unless you specify a different unit of measurement with the units argument units varchar optional this argument specifies the unit of measurement for the distance input arguments and the function output supported units are 'feet' , 'kilometers' , 'miles' , and 'meters' the default value is 'meters' examples this example takes a line from two points and calculates the distance of the nearest point to the surface of the earth select st minimumdistancetosurface( st point(0,0),1000 0, 	st point(0,1),1000 0, 	'meters'); output 757 3734914669767 in this example, the output is negative, meaning the smallest distance is actually below the surface of the earth select st minimumdistancetosurface( st point(0,0),1000 0, 	st point(0,5),1000 0, 	'meters'); output 5064 738915202208 st maxdistance returns maximum distance between the specified arguments st maxdistance returns the maximum distance in the specified unit of measurement the use spheroid optional argument enables the computation to use a spheroid model rather than a spherical one syntax st maxdistance(geo1, geo2 \[, units] \[, use spheroid] ) argument data type description geo1 point , linestring , or polygon a geospatial object to be measured for its maximum distance to geo2 geo2 point , linestring , or polygon a geospatial object to be measured for its maximum distance to geo1 units string optional supported units are 'feet' , 'kilometers' , 'miles' , and 'meters' the default value is 'meters' use spheroid boolean optional if you set this argument to true , this function uses a spheroid model instead of a spherical model defaults to false example select st maxdistance( st polygon(st linestring('linestring(1 2, 1 4, 1 7, 1 2)')), st polygon(st linestring('linestring(1 2, 1 4, 1 5, 1 2)')), 'meters', false); output 555975 2598904524 to use st maxdistance as a spatiotemporal function to calculate the maximum simultaneous distance between two linestring timestamp array pairs, see the spatiotemporal measurement docid\ absjkm232p3oz0u4ex m1 function in spatiotemporal measurement docid\ absjkm232p3oz0u4ex m1 st perimeter returns the length of the exterior (outer ring) of the polygon in the specified unit of measurement returns 0 for all geography types except for polygon to return the length of a linestring , use spatial measurement docid\ afqtjckbrk6j8olx4pqby syntax st perimeter(geo, \[, units] \[, use spheroid] ) argument data type description geo polygon a polygon that is to be measured for its total perimeter length the function accepts point and linestring data types, but returns 0 units string optional supported units are 'feet' , 'kilometers' , 'miles' , and 'meters' the default value is 'meters' use spheroid boolean optional if you set this argument to true , this function uses a spheroid model instead of a spherical model defaults to false example select st perimeter( st polygon(st linestring('linestring(1 2, 1 3, 1 3, 1 2)')), 'meters', false); output 222390 1039561809 st perimeter2d alias for spatial measurement docid\ afqtjckbrk6j8olx4pqby related links data types docid\ salunscyvkcxpgzh3m0hd spatial operators docid\ mn1u46fvbovqpnj63x 6b spatial relationships docid\ nrkafmnhcwwnlf2 p16rm