Use MySQL LIKE or alternative?

Question

Users of my DB can add their favorite locations to a DB. That table contains GPS coordinates as well as names. I want to minimize redundancy and when a user tries to add a new location to the DB, check whether one already exists. I can surely go by GPS coordinate. However, I also have to check the name of the location. Hence, users can misspell the identical location, so my check should be tolerant to false typing. On the other hand, they may mean something else.

This is not interactive, so no user feedback and I must avoid the number of false positives in the query. Any way to adjust the LIKE lexicographical tolerance?

Here's the SQL code I thought of:

SELECT id FROM Locations AS L WHERE L = :LocationName AND distance(L.lat,L.lng,:lat,:lng) < 100;

where distance is the GPS distance in meters.

http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_soundex — juergen d, Apr 05 '16 at 22:18
[Levensteihn distance](http://stackoverflow.com/questions/13909885/how-to-add-levenshtein-function-in-mysql) — Kamil Gosciminski, Apr 05 '16 at 22:21
One way to handle this is to enforce correct location names. Either correct them after the user has entered them, or only allow the user to select (not type) a location name. — devlin carnate, Apr 05 '16 at 22:22
What @juergend said... SOUNDEX(str) He gave the right answer. — Ricardo C, Apr 05 '16 at 22:25

Use MySQL LIKE or alternative?

0 Answers0