Sorting by Lat\Long

[BrianGustafson]

We are running reports out of the database and trying to find a way to sort by Lat\Long in SQL.  Has anyone come across a way to tackle this issue?  We don't want to maintain a field only for the purpose of ordering records.

[JoeBorgione]

We are running reports out of the database and trying to find a way to sort by Lat\Long in SQL.  Has anyone come across a way to tackle this issue?  We don't want to maintain a field only for the purpose of ordering records.

Would this work?

Ordering by two columns. This query first sorts in ascending order by the FirstName column, then sorts in descending order by the LastName column.

Copy
SELECT LastName, FirstName FROM Person.Person
WHERE LastName LIKE 'R%'
ORDER BY FirstName ASC, LastName DESC ;

Found it at a microsoft site after a google search....

[BrianGustafson]

That won't work.  I am looking for a function or something that actually takes into account that the values are a lat long value pair not just two columns with numbers in them.

[VinceAngelo]

What database are you using? 

What is the geometry storage type?  Does it have SQL accessor functions that would allow
access to the location (point) or envelope corners (non-point)?

- V

[BrianGustafson]

We have an oracle database which is not oracle spatial.  In Toad it says that the shape field is an integer.

[VinceAngelo]

You have a lot fewer options than if the column was ST_GEOMETRY or even SDO_GEOMETRY
(which doesn't require Spatial -- just Intermedia).  You'd get a lot better performance if you
added sort_key or lat/lon columns to the business table.  

What application will be making the query?  And what version?  (Accessing shape properties
is easy from ArcGIS [and unsupported from other applications], but doing an ORDER BY in
ArcGIS is difficult.)

- V

[BrianGustafson]

There are lat long columns in the table and they are stored as a double.  We are planning on running a straight sql query(Toad) and then using that to populate a report in a custom Silverlight application.

[VinceAngelo]

If you have the needed information in the table, then Joe's description of how to use an
ORDER BY clause should suffice; you just need to determine if you want North->South
ordering (DESCending) or South->North (ASCending), and if you want latitude-major or
longitude-major ordering.

If you want ordering by map page, you'd need to either add a page column to the table
or do the math to simulate one --

ORDER BY (trunc((lon+180) / 60)*6 + trunc((180-lat)/30)),lon,lat DESC

[longitude major, N->S, with 36 pages 60deg x 30deg]

- V