mrteeth mrteeth - 2 years ago 91
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 Source

Mysql syntax:

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'


  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)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download