Find the greatest and the smallest
-
Hello, I have a database where I store points by memorizing their coordinated in two columns namely x and y. I have written a method that allows the user to insert two arbitrary coordinates (x,y) and the method will return all the points in a 1,5,10,..n km radius. With these query result I want to find the point with the smallest x coordinate, the point with the greatest x coordinate, that with the smallest y coordinate, and that with the greatest y coordinate. I need to do this in order to be able to fit all the area that has been returned into a 200x200 pixels area. The first idea that I got is to sort the dataset by column x and extract the first and last record, then sort again by y column and do the same. Is there any better way to achieve this? I need fast performance because the program will be computing thousands of query from different users at the same time. Thanks in advance, Edo
-
Hello, I have a database where I store points by memorizing their coordinated in two columns namely x and y. I have written a method that allows the user to insert two arbitrary coordinates (x,y) and the method will return all the points in a 1,5,10,..n km radius. With these query result I want to find the point with the smallest x coordinate, the point with the greatest x coordinate, that with the smallest y coordinate, and that with the greatest y coordinate. I need to do this in order to be able to fit all the area that has been returned into a 200x200 pixels area. The first idea that I got is to sort the dataset by column x and extract the first and last record, then sort again by y column and do the same. Is there any better way to achieve this? I need fast performance because the program will be computing thousands of query from different users at the same time. Thanks in advance, Edo
Well, firstly you can use the SQL Aggregation functions
MINandMAXon the columns... i.e.SELECT MAX(xCord) FROM Coords. If you want to tie it into one query you'll probably have to start using sub-queries. Performance wise, I'm not sure how you'll optimise it. The only other alternative would be to re-visit the design so that if it's read-intensive that the majority of the calculations etc. are done during an insert or update, whether that's possible (and how it might be so) is dependent upon the application and design, sorry to be so vague :) -- Paul "Put the key of despair into the lock of apathy. Turn the knob of mediocrity slowly and open the gates of despondency - welcome to a day in the average office." - David Brent, from "The Office" MS Messenger: paul@oobaloo.co.uk Download my PGP public key