CODEWITHSUNDEEP
mysqllatitude-longitude
Kevin Revill
Kevin Revill

Reputation: 15

How do i in mysql match all rows in table A with all rows in table b based on the nearest longitude and latitude point?

HI Guys first question here. I have simplified the table structure below to make it more apparent the problem. I am using mysql and I have no idea where to start with this one. I need to find the closest store for each of the customers in my database.

I have table "A" which contains the following

id primary key
customer - name of my customer
longitude
latitude

I have table "B"

id 
storeName - name of one of our branches
longitude
latitude
storeAddress - Address of store.

I wish to connect these two tables together so I get the following output

result set

A.id <- customers id from table
A.customerName
B.id relabelled as store_id
B.StoreName
B.longitude
B.latitude
B.storeAddress

Any ideas guys?

Thus when I run the query I get the nearest store for each of my customers in my database.

Upvotes: 0

Views: 91

Answers (1)

0ndre_
0ndre_

Reputation: 3631

SELECT latitude, longitude, SQRT(     POW(69.1 * (latitude - [startlat]), 2) +     POW(69.1 * ([startlng] - longitude) * COS(latitude / 57.3), 2)) AS distance FROM TableName1  HAVING distance < 25 ORDER BY distance;

This sql gives you data sorted by nearest location and this question should help you with those two tables How can an SQL query return data from multiple tables . Hope it will help you.

Upvotes: 2

Related Questions