Lat/Lng and mysql (also php)
October 18, 2022 7:36 AM
So I have a script that runs a search for some properties, the search results are supposed to be limited to a specific town but we're finding results from all over the place. I wanted to limit the results to a specific coordinate box and I tried to remove results that were outside that area but I'm getting inconsistent results with the update and select methods
eg.
87 results
No rows affected
lat and lng are both float(20,17)
I know floats have some precision issues but the properties that are outside the range aren't borderline, I've got one that is 13.72556018829345700, 100.56301879882812000, and the select finds them but the update doesn't? I'm so confused.
I may also be having a similar issue in php, I have a different search that's confined to a geocoordinate box but we found we were getting results that were outside the area (a little), so we put in an additional check
But there are still properties making it to the database that are outside that area eg. a lng in the region of 6.87>4>
eg.
SELECT * FROM properties WHERE (lat <-17.94 OR lat>-17.51 OR lng<30.92 OR lng>31.26) AND offline=0
87 results
UPDATE properties SET offline=1 AND offline_date=NOW() WHERE (lat <-17.94 OR lat>-17.51 OR lng<30.92 OR lng>31.26) AND offline=0
No rows affected
lat and lng are both float(20,17)
I know floats have some precision issues but the properties that are outside the range aren't borderline, I've got one that is 13.72556018829345700, 100.56301879882812000, and the select finds them but the update doesn't? I'm so confused.
I may also be having a similar issue in php, I have a different search that's confined to a geocoordinate box but we found we were getting results that were outside the area (a little), so we put in an additional check
if((float)$t->lat>43.564640138848475 || (float)$t->lat<43.53631009849972 || (float)$t->lng>7.042658847696089 || (float)$t->lng <7.002146762735151)
{
continue;
}
But there are still properties making it to the database that are outside that area eg. a lng in the region of 6.87>4>
Unless I'm misunderstanding, shouldn't these 'ors' be 'ands'?
lat <>-17.51 OR lng<30.92 OR lng>31.26>
posted by Don_K at 8:16 AM on October 18, 2022
lat <>-17.51 OR lng<30.92 OR lng>31.26>
posted by Don_K at 8:16 AM on October 18, 2022
There's a lot of questions in here!
I agree with mekily - your update statement appears to be incorrect. See this link for MySQL update statement syntax.
Floats in general are kind of hinky to compare in computer programming. MySQL specifically recommends to check that your numbers are in acceptable bounds (something like:
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
-> GROUP BY i HAVING ABS(a - b) > 0.0001; ).
See this link for more info.
It also looks like your query might not be correctly formatted? If your intent is to constrain lat between -17.94 and -17.51, and lng between 30.92 and 31.26, I agree with Don_K - your ORs should be ANDs. So you'd want something like:
SELECT * FROM properties
WHERE (lat < -17.94 AND lat > -17.51 AND lng > 30.92 AND lng < 31.26) AND offline=0
Good luck!>
posted by Stephanie Duy at 8:22 AM on October 18, 2022
I agree with mekily - your update statement appears to be incorrect. See this link for MySQL update statement syntax.
Floats in general are kind of hinky to compare in computer programming. MySQL specifically recommends to check that your numbers are in acceptable bounds (something like:
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
-> GROUP BY i HAVING ABS(a - b) > 0.0001; ).
See this link for more info.
It also looks like your query might not be correctly formatted? If your intent is to constrain lat between -17.94 and -17.51, and lng between 30.92 and 31.26, I agree with Don_K - your ORs should be ANDs. So you'd want something like:
SELECT * FROM properties
WHERE (lat < -17.94 AND lat > -17.51 AND lng > 30.92 AND lng < 31.26) AND offline=0
Good luck!>
posted by Stephanie Duy at 8:22 AM on October 18, 2022
(I believe missmagenta is trying to query rows outside the bounding box — so that they can be marked as invalid — therefore the ORs are correct.)
posted by mekily at 8:24 AM on October 18, 2022
posted by mekily at 8:24 AM on October 18, 2022
The OR/AND thing is likely your problem here. Just wanted to add the naive bounding box you're doing is not really correct and won't work well for, say, far Northern cities. It may be close enough for your purposes, but the right way is to use a spatial database and a function like ST_Distance() to query for things near a point (or some more complex spatial query). You'll get spatial indexing too, which makes the query much more efficient.
MySQL has some basic GIS capability now. I'd use Postgres+PostGIS or Spatialite myself. Switching to that is a moderately complex project though, so if the simple bounding box works for you then great!
posted by Nelson at 8:37 AM on October 18, 2022
MySQL has some basic GIS capability now. I'd use Postgres+PostGIS or Spatialite myself. Switching to that is a moderately complex project though, so if the simple bounding box works for you then great!
posted by Nelson at 8:37 AM on October 18, 2022
Thanks mekily, I got distracted by floats (and why the results even got there in the first place) and missed the obvious!
posted by missmagenta at 8:43 AM on October 18, 2022
posted by missmagenta at 8:43 AM on October 18, 2022
This thread is closed to new comments.
SET offline=1 AND offline_date=NOW()
you should instead write
SET offline=1, offline_date=NOW()
(my memory on this is a bit spotty so it’s possible I’m wrong here and AND does in fact work as well, but that’s the first thing that stood out to me)
posted by mekily at 8:11 AM on October 18, 2022