Collections: Computed gaps for continuous numbers

  • Bogdan SAHLEAN

    SSC Enthusiast

    Points: 165

    Comments posted to this topic are about the item Collections: Computed gaps for continuous numbers

  • Alex Burlan

    SSC Enthusiast

    Points: 106

    Never would have thought to solve this kind of problem this way. Superb solution! 🙂

  • Bogdan SAHLEAN

    SSC Enthusiast

    Points: 165

    Thanks. The best thing is that sql spatial extensions, are part of sql standard.

  • pdanes

    SSCrazy Eights

    Points: 8368

    How well does this perform? I admit it's interesting, and shows techniques I didn't even know existed, but is it more complex than necessary?

    I have always used a simple self-join on I + 1 = I for this task  of finding gaps, and it works well. However, I have a dataset of only around 100,000 records and a lightly loaded machine - I don't know how well it would perform if the task load got heavy.

    • This reply was modified 4 months, 1 week ago by  pdanes.
  • Jeff Moden

    SSC Guru

    Points: 996832

    Ok... so we have the following test code from the article...

    DECLARE @tab TABLE(Col1 GEOMETRY);
    INSERT @tab(Col1)
    SELECT GEOMETRY::STGeomFromText('POLYGON((1 0, 1 1, 0 1, 0 0, 1 0))', 0) -- 1
    UNION ALL
    SELECT GEOMETRY::STGeomFromText('POLYGON((2 0, 2 1, 1 1, 1 0, 2 0))', 0) -- 2
    UNION ALL
    SELECT GEOMETRY::STGeomFromText('POLYGON((7 0, 7 1, 6 1, 6 0, 7 0))', 0) -- 7
    UNION ALL
    SELECT GEOMETRY::STGeomFromText('POLYGON((8 0, 8 1, 7 1, 7 0, 8 0))', 0) -- 8
    UNION ALL
    SELECT GEOMETRY::STGeomFromText('POLYGON((9 0, 9 1, 8 1, 8 0, 9 0))', 0) -- 9
    UNION ALL
    SELECT GEOMETRY::STGeomFromText('POLYGON((10 0, 10 1, 9 1, 9 0, 10 0))', 0) -- 10
    UNION ALL
    SELECT GEOMETRY::STGeomFromText('POLYGON((30 0, 30 1, 29 1, 29 0, 30 0))', 0); -- 30

    All of that requires string literals.  If the numbers were in 1,2,7,8,9,10,30 were in a table, what would be the code to set them to geometry in a single select?

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996832

    Anyone?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • David Burrows

    SSC Guru

    Points: 64736

    Never used geometry by I suppose some this like this

    SELECT N, 
    GEOMETRY::STGeomFromText('POLYGON(('+CAST(N as varchar(10))+' 0, 1 1, 0 1, 0 0, 1 0))', 0)
    FROM (VALUES(1)) x (N);

    which could be extended to replace all the values.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Jeff Moden

    SSC Guru

    Points: 996832

    David Burrows wrote:

    Never used geometry by I suppose some this like this

    SELECT N, 
    GEOMETRY::STGeomFromText('POLYGON(('+CAST(N as varchar(10))+' 0, 1 1, 0 1, 0 0, 1 0))', 0)
    FROM (VALUES(1)) x (N);

    which could be extended to replace all the values.

    Thanks, David.  Yeah, I thought of that but, lordy.  There's got to be an easier, more direct way to load this stuff from and existing table especially since you have to use the value and value-1 in several different places.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • pdanes

    SSCrazy Eights

    Points: 8368

    Jeff Moden wrote:

    Anyone?

    The documentation for STGeomFromText only lists strings as possible inputs. How about using STUFF and FOR XML to build that string? It's a bit clunky, but it probably beats a cursor.

     

    There is also STGeomFromWKB, which may be more useable in a real-life situation, rather than a demo.

    https://docs.microsoft.com/en-us/sql/t-sql/spatial-geography/stgeomfromwkb-geography-data-type?view=sql-server-ver15

    https://en.wikipedia.org/wiki/Well-known_text_representation_of_geometry

    • This reply was modified 4 months, 1 week ago by  pdanes.
    • This reply was modified 4 months, 1 week ago by  pdanes.
    • This reply was modified 4 months, 1 week ago by  pdanes.
    • This reply was modified 4 months, 1 week ago by  pdanes.
  • Jeff Moden

    SSC Guru

    Points: 996832

    Thanks.  I was coming up against the very same thing and I was trying to avoid the use of any form of dynamic SQL.  For now, that seems improbable at best.  I had mostly dismissed the use of STGeomFromWKB for this but may have to revisit it.

    This reminds me of OPENROWSET and several other incredibly useful methods (even sp_ExecuteSQL in many cases) that won't take variables as operands.  I wish MS would spend some time working on such functionality.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • pdanes

    SSCrazy Eights

    Points: 8368

    I've thought a few times about applying for a job at Microsoft, not in the normal way, that I want to work for the company, but with the specific goal of, "I want to fix THIS, so I can use it properly." I wonder what they would say.

    • This reply was modified 4 months, 1 week ago by  pdanes.
  • Jeff Moden

    SSC Guru

    Points: 996832

    Unfortunately, they'd probably say the same things they said on the CONNECT site for years and years and they are now saying on the FEEDBACK site.  "Works as designed" or "Unplanned". 😀  If you look at Erland Somarskog's request for a "sequence function" to generate sequences of numbers like we now do with Tally tables and functions, it's an 11 year old request that they keep shining on but won't close because they don't actually understand the utility of it even though many other RDBMS engines have one.

    And, if you look at things like PIVOT, it's a truly lame bit of functionality they added for marketing purposes just to say they had it.  If you look at what PIVOT can do in ACCESS, you'll wonder if the two groups even speak with each other about functionality.  The one in ACCESS is pretty much a marvel.

    And then there's the train wreck that they call STRING_SPLIT().  What on Earth were they thinking when they built that?

    Don't get me started on what I think of their index maintenance tools and database size tools.  Talk about train wrecks!  They never really finished what they were working on there because they wouldn't let the Developers involved finish what they were thinking.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply