Getting correct data from X/Y Coords within certain range BUT including Width & Height

user_1255427 0
Viewed 112

I'm writing radar logic that finds the nearby objects and then displays them in a 'radar' window as such:

enter image description here

The logic (That works!) is as shows - slightly simplified from the original:

 -- 5000 = radar distance
 -- Width can be called from a.width
 -- Height can be called from a.height

.

select * from positions a
    inner join positions b on b.user_id = :user_id
    left join users u on a.user_id = u.id
where 1=1
and (
    a.x >= (b.x - 5000)
 && a.x <= (b.x + 5000)
 && a.y >= (b.y - 5000)
 && a.y <= (b.y + 5000)
)

The problem I have is that some objects are very large. I.e. larger than even the radar distance. This means that if the center point of the large object creeps out of the radar distance, the whole object goes even if the height/width still would span within the radar distance.

Here is an example of the problem (Moving left causes the yellow shape to disappear even though it is still technically in radar view distance however the center point of the object left the radar distance causing it to not show in the sql results):

enter image description here

I hope I have explained myself well enough to be understood. Here are my attempts to resolve this issue myself (None I've manged to get working):

Failed attempt #1:

select * from positions a
    inner join positions b on b.user_id = 10
    left join users u on a.user_id = u.id
where 1=1
and (
    a.x >= ((b.x+a.width) - 5000)
 && a.x <= ((b.x-a.width) + 5000)
 && a.y >= ((b.y+a.height) - 5000)
 && a.y <= ((b.y+a.height) + 5000)
)

Failed attempt #2:

select * from positions a
    inner join positions b on b.user_id = 10
    left join users u on a.user_id = u.id
where 1=1
and (
    a.x >= (b.x - 5000)
 && a.x <= (b.x + 5000)
 && a.y >= (b.y - 5000)
 && a.y <= (b.y + 5000)
)
OR (
    (a.x+a.width) >= ((b.x+a.width) - 5000)
 && (a.x-a.width) <= ((b.x-a.width) + 5000)
 && (a.y+a.height) >= ((b.y+a.height) - 5000)
 && (a.y-a.height) <= ((b.y+a.height) + 5000)
)

I think I'm getting to the point where I'm starting to confuse myself. Please do let me know if you need any additional information.

Thank you for considering my question

More thorough example:

Every object has a height/width/x/y coordinate as such:

+----+------+------+--------+-------+
| id |  x   |  y   | height | width |
+----+------+------+--------+-------+
|  1 |  100 |  100 |    150 |   150 |
|  2 | -250 |  500 |    150 |   150 |
|  3 | 5000 | 2000 |  10000 | 10000 |
+----+------+------+--------+-------+

Imagine there is an arbitrary 'radar distance' set to 5,000.

If I am sitting at coordinates: 0x, 0y I can see ID3. If I move to coordinates: -100x, 0y my SQL no longer retrieves the ID3 because the central point of the coordinates expands outside the 5,000 radar distance. However - the width expands 50% into the radar and the height expands 50% into the radar which means the object should still be seen and retrieved via SQL.

SQL Fiddle (Change -100 to 0 and you will see the large object in the returned data again)

mariadb logic coordinates mysql sql
3 Answers

Okay, I think I worked out the answer to my question. I think this does the job:

set @my_x = -100;
set @my_y = 0;
set @radar_distance = 5000;

select * from positions a
where 1=1
and (
    a.x >= (@my_x - @radar_distance)
 && a.x <= (@my_x + @radar_distance)
 && a.y >= (@my_y - @radar_distance)
 && a.y <= (@my_y + @radar_distance)
)
or (
    a.x+(a.width/2) >= (@my_x - @radar_distance)
 && a.x-(a.width/2) <= (@my_x + @radar_distance)
 && a.y+(a.height/2) >= (@my_y - @radar_distance)
 && a.y-(a.height/2) <= (@my_y + @radar_distance)
)

Although I think, still, if the dimensions of the object stretched longer than the radar it would still break. I will be testing now

Yes, this works.

I also think I can take the whole first block of conditions out before my OR as well. Will need to test

SQL Fiddle

user_12554270

While using the square limits appropriately limits the data, the distance from a point needs to be the main SQL criteria.

SELECT * FROM positions a
INNER JOIN positions b ON b.user_id = 10
WHERE POW(a.x - b.x, 2) + POW(a.y - b.y, 2) < POW(5000,2);
user_101951530

Your "width" and "height" sound like the "bounding box" approach to graphical distances. Do you also do the Pythagorean distance as danblack suggested?

You actually have two widths and heights (or two radiuses) -- one for how far your radar stretches, one for how far each object stretches. Something like:

 AND a.x >= ((b.x+a.width) - (5000 + b.width))
 AND a.x <= ((b.x-a.width) + (5000 + b.width))
 AND a.y >= ((b.y+a.height) - (5000 + b.height))
 AND a.y <= ((b.y+a.height) + (5000 + b.height))

This implies having a height and width (and radius) for each object.

user_17668310
Related