CODEWITHSUNDEEP
mysqlsearchgeolocationgeocoding
Justgrant2009
Justgrant2009

Reputation: 603

Using Geo Tracking and an SQL query

I'm building a database where the user will be able to type in a search field for what they are looking for, and I need the results to list in a manner where those closest to the user will show first, and work their way further from the user's current location.

So I'm looking for a method to include (factor in) Geo location with a query. The database will have a multitude of fields that will be searched through, including the location of each.

This whole process will be much like that of Google's Map location searches (ie. Searching for "Restaurants near me").

I've tried googling for this answer, I've asked on other forums, and I've searched stackoverflow and have yet to find help in accomplishing this.

Upvotes: 0

Views: 314

Answers (1)

Eric J.
Eric J.

Reputation: 150108

You can use MySQL Spatial Extensions to order results by distance

http://dev.mysql.com/doc/refman/5.5/en/spatial-extensions.html

That allows you to do queries like

SELECT name, AsText(location), SQRT(POW( ABS( X(location) - X(@center)), 2) + POW( ABS(Y(location) - Y(@center)), 2 )) AS distance 
FROM Points 
WHERE Intersects( location, GeomFromText(@bbox) ) 
AND SQRT(POW( ABS( X(location) - X(@center)), 2) + POW( ABS(Y(location) - Y(@center)), 2 )) < @radius 
ORDER BY distance;

http://howto-use-mysql-spatial-ext.blogspot.com/

Upvotes: 1

Related Questions