I can't solve this problem:
I have mysql DB with many geo-coordinates.
I liked to know which points are within a specific number of miles from specific coordinates AND Which are within specific square.
Points within a specific area:
(SELECT *, 'area' as type, ( 3959 * acos( cos( radians(".$Plat.") ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(".$Plng.") ) + sin( radians(".$lat.") ) * sin( radians( lat ) ) ) ) AS distance FROM ".$db." HAVING distance < ".$radius."points within a specific square:
SELECT *, 'square' as type, -1 AS distance FROM ".$db." WHERE ((Alat <= ".$Alat." AND ".$Alat." <= Blat) OR (Alat >= ".$Alat." AND ".$Alat." >= Blat)) AND ((Alng <= ".$Alng." AND ".$Alng." <= Blng) OR (Alng >= ".$Alng." AND ".$Alng." >= Blng)) ...I use UNION to combine both queries but the thing is "type" and "distance" have different values. So I get the searched point twice. I liked to have every point just once.
But I have no idea how to handle that.
Edit:
For an overview I took just some of the data.
As result I get something like that:
+------+-----------+-----------+----------+-------------+| id | Alat | Alng | distance | type |+------+-----------+-----------+----------+-------------+| 42 | 53.704678 | 10.202164 | 12345 | area +------+-----------+-----------+------------------------+| 72 | 23.704678 | 15.202164 | 12345 | area+------+-----------+-----------+------------------------+ ......+------+-----------+-----------+------------------------+| 42 | 53.704678 | 10.202164 | -1 | square+------+-----------+-----------+------------------------+| 81 | 43.778 | 15.201212 | -1 | square+------+-----------+-----------+------------------------+ ......id(72) exists just in the area an not in the square. id(81) exists just in the square an not in the area.id(42) is in the area and in the square too. So it appears two times. But it only should once. The area should get priority.but it just should be
+------+-----------+-----------+----------+-------------+| id | Alat | Alng | distance | type |+------+-----------+-----------+----------+-------------+| 42 | 53.704678 | 10.202164 | 12345 | area +------+-----------+-----------+------------------------+| 72 | 23.704678 | 15.202164 | 12345 | area+------+-----------+-----------+------------------------+ ......+------+-----------+-----------+------------------------+| 81 | 43.778 | 15.201212 | -1 | square+------+-----------+-----------+------------------------+ ......
ليست هناك تعليقات:
إرسال تعليق