Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

Calculating Distance

by jeffthewookiee (Sexton)
on Feb 21, 2007 at 17:55 UTC ( #601377=perlquestion: print w/ replies, xml ) Need Help??
jeffthewookiee has asked for the wisdom of the Perl Monks concerning the following question:

I have a database containing addresses with longitude/latitude calculated using Geo::Coder::US. I'd like to be able to run SQL queries based on points within a certain distance of another point. In other words, I provide a lat/long and a distance in miles, the database gives me a list of results within that area. The problem I've run into is converting a distance in miles to something to use as a distance in the longitude/latitude scheme of things. Does anyone know how to do this?

Comment on Calculating Distance
Re: Calculating Distance
by derby (Abbot) on Feb 21, 2007 at 18:01 UTC

    A good starting point would be the closest method in Geo::Distance.

Re: Calculating Distance
by Zaxo (Archbishop) on Feb 21, 2007 at 18:05 UTC

    In Perl, you can use Math::Trig::great_circle_distance(). To run queries on the database, you'll need to cook up an SQL function which does the same calculation.

    Added: Yes, this would be a good place for a stored procedure.

    After Compline,

      If he is using Postgresql, could he create a procedure that does this?
        If he is using Postgresql then PostGIS might also be helpful.
Re: Calculating Distance
by ysth (Canon) on Feb 21, 2007 at 18:47 UTC
    I believe (but haven't actually seen myself) that some RDBMS's have builtin support for this kind of thing; check out what (the latest version of) your RDBMS has to offer.

    I've given some thought to how I'd do this before, and come up with the following:

    Pretend the earth is flat. Divide it up into equilateral triangles, each with an id and lat/long of the center. Add the triangle id to your records; if any triangle ends up with an unwieldy number of distinct lat/longs, divide that triangle into 4 sub-triangles like so:

    ____________ \ /\ / \ / \ / \/____\/ \ / \ / \/
    Then select triangles that may contain points in your range, and select records with those triangle ids. If complete accuracy is needed, end by filtering out records not actually in your distance.

    I have the feeling database vendors do something like this if they support indexing lat/longs.

    Update: why triangles instead of squares? I have a gut feeling that it will produce fewer outside-the-distance results, but am unable to say why.

      Nice gut! You're thinking about Dymaxion maps. And CPAN is your friend, in this case.
Re: Calculating Distance
by rinceWind (Monsignor) on Feb 21, 2007 at 19:13 UTC

    We've encountered this requirement for Here, alongside the lat + long, we store planar X-Y coordinates in the database, using a projection such as UTM or Ordnance Survey. Then, we use Pythagoras in SQL queries to find all entries within the distance.

    This works well for city guides - our main application, but less well over large distances, where curvature of the oblate spheroid that is the Earth, becomes a factor.

    I don't know what your application is, but check out OpenGuides, as there's probably quite a bit of code you can reuse. Or even set up a Guide yourself, if that's what you are trying to achieve.


    Oh Lord, won’t you burn me a Knoppix CD ?
    My friends all rate Windows, I must disagree.
    Your powers of persuasion will set them all free,
    So oh Lord, won’t you burn me a Knoppix CD ?
    (Missquoting Janis Joplin)

Re: Calculating Distance
by eric256 (Parson) on Feb 21, 2007 at 23:36 UTC

    One other idea. I did this before in MySQL and using the full formulas in SQL was too much, and computing them all in perl was annoying, so instead I computed a square of the right size, found all the matches in that with SQL then refined that down once in my perl script. I hope that doesn't sound like raving lunacy! ;) Shortened: Do a quicker less accurate search in your database, then refine the listing in perl. That is unless of course your database has native support then it is probably going to win speed contests by doing it purely in your database.

    Eric Hodges

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://601377]
Approved by Paladin
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (6)
As of 2014-09-23 21:26 GMT
Find Nodes?
    Voting Booth?

    How do you remember the number of days in each month?

    Results (241 votes), past polls