How to use Convex Hull for SQL Server 2012 and newer

Question

I am struggling to find a straight forward answer on the internet. Hoping someone can help me out with this.

I have a dataset of customer locations with Longitude and Latitude. Each customer is part of a sales territory. Below is an example of the data headers that I have.

Table1:

SalesTerritory  -----  Customer -----   Longitude  -----  Latitude

I am trying to make a sql query that will conduct a convex hull around these territories at the Sales Territory level of detail. My end goal is to use the output on a map to show sales territories.

Thanks for the help!

Answer 1

Convert the lat/lng to a geography (or geometry) value then aggregate at the SalesTerritory level using ConvexHullAggregate.

;with geog_cte as
(
    SELECT SalesTerritory, geography::Point([Longitude], [Latitude], 4326) as customer_geog
    from Table1
)
SELECT SalesTerritory , geography::ConvexHullAggregate(customer_geog) as territory_convex_hull
FROM geog_cte 
group by SalesTerritory  ;