mrteeth mrteeth - 7 months ago 22
SQL Question

Searching within a distance in SQL

I have database with a list of contacts with puesdo coordinates of where they live. Here's a sample:

name e_point n_point
David 102 345
James 174 746
Ali 460 584
Kevin 364 479
Mark 385 274


I was wondering is it possible to create a query that can search within a distance of the two coordinates? E.g., I want a list of people who live within a 20 sqr mile radius of James.

What functions can help me do this?

Answer

Mysql syntax:

SELECT name FROM `table` WHERE SQRT(
POW(e_point - (SELECT e_point FROM `table` WHERE name='james'), 2) + 
POW(n_point - (SELECT n_point FROM `table` WHERE name='james'), 2)) < 20
AND name <> 'james'

Notice:

  1. you need to change 'name' in 3 places.
  2. subqueries added for let you run one query with just one variable (name). If you remove subqueries, you need to run 2 queries (first query retrieving coord, and second query searching near people)
  3. equation is:

enter image description here

Where p1=(p1x, p1y) and p2=(p2x, p2y)