Lat/Lng and mysql (also php)
October 18, 2022 7:36 AM   Subscribe

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.
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.8
posted by missmagenta to Computers & Internet (6 answers total)
 
Best answer: I’m not sure whether this is your main issue, but I believe MySQL syntax uses commas to separate the values to be updated, rather than AND. So where you have

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 [1 favorite]


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


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 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 [2 favorites]


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 [2 favorites]


Response by poster: 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


« Older Potato, תפוח אדמה.   |   FMLA vs sick leave Newer »
This thread is closed to new comments.