locked
Looking for some help, trying to chop up some polygons into smaller pieces. RRS feed

  • Question

  • Hi folks,

    I would love some suggestions about how I can split up some of our data into small pieces - mainly an algorithm if typing up some Sql is too much effort.

    So, I've just imported in around 6.8Mil routes in the system (yeah, that's a lot of routes). Then we have polygons that represent the 

    1. state manager (the green square .. but only a tiny portion of the state is shown, below)
    2. city manager (the light blue poly)
    3. franchise manager (the smallest poly)

     

    So what we're after is splitting up that RED polygon (aka a single route) into smaller sections. If the section exists in a franchise, then cut it by that. If it's not in a franchise but in a city, cut it by that. If it's not in either, then it's by state. 

    In this example, i'm expecting 3 rows to be returned.

    franchise segment - part 2
    city segment - part 3
    state segment - part 1 and 4 combined.

    right now, the red polygon is a single row. I had to use the sql spatial tools GeographyUnionAggregate method to join all of the initial data into meaningful results - single polys per route. Now I need to split them up into usable segments.

    Once I have a segment, it's easy for me to see if that new segment intersects a franchise, city and/or state.

    Not all routes exist in a city manager.
    Not all routes exist in a city AND a franchise. (i didn't make these rules up .. I just have to work with it).
    All routes are in a state, of course and have a state manager.

    So - does anyone have any ideas?

    At first I was thinking about finding all the segments that exist in the franchise zones - start at the bottom. Then, to figure out if they are in a city but not a franchise, then somehow ask the Sql to find all intersections but exclude those previously found. Finally, repeat the same for the states and exclude BOTH previous results.

    But i'm not sure if that's a good algorithm AND how to do that as SQL.

    Finally, this is a once of data import -> so speed is not tooo much of an issue. It took me 28 hours to do the GeographyUnionAggregate .. so i'm kewl if this takes as long or so. (my longest time i took data importing was having to unzip, convert to WGS84 and the read in the USA TigerLines data -> that took a month).

    Anyone have any suggestions? (Hi Tan - <3 )


    -Pure Krome-
    Friday, November 19, 2010 11:18 PM

Answers

  • declare @route geography ='...'
    declare @franchise geography = '...'
    declare @city geography = '...'
    declare @state geography = '...'
    
    declare @empty geography = geography::STCreateFromText('POINT EMPTY',4326)
    
    set @franchise = ISNULL(@franchise,@empty)
    set @city = ISNULL(@city,@empty)
    set @state = ISNULL(@state,@empty)
    
    
    declare @franchise_segment geography= @route.STIntersection(@franchise)
    declare @city_segment geomety = @route.STIntersection(@city).STDifference(@franchise)
    declare @state_segment geography = @route.STIntersection(@state).STDifference(@city)
    

    Sunday, November 21, 2010 6:01 PM

All replies

  • declare @route geography ='...'
    declare @franchise geography = '...'
    declare @city geography = '...'
    declare @state geography = '...'
    
    declare @empty geography = geography::STCreateFromText('POINT EMPTY',4326)
    
    set @franchise = ISNULL(@franchise,@empty)
    set @city = ISNULL(@city,@empty)
    set @state = ISNULL(@state,@empty)
    
    
    declare @franchise_segment geography= @route.STIntersection(@franchise)
    declare @city_segment geomety = @route.STIntersection(@city).STDifference(@franchise)
    declare @state_segment geography = @route.STIntersection(@state).STDifference(@city)
    

    Sunday, November 21, 2010 6:01 PM
  • Ahh :) I missed the STDifference method :)

    that's for a single route ... can you update it for a table that contains routes?

     

    i have a single table for routes, franchises, cities and states.

     

    please ?


    -Pure Krome-
    Monday, November 22, 2010 3:51 AM
  • Hi PK,

    In your original post, you said "I'm expecting 3 rows to be returned ", and that's exactly what vIndEx gave you...so I'm marking their post as the answer to your (original) question!

    To update this code to work against a table(s), you need to be more specific about the schema of your data:

    - You have a single table that contains routes, franchises, cities, and states?

    - Or, you have a single table that contains routes, a single table that contains franchises, one that contains cities, and one that contains states?

    What are the columns in these tables?

    Secondly, exactly what output do you expect (again, column names/types please)? Do you want a single table that contains (up to) 3x the number of rows in the original routes table, with one row for each segment? Or do you want the segments in separate tables?


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Monday, November 22, 2010 8:47 AM
    Answerer
  • Something like this?

     

    declare @route geography
    select @route = GEO from ROUTES WHERE ID=...
    
    declare @franchise geography
    select @franchise = GEO from ROUTES WHERE GEO.STIntersects(@route)=1
    
    declare @city geography
    select @city = GEO from ROUTES WHERE GEO.STIntersects(@route)=1
    
    declare @state geography
    select @state = GEO from ROUTES WHERE GEO.STIntersects(@route)=1
    

     

    This is the simple solution, but it won't work if a route intersects more than one franchise,city or state

     

     

    Monday, November 22, 2010 8:01 PM
  • Hi folks :)

     

    @vIndEx : yep - that's great :) And yep, that does answer my question. I must confess, i was hoping that the answer would lead to my real problem - i was going to extrapolate the answer, modify it a wee bit and smile away. 

    @Tan : as always, you're right. I'll try and explain the real problem....

     

    ... and due to NDA's, I've always been trying to avoid asking my real questions so I don't allude to our real problems which could hint/suggest our future developement/plans/etc. Rules are rules and I need to abide by them :( So I do appologise for the fake-modified questions. Trust me - I'd love to just dump my data in the public arena and ask the _exact_ question where all having problems with here at the office. *sigh* *double sigh* (unless anyone else wants to sign an NDA with us and take this offline :) .. all in the name of helping people and making the world a better place!)

    -----

    So this is what we have (while keeping under our NDA).

    Table 1:  US_TrainTruckCourierRoutes => 3,464,465 records of trucking/train/courier routes.

    Table 2: Franchises / FranchiseBoundaries => 12,798 records (or in other words nearly 12,800 franchises).

    Now our second table, contains shapes that define all our managers at various levels. These guys micromanage their divisions. So for big cities, there's a lot more lower-lever managers (eg. franchises) because of the number of jobs that happen every day.

     

    So what we're tying to get are all routes broken up by franchises .. I've tried the following :-

    EG Data.

    Routes:

    1. Miami - NYC - Chicago - Las Vegas   ||  FL   || <Geog Data>
    2. Miami - NYC - Chicago - Las Vegas   ||  GA   || <Geog Data>
    3. Miami - NYC - Chicago - Las Vegas   ||  SC   || <Geog Data>
    ...
    100. Miami - Atlanta   || FL  || <Geog Data>
    100. Miami - Atlanta   || GA  || <Geog Data>

    .. etc.

    Franchises:

    1. South Beach
    2. Coconut Grove
    3. Little Havanah

    .. etc.

    Required Results :-

    1.  Miami - NYC - Chicago - Las Vegas | South Beach | <Geog part STIntersection>
    2.  Miami - NYC - Chicago - Las Vegas | Coconut Grove | <Geog part STIntersection>

    .. etc..

     

    For each route (eg.  Miami - NYC - Chicago - Las Vegas ), it might not cross through a Franchise. So then, for the parts that haven't intersected a Franchise, we then see if these remaining parts intersect a City .. and finally, for what's left, they become a State part.

    So my opening post was really only referring to Route #1 (for example), in that sample data above.

    ---

    So i tried the following sql :-

    1. Grab the state of Florida.
    2. Grab all the Franchises in Florida.
    3. Grab all the routes and break them up by Franchises (ie. STIntersection)

     

    DECLARE @State GEOGRAPHY
    SELECT @State = OriginalBoundary
    FROM States
    WHERE Name = 'New York';
              

    -- Grab all the franchises in FL
    WITH CTE
    AS
    (
            SELECT a.Name, b.OriginalBoundary
            FROM Franchises a    
                     INNER JOIN FranchiseBoundaries b on a.LocationId = b.LocationId
            where @State.STIntersects(b.OriginalBoundary) = 1
    )
    SELECT b.Name, a.Name, a.OriginalBoundary.STIntersection(b.Route)
    FROM CTE a
             INNER JOIN US_TrainTruckCourierRoutes b on a.OriginalBoundary.STIntersects(b.Route) = 1
    where b.State = 'FL';

     

    But after 18 hours, it only got up to the routes starting with 'B' .... :( and that's for one state AND i only had started doing the first part of my problem -> routes broken up by franchises ... not even done cities and states!

    So yeah .. that's the question, Tan :( 

    This is very distressing .. because I believe Sql2008 should be able to do this .. and not taken months to do.

    Extra Info:

    1. 10.50.1600.1 RTM Developer Edition (64-bit)
    2. Intel Core2 Quad CPI Q6600 @ 2.4GHz with 4Gig RAM.
    3. HD has 50 Gig free (D:\ drive, log + mdf) and 40 Gig free (C:\ master / temp)


    -Pure Krome-
    Wednesday, November 24, 2010 3:49 AM