Metafilter users closest to this user

December 21, 2005 10:37 PM

Pony: at the end of the new "MetaFilter users near this user" query slap an "order by miles ascending". Not a big deal. I just want to stalk people in order, s'all. (That's a pretty damned sweet feature, btw).
posted by popechunk to Feature Requests at 10:37 PM (30 comments total)

Yeah, I'm still working on it.

Actually, if anyone has ever done DBA work, the thing that is stopping me from sorting them by distance is that I can't quite figure out how to calculate distance within SQL.

Basically I want a query to say "select the 50 closest users to this user page person's lat/lon coordinates, and sort the results by distance." It doesn't have to be too technical, it's just figuring out the difference between one lat value and others, assuming the minimum distances between two are closest.

So far, I've figured out how to select a bunch of nearby users by just looking +/- 0.5 degrees lat/lon from one user, then I'm calculating distance in coldfusion using the Great Circle Distance formula. The problem is that in NYC, plus or minus half a degree is 400 people, and the sort is by user_id, since that's an easy db value to order on.
posted by mathowie (staff) at 10:45 PM on December 21, 2005

Indeed, it is damned sweet. The damned sweetest thing ever. Great work sir.
posted by panoptican at 10:47 PM on December 21, 2005

Once I figure out how to limit the number of results by closest distance, then we can start doing cool stuff like show a google map of everyone near you. I did that in the past, but in busy places like NYC, there were so many points that it would crash browsers.

Also, this is all lat/lon now, instead of zips, so everyone outside the US can finally play along.
posted by mathowie (staff) at 10:57 PM on December 21, 2005

Ahhh, that makes lotsa sense. Very cool indeed. I wish you were using perl so I could help :-(

When I first saw that the lat/long was carried out to six decimal places, I instinctively went and closed my blinds in case pfuller and fnord were out on my lawn peeking in the windows.

I should change my username to fpopechunk, the better to fit in

Not that I have a clue how narrow six decimal places gets you.
posted by popechunk at 11:08 PM on December 21, 2005

the six decimal places is just what data I got from here using their REST interface.

Any basic sql should do the trick, I guess I just need to figure out how to evaluate differences between records, or do a subselect on the user db again or something.
posted by mathowie (staff) at 11:18 PM on December 21, 2005

So you have a big table with lat/long values for zip codes. You used that to convert the data you already had (zip codes) into something that might also work for non-US folks. I gotcha now.

That's hurting my brain. I had it in my head for a second there with just one value (lat OR long), but getting two solved on the fly every time you draw the page is beyond my ken.
posted by popechunk at 11:38 PM on December 21, 2005

There's a function here. It's in PHP, but the basic formula is there, so maybe you can figure out how to convert it.
posted by willnot at 1:33 AM on December 22, 2005

Sorry - I see you already have it working in Colfd Fusion, so you clearly know the formula.
posted by willnot at 1:34 AM on December 22, 2005

Hey! Jessamyn's no longer my neighbor, and I am pretty sure I live closer to her than to the others I see on my page. *sniff*
posted by terrapin at 4:57 AM on December 22, 2005

On behalf of the world....

Nah, seriously, thanks Matt. This is sweet.
posted by goodnewsfortheinsane at 5:28 AM on December 22, 2005

Oh, I agree it is an improvement over the previous American-centric version.

What I'd like to see is the ability to allow users to define the radius. Perhaps in 5, 10, 25, 50, 100 miles/kilometers settings.
posted by terrapin at 5:47 AM on December 22, 2005

Jessamyn's no longer my neighbor

That's weird, I see that too. My three neighbors are 8, 29 and 36 miles away respectively. According to Google Maps, you guys live 37 miles away from center-of-town to center-of-town, so maybe 36 is the cutoff?
posted by jessamyn at 5:50 AM on December 22, 2005

When I just had the default values you pre-set for Seattle, I had a large number of neighbors (greater than 47; I didn't count), although it said most of them were 0 miles from me. I know some of these people live in different zip codes than myself, so I checked their profiles, and they had the same lat/long as I did, so I presumed you had pre-set them by city instead of by zip code. That's fine. What's weird is: Once I put in specific lat/long values, I now only have 47 neighbors - all the people that previously were 0 miles from me are now completely missing from the list. This may be a bug.

P.S. Awesome feature!
posted by matildaben at 6:01 AM on December 22, 2005

How about at least a link to a map of some sort (for now), since it makes it reeeeally hard to find out where someone is now. My lat/long was pretty off, BTW, but I think that's just approximation error.

Y! Maps certainly takes lat/long pairs RESTily:

http://maps.yahoo.com/beta/index.php#maxp=search&trf=0&lon=-122.05&lat=37.38&mag=3
posted by kcm at 6:06 AM on December 22, 2005

Note that putting in lat and long in non-decimal form causes a db error. Maybe add a note to put in location in decimal coordiates only? Google maps can be used to convert one to the other.
posted by bonehead at 6:37 AM on December 22, 2005

I'm calculating distance in coldfusion using the Great Circle Distance formula

holy crap! what's your db? spatially enable it if possible and forgeaboudit.
posted by 3.2.3 at 6:58 AM on December 22, 2005

Uh... On my user page I see something like:

The following information is meant for the website developer for debugging purposes.
Error Occurred While Processing Request
1.0000000000000002 must be within range: ( -1 : 1 )


Does that mean nobody is near enough to me to actually make this work, or am I missing something?
posted by caution live frogs at 6:59 AM on December 22, 2005

You can't sort the data in coldfusion before you print it?
posted by smackfu at 7:12 AM on December 22, 2005

Calulating Distance based on Lat/Long in SQL Server (as a subset of an article about using the .NET CLR in SQL to do the same thing)
posted by blue_beetle at 7:37 AM on December 22, 2005

Crap. If I join the fun and add lat/long to my profile to see who else is in The Wang, I'll lose my custom stylesheet.

Oh well. *sniffle*
posted by Gator at 8:19 AM on December 22, 2005

I'm also excited at this development. Thanks!

What I'd like to see is the ability to allow users to define the radius. Perhaps in 5, 10, 25, 50, 100 miles/kilometers settings. - terrapin

That would be a most fantastic pony. Up here in the Great White North, we're used to large distances between people.
posted by raedyn at 8:36 AM on December 22, 2005

That's one awesome style sheet Gator. I say screw lat and lon, find someone who's close to you and check their user page out.
posted by panoptican at 8:55 AM on December 22, 2005

popechunk: "Not that I have a clue how narrow six decimal places gets you."

Along lines of longitude, one degree is 110.95 km (68.94 mi), so one millionth of a degree is 110.95 mm (about 4.37 inches). Lines of latitude are not all the same length, but at the equator, one millionth of a degree is 111.32 mm (4.38 inches). At your latitude (30.21°N), one millionth of a degree is 101.54 mm (4.00 inches)
posted by Plutor at 10:46 AM on December 22, 2005

wouldn't I have to update my six-digit precision lat/long if I moved my computer to another room in my house? or if my mouse crosses the circle of influence?

(in all seriousness, I think 2-3 should be enough..)
posted by kcm at 10:52 AM on December 22, 2005

I changed mine to 7 digits and now my wife is 2 miles away from me. I hope we can fix that soon. I hate to shout.
posted by terrapin at 11:59 AM on December 22, 2005

jessamyn: "...so maybe 36 is the cutoff?"

Except that Hanover, NH is over 60 miles away and people there show up on my page.
posted by terrapin at 1:29 PM on December 22, 2005

One problem: I can't update my preferences, at all...

http://www.metafilter.com/contribute/customize.cfm
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 35: Incorrect syntax near '='.
posted by smackfu at 2:16 PM on December 22, 2005

cool
posted by amberglow at 3:00 PM on December 22, 2005

I having the same crisis Gator is.
Waaaaah!
posted by deborah at 3:15 PM on December 22, 2005

Error Occurred While Processing Request
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 32: Incorrect syntax near '='.
posted by mr.marx at 3:50 PM on December 22, 2005