locked
STconvexhull on SToverlaps RRS feed

  • Question

  • Hi all, I'm just starting to work with spatial in sql server, but have hit a wall.  I have a database of 16 million x/y points each in a different record, I want to buffer (STbuffer) each point by 10 metres, then any that overlap (SToverlaps) each other, create a new geometry polygon record in a different table using STConvexhull.  I can do this in MapInfo but only for 10,000 points, so looking for a T-SQL solution.

    Cheers,

    Bill


    Monday, March 21, 2011 4:48 PM

All replies

  • Hi there,

    Everything you state is perfectly possible with T-SQL in SQL Server, but with 16 million points it's going to be a pretty intensive operation (or, rather, set of operations). I'm curious to know the reason why you want to do this (and also, is this going to be a one-off operation only?)

    Some other questions/clarifications:

    • You're STBuffering each point by 10 metres, and then testing for STOverlaps of the buffered geometries. In which case, is all you're really trying to do in the first step to identify any points in the dataset where a.STDistance(b) is less than 10 metres? (Do you want the buffered geometries for any other reason?)
    • You said you want to create a polygon from the convex hull from any points that overlap. What do you want to do in the case of only two points whose buffers intersect? The STConvexHull of these points will return a linestring, not a polygon. Or do you want to create a convexhull of the buffered areas rather than the original points?
    • I'm slightly unclear as to exactly what results you expect in your new table. Let's say that your points table looks like this:
    DECLARE @t table (
     id char(1),
     geom geometry
     );
     
    INSERT INTO @t VALUES
    ('A', 'POINT(-12 12)'),
    ('B', 'POINT(12 12)'),
    ('C', 'POINT(0 0)'),
    ('D', 'POINT(-12 -12)'),
    ('E', 'POINT(12 -12)');
    
    SELECT id, geom.STBuffer(10) FROM @t;
    

     

    If you were to examine each point in order, then you'd find that Buffered A overlaps Buffered C, so you'd first create a ConvexHull of (A,C) (a linestring).

    Then, examining Buffered Point B, which overlaps Buffered Point C only, you'd also get another linestring.

    Then you'd examine Buffered Point C, which overlaps all the other points. So, STConvexHull would give you a square polygon A->B->E->D->A

    Points D and E only overlap Point C, so you'd get another two LineStrings.

    So, you'd end up in your table with 4 LineStrings and one Polygon - is that what you want?


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Monday, March 21, 2011 5:34 PM
    Answerer


  • Hi thanks for the prompt reply.

     

    1) It will be a one-off operation.

    2) No preference to use STbuffer so STdistance could be used instead.

    3) There will always be more than 2 points so will always get polygon.  No NOT want to convexhull buffered areas as i want final polygons to be limited to the extremes of the original x/y points.

    4) based on your example, i would want the final table to have only one record with a polygon.

     I thought about creating a temporary table with with the results of the buffering and overlaps, and then looping through this for each group to create the convexhull polygons.  But this is beyond my limited knownledge at the moment.

    Monday, March 21, 2011 5:51 PM
  • Ok, secondary scenario - How many polygons do you want inserted into your table if your source points looked like this? (note that the top and bottom polygons intersect every other polygon, whereas the left and right polygons do not intersect each other)

    If you examine each point in turn and consider the other points that it intersects with, you'll get four polygons (three distinct):

    Convex Hull of points that overlap Buffered W: Polygon W->X->Y->W

    Convex Hull of points that overlap Buffered X: Polygon X->Z->Y->W->X

    Convex Hull of points that overlap Buffered Z: Polygon Z->Y->X->Z

    Convex Hull of points that overlap Buffered Y: Polygon Y->W->X->Z->Y (same as for X)

    Or, do you want to consider the set of points overall and get just a single polygon from all of these four points?

    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Monday, March 21, 2011 6:08 PM
    Answerer
  • I'm aiming for a single polygon in both the first and second scenario.

     

     

    Tuesday, March 22, 2011 9:47 AM
  • That's going to be a lot harder ;)

    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Tuesday, March 22, 2011 10:13 AM
    Answerer
  • got to this stage so far, now have the items grouped in the table, need to work out the STConvexHull

     

    USE test
    GO

    CREATE TABLE FootballStadiums (
     TeamName nvarchar(255) NULL,
     Location geography NULL,
     GroupNo int NULL
    );
    GO

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

     


    UPDATE    test.dbo.FootballStadiums
    SET              GroupNo = 0


    DECLARE @looplocation geography
    DECLARE @ID nvarchar(255)
    DECLARE @counter int
    DECLARE @tmpGroupNo int

    declare db_cursor cursor for
    select TeamName, Location from FootballStadiums

    set @counter = 1

    open db_cursor
    fetch next from db_cursor into @ID, @looplocation

    while @@fetch_status = 0
    begin
        set @tmpGroupNo = 0
        SELECT @tmpGroupNo = (select GroupNo from FootballStadiums where teamname = @ID )
        if @tmpGroupNo = 0
        begin
            set @tmpGroupNo = @counter
        end
       
        UPDATE  test.dbo.FootballStadiums
        SET              GroupNo = @tmpGroupNo
        WHERE     (Location.STDistance(@looplocation) < 20000) AND GroupNo = 0
       
        set @counter = @counter + 1
        fetch next from db_cursor into @ID, @looplocation
    end

    CLOSE db_cursor
    DEALLOCATE db_cursor

     

     

    Tuesday, March 22, 2011 11:23 AM