Results 1 to 14 of 14
Thread: my SQL  Wrap Around Map

052512, 19:46 #1Registered 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))
Code:SELECT * FROM map WHERE ((xaxis >= 795 AND xaxis <= 10) AND (yaxis >= 795 AND yaxis <= 10))
Code:SELECT * FROM map WHERE (((MOD(799,xaxis1) >= 0) AND xaxis <= 10) AND ((MOD(799,yaxis1) >= 0) AND yaxis <= 10)) ORDER BY xaxis,yaxis
Thanks in advance!

052512, 23:19 #2Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54The 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!
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

052512, 23:34 #3Registered 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 wraparound 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!

052612, 00:02 #4Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54There 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 ntier 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.
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

052612, 00:40 #5Registered 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 ))
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; 052612 at 00:44.

052612, 01:09 #6Registered 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 lookingLast edited by sgkdnay; 052612 at 04:22.

052612, 05:18 #7Registered 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 ;

052612, 14:13 #8Registered 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 xinput}  COALESCE(xaxis , 0) + 800 , 800 ) BETWEEN 0 AND MOD( COALESCE(xaxis , 799)  COALESCE(xaxis , 0) + 800 , 800 ) AND MOD( {user yinput}  COALESCE(yaxis , 0) + 800 , 800 ) BETWEEN 0 AND MOD( COALESCE(yaxis , 799)  COALESCE(yaxis , 0) + 800 , 800 )

052612, 16:31 #9Registered 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 ... ...

052612, 16:47 #10Registered User
 Join Date
 May 2012
 Posts
 8
Almost but since it's a gamelike stuff, it won't be anything like longitude/latitude but has a concept close to it. It will consist of xaxis an yaxis 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))
Hope it's more clearer.

052712, 04:08 #11Registered 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 )
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 )
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; 052712 at 04:14. Reason: Add Note for sample query.

052712, 16:04 #12Registered 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 )";

052912, 01:41 #13Registered 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 );
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; 052912 at 01:44.

052912, 02:59 #14Registered 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!