CODEWITHSUNDEEP
mysqlgeolocationmaps
user1787489
user1787489

Reputation: 1007

How can I filter the results of a database of addresses based on their distance from a specific address?

I have a database of local businesses on my website.

Essentially i need to add a feature where the user can enter their address and a "max distance" filter and only have results within that max distance from their address returned.

Which library/program/api/ has the capabilities i require?

I have researched the google maps api and it is not feasible, given that their quotas on distance matrix calculations are incredibly restrictive, at 2500 elements per day.

Upvotes: 0

Views: 239

Answers (2)

joseangelmt
joseangelmt

Reputation: 2148

You need to use the "spatial" capabilities of your database. If you database server does not support it, try to find a "spatial" plugin for it. I don't know if with mySql is possible. With SQL Server and with PostGIS.

If your database is SQL Server 2008 for example:

Add a "geography" field to the address table and add a point with the geographic coodinates (latitude, longitude) of each local business:

INSERT INTO @addressTable (GeographyColumn) VALUES (geography:STGeomFromText('POINT(longitude,latitude)' , 4326))

(the value 4326 means WGS84 coordinate system (that's used by your GPS))

Then when quering create an object with the coordinates of the client:

DECLARE @client geography
SET @client = geography::STPointFromText('POINT (longitude, latitude)', 4326)

SELECT * 
FROM addressTable
ORDER BY Location.STDistance(@client)

Upvotes: 1

Uday Sawant
Uday Sawant

Reputation: 5798

You can get latLong of both addresses (using geoCoding on google maps api), Then you can compare them with mysql query.
Check this Google search page for a list of Stack Exchange questions about how to points within specified distance or distance look-up.

Upvotes: 0

Related Questions