SQL: search of nearest in 2d square and circle.

Question

I have a table: points

it has 2 filds: X, Y

each row represents a dot on 2d field

I want to be capable of performing search of dots with in some radius R like

and is square with side A like

BTW: I use PHP to access my DB.

My main point is to get nearest to center of figure point as first result in the quqe like

How to do such thing in SQL?

Answer 1

Mathematically point in circle statisfies equation

(c.X-p.X)^2 + (c.Y-p.Y)^2 <= R^2

Where c is the cetner of circle, p - point, R - radius

For square

max(abs(c.X-p.X),abs(c.Y-p.Y)) <= A/2

Where c is the cetner of square, p - point, A - side of square

You can just write theese equations in any language.

Left side of equations called distance for various measures. For finding nearest point you should order resultset by distance asceniding and take first result.

Something like this:

select top 1 p.X, p.X from Points p
otrder by ((@x - p.X)*(@x - p.X)+(@y - p.Y)*(@y - p.Y))

Answer 2

Gandjustas answer is an ok solution if the your queries are not the core of your application. If this geometircal/spatial data is very important to you and you need speed when working with such data specifically you should look into geospatial extensions for your RDBMS.

I will assume that you use mysql, you have spatial extensions on your disposal. With proper data types and indexes that will give you

Distance(g1, g2)

and other usefull functions.