1

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.

Little Brother
  • 53
  • 2
  • 7

1 Answers1

0

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
Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • thanks, I am using 2008R2 but I could upgrade it to 2012 if I know there is a build-in function I can use. I'd love to hear more detail... – Little Brother Mar 23 '15 at 02:38
  • Thanks, I upgraded the SQL to 2012 DEV edition today. Can anyone kindly show an example of how ConvexHullAggregate function can return a set of points needed for polygon based on a result (Longitudes and Latitudes) of a query? – Little Brother Mar 24 '15 at 02:39
  • I could not get it to work. But I finally decided to follow this thread (http://stackoverflow.com/questions/6768477/adding-many-circles-to-a-google-map) and get it to do what I wanted to with reasonable performance. Thanks Ben for your time. – Little Brother Apr 04 '15 at 21:06