0

I'm developing a website using PHP. I have a MySQL database and it has three columns called name, latitude and longitude. It is as follows.

Name | latitude | longitude
----------------------------
AAAA| 6.9270974 | 79.8612478
BBBB| 7.2667576 | 80.380611

My interface has three text boxes called txtname, txtlatitude, txtlongitude and they return values just like this.

txtname - CCCC
txtlatitude - 6.9271786
txtlongitude - 79.86224300000006

I want to update the “Name” column of the database table which is approximately similar to the latitudes and longitudes values of these text boxes.

(For example, according to the above database table, I want to replace “AAAA“ with “CCCC”)

Is it possible? Any help would be appreciated.

Thanks.

1
  • 3
    How similar is "similar"? Oct 27, 2015 at 7:54

1 Answer 1

1

Yes you can do that. The easiest way to do if your amount of rows is relatively small is:

update vertex set name = 'CCCC' 
where latitude between 6.9271786 * 0.99 and 6.9271786 * 1.01 and 
      longitude between 79.86224300000006 * 0.99 and 79.86224300000006 * 1.01;

With of course setting your threshold to the right level. The above example assumes 1% which is huge in terms of geo coordinates but you get the idea.

Since MySQL can only use one column for range queries from a composite key having a combined index won't help but separate index on latitude and longitude will. Then MySQL can choose the one which is more distinctive for the specific case.

create index lat_idx on vertex (latitude);
create index lat_idx on vertex (longitude);

For further optimizations if necessary you can look into spatial indexes (you haven't mentioned which MySQL version and engine you're using).

Also you can geohash the coordinates into a single numerical column and have proximity match on that then filter by the coordinates which I use on bigger database and works like a charm.

https://en.wikipedia.org/wiki/Z-order_curve https://en.wikipedia.org/wiki/Geohash

2
  • MySQL 5.6 and newer can use Index Condition Pushdown which allows to check both columns before ever touching the table - again having both versions ((latitude, longitude) and (longitude, latitude)) can help (how much depends on your specific case).
    – jkavalik
    Oct 29, 2015 at 6:24
  • @KarolyNagy - keep in mind that Z-order can need up to 4 tests due to the edge cases.
    – Rick James
    Dec 7, 2015 at 6:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.