SQL Fun

tolosenc

From having, well, not cared about Databases/SQL in college to going through a ton of it most days in work, my ability has grown exponentially. Was playing around with some stuff and came up with a problem I couldn't solve, and don't know if it's even possible! Any insight would be appreciated!

Say you have a table:

CREATE TABLE `user` (
id int PRIMARY KEY AUTO_INCREMENT,
lat float NOT NULL,
long float NOT NULL);

How would I call pairs of id's ordered by how close they are to each other geographically? (where GSD(lat1, long1, lat2, long2) gets the great circle distance between the two - spherical geometry is a pain!)

Easy enough to do across two tables or given 1 user's lat and long to start out with, but I'm stuck as to how to refer to 2 different rows in the same table in the same call.

Best I had was something along the lines of:

SELECT A.id, B.id FROM user AS A FULL OUTER JOIN (SELECT * FROM user) AS B ON A.id <> B.id ORDER BY GSD(A.lat, A.long, B.lat, B.long) ASC

Server had a bit of a fit with that one, though, and there must be a better way... (I'm known for writing the odd Rube Goldberg machine, so excuse me if the above call is awful!)

Giblet

select a.id, b.id from user a join user b on a.id <> b.id order by gsd(a.lat,a.long,b.lat,b.long)

Paste the GSD function aswell.

tolosenc

I took it from here:

https://developers.google.com/maps/articles/phpsqlsearch#findnearsql

amen

If you are using MS Sql Server then you can use the Spatial Data Support http://msdn.microsoft.com/en-us/magazine/dd434647.aspx.

I believe Oracle has something similar. Not sure about MySql.

GSD(A.lat, A.long, B.lat, B.long) ASC

Server had a bit of a fit with that one, though,

Can you post the code for GSD ?

And don't be using Select *. Only every select the rows you need.

do you really need to do a full outer join?

They query would most likely be quicker if you just computed the gsd for each row and out it in a temp table and then ordered the table at then end but you would have to look at the query execution plan to verify this.

Also are the Ids,lat and long indexed ?

tolosenc

Thanks guys, was mostly just wondering how to compare all combinations of rows in a table, and it seems that joining the table to itself is the best way to go.