# Thread: my SQL - Wrap Around Map

1. Registered User
Join Date
May 2012
Posts
8

## Unanswered: my SQL - Wrap Around Map

I have a grid that goes from 0,0 to 799,799 (800x800 blocks). The issue I'm having, I can't get my head working on this, an guidance or hint of help would be great. I can get the result as long it's inside the stated grid, but when it comes to wrap around the grid, it won't give me results.

ie (this will give me result with no problem):
Code:
```SELECT * FROM map
WHERE ((xaxis >= 0 AND xaxis <= 10) AND (yaxis >= 0 AND yaxis <= 10))```
if i tried (wrap around grid), it will not give me any error or result:
Code:
```SELECT * FROM map
WHERE ((xaxis >= 795 AND xaxis <= 10) AND (yaxis >= 795 AND yaxis <= 10))```
Then I got new help in trying using this code:
Code:
```SELECT * FROM map
WHERE (((MOD(799,xaxis-1) >= 0) AND xaxis <= 10)
AND ((MOD(799,yaxis-1) >= 0) AND yaxis <= 10))
ORDER BY xaxis,yaxis```
with this, it will only display starting from 0,0 and on. I'm trying to include, eg: 795,795 - 0,0. Any idea?

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
The problem is more complex than that code can process. If you can explain what you're doing, I can probably simplify the problem quite a bit. If you want a truly general solution you have to consider four cases: no wrap, x wrap, y wrap, both x and y wrap.

This is a fairly common problem in an area of computer science called "strict topology" that most often occurs in matrix manipulation. The most common occurances of that manipulation that I see are elementary game mapping.

I'd be happy to help if you can explain what you're trying to do in a bit more detail!

-PatP

3. Registered User
Join Date
May 2012
Posts
8
Yeah that's what I thought in the first place about "multiple case" with no x, no y, so forth. I was hoping for a simple solution with one simple mySQL select statement, guess not.

As for map, yeah it's basically a "game" but it's not. Just a side hobby in REGARDS to the game but no "interactive" mode to it. I can do 0,0 799,799 in a single node no problem, but it's only an wrap-around that's giving me issue.

Can you give me some idea how you THINK "case" should be like? I was thinking along the line like: crud, my mind went blank lol.. Hope you can give me insight where to start on that.

Many thanks!

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
There are a bazillion possible "short cut" solutions, but they depend on which problem you want to solve. Examples of the problems are:

1) Are two points within R radius of each other?
2) What points are within R radius of a given location?
3) What points fix within a rectangle of W by H from X, Y ?
4) What points fit within a regular polygon?
5) What points fit within an irregular polygon?
6) What points fit within a 3D projection onto the map?

Each of these problems has short cut solutions, they are roughly in order of complexity. Some are better solved using n-tier computing than via a database.

So, what are we trying to do? It sounds like you're new to this kind of problem. If so, this will go a LOT faster if you describe your problem in specific, real world terms and let me find the simplest solution.

-PatP

5. Registered User
Join Date
May 2012
Posts
8
Ahh, basically.. grr hard to describe, my first experience with "complexity" of map of any sort. Normally my map are basically straight forward since it's territory based and the x/y coord are relatively easy to retrieve using:

Code:
```SELECT * FROM map
WHERE ((xaxis >= \$A1 AND xaxis <= \$A4 ) AND (yaxis >= \$B1 AND yaxis <= \$B4 ))```
Map Display like with 4 corner points, it basically divides into 16 squares but that's another story, this below is just a basic grasp on how mapping looks when retrieving coords (x,y):
A1,B1 A2,B2
A3,B3 A4,B4

Now with what I'm asking above is, say a user manually input a coord with a mile radius of 20. so if the user input 5,5 and a radius of 10, that would be 795,795 to 15,15.

Obviously I cannot use my previous code stated above, hence looking for a way to fix that with new coding "mod" but again apparently due to complexity of code, it won't work either. Hope it's clear on what I'm trying to achieve.

Thanks!
Last edited by sgkdnay; 05-26-12 at 01:44.

6. Registered User
Join Date
May 2012
Posts
8
Since you mentioned radius, gave me some idea, been googling.. and came across this. Dunno if it's ideal and applicable:

Selecting points from a database by latitude/longitude within a bounding circle

..Edit: That didn't work too well lol.. still looking
Last edited by sgkdnay; 05-26-12 at 05:22.

7. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Give parameters (left_x , right_x , left_y , right_y).
If left_x or left_y is null, assume 0.
If right_x or right_y is null, assume 799.

Shift x axis left_x to 0. Shift y axis left_y to 0.

Example:
Code:
```SELECT left_x , right_x , left_y , right_y
, COUNT(DISTINCT x) AS width_x
, COUNT(DISTINCT y) AS width_y
, COUNT(*)          AS nmbr_blocks
FROM  map
WHERE MOD( x - COALESCE(left_x , 0) + 800 , 800 )
BETWEEN 0
AND MOD( COALESCE(right_x , 799) - COALESCE(left_x , 0) + 800 , 800 )
AND MOD( y - COALESCE(left_y , 0) + 800 , 800 )
BETWEEN 0
AND MOD( COALESCE(right_y , 799) - COALESCE(left_y , 0) + 800 , 800 )
GROUP BY
left_x , right_x , left_y , right_y
;```
Note: COALESCE may be replaced by IFNULL.

8. Registered User
Join Date
May 2012
Posts
8
It looks almost convincing. Except I only have 2 columns of specific coord - xaxis and yaxis with other miscellaneous information. In your code, where would the user's input (from text box) be in the regard? x and y? Reason I asked, I tried your code and tweak it a bit, didn't give me any result unless I missed something.

Code:
```SELECT *
FROM  map
WHERE MOD( {user x-input} - COALESCE(xaxis , 0) + 800 , 800 )
BETWEEN 0
AND MOD( COALESCE(xaxis , 799) - COALESCE(xaxis , 0) + 800 , 800 )
AND MOD( {user y-input} - COALESCE(yaxis , 0) + 800 , 800 )
BETWEEN 0
AND MOD( COALESCE(yaxis , 799) - COALESCE(yaxis , 0) + 800 , 800 )```
The {user x/y-input} is what I'm assuming where user's input goes.

9. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
I assumed a table "map" exists, something like...
Code:
```CREATE TABLE map
( x INTEGER NOT NULL
, y  INTEGER NOT NULL
, PRIMARY KEY (x , y)
);

INSERT INTO
...
...```

10. Registered User
Join Date
May 2012
Posts
8
Almost but since it's a game-like stuff, it won't be anything like longitude/latitude but has a concept close to it. It will consist of x-axis an y-axis with number from 0 to 799.

For example:
A1,B1 A2,B2
A3,B3 A4,B4

The map would be something like:
0,0 0,799
799,0 799,799

My location could be 50,50, so what I wanted to do is find the special area of another building close by within 10 miles (each block is 1 miles). So that means I need to find something from 40,40 to 60,60.

Now with that, I can solve easily with the formula of:
Code:
```SELECT * FROM map
WHERE ((xaxis >= 40 and xaxis <= 60) AND (yaxis >= 40 and yaxis <= 60))```
My issue is, supposedly a user selected 0,0 and a radius of 10, which means (according to map example above): 790,790 to 10,10. That's my trouble here with "wrap-around" map. The formula doesn't recognize it.

Hope it's more clearer.

11. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Your query looks differently from my intended query,
perhaps because of different names I used.
My intended query was...
Code:
```SELECT *
FROM  map_x
WHERE MOD( xaxis - COALESCE(input_left_x , 0) + 800 , 800 )
BETWEEN 0
AND MOD( COALESCE(input_right_x , 799) - COALESCE(input_left_x , 0) + 800 , 800 )
AND MOD( yaxis - COALESCE(input_left_y , 0) + 800 , 800 )
BETWEEN 0
AND MOD( COALESCE(input_right_y , 799) - COALESCE(input_left_y , 0) + 800 , 800 )```
Note: If input date were guaranteed not null, you may want to remove all COALESCE().

If imput were input_location(input_location_x , input_location_y) and input_distance,
input_left_x, input_right_x, input_left_y, input_right_y would be calculated like...
Code:
```/* Note: these expressions are pseudo code.
you can calculate them outside SQL
or you can include them in SQL code.
*/
input_left_x   := MOD( input_location_x - input_distance + 800 , 800 )
input_right_x  := MOD( input_location_x + input_distance , 800 )
input_left_y   := MOD( input_location_y - input_distance + 800 , 800 )
input_right_y  := MOD( input_location_y + input_distance , 800 )```
Examples:
Code:
```input_location_x :=  50
input_distance   :=  10
input_left_x     := MOD( input_location_x - input_distance + 800 , 800 )
:= MOD( 50 - 10 + 800 , 800 ) := MOD( 840 , 800 )
:=  40
input_right_x    := MOD( input_location_x + input_distance , 800 )
:= MOD( 50 + 10 , 800 ) := MOD(  60 , 800 )
:=  60

input_location_x :=   0
input_distance   :=   5
input_left_x     := MOD( input_location_x - input_distance + 800 , 800 )
:= MOD(  0 -  5 + 800 , 800 ) := MOD( 795 , 800 )
:= 795
input_right_x    := MOD( input_location_x + input_distance , 800 )
:= MOD(  0 +  5 , 800 ) := MOD(   5 , 800 )
:=   5```
Last edited by tonkuma; 05-27-12 at 05:14. Reason: Add Note for sample query.

12. Registered User
Join Date
May 2012
Posts
8
It works like charm! Small tweak to use your code and into PHP format:

Code:
```\$input_location_x =  \$XCoord;
\$input_location_y =  \$YCoord;
\$input_distance   =  \$RedMiles;

\$input_left_x     = ( \$input_location_x - \$input_distance + 800 % 800 );
\$input_right_x    = ( \$input_location_x + \$input_distance % 800 );
\$input_left_y     = ( \$input_location_y - \$input_distance + 800 % 800 );
\$input_right_y    = ( \$input_location_y + \$input_distance % 800 );

\$MapSQL = "SELECT *";
\$MapSQL .= " FROM  map";
\$MapSQL .= " WHERE MOD( xaxis - COALESCE(".\$input_left_x." , 0) + 800 , 800 )";
\$MapSQL .= "       BETWEEN 0";
\$MapSQL .= "           AND MOD( COALESCE(".\$input_right_x." , 799) - COALESCE(".\$input_left_x." , 0) + 800 , 800 )";
\$MapSQL .= "   AND MOD( yaxis - COALESCE(".\$input_left_y." , 0) + 800 , 800 )";
\$MapSQL .= "       BETWEEN 0";
\$MapSQL .= "           AND MOD( COALESCE(".\$input_right_y." , 799) - COALESCE(".\$input_left_y." , 0) + 800 , 800 )";```
It gave me everything as expected! Many Many thanks for your time in attempting to put this together. I would never thought of using MOD in this type of coding. Thanks again!

13. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Code:
```\$input_left_x     = ( \$input_location_x - \$input_distance + 800 % 800 );
\$input_right_x    = ( \$input_location_x + \$input_distance % 800 );
\$input_left_y     = ( \$input_location_y - \$input_distance + 800 % 800 );
\$input_right_y    = ( \$input_location_y + \$input_distance % 800 );```
Although apart from SQL,
was these php code realy worked?
Because I saw modulus("%") has a higher precedence than addition("+") and subtraction("-") in Operator Precedence table.
PHP: Operator Precedence - Manual

Should it be like this?
Code:
```\$input_left_x     = ( \$input_location_x - \$input_distance + 800 ) % 800;
\$input_right_x    = ( \$input_location_x + \$input_distance ) % 800;
\$input_left_y     = ( \$input_location_y - \$input_distance + 800 ) % 800;
\$input_right_y    = ( \$input_location_y + \$input_distance ) % 800;```
Last edited by tonkuma; 05-29-12 at 02:44.

14. Registered User
Join Date
May 2012
Posts
8
I thought so too, but it spew out desirable result I was looking for. Even when I put in the proper () for the +/- and %, it spew the exact same result.

Thanks for pointing that out, to save myself headache in the future if something comes up haha.. again thanks!