Answered by:
Trying to determing boundary from group of Lat / Long points
Question

So I have lots of different addresses for my city and have them grouped into different areas. I have obtained the latitudes and longitudes for these addresses as well. What I want to know is how to determine the "boundary" for my addresses (if that is even possible).
So for instance
Address, City, State, Zip, Lat, Long, Section Of Town
1 MyPlace St., Somewhere, IL, 00000, LatValue, LongValue, AreaOne
2 MyPlace St., Somewhere, IL, 00000, LatValue, LongValue, AreaOne
3 SomeOtherPlace St., Somewhere, IL, 00000, LatValue, LongValue, AreaOne
But with hundreds of other addresses also in AreaOne. I want to then determine what the boundary of AreaOne would be. I'm pretty new at spatial data and so haven't found anything that has helped me. Any help would be appreciated.
Thanks!
Wednesday, September 15, 2010 3:16 AM
Answers

Here's a full code sample:
 Create some test data CREATE TABLE #ConvexHullDemo ( Address varchar(32), LatValue float, LongValue float, Section varchar(32) ); INSERT INTO #ConvexHullDemo VALUES ('1 Test Street', 49, 120, 'Downtown'), ('5 Test Street', 49.1, 120.3, 'Downtown'), ('13 New Road', 49.4, 120.1, 'Downtown'), ('2 Scary Alley', 49, 120, 'Waterside'), ('8 New Road', 48.5, 120.2, 'Downtown'), ('4 Winding Lane', 48.8, 119.5, 'Downtown')  Add a geometry column ALTER TABLE #ConvexHullDemo ADD Geom geometry;  Populate the geometry column UPDATE #ConvexHullDemo SET Geom = geometry::Point(LongValue, LatValue, 4326);  Create an empty collection DECLARE @Multipoint geometry = geometry::STGeomFromText('POINT EMPTY', 4326);  Union all points in a given area into the collection SELECT @Multipoint = @Multipoint.STUnion(Geom) FROM #ConvexHullDemo WHERE Section = 'Downtown';  Define the area boundary as the convexhull around all the points DECLARE @Boundary geometry = @Multipoint.STConvexHull();  Select the points and the boundary around them SELECT @Multipoint.STBuffer(0.01) UNION ALL SELECT @Boundary;
which generates the following "boundary" around the five address points:
Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290 Marked as answer by kendrakuhl Thursday, September 16, 2010 9:15 PM
Thursday, September 16, 2010 7:49 AMAnswerer
All replies

Hi there,
One method of creating a polygon representing the area covered by a set of points is to determine the convex hull. In SQL Server, you can do this using the STConvexHull() method. From http://msdn.microsoft.com/enus/library/bb933878.aspx  "returns the smallest convex polygon that contains the given geometry instance."
The only problem I forsee is that you can only use the STConvexHull() method on the geometry datatype, whereas if you're storing lat/long coordinates, you're probably (rightly) using the geography datatype.
So, the steps you need to follow are:
1.) Create geometry point instances of all your addresses, where longitude = X and latitude = Y. i.e. for each row in your table, create a new column populated with geometry::Point(LongValue, LatValue, 4326);
2.) Next, you need to group all of the points contained in a single area into a single multipoint instance. A query like this will do it:
DECLARE @Multipoint = geometry::STGeomFromText('POINT EMPTY', 4326);  Create an empty collection
SELECT @Multipoint = @Multipoint.STUnion(MyGeomColumn) FROM MyTable WHERE Area = 'AreaOne';  Union all points in the given area into the collection
3.) Now you can create the convex hull of this area:
SELECT @Multipoint.STConvexHull();
Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290Wednesday, September 15, 2010 7:36 AMAnswerer 
Thank you for helping me. I'm not sure if I have done something wrong, but I'm getting a NULL in the SELECT @Multipoint.ToString(). So here is what I did:
1. Created a new column called Geometry and populated it with. I tried the command you gave, but it errored. I'm hoping that what I did was the same thing.:
UPDATE MyTable SET [Geometry] = geometry::STGeomFromText('Point(' + Longitude + ' ' + Latitude + ')', 4326;
2. I then tried the next two statements you gave. It seemed to work, but if I do a ToString() I get a NULL.
DECLARE @Multipoint = geometry::STGeomFromText('POINT EMPTY', 4326);  Create an empty collection
SELECT @Multipoint = @Multipoint.STUnion([Geometry]) FROM MyTable WHERE Name = 'Murphy Park';  Union all points in the given area into the collection
SELECT @Multipoint.ToString()  this gives me a null
3. The STConvexHull() then also gives me nothing.
Thanks again for helping me. I searched for hours and didn't even come close to finding these methods.
Wednesday, September 15, 2010 11:33 PM 
Hi again,
1.) I'd assumed that your LatValue and LongValue columns were being stored as numeric values in the database. However, the UPDATE statement you've used above makes it look like these are actually strings (e.g. varchars), which would be why the STGeomFromText() method works but the Point() method failed. It doesn't really matter  the result of both methods will be the same.
2.) You need to DECLARE the datatype of @Multipoint before assigning a value to it. (Mistake in my original code). Try this instead:
DECLARE @Multipoint geometry = geometry::STGeomFromText('POINT EMPTY', 4326);  Create an empty collection SELECT @Multipoint = @Multipoint.STUnion(Geom) FROM #ConvexHullDemo WHERE Section = 'Downtown';
3. This is to be expected  if @Multipoint is NULL, then the convexhull of that multipoint will certainly be NULL.
Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290Thursday, September 16, 2010 7:37 AMAnswerer 
Here's a full code sample:
 Create some test data CREATE TABLE #ConvexHullDemo ( Address varchar(32), LatValue float, LongValue float, Section varchar(32) ); INSERT INTO #ConvexHullDemo VALUES ('1 Test Street', 49, 120, 'Downtown'), ('5 Test Street', 49.1, 120.3, 'Downtown'), ('13 New Road', 49.4, 120.1, 'Downtown'), ('2 Scary Alley', 49, 120, 'Waterside'), ('8 New Road', 48.5, 120.2, 'Downtown'), ('4 Winding Lane', 48.8, 119.5, 'Downtown')  Add a geometry column ALTER TABLE #ConvexHullDemo ADD Geom geometry;  Populate the geometry column UPDATE #ConvexHullDemo SET Geom = geometry::Point(LongValue, LatValue, 4326);  Create an empty collection DECLARE @Multipoint geometry = geometry::STGeomFromText('POINT EMPTY', 4326);  Union all points in a given area into the collection SELECT @Multipoint = @Multipoint.STUnion(Geom) FROM #ConvexHullDemo WHERE Section = 'Downtown';  Define the area boundary as the convexhull around all the points DECLARE @Boundary geometry = @Multipoint.STConvexHull();  Select the points and the boundary around them SELECT @Multipoint.STBuffer(0.01) UNION ALL SELECT @Boundary;
which generates the following "boundary" around the five address points:
Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290 Marked as answer by kendrakuhl Thursday, September 16, 2010 9:15 PM
Thursday, September 16, 2010 7:49 AMAnswerer 
This is exactly what I was looking for! Thank you!!Thursday, September 16, 2010 9:16 PM