Order by closeness to integer

[Joseph Hill]

Hi

My queries need to search for 1600 higher, and 1600 lower than a given
integer. One integer is a 'northing' and another an 'easting' - map
grid references.

I would like the results returned to be ordered with the numbers
'closest' to the given northing and easting combing first.

my query is

---
SELECT userid,postcode,northing,easting FROM memberpostcodes WHERE
(northing > $min_seek_northing) AND (northing < $max_seek_northing)
AND (easting > $min_seek_easting) AND (easting < $max_seek_easting)
---

Therefore, I cannot 'order by easting' etc - as it'll unfairly load
the results (I'm trying to list the results in order of closeness).

Any ideas?

Joe

[Bill Karwin]

Joseph Hill wrote:

I would like the results returned to be ordered with the numbers
'closest' to the given northing and easting combing first.

Use the Pythagorean theorem to compute distance between two points:
distance is SQRT(X*X+Y*Y), where X and Y are the differences between the
north and east components of the points. But you can skip the square
root computation, because you don't need it just to sort.

You're trying to compute distance from each row's point to your given
point (e.g. $given_northing,$given_easting).

SELECT userid,postcode,northing,easting
FROM memberpostcodes
WHERE (northing BETWEEN $min_seek_northing AND $max_seek_northing)
AND (easting BETWEEN $min_seek_easting AND $max_seek_easting)
ORDER BY ABS(northing-$given_northing)*ABS(northing-$given_northing)
+ABS(easting-$given_easting)*ABS(easting-$given_easting)

Regards,
Bill K.

[Siemel Naran]

"Bill Karwin" <bi**@karwin.com> wrote in message
news:cn********@enews4.newsguy.com...

SELECT userid,postcode,northing,easting
FROM memberpostcodes
WHERE (northing BETWEEN $min_seek_northing AND $max_seek_northing)
AND (easting BETWEEN $min_seek_easting AND $max_seek_easting)
ORDER BY ABS(northing-$given_northing)*ABS(northing-$given_northing)
+ABS(easting-$given_easting)*ABS(easting-$given_easting)

There is a POW function in MySql too.

ORDER BY POW(northing-$given_northing, 2) + POW(easting-$given_easting,
2)

[Joseph Hill]

Bill Karwin <bi**@karwin.com> wrote in message news:<cn********@enews4.newsguy.com>...

SELECT userid,postcode,northing,easting
FROM memberpostcodes
WHERE (northing BETWEEN $min_seek_northing AND $max_seek_northing)
AND (easting BETWEEN $min_seek_easting AND $max_seek_easting)
ORDER BY ABS(northing-$given_northing)*ABS(northing-$given_northing)
+ABS(easting-$given_easting)*ABS(easting-$given_easting)

Thanks Bill!