Query database values based on user's location

Question

How can I perform a query on the database as per the user's location value? The application was developed with HTML5, CSS, Javascript, PHP has a database with columns as in the below table.

On the html webpage the users geo coordinates are collected and are to be compared with the values in the database to find the nearest place to the user with the places in the database.

Please let me know how to achieve this. Any examples / samples will be appreciated.

Answer 1

For a rough measure I would try something like the following (only Euclidean geometry, it doesn't take into account the Earth curvature or problems like this).

First you could compute the difference between the user's coordinates and the coordinates of places in the database. Like this:

distLat = abs(userLat - placeLat)
distLong = abs(userLong - placeLong)

Then I would compute the distance between the two points using Pythagora's theorem. So:

distance = squareRoot(distLat * distLat + distLong * distLong)

You can compare the distances of all places in the database and take the minimum, which teoreticaly is the place nearest to the user's position.

If you use MySQL I think that a query like this should work:

SELECT * FROM places ORDER BY MIN(SQRT((p.latitude - userLatitude) * (p.latitude - userLatitude) + (p.longitude - userLongitude) * (p.longitude - userLongitude))) LIMIT 1

Beware that this query could be very slow depending on how many places you have, because it needs to read all the rows in the table and compute the distance for each one. Indexes have no effects.

Anyway, for this kind of problems you should better use GIS or databases with good geospatial extensions. MySQL geospatial extension is not very powerful, even in MySQL 5.6. It has a ST_DISTANCE function but still uses Euclidean geometry which is not very accurate for calculation on a spherical surface like the Earth. Anyway, if you use MySQL 5.6, I think that it should be better to use the ST_DISTANCE function, which is for sure much more optimized than doing calculations "manually" in the query.

See also this article for a deep explanation and more examples: http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc

EDIT

As requested by the OP, I add more details on how to deal with negative coordinates.

Negative coordinates are generally not a big issue. I will try to explain this with some examples.

Take for example the coordinates of the Brooklyn bridge: 40.704391, -73.994675. With the above formula, the distance of the Brooklyn bridge from the Statue of Liberty is:

sqrt((40.704391 - 40.689167)^2 + (-73.994675 - -74.044444)^2) = 0.052045399

Now consider the distance between the Statue of Liberty and the Brooklyn Bowl (coordinates 40.7179666, -73.9670125), that is:

sqrt((40.7179666 - 40.689167)^2 + (-73.9670125 - -74.044444)^2) = 0.082613886

As you can see the distance of the Brooklyn Bowl from the Statue of Liberty is bigger than the distance of the Brooklyn Bridge. It is correct since the Brooklyn Bowl is 4 miles away from the Statue of Liberty, while the Brooklyn Bridge is only 1 mile away from it.

In this example both the two points has a negative longitude. But the formula works even if one of the two has positive coordinates. For example, the distance between the Statue of Liberty and the Tour Eiffel (Paris, coordinates 48.858360, 2.294460), is:

 sqrt((48.858360 - 40.689167)^2 + (2.294460 - -74.044444)^2) = 76.77476134

Then calculate also the distance between the Statue of Liberty and the Colosseum (Rome, coordinates 41.890238, 12.492242):

sqrt((41.890238 - 40.689167)^2 + (12.492242 - -74.044444)^2) = 86.54502063

As you can see it works, the distance of the Colosseum is bigger since it is about 8000km away from the Statue of Liberty, while the Tour Eiffel is about 800 km closer.

The only issue I see is when you have to calculate the distance between two places that are in the far east and in the far west respectively. The above formula will give you a very high distance, but actually the they could be very close. Take for example Anchorage, a city in Alaska (coordinates 61.252240, -149.896769), and Beringovskij, a city in the very east of Russia (coordinates 63.049797, 179.310011). They have a distance of only about 1500 km but with the above formula you get:

sqrt((61.252240 - 63.049797)^2 + (-149.896769 - 179.310011)^2) = 329.2116875

Definitely a too high value for only 1500 km: I would expect something less than 50.

The problem is that the formula calculates the distance taking the central meridian as a reference point, that is the meridian with a 0 degrees latitude. This is good until the distance is "no more that half the Earth".

I think that a solution could be to calculate two distances. The first with a reference point of 0 degrees: it is what the above formula does. The second with a reference point of 180 degrees. It's like calculating the distance on a world map shifted by 180 degrees, like this: http://www.bouwman.com/world/Formilab-180.html. And then take the minimum of these two distances.

Thus the formula becomes a little more complex:

distance = squareRoot(min((userLat - placeLat)^2, (userLat - placeLat - 360)^2) + (userLong - placeLong)^2)

Note that we subtract 360 because it is the distance between the degree -180 and the degree 180.

With this new formula we get correct results for places that are more then 180 degrees away from each other, and we get also the same result given by the previous formula when comparing places that are less then 180 degrees away from each other. The calculate distance Anchorage - Beringovskij is now: 30.84564166.

Of course, as I have already said, this is not an exact method for calculating distances. You can take a look at this article for more "scientific" techniques: https://en.wikipedia.org/wiki/Geographical_distance :D

Answer 2

There is a question that compares the capabilities of various spatial databases, GIS: PostGIS/PostgreSQL vs. MySql vs. SQL Server?, where Postgis comes out a pretty clear winner over MySQL.

Whether you use MySQL or Postgis, you would be much better off, if you can, storing your latitude and longitude values as a geometry/geography (Point), as the functions that can be used to find things nearby, ST_Distance, ST_Distance_Sphere and the more obscure <-> operator, see Find n Nearest Neighbors for given Point using PostGIS? (for example usage) work directly on geometry/geography columns. Even more importantly, you can add a spatial index, which these functions need to work properly, which will outperform searches on separately indexed latitude and longitude columns by a large margin (this will depend on table size, but will grow as table size grows),

In Postgis, you can convert lat and lon to a geometry with:

alter table mytable add column geom (Geometry, 4326);
update mytable set geom = ST_SetSRID(ST_MakePoint(lon, lat), 4326)
create index ix_spatial_mytable_geom on mytable using gist(geom);

At this point, you will be able to very efficient queries to find points near other points, using any of the examples in the above links.

You can do similar things in MySQL, although, it does not support a spatial reference system, ie, the 4326 above, which means lat/lon, and it lacks a ST_MakePoint function, so you would need to use STGeomFromText and concatenate the lat/lon together to make a POINT. It also does everything in planar coordinates, as Claudio and others have stated, which is not an issue with Postgis.

I apologize for a long and somewhat tangential answer, but having done various migrations between databases on large amounts of data (MySQL, SQL Server and Postgres/GIS) and made lots of mistakes on the way, I hope I can set you off in the right direction (and add a bit of future proofing, if you want to start using some other spatial functionality, which Postigs has in spades).