topic Re: Sorting by Lat\Long in Data Management Questions

Sorting by Lat\Long

BrianGustafson — Thu, 23 Jun 2011 15:00:25 GMT

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.

Re: Sorting by Lat\Long

JoeBorgione — Thu, 23 Jun 2011 16:59:00 GMT

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

Re: Sorting by Lat\Long

BrianGustafson — Thu, 23 Jun 2011 18:01:08 GMT

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.

Re: Sorting by Lat\Long

VinceAngelo — Thu, 23 Jun 2011 18:11:45 GMT

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

Re: Sorting by Lat\Long

BrianGustafson — Thu, 23 Jun 2011 18:14:59 GMT

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

Re: Sorting by Lat\Long

VinceAngelo — Thu, 23 Jun 2011 19:47:53 GMT

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

Re: Sorting by Lat\Long

BrianGustafson — Fri, 24 Jun 2011 00:42:24 GMT

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.

Re: Sorting by Lat\Long

VinceAngelo — Fri, 24 Jun 2011 13:58:13 GMT

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