Self-join dilemma: calculating distance between geographical locations

[glasgowduo]

Hi - just joined, so a big "Hi" first of all. Only just discovered this forum and it looks great! I hope to be here for many years helping and being helped.

I have a bit of a problem and need a nudge in the right direction. Any takers?

My database is managing multiple "Places" at different geographical locations. I would like to be able to automatically produce information on the "as the crow flies" distance between the co-ordinates captured for each record of a Place.

My "Place" table holds fields that capture "lat" and "long" aspects of geospherical co-ordinates and a calculated "distance" field that uses a custom function I created (well, the authors of "FileMaker 8 Functions and Scripts" created to be fair!) that can take these lat and long co-ordinates for two separate Place records (so, four pieces of data) and will calculate the distance using the Haversine forumala. (http://en.wikipedia.org/wiki/Haversine_formula) for anyone interested.

My inclination was to create a self-join relationship between my original "Place" table and a new table occurrence of Place that I have called "OtherPlace" and so have done so using a cross-product relationship.

I then embed a portal control for the "OtherPlace" table occurrence in one of my tabbed "Place" layouts and this dutifully shows a list of all of the "Place/OtherPlace" records in my database. It also correctly shows the lat and long values for every record in "OtherPlace" AND the lat and long values for the parent "Place" repeated identically as expected on every row of the portal.

So far so good! - so, what is my problem?

The problem I cannot quite figure out why my new portal won't make the calculation between every "pair" of Places according to the Haversine forumula in my custom function.

[if I create DUMMY fields for "another" place in just one record, the forumula works just fine, by the way - so the problem is not with the formula/ custom function.]

What it won't do is, via the portal of "OtherPlace" records, calculate the distance between each "OtherPlace" record in a row of the portal and the single 'parent' "Place" record shown in the main layout. It is as if the coordinates for the other side of the pairing of places are all set to zero, even though they show up in the portal row correctly.

I've tried changing the relationship between "Place" and "OtherPlace" to be a exclusionary type (where the "Place" primary key does NOT equal the "OtherPlace" primary key) and this does remove the parent record from the portal list as expected but still the Haversine calculations are wrong.

I know this has to be to do with how I am designing the relationship between an individual record in the "Place" table occurrence and all other records for Places in a "OtherPlace" table ocurrence but can't make my brain fire-up to puzzle it out.

Self-joins are always a head-nip for me! Where am I going wrong?

Perhaps there is some other way of building up some sort of data array to hold the distances between every pair of records in a single "Place" table but that's beyond me (using FileMaker) if I'm being honest - but if there is some way to do that I'd be grateful for a nudge in the right direction.

Thanks a million,

Paul

[Raybaudi]

Hi

I just need a file like your "place" table.

Can you send that to me ?

This is my email: [email protected]

[mr_vodka]

Make sure that when you are specifying your calculation for your custom function, that you are evaluating the calculation from the context of the Child table (Other Place). It seems as though you are evaluating it from the parent table (Place), thus only performing the calc between the first related record only.

The option can be found at the top of the specify calculations window, when you are writing your calc equation.

[Ender]

Greetings glasgow,

A self-join is not appropriate here since you want to remember the distance between each pair of Places. Add a join table, where each record is the Place-Place join. When the join record is created, you then will have the two pairs of lat-long points along with a place to store the distance between them.

You might set up a script process to generate each join record for all the Place-Place pairs, but the size of the resulting set will be pretty big (n*n I think).

[comment]

I hope I am not merely adding more confusion here.

It seems you expect all Place records to automatically calculate their distance from the currently viewed Place. The problem is that your navigation from one record to another is a non-event as far as the records are concerned. You cannot, in a calculation, refer to a "value from the currently active record".

You could make your navigation scripted, and in the course of the script set a couple of global fields to the lat/lon of the current records. Then set your calculation to calculate the distance to the globals.

---

P.S. No relationship is required for this - except the "x" join used to DISPLAY the records in a portal.

Edited March 13, 2007 by Guest

[glasgowduo]

Hi Daniele - your English is perfect! (And WAY better than my Italian!)

I'd be happy to send you the table but it is really nothing more than I said in my original post, just a primary key, a name, a couple of fields to hold the co-ordinates and the calculation fields. I really don't think you'd find this tiny table very useful.

Once I have this functionality sorted out, I'll be adding many more fields to describe each recorded "place" but that's not there yet and, anyway, will be very specific to the sorts of places I'm recording and not much use to anyone else.

But, if you really do want it, then let me know and I'll email an FM file with a copy of this "Place" table.

cheers...

Paul

[Raybaudi]

Many thanks, Paul.

I was thinking that you had a DB of all the main places in the world and relative co-ordinates...

If no, can you give me a link to download it ?

[glasgowduo]

Thanks mr (john) vodka, ender and comment... now starting to work through your suggestions - all very gratefully received!

Will post back when I seem to get this through my thick skull and make some way forward with it...

speak soon,

Paul

[glasgowduo]

Ah. I see - sorry I misled you there. No I don't have anything like that (but would find that very useful for other things I'm working on so will keep my eyes open for something and will copy you in if I find anything - promise!)

No, my current db application is going to be very specific to my local environment in Glasgow here in Scotland. The places in question will be various specific places in and around Glasgow and so the distances will be between specific individual buildings.

Not much use to most folk! Sorry.

All the best,

Paul

[comment]

I was thinking that you had a DB of all the main places in the world and relative co-ordinates...

If no, can you give me a link to download it ?

How about this one, for example?

EDIT:

Wait: what do you mean by relative co-ordinates?

Edited March 13, 2007 by Guest

[Raybaudi]

Perfect, comment !

Thank you.

relative was for: "of each place"