Calculate Convex Hull of points from a set of (lat

Question

I am working on a web app with Google Map that I’d like to display a “coverage area”/’ geographical area” by creating a polygon overlay of a given set of geo coordinates/points.

The “coverage area” can consist of thousands of the geo coordinates (Longitude and Latitude data stored in a table in sql server). Ideally, I’d like to calculate the Convex Hull points from the sql server database (2008 R2) so I can pass the results (points) to the Google Map to create the polygon overlay.

The sample here (http://www.geocodezip.com/v3_map-markers_ConvexHull.asp) is exactly what I am looking for, except that I’d like to get the hull points on the right-panel straight from the SQL server if possible. The reason is that I may have to process thousands of the geo coordinates. I’d rather not to retrieve a huge amount of data from the database and then send to the client using JavaScript to calculate the convex hull points.

Any help will be very much appreciated!!!

Thank you.

Answer 1

You didn't mention what version you're on, but there's a built in ConvexHullAggregate starting in SQL 2012 that should do exactly what you're looking for.

---

Here's an extension of the example in the linked to documentation that gets the coordinates of the corners of the convex hull. It assumes that you have a table of numbers laying around (a pretty useful thing in my experience).

with cte as (
    SELECT City, geography::ConvexHullAggregate(SpatialLocation) AS Hull
    FROM Person.Address
    WHERE City in ('Ottawa', 'Burnaby')
    group by City
)
select City, Number, Edge.Long as Long, Edge.Lat as Lat
from cte
cross apply (
    select Number, Hull.STPointN(Number) as Edge
    from dbadmin.dbo.Numbers
    where Number < Hull.STNumPoints()
) as HullEdges