Understanding SQL Geography::EnvelopeAggregate and Its Limitations

When working with spatial data in SQL Server, it’s essential to understand how different functions can affect the results. The geography::EnvelopeAggregate function is one such function that provides a way to calculate the bounding box of a set of points.

Introduction to SQL Geography

SQL geography is a type of user-defined data type introduced in SQL Server 2008. It allows you to store and manipulate spatial data using standard geographic coordinate reference systems (GCRS) like WGS 84, NAD 83, etc.

The geography::Point data type represents a point on the surface of the Earth with latitude and longitude coordinates. The geography::EnvelopeAggregate function aggregates these points into a bounding box that encloses all the points.

Envelope Aggregate Function

The geography::EnvelopeAggregate function takes a collection of geography objects as input and returns an envelope geometry that represents the minimum and maximum bounds of the entire set.

Here’s a simplified representation of how it works:

1. Point Accumulation: The geography::EnvelopeAggregate function starts by accumulating all the points in the input collection.
2. Minimum-Maximum Bounds Calculation: Then, for each point, it calculates its minimum and maximum bounds based on its latitude and longitude coordinates.
3. Envelope Geometry Construction: Finally, the function constructs an envelope geometry that represents the minimum and maximum bounds of the entire set.

Example Code

To demonstrate how to use geography::EnvelopeAggregate, you can refer to the example provided in the original Stack Overflow question:

-- Create a temporary table with points
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N'tempdb..[#ServicePoints]'))
    BEGIN
        DROP TABLE #Points
    END
CREATE TABLE #Points
(
    PointId nvarchar(max),
    Lat float,
    Long float,
    Location geography
)

-- Insert sample points into the table
insert into #Points(PointId, Lat, Long, Location)
values ('point1', 47.659226, -117.218292, geography::Point(47.659226, -117.218292, 4326))
insert into #Points(PointId, Lat, Long, Location)
values ('point2', 47.659226, -117.218292, geography::Point(47.659226, -117.218292, 4326))
insert into #Points(PointId, Lat, Long, Location)
values ('point3', 47.659226, -117.218292, geography::Point(47.659226, -117.218292, 4326))

-- Calculate the envelope aggregate
Select E1.ID,
                   E1.SPEXtents.STPointN(3).Lat as BottomLeft_Lat, 
                   E1.SPEXtents.STPointN(3).Long as BottomLeft_Long,
                   E1.SPExtents.STPointN(1).Lat as RightTop_Lat,         
                   E1.SPExtents.STPointN(1).Long as RightTop_Long, 
                   E1.SPExtents.STPointN(2).Lat as BottomRight_Lat,
                   E1.SPExtents.STPointN(2).Long as BottomRight_Long,
                   E1.SPExtents.STPointN(4).Lat as TopRight_Lat,
                   E1.SPExtents.STPointN(4).Long as TopRight_Long

from
(
    SELECT 1 as ID,
            geography::EnvelopeAggregate(sp.Location) AS SPExtents
    FROM #Points sp
) E1;

Explanation of Output

When you run the query above, it calculates the envelope aggregate and returns an envelope geometry with four corner points:

• BottomLeft_Lat and BottomLeft_Long: The minimum latitude and longitude coordinates.
• RightTop_Lat and RightTop_Long: The maximum latitude and longitude coordinates.

However, in this example, there are two points (point2 and point3) with the same location as point1. Therefore, the envelope aggregate returns a bounding box that encloses all three points. As expected, the output shows that the points fall within this circular area.

Conclusion

In conclusion, while the geography::EnvelopeAggregate function provides a way to calculate the bounding box of a set of points in SQL Server, it has limitations when working with points that have identical locations.

If you want to visualize the actual area described by the envelope aggregate, removing the .ToString() call will provide more detailed information about the bounds.

Last modified on 2023-07-26