Collections: Computed gaps for continuous numbers

Question

Post reply

Collections: Computed gaps for continuous numbers

Bogdan SAHLEAN

SSC Veteran

Points: 229
More actions
March 30, 2020 at 12:00 am

#3731084

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

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

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

Alex Burlan SSC Enthusiast Points: 106 More actions · Answer 1

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

Bogdan SAHLEAN SSC Veteran Points: 229 More actions · Answer 2

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

This reply was modified 4 years, 8 months ago by Bogdan SAHLEAN.

pdanes SSCrazy Eights Points: 8497 More actions · Answer 3

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, 8 months ago by pdanes.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

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.

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

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.

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

David Burrows SSC Guru Points: 65143 More actions · Answer 6

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: 1004406 More actions · Answer 7

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.

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

pdanes SSCrazy Eights Points: 8497 More actions · Answer 8

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

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.

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

pdanes SSCrazy Eights Points: 8497 More actions · Answer 10

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, 8 months ago by pdanes.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

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.

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