locked
Creating a polygon from multiple points RRS feed

  • Question

  • I have spatial data (latitude and longitude) of all our stores and all our customers in a SQL table.  I have ranked them by distance from the store and used cumulative percentages to work out who is within the 60% closest customers (weighted by sales value), 70%, 80%, 90%.  I can obviously create various points using my data and also draw linestrings from store to customer. 

    What I would like to do is create a polygon from the points that represent 60% of the sales, a further polygon that represents 70% of the sales etc for each store.  I know how to create polygons from points, but I'm not sure how to order the points and allow for more or less points.  It seems that there should be a way of comprehending that maximum area represented by all the points in a set and create a polygon from this, but have not been able to find such a method.  Anyone out there have a similar challenge or a solution?  I am dealing with nearly 1000 stores and over 1 million customers that I can geolocate.

    Wednesday, June 30, 2010 9:10 AM

Answers

  • Ok, so first of all let's create some sample data. Here's a table of points representing football teams in the United Kingdom. Simple schema - just team name, and a single geography point at the latitude/longitude of the team stadium. I think this is equivalent to your stores table.

    CREATE TABLE FootballStadiums (
     TeamName nvarchar(255) NULL,
     Location geography NULL
    );
    GO
    
    INSERT INTO FootballStadiums VALUES
    ('Plymouth Argyle',geography::Point(50.3875,-4.1548,4326)),
    ('Swansea City',geography::Point(51.6426,-3.93417,4326)),
    ('Torquay United',geography::Point(50.4726,-3.52564,4326)),
    ('Exeter City',geography::Point(50.7305,-3.52229,4326)),
    ('Cardiff City',geography::Point(51.4748,-3.20126,4326)),
    ('Hereford United',geography::Point(52.0601,-2.71893,4326)),
    ('Yeovil Town',geography::Point(50.9523,-2.67398,4326)),
    ('Bristol City',geography::Point(51.4384,-2.62182,4326)),
    ('Bristol Rovers',geography::Point(51.4875,-2.58377,4326)),
    ('Forest Green Rovers',geography::Point(51.6992,-2.23395,4326)),
    ('Cheltenham Town',geography::Point(51.9053,-2.06078,4326)),
    ('Bournemouth',geography::Point(50.7364,-1.84155,4326)),
    ('Wrexham',geography::Point(53.0521,-3.00586,4326)),
    ('Shrewsbury Town',geography::Point(52.6908,-2.75151,4326)),
    ('Blackpool',geography::Point(53.804,-3.04793,4326)),
    ('Tranmere Rovers',geography::Point(53.3737,-3.03387,4326)),
    ('Everton',geography::Point(53.438,-2.96711,4326)),
    ('Liverpool',geography::Point(53.4301,-2.96224,4326)),
    ('Chester City',geography::Point(53.1895,-2.92095,4326)),
    ('Preston North End',geography::Point(53.7724,-2.69059,4326)),
    ('Wigan Athletic',geography::Point(53.5462,-2.65374,4326)),
    ('Telford United',geography::Point(52.6963,-2.50648,4326)),
    ('Crewe Alexandra',geography::Point(53.0877,-2.43676,4326)),
    ('Kidderminster Harriers',geography::Point(52.3823,-2.2433,4326)),
    ('Port Vale',geography::Point(53.0496,-2.19071,4326)),
    ('Stoke City',geography::Point(52.99,-2.1779,4326)),
    ('Wolverhampton Wanderers',geography::Point(52.592,-2.13019,4326)),
    ('Walsall',geography::Point(52.5731,-1.99277,4326)),
    ('West Bromwich Albion',geography::Point(52.51,-1.96435,4326)),
    ('Aston Villa',geography::Point(52.5087,-1.88619,4326)),
    ('Birmingham City',geography::Point(52.4746,-1.86835,4326)),
    ('Bolton Wanderers',geography::Point(53.5802,-2.53696,4326)),
    ('Leigh RMI',geography::Point(53.5023,-2.52098,4326)),
    ('Northwich Victoria',geography::Point(53.2724,-2.49167,4326)),
    ('Blackburn Rovers',geography::Point(53.729,-2.49052,4326)),
    ('Accrington Stanley',geography::Point(53.7641,-2.3715,4326)),
    ('Bury',geography::Point(53.5817,-2.29494,4326)),
    ('Manchester United',geography::Point(53.4628,-2.28908,4326)),
    ('Burnley',geography::Point(53.7886,-2.23051,4326)),
    ('Manchester City',geography::Point(53.4818,-2.20533,4326)),
    ('Rochdale',geography::Point(53.6199,-2.18065,4326)),
    ('Stockport County',geography::Point(53.3997,-2.16828,4326)),
    ('Oldham Athletic',geography::Point(53.5547,-2.12782,4326)),
    ('Macclesfield Town',geography::Point(53.2429,-2.12618,4326)),
    ('Halifax Town',geography::Point(53.7166,-1.85729,4326)),
    ('Stranraer',geography::Point(54.9035,-5.01429,4326)),
    ('Ayr United',geography::Point(55.4694,-4.62026,4326)),
    ('Kilmarnock',geography::Point(55.6054,-4.50677,4326)),
    ('Queens Park',geography::Point(55.8244,-4.25146,4326)),
    ('Motherwell',geography::Point(55.7803,-3.9816,4326)),
    ('Queen of the South',geography::Point(55.0705,-3.62233,4326)),
    ('Morton',geography::Point(55.9413,-4.72611,4326)),
    ('Dumbarton',geography::Point(55.9486,-4.5525,4326)),
    ('St Mirren',geography::Point(55.8524,-4.42664,4326)),
    ('Ross County',geography::Point(57.5963,-4.41991,4326)),
    ('Rangers',geography::Point(55.8528,-4.31074,4326)),
    ('Hamilton Academical',geography::Point(55.7822,-4.05894,4326)),
    ('Partick Thistle',geography::Point(55.8813,-4.2703,4326)),
    ('Inverness C T',geography::Point(57.4956,-4.2178,4326)),
    ('Celtic',geography::Point(55.849,-4.20695,4326)),
    ('Clyde',geography::Point(55.9443,-4.03684,4326)),
    ('Albion Rovers',geography::Point(55.8597,-4.01158,4326)),
    ('Stirling Albion',geography::Point(56.1187,-3.91315,4326)),
    ('Stenhousemuir',geography::Point(56.0278,-3.81518,4326)),
    ('Falkirk',geography::Point(56.0031,-3.78846,4326)),
    ('East Stirlingshire',geography::Point(56.0055,-3.77838,4326)),
    ('Alloa Athletic',geography::Point(56.1161,-3.77756,4326)),
    ('Livingston',geography::Point(55.886,-3.5231,4326)),
    ('St Johnstone',geography::Point(56.4077,-3.47548,4326)),
    ('Dunfermline Athletic',geography::Point(56.0753,-3.44171,4326)),
    ('Gretna',geography::Point(54.9945,-3.07162,4326)),
    ('Carlisle United',geography::Point(54.895,-2.91259,4326)),
    ('Morecambe',geography::Point(54.0685,-2.84713,4326)),
    ('Berwick Rangers',geography::Point(55.7582,-2.01544,4326)),
    ('Cowdenbeath',geography::Point(56.1101,-3.34514,4326)),
    ('Elgin City',geography::Point(57.6517,-3.32013,4326)),
    ('Heart of Midlothian',geography::Point(55.94,-3.23234,4326)),
    ('Raith Rovers',geography::Point(56.0991,-3.16681,4326)),
    ('Hibernian',geography::Point(55.9622,-3.16649,4326)),
    ('East Fife',geography::Point(56.1893,-3.0026,4326)),
    ('Dundee',geography::Point(56.4753,-2.97305,4326)),
    ('Dundee United',geography::Point(56.4738,-2.9673,4326)),
    ('Forfar Athletic',geography::Point(56.6514,-2.88434,4326)),
    ('Brechin City',geography::Point(56.7365,-2.65851,4326)),
    ('Arbroath',geography::Point(56.552,-2.59325,4326)),
    ('Montrose',geography::Point(56.7141,-2.46057,4326)),
    ('Aberdeen',geography::Point(57.1599,-2.08862,4326)),
    ('Swindon Town',geography::Point(51.5647,-1.76984,4326)),
    ('Southampton-FC',geography::Point(50.906,-1.391,4326)),
    ('Oxford United',geography::Point(51.7138,-1.21833,4326)),
    ('Portsmouth',geography::Point(50.7957,-1.06493,4326)),
    ('Reading',geography::Point(51.4233,-0.98269,4326)),
    ('Wycombe Wanderers',geography::Point(51.631,-0.79314,4326)),
    ('Wimbledon',geography::Point(52.0372,-0.77398,4326)),
    ('Farnborough Town',geography::Point(51.3084,-0.76193,4326)),
    ('Aldershot Town',geography::Point(51.2481,-0.7562,4326)),
    ('Woking',geography::Point(51.3078,-0.55913,4326)),
    ('Luton Town',geography::Point(51.8837,-0.43066,4326)),
    ('Watford',geography::Point(51.6511,-0.40162,4326)),
    ('Brentford',geography::Point(51.4883,-0.30259,4326)),
    ('Queens Park Rangers',geography::Point(51.5099,-0.23199,4326)),
    ('Fulham',geography::Point(51.5099,-0.23154,4326)),
    ('Tamworth',geography::Point(52.6286,-1.68702,4326)),
    ('Burton Albion',geography::Point(52.8216,-1.62859,4326)),
    ('Coventry City',geography::Point(52.4487,-1.49759,4326)),
    ('Derby County',geography::Point(52.9152,-1.446,4326)),
    ('Leicester City',geography::Point(52.6242,-1.14028,4326)),
    ('Notts County',geography::Point(52.9419,-1.13651,4326)),
    ('Nottingham Forest',geography::Point(52.9387,-1.13357,4326)),
    ('Huddersfield Town',geography::Point(53.6523,-1.77127,4326)),
    ('Bradford City',geography::Point(53.8039,-1.75976,4326)),
    ('Leeds United',geography::Point(53.778,-1.57009,4326)),
    ('Sheffield Wednesday',geography::Point(53.412,-1.50285,4326)),
    ('Sheffield United',geography::Point(53.3691,-1.47037,4326)),
    ('Barnsley',geography::Point(53.5516,-1.46937,4326)),
    ('Chesterfield',geography::Point(53.2384,-1.43947,4326)),
    ('Rotherham United',geography::Point(53.4295,-1.37047,4326)),
    ('Mansfield Town',geography::Point(53.1372,-1.20048,4326)),
    ('Doncaster Rovers',geography::Point(53.5178,-1.10374,4326)),
    ('York City',geography::Point(53.9695,-1.08688,4326)),
    ('Northampton Town',geography::Point(52.2354,-0.93601,4326)),
    ('Rushden and Diamonds',geography::Point(52.3265,-0.60074,4326)),
    ('Peterborough United',geography::Point(52.5651,-0.23984,4326)),
    ('Scunthorpe United',geography::Point(53.5861,-0.70868,4326)),
    ('Lincoln City',geography::Point(53.2175,-0.54232,4326)),
    ('Hull City AFC',geography::Point(53.7469,-0.373899,4326)),
    ('Barnet',geography::Point(51.6478,-0.19125,4326)),
    ('Stevenage Borough',geography::Point(51.8908,-0.19102,4326)),
    ('Chelsea',geography::Point(51.4807,-0.18909,4326)),
    ('Brighton and Hove Albion',geography::Point(50.8535,-0.15942,4326)),
    ('Arsenal',geography::Point(51.5579,-0.10207,4326)),
    ('Crystal Palace',geography::Point(51.3984,-0.087,4326)),
    ('Tottenham Hotspur',geography::Point(51.6028,-0.06775,4326)),
    ('Millwall',geography::Point(51.486,-0.05259,4326)),
    ('Leyton Orient',geography::Point(51.56,-0.01145,4326)),
    ('Charlton Athletic',geography::Point(51.4873,0.03631,4326)),
    ('West Ham United',geography::Point(51.532,0.03816,4326)),
    ('Dagenham and Redbridge',geography::Point(51.5475,0.16242,4326)),
    ('Gravesend',geography::Point(51.45,0.32082,4326)),
    ('Gillingham',geography::Point(51.3843,0.56208,4326)),
    ('Southend United',geography::Point(51.5496,0.70396,4326)),
    ('Colchester United',geography::Point(51.8775,0.88465,4326)),
    ('Ipswich Town',geography::Point(52.0551,1.1461,4326)),
    ('Margate',geography::Point(51.3806,1.36929,4326)),
    ('Grimsby Town',geography::Point(53.5699,-0.04757,4326)),
    ('Boston United',geography::Point(52.9766,-0.02063,4326)),
    ('Cambridge United',geography::Point(52.2132,0.15425,4326)),
    ('Norwich City',geography::Point(52.6218,1.30743,4326)),
    ('Peterhead',geography::Point(57.5122,-1.79771,4326)),
    ('Newcastle United',geography::Point(54.9744,-1.62178,4326)),
    ('Darlington',geography::Point(54.5211,-1.55419,4326)),
    ('Sunderland',geography::Point(54.9151,-1.38586,4326)),
    ('Middlesbrough',geography::Point(54.5788,-1.21913,4326)),
    ('Hartlepool United',geography::Point(54.6884,-1.21492,4326)),
    ('Scarborough',geography::Point(54.2651,-0.41677,4326)),
    ('Portadown',geography::Point(54.4134,-6.4564,4326)),
    ('Newry City',geography::Point(54.1613,-6.3306,4326)),
    ('Loughall',geography::Point(54.4069,-6.6114,4326)),
    ('Lisburn Distillery',geography::Point(54.5349,-6.0087,4326)),
    ('Linfield',geography::Point(54.5827,-5.9553,4326)),
    ('Limavady United',geography::Point(55.0523,-6.9397,4326)),
    ('Larne',geography::Point(54.848,-5.8274,4326)),
    ('Institute',geography::Point(54.9766,-7.2681,4326)),
    ('Glentoran',geography::Point(54.603,-5.8916,4326)),
    ('Glenavon',geography::Point(54.4538,-6.3384,4326)),
    ('Dungannon Swifts',geography::Point(54.4894,-6.7446,4326)),
    ('Coleraine',geography::Point(55.134,-6.6609,4326)),
    ('Cliftonville',geography::Point(54.6192,-5.9477,4326)),
    ('Ballymena United',geography::Point(54.8699,-6.2663,4326)),
    ('Armagh City',geography::Point(54.3435,-6.6291,4326)),
    ('Bangor / Ards',geography::Point(54.6499,-5.6852,4326)),
    ('Derry City',geography::Point(54.9892,-7.3357,4326)),
    ('Gala Fairydean',geography::Point(55.6061,-2.78411,4326)),
    ('RNetherdale',geography::Point(55.6075,-2.78307,4326)),
    ('RGreenyards',geography::Point(55.5995,-2.72388,4326)),
    ('Redditch United',geography::Point(52.3068,-1.95104,4326));

     

    The next step is to create a multipoint instance containing the points that you want to include. For this example, I'll include all stadiums within 100km of (52,-1). There's a couple of ways to create spatial aggregates such as this, but the easiest is to just use STUnion() and store the result in a variable as follows:

    -- Create a (geography) multipoint instance of all football stadiums within 100km of (52, -1)
    DECLARE @Multipoint geography = geography::STMPointFromText('MULTIPOINT EMPTY', 4326);
    SELECT @Multipoint = @Multipoint.STUnion(Location) FROM FootballStadiums WHERE Location.STDistance(geography::Point(52,-1, 4326)) < 100000;
    

     

    Now, I'm going to convert the Multipoint to geometry, create a convex hull from it, and then convert the convex hull back to geography:

    -- Convert the multipoint to geometry
    DECLARE @GeomMultipoint geometry;
    SET @GeomMultipoint = geometry::STGeomFromWKB(@Multipoint.STAsBinary(), @Multipoint.STSrid);
    
    -- Create convex hull of the multipoint
    DECLARE @ConvexHull geometry;
    SET @ConvexHull = geometry::STGeomFromWKB(@Multipoint.STAsBinary(), @Multipoint.STSrid).STConvexHull();
    
    -- Convert the convex hull back to geography 
    DECLARE @GeogConvexHull geography;
    SET @GeogConvexHull = geography::STGeomFromWKB(@ConvexHull.STAsBinary(), @ConvexHull.STSrid);

     

    The result is as follows (@GeogConvexHull is the dark polygon in the centre, which is drawn around the outside of the points within the requested range). To change the polygon, you simply amend the query that created @Multipoint:

     



    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Gavin Linnett Thursday, July 1, 2010 9:46 AM
    Thursday, July 1, 2010 9:17 AM
    Answerer
  • It sounds like you're describing a convex hull created from the multipoint geometry of your customers, as in the following:

    If so, try the STConvexHull() method - http://msdn.microsoft.com/en-us/library/bb933878.aspx

    (Note this applies to the geometry datatype - if you're currently using geography you can convert it temporarily to create the convex hull and then save the result back to geography)


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Gavin Linnett Thursday, July 1, 2010 6:51 AM
    Wednesday, June 30, 2010 9:59 AM
    Answerer

All replies

  • It sounds like you're describing a convex hull created from the multipoint geometry of your customers, as in the following:

    If so, try the STConvexHull() method - http://msdn.microsoft.com/en-us/library/bb933878.aspx

    (Note this applies to the geometry datatype - if you're currently using geography you can convert it temporarily to create the convex hull and then save the result back to geography)


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Gavin Linnett Thursday, July 1, 2010 6:51 AM
    Wednesday, June 30, 2010 9:59 AM
    Answerer
  • That does answer my question thanks, but leaves me with a few more, which will probably show my programming inability but I'll ask it anyway.  The first is how do I automate the creation of these convex hulls.  My problem is that I have over 1000 unique sets of over 100 points of X and Y co-ordinates.  So I can create the multipoint geometry as follows:

    @g = geometry::STGeomFromText('MULTIPOINT(X1 Y1,X2 Y2, ...,X101 Y101)',0)

    I can then get the convex hull from the resulting multipoint geometry.  The only way I know (which as I say will no doubt show my ignorance) is to manually copy and paste the co-ordinate into the query.  If I had a fixed number of points then it would be no problem to create x number of variables, but some stores will have 100, others 200 etc, and that is just using postal code centroids.  When I geolocate each customer down to house number, I will have a far greater challenge.  So the questions is how do I automate the scripting to create the multipoint geometry, so I can get the convex hull? 

    Secondly, how can I convert the resulting convex hull into geography datatype - may not be necessary (isn't at this stage), but useful to know for future use?

    Thursday, July 1, 2010 7:27 AM
  • Ok, so first of all let's create some sample data. Here's a table of points representing football teams in the United Kingdom. Simple schema - just team name, and a single geography point at the latitude/longitude of the team stadium. I think this is equivalent to your stores table.

    CREATE TABLE FootballStadiums (
     TeamName nvarchar(255) NULL,
     Location geography NULL
    );
    GO
    
    INSERT INTO FootballStadiums VALUES
    ('Plymouth Argyle',geography::Point(50.3875,-4.1548,4326)),
    ('Swansea City',geography::Point(51.6426,-3.93417,4326)),
    ('Torquay United',geography::Point(50.4726,-3.52564,4326)),
    ('Exeter City',geography::Point(50.7305,-3.52229,4326)),
    ('Cardiff City',geography::Point(51.4748,-3.20126,4326)),
    ('Hereford United',geography::Point(52.0601,-2.71893,4326)),
    ('Yeovil Town',geography::Point(50.9523,-2.67398,4326)),
    ('Bristol City',geography::Point(51.4384,-2.62182,4326)),
    ('Bristol Rovers',geography::Point(51.4875,-2.58377,4326)),
    ('Forest Green Rovers',geography::Point(51.6992,-2.23395,4326)),
    ('Cheltenham Town',geography::Point(51.9053,-2.06078,4326)),
    ('Bournemouth',geography::Point(50.7364,-1.84155,4326)),
    ('Wrexham',geography::Point(53.0521,-3.00586,4326)),
    ('Shrewsbury Town',geography::Point(52.6908,-2.75151,4326)),
    ('Blackpool',geography::Point(53.804,-3.04793,4326)),
    ('Tranmere Rovers',geography::Point(53.3737,-3.03387,4326)),
    ('Everton',geography::Point(53.438,-2.96711,4326)),
    ('Liverpool',geography::Point(53.4301,-2.96224,4326)),
    ('Chester City',geography::Point(53.1895,-2.92095,4326)),
    ('Preston North End',geography::Point(53.7724,-2.69059,4326)),
    ('Wigan Athletic',geography::Point(53.5462,-2.65374,4326)),
    ('Telford United',geography::Point(52.6963,-2.50648,4326)),
    ('Crewe Alexandra',geography::Point(53.0877,-2.43676,4326)),
    ('Kidderminster Harriers',geography::Point(52.3823,-2.2433,4326)),
    ('Port Vale',geography::Point(53.0496,-2.19071,4326)),
    ('Stoke City',geography::Point(52.99,-2.1779,4326)),
    ('Wolverhampton Wanderers',geography::Point(52.592,-2.13019,4326)),
    ('Walsall',geography::Point(52.5731,-1.99277,4326)),
    ('West Bromwich Albion',geography::Point(52.51,-1.96435,4326)),
    ('Aston Villa',geography::Point(52.5087,-1.88619,4326)),
    ('Birmingham City',geography::Point(52.4746,-1.86835,4326)),
    ('Bolton Wanderers',geography::Point(53.5802,-2.53696,4326)),
    ('Leigh RMI',geography::Point(53.5023,-2.52098,4326)),
    ('Northwich Victoria',geography::Point(53.2724,-2.49167,4326)),
    ('Blackburn Rovers',geography::Point(53.729,-2.49052,4326)),
    ('Accrington Stanley',geography::Point(53.7641,-2.3715,4326)),
    ('Bury',geography::Point(53.5817,-2.29494,4326)),
    ('Manchester United',geography::Point(53.4628,-2.28908,4326)),
    ('Burnley',geography::Point(53.7886,-2.23051,4326)),
    ('Manchester City',geography::Point(53.4818,-2.20533,4326)),
    ('Rochdale',geography::Point(53.6199,-2.18065,4326)),
    ('Stockport County',geography::Point(53.3997,-2.16828,4326)),
    ('Oldham Athletic',geography::Point(53.5547,-2.12782,4326)),
    ('Macclesfield Town',geography::Point(53.2429,-2.12618,4326)),
    ('Halifax Town',geography::Point(53.7166,-1.85729,4326)),
    ('Stranraer',geography::Point(54.9035,-5.01429,4326)),
    ('Ayr United',geography::Point(55.4694,-4.62026,4326)),
    ('Kilmarnock',geography::Point(55.6054,-4.50677,4326)),
    ('Queens Park',geography::Point(55.8244,-4.25146,4326)),
    ('Motherwell',geography::Point(55.7803,-3.9816,4326)),
    ('Queen of the South',geography::Point(55.0705,-3.62233,4326)),
    ('Morton',geography::Point(55.9413,-4.72611,4326)),
    ('Dumbarton',geography::Point(55.9486,-4.5525,4326)),
    ('St Mirren',geography::Point(55.8524,-4.42664,4326)),
    ('Ross County',geography::Point(57.5963,-4.41991,4326)),
    ('Rangers',geography::Point(55.8528,-4.31074,4326)),
    ('Hamilton Academical',geography::Point(55.7822,-4.05894,4326)),
    ('Partick Thistle',geography::Point(55.8813,-4.2703,4326)),
    ('Inverness C T',geography::Point(57.4956,-4.2178,4326)),
    ('Celtic',geography::Point(55.849,-4.20695,4326)),
    ('Clyde',geography::Point(55.9443,-4.03684,4326)),
    ('Albion Rovers',geography::Point(55.8597,-4.01158,4326)),
    ('Stirling Albion',geography::Point(56.1187,-3.91315,4326)),
    ('Stenhousemuir',geography::Point(56.0278,-3.81518,4326)),
    ('Falkirk',geography::Point(56.0031,-3.78846,4326)),
    ('East Stirlingshire',geography::Point(56.0055,-3.77838,4326)),
    ('Alloa Athletic',geography::Point(56.1161,-3.77756,4326)),
    ('Livingston',geography::Point(55.886,-3.5231,4326)),
    ('St Johnstone',geography::Point(56.4077,-3.47548,4326)),
    ('Dunfermline Athletic',geography::Point(56.0753,-3.44171,4326)),
    ('Gretna',geography::Point(54.9945,-3.07162,4326)),
    ('Carlisle United',geography::Point(54.895,-2.91259,4326)),
    ('Morecambe',geography::Point(54.0685,-2.84713,4326)),
    ('Berwick Rangers',geography::Point(55.7582,-2.01544,4326)),
    ('Cowdenbeath',geography::Point(56.1101,-3.34514,4326)),
    ('Elgin City',geography::Point(57.6517,-3.32013,4326)),
    ('Heart of Midlothian',geography::Point(55.94,-3.23234,4326)),
    ('Raith Rovers',geography::Point(56.0991,-3.16681,4326)),
    ('Hibernian',geography::Point(55.9622,-3.16649,4326)),
    ('East Fife',geography::Point(56.1893,-3.0026,4326)),
    ('Dundee',geography::Point(56.4753,-2.97305,4326)),
    ('Dundee United',geography::Point(56.4738,-2.9673,4326)),
    ('Forfar Athletic',geography::Point(56.6514,-2.88434,4326)),
    ('Brechin City',geography::Point(56.7365,-2.65851,4326)),
    ('Arbroath',geography::Point(56.552,-2.59325,4326)),
    ('Montrose',geography::Point(56.7141,-2.46057,4326)),
    ('Aberdeen',geography::Point(57.1599,-2.08862,4326)),
    ('Swindon Town',geography::Point(51.5647,-1.76984,4326)),
    ('Southampton-FC',geography::Point(50.906,-1.391,4326)),
    ('Oxford United',geography::Point(51.7138,-1.21833,4326)),
    ('Portsmouth',geography::Point(50.7957,-1.06493,4326)),
    ('Reading',geography::Point(51.4233,-0.98269,4326)),
    ('Wycombe Wanderers',geography::Point(51.631,-0.79314,4326)),
    ('Wimbledon',geography::Point(52.0372,-0.77398,4326)),
    ('Farnborough Town',geography::Point(51.3084,-0.76193,4326)),
    ('Aldershot Town',geography::Point(51.2481,-0.7562,4326)),
    ('Woking',geography::Point(51.3078,-0.55913,4326)),
    ('Luton Town',geography::Point(51.8837,-0.43066,4326)),
    ('Watford',geography::Point(51.6511,-0.40162,4326)),
    ('Brentford',geography::Point(51.4883,-0.30259,4326)),
    ('Queens Park Rangers',geography::Point(51.5099,-0.23199,4326)),
    ('Fulham',geography::Point(51.5099,-0.23154,4326)),
    ('Tamworth',geography::Point(52.6286,-1.68702,4326)),
    ('Burton Albion',geography::Point(52.8216,-1.62859,4326)),
    ('Coventry City',geography::Point(52.4487,-1.49759,4326)),
    ('Derby County',geography::Point(52.9152,-1.446,4326)),
    ('Leicester City',geography::Point(52.6242,-1.14028,4326)),
    ('Notts County',geography::Point(52.9419,-1.13651,4326)),
    ('Nottingham Forest',geography::Point(52.9387,-1.13357,4326)),
    ('Huddersfield Town',geography::Point(53.6523,-1.77127,4326)),
    ('Bradford City',geography::Point(53.8039,-1.75976,4326)),
    ('Leeds United',geography::Point(53.778,-1.57009,4326)),
    ('Sheffield Wednesday',geography::Point(53.412,-1.50285,4326)),
    ('Sheffield United',geography::Point(53.3691,-1.47037,4326)),
    ('Barnsley',geography::Point(53.5516,-1.46937,4326)),
    ('Chesterfield',geography::Point(53.2384,-1.43947,4326)),
    ('Rotherham United',geography::Point(53.4295,-1.37047,4326)),
    ('Mansfield Town',geography::Point(53.1372,-1.20048,4326)),
    ('Doncaster Rovers',geography::Point(53.5178,-1.10374,4326)),
    ('York City',geography::Point(53.9695,-1.08688,4326)),
    ('Northampton Town',geography::Point(52.2354,-0.93601,4326)),
    ('Rushden and Diamonds',geography::Point(52.3265,-0.60074,4326)),
    ('Peterborough United',geography::Point(52.5651,-0.23984,4326)),
    ('Scunthorpe United',geography::Point(53.5861,-0.70868,4326)),
    ('Lincoln City',geography::Point(53.2175,-0.54232,4326)),
    ('Hull City AFC',geography::Point(53.7469,-0.373899,4326)),
    ('Barnet',geography::Point(51.6478,-0.19125,4326)),
    ('Stevenage Borough',geography::Point(51.8908,-0.19102,4326)),
    ('Chelsea',geography::Point(51.4807,-0.18909,4326)),
    ('Brighton and Hove Albion',geography::Point(50.8535,-0.15942,4326)),
    ('Arsenal',geography::Point(51.5579,-0.10207,4326)),
    ('Crystal Palace',geography::Point(51.3984,-0.087,4326)),
    ('Tottenham Hotspur',geography::Point(51.6028,-0.06775,4326)),
    ('Millwall',geography::Point(51.486,-0.05259,4326)),
    ('Leyton Orient',geography::Point(51.56,-0.01145,4326)),
    ('Charlton Athletic',geography::Point(51.4873,0.03631,4326)),
    ('West Ham United',geography::Point(51.532,0.03816,4326)),
    ('Dagenham and Redbridge',geography::Point(51.5475,0.16242,4326)),
    ('Gravesend',geography::Point(51.45,0.32082,4326)),
    ('Gillingham',geography::Point(51.3843,0.56208,4326)),
    ('Southend United',geography::Point(51.5496,0.70396,4326)),
    ('Colchester United',geography::Point(51.8775,0.88465,4326)),
    ('Ipswich Town',geography::Point(52.0551,1.1461,4326)),
    ('Margate',geography::Point(51.3806,1.36929,4326)),
    ('Grimsby Town',geography::Point(53.5699,-0.04757,4326)),
    ('Boston United',geography::Point(52.9766,-0.02063,4326)),
    ('Cambridge United',geography::Point(52.2132,0.15425,4326)),
    ('Norwich City',geography::Point(52.6218,1.30743,4326)),
    ('Peterhead',geography::Point(57.5122,-1.79771,4326)),
    ('Newcastle United',geography::Point(54.9744,-1.62178,4326)),
    ('Darlington',geography::Point(54.5211,-1.55419,4326)),
    ('Sunderland',geography::Point(54.9151,-1.38586,4326)),
    ('Middlesbrough',geography::Point(54.5788,-1.21913,4326)),
    ('Hartlepool United',geography::Point(54.6884,-1.21492,4326)),
    ('Scarborough',geography::Point(54.2651,-0.41677,4326)),
    ('Portadown',geography::Point(54.4134,-6.4564,4326)),
    ('Newry City',geography::Point(54.1613,-6.3306,4326)),
    ('Loughall',geography::Point(54.4069,-6.6114,4326)),
    ('Lisburn Distillery',geography::Point(54.5349,-6.0087,4326)),
    ('Linfield',geography::Point(54.5827,-5.9553,4326)),
    ('Limavady United',geography::Point(55.0523,-6.9397,4326)),
    ('Larne',geography::Point(54.848,-5.8274,4326)),
    ('Institute',geography::Point(54.9766,-7.2681,4326)),
    ('Glentoran',geography::Point(54.603,-5.8916,4326)),
    ('Glenavon',geography::Point(54.4538,-6.3384,4326)),
    ('Dungannon Swifts',geography::Point(54.4894,-6.7446,4326)),
    ('Coleraine',geography::Point(55.134,-6.6609,4326)),
    ('Cliftonville',geography::Point(54.6192,-5.9477,4326)),
    ('Ballymena United',geography::Point(54.8699,-6.2663,4326)),
    ('Armagh City',geography::Point(54.3435,-6.6291,4326)),
    ('Bangor / Ards',geography::Point(54.6499,-5.6852,4326)),
    ('Derry City',geography::Point(54.9892,-7.3357,4326)),
    ('Gala Fairydean',geography::Point(55.6061,-2.78411,4326)),
    ('RNetherdale',geography::Point(55.6075,-2.78307,4326)),
    ('RGreenyards',geography::Point(55.5995,-2.72388,4326)),
    ('Redditch United',geography::Point(52.3068,-1.95104,4326));

     

    The next step is to create a multipoint instance containing the points that you want to include. For this example, I'll include all stadiums within 100km of (52,-1). There's a couple of ways to create spatial aggregates such as this, but the easiest is to just use STUnion() and store the result in a variable as follows:

    -- Create a (geography) multipoint instance of all football stadiums within 100km of (52, -1)
    DECLARE @Multipoint geography = geography::STMPointFromText('MULTIPOINT EMPTY', 4326);
    SELECT @Multipoint = @Multipoint.STUnion(Location) FROM FootballStadiums WHERE Location.STDistance(geography::Point(52,-1, 4326)) < 100000;
    

     

    Now, I'm going to convert the Multipoint to geometry, create a convex hull from it, and then convert the convex hull back to geography:

    -- Convert the multipoint to geometry
    DECLARE @GeomMultipoint geometry;
    SET @GeomMultipoint = geometry::STGeomFromWKB(@Multipoint.STAsBinary(), @Multipoint.STSrid);
    
    -- Create convex hull of the multipoint
    DECLARE @ConvexHull geometry;
    SET @ConvexHull = geometry::STGeomFromWKB(@Multipoint.STAsBinary(), @Multipoint.STSrid).STConvexHull();
    
    -- Convert the convex hull back to geography 
    DECLARE @GeogConvexHull geography;
    SET @GeogConvexHull = geography::STGeomFromWKB(@ConvexHull.STAsBinary(), @ConvexHull.STSrid);

     

    The result is as follows (@GeogConvexHull is the dark polygon in the centre, which is drawn around the outside of the points within the requested range). To change the polygon, you simply amend the query that created @Multipoint:

     



    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    • Marked as answer by Gavin Linnett Thursday, July 1, 2010 9:46 AM
    Thursday, July 1, 2010 9:17 AM
    Answerer
  • Thank you very much for the help.  Exactly what I was looking for and a few more answers for questions I didn't ask, but certainly wanted to know.
    Thursday, July 1, 2010 9:49 AM
  • Great answer! Thank you
    Tuesday, March 12, 2013 1:28 PM