locked
Trying to determing boundary from group of Lat / Long points RRS feed

  • 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 AM
    Answerer

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/en-us/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/1430218290
    Wednesday, September 15, 2010 7:36 AM
    Answerer
  • 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/1430218290
    Thursday, September 16, 2010 7:37 AM
    Answerer
  • 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 AM
    Answerer
  • This is exactly what I was looking for! Thank you!!
    Thursday, September 16, 2010 9:16 PM