Results 1 to 14 of 14
  1. #1
    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?

    Thanks in advance!

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

  3. #3
    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. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    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. #6
    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. #7
    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. #8
    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. #9
    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. #10
    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. #11
    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. #12
    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. #13
    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. #14
    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!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •