Selecting rectangle from large 'continuous' matrix

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting rectangle from large 'continuous' matrix

Author

Topic

ropstah
Starting Member

3 Posts

Posted - 2010-01-20 : 14:29:54

I'm having a hard time working out the following problem efficiently.

I have a 15000x15000 xy matrix. I'm storing element locations in this matrix by defining a x,y coordinate for the element. I want to display a part of the matrix in a so called viewport. The viewport dimensions are e.g. 1600x1000

Consider the following db structure:

Element (element_id, image, width, height)
Globe_Element (ge_id, x, y, element_id)

With the only input being a x,y coordinate somehwere in the grid (500x500) how can I select all the Globe_Element rows which are visible in the viewport (1600x1000)?

The above image demonstrates the issues. The small orange squares are elements which should be included, small red squares shouldn't (looking at the nearest viewport). The big colored blocks (blue, yellow, green and red (yes again, sorry for confusion) are viewports. One color is one viewport. Gray circles define the input coordinates.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-20 : 15:01:20

Wouldn't you just want every thing where x is Globe_Element.X between X AND X + 6 and Globe_Element.Y Between Y and Y + 4.

If there is some scaling factor, c, then put it as c(X+6) and c(Y+4)

Or give an example, say the picked coordinates were ( 100 , 200) What values would you want to see returned?

ropstah
Starting Member

3 Posts

Posted - 2010-01-20 : 15:35:25

Sorry the viewport is 1600 by 1000 (as in the image).

The problem occurs when the input coordinate is e.g. (14000, 7500). Here the viewport needs to take the remaining 1000 (to 15000) and continue at 0 again for the remaining 600 of the viewport (which is 1600 wide). This is the yellow example in the image...

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-20 : 15:41:26

I see the problem now, I am mulling it over in my head.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-20 : 15:56:38

Here try this...

X 14000 X 1100
Y 7500 Y 7500

14000 7500 1100 7500
600 8500 2700 8500

(X > X AND X < MOD(X + 1600, 15000)) AND (Y > Y AND Y < MOD(Y + 1000, 15000)

If you do it this way you can see my beggining point, 14000, 7500 and 1100,7500, then the upper bound created by the MOD().

X being > 14000 will go over 15000 but you will have no results out there, and then (14000 + 1600)%15000 is 600, so putting the less than constraint still works. So greater then 14000 and less than 1600 (for X) and Greater than 7500 and less tan 8500 (for Y). Now look at the example where it does not over lap, the equations still come out to work. Greater than 1100 and Less than 2700 For X (note that (1100 + 1600) % 15000 is still 2700 same with the Y values) and Greater Than 7500 and less than 8500 for Y.

My excel formulas.

	A	B	C	D
1	X	14000	X	1100
2	Y	7500	Y	7500
3				
4	=MOD(C2,15000)	=MOD(C3,15000)	=MOD(E2,15000)	=MOD(E3,15000)
5	=MOD(C2+1600,15000)	=MOD(C3+1000,15000)	=MOD(E2+1600,15000)	=MOD(E3+1000,15000)

Results:

	A	B	C	D
1	X	14000	X	1100
2	Y	7500	Y	7500
3				
4	14000	7500	1100	7500
5	600	8500	2700	8500

ropstah
Starting Member

3 Posts

Posted - 2010-01-20 : 16:38:17

quote:
Originally posted by DP978
(X > X AND X < MOD(X + 1600, 15000)) AND (Y > Y AND Y < MOD(Y + 1000, 15000)

you're throwing x's and y's

. I've tried the following two options, both give 'results' but not meet all requirements...

I have an element at coordinates (25, 500). This element should show up with input coordinates:

- (1, 1) - where the viewport goes to 1601,1001
- (14901, 14901) - where the viewport goes to 1501, 901

This one only shows with @x,@y being 1,1


SELECT
    me.*
FROM
    Map_Element me
WHERE
    (me.x > @x AND me.x < MOD(@x + @vpWidth, @gridWidth))
AND 
    (me.y > @y AND me.y < MOD(@y + @vpHeight, @gridHeight))

This one only shows up with @x,@y being 14901,14901.


SELECT
    me.*
FROM
    Map_Element me
WHERE
    (@x > me.x AND me.x < MOD(@x + @vpWidth, @gridWidth))
AND 
    (@y > me.y AND me.y < MOD(@y + @vpHeight, @gridHeight))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources