469,148 Members | 1,333 Online

# 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.