Home » SQL & PL/SQL » SQL & PL/SQL » Results sorting from the middle outward
Results sorting from the middle outward [message #294434] Thu, 17 January 2008 11:02 Go to next message
jman27
Messages: 37
Registered: September 2005
Member
I have a database of points of interest on a map. Each record contains a longitude/latitude. I'm using these points to plot on a MS virtual earth.

Just to keep things simple, I'll break the table down pretty simple. Assume that the table has the following columns:

ID
DESC
LONG
LAT

Because I have tens of thousands of items in the database, when viewing the map I only want to display a maximum of 100 (or the map gets too cluttered). What I would like to do is start placing items on the map starting at the middle and continuing outward in all directions. I can get the longitude/latitude of the middle as well as the 4 corners of the map.

My question... when querying for all records between a particular longitude/latitude (for each of the 4 corners of the map), is it possible to order the results in a way of beginning with the center?

To make this question a little simpler, perhaps we should assume a 10x10 square, X-axis is 1 to 100 and Y-axis is -1 to -100. Is it possible to order results beginning with the center most result and moving outward?

(hopefully that made some sense)
Re: Results sorting from the middle outward [message #294437 is a reply to message #294434] Thu, 17 January 2008 11:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Subtract the latitude of the record from the latitude of your centerpoint, do an ABS() on that and order by that.
Re: Results sorting from the middle outward [message #294439 is a reply to message #294437] Thu, 17 January 2008 11:55 Go to previous messageGo to next message
jman27
Messages: 37
Registered: September 2005
Member
Interesting... I'll give that a shot - thanks!
Re: Results sorting from the middle outward [message #294442 is a reply to message #294434] Thu, 17 January 2008 12:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many possible orders:
- you can order by the distance to the center point
- you can use a lexicographic order from coordinates relative to the center point

Regards
Michel
Re: Results sorting from the middle outward [message #294479 is a reply to message #294442] Thu, 17 January 2008 14:01 Go to previous messageGo to next message
jman27
Messages: 37
Registered: September 2005
Member
Great suggestions...

It seems I'm probably going to have to pull out my old geometry books to figure out the closest points. I can order by the latitude (or longitude) as Frank suggested using ABS()... but because the next closes latitude could be very far away on the longitude scale, it may not be the next closest point. I need to order them in 2 dimensions with respect to both longitude and latitude. Perhaps a little a^2+b^2=c^2. Smile

But... you got me on the right track. So thanks.

[Updated on: Thu, 17 January 2008 14:10]

Report message to a moderator

Re: Results sorting from the middle outward [message #294481 is a reply to message #294479] Thu, 17 January 2008 14:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle has a "spatial" option with so many geographic powerful functions.
But this is not free.

Regards
Michel
Re: Results sorting from the middle outward [message #294550 is a reply to message #294479] Thu, 17 January 2008 23:58 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
jman27 wrote on Thu, 17 January 2008 21:01
Great suggestions...

It seems I'm probably going to have to pull out my old geometry books to figure out the closest points. I can order by the latitude (or longitude) as Frank suggested using ABS()... but because the next closes latitude could be very far away on the longitude scale, it may not be the next closest point. I need to order them in 2 dimensions with respect to both longitude and latitude. Perhaps a little a^2+b^2=c^2. Smile

But... you got me on the right track. So thanks.

Not sure if this will work, but my first idea would be to use Pythagoras on the subtractions. That would give you a good indication of the distance.

[Edit: oops... Now I see you thought of that too.. I must read posts right up till the end next time BEFORE replying Smile]

[Updated on: Thu, 17 January 2008 23:59]

Report message to a moderator

Previous Topic: Date Between Help
Next Topic: create a file that contains the output
Goto Forum:
  


Current Time: Fri Dec 09 17:13:52 CST 2016

Total time taken to generate the page: 0.57214 seconds