# Collections: Computed gaps for continuous numbers

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

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

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

• 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 years, 3 months ago by  pdanes.
• 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) -- 1UNION ALLSELECT GEOMETRY::STGeomFromText('POLYGON((2 0, 2 1, 1 1, 1 0, 2 0))', 0) -- 2UNION ALLSELECT GEOMETRY::STGeomFromText('POLYGON((7 0, 7 1, 6 1, 6 0, 7 0))', 0) -- 7UNION ALLSELECT GEOMETRY::STGeomFromText('POLYGON((8 0, 8 1, 7 1, 7 0, 8 0))', 0) -- 8UNION ALLSELECT GEOMETRY::STGeomFromText('POLYGON((9 0, 9 1, 8 1, 8 0, 9 0))', 0) -- 9UNION ALLSELECT GEOMETRY::STGeomFromText('POLYGON((10 0, 10 1, 9 1, 9 0, 10 0))', 0) -- 10UNION ALLSELECT 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.

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

• 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.

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

• 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.

• 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.

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

• 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 years, 3 months ago by  pdanes.
• This reply was modified 4 years, 3 months ago by  pdanes.
• This reply was modified 4 years, 3 months ago by  pdanes.
• This reply was modified 4 years, 3 months ago by  pdanes.
• 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.

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

• 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 years, 3 months ago by  pdanes.
• 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.