469,148 Members | 1,333 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,148 developers. It's quick & easy.

Order by closeness to integer

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
Jul 20 '05 #1
3 2143
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.
Jul 20 '05 #2
"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)
Jul 20 '05 #3
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!
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Tony Johansson | last post: by
4 posts views Thread by Deborah V. Gardner | last post: by
1 post views Thread by S Shulman | last post: by
4 posts views Thread by nafri | last post: by
104 posts views Thread by Beowulf | last post: by
4 posts views Thread by Frederick Gotham | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.