Jump to content
Salesforce and other SMB Solutions are coming soon. ×

geographic relationship help


drbarnard
 Share

Recommended Posts

I'm having a bit of trouble trying to figure out a set of relationships... here's the situation:

 

I have a database full of property information (for a commercial real estate agent). Each property has a Latitude and Longitude field. I'm using LatitudeZERO (http://www.hi-voltage.com.au/latitudezero.php) to evaluate whether or not other properties are within a specified radius. The calculation requires two sets of information LatA & LonA and LatB & LonB. How do I set up a calculation that compares the current record with all the other records in the database and display the results in a portal? The calculation is: External("LatZ-WithinRadius"; LatA & "|" & LonA & "|" & LatB

& "|" & LonB & "|" & "M" & "|" & "3"). Where the "m" tells the calculation to use miles and the "3" is the size of the radius to evaluate (I'll eventually make the radius a user defined global field, but for now I just want to get the thing working).

 

So, I tried setting up a second table occurrence, tableb (name simplified), doing the calculation between the related tables ie. lat & lon compared to tableb::lat & tableb::lon. I seem to be on the right track (it's calculating things properly), but I now need to create a second relationship to filter the results to a portal ie. every record that results in a 1 (within the specified radius) will show up in a portal. That's where I'm stuck... It's almost 3AM, and I just can't think strait. Am I on the right track? What's the next step?

Link to comment
Share on other sites

Am I on the right track?

I'm afraid not. You can't base a relationship on a unstored calculation in the chid table. I don't know which functions your plugin has, but you need one where the input is ( lat ; lon ; radius ) and the output is in the form of ( maxLat ; minLat ; maxLon ; minLon ). Then you can define your relationship in terms of ranges - i.e. 4 criteria to determine whether the child data falls within the specified boundaries.

Link to comment
Share on other sites

thanks for the quick reply... but, there is no calculation that can do this with only ( lat ; lon ; radius ). If you think about plotting a circle on a graph, it can't be defined by ( maxLat ; minLat ; maxLon ; minLon ) that creates a square. The equation to determine whether or not a point is within the circle is quite complex, which is why I chose to go the plugin route rather than actually do a calculation directly in filemaker.

 

So, I'm really no closer to achieving my goal... apparently I am off track trying to do the calculation via relationships. All I basically want to do is compare a field in recordA with all the other records within the same table via a calculation. Could I do this by creating a global field? ie. store the lat & lon of the current field in glat & glon, then doing the calculation within the same table? Then just base the relationship on the result of that calculation?

 

thanks,

 

david

Link to comment
Share on other sites

I believe you are mistaken to treat latitude and longitude as if they were ( x ; y ) coordinates on a plane; first, they are not coordinates but ANGLES, and second, the surface is spherical.

 

Therefore, if you take all points that are within ( maxLat ; minLat ; maxLon ; minLon ), you will NOT get a square. I think the actual shape varies betwen a perfect circle and something that resembles a barrel in profile - depending on the point of origin and the distance you have specified (for example, if you start at a pole, the shape will always be a perfect circle).

 

Now, all depends on how accurate you need to be. I don't know which formula your plugin uses, but I suspect that it too has a limit on its accuracy. One thing is certain: to compute the actual distance from A, you need to perform the calculation for each B. In Filemaker terms, this means that either A needs to stored in each B, or you cannot base a relationship on the result.

 

I think you have basically two options: either live with the limitations of the "barrel", or give up on the portal, and show the results as a found set. You would produce the found set by looping thru the records, computing the distance from global A, and omitting records that are too far away (you could speed this up by starting with a found set of records within the "barrel").

Link to comment
Share on other sites

It has been very hard for me to finally decide that a sarcastic reply to your last post would do no benefit (though I may have enjoyed it for a few minutes)... you know... something about selling land on the north pole... that sort of thing.

 

In my last post, I was trying to over-simplify the geography of it all and focus on database structure (not demean your knowledge of geography, cartography, geometry, or any other mathematical or scientific pursuit). In the case of the database I'm building, I will be dealing radii under 5 miles. So, even taking into account the curvature of the earth, ( maxLat ; minLat ; maxLon ; minLon ) would be very close to square, and therefore unusable (commercial real estate brokers are used to being shown demographics and other property information distinguished by 1,3 and 5 mile rings, and would relate much better to a 3 mile radius than 3 miles square. I can hear it now, "do you mean 3 square miles, or a square that is three miles on each side... 9 square miles?"). I understand that without in-depth knowledge of a project, certain assumptions can be made in trying to help someone... and though I probably don't seem to, I do appreciate your help.

 

So... your last response did get me thinking. Would it be feasible to script an "update" button that ran the calculation and updated a field in the found set (1=within the radius, 0=outside)? Then base the portal on a relationship looking for the 1's. This solution seems like a major design snafu, displaying misinformation until a user intervenes (I guess I could even script the portal to not even appear until the update query was run), but I don't want the user to have to leave the current record to look at a found set. Also, I currently have 5 layouts (setup as faux tabs) to display various information about each record. Once the user has hit "update", they could go back and forth among tabs, and the portal would stay current until they left that record. If I am on the right track conceptually, how would I actually implement this idea?

 

By the way, I do really like your idea of starting the find with ( maxLat ; minLat ; maxLon ; minLon ), then narrowing down... it could save some serious processor cycles (depending on the size of the database and the complexity of the formula contained within the plugin I will be using).

Link to comment
Share on other sites

I don't mind sarcasm, explicit or implicit - provided it's not misplaced. You disagreed with me on theory, and I tend to be rather precise in such matters. I agree that in practice, given a location in Texas and a radius of a few miles, the shape defined by ( maxLat ; minLat ; maxLon ; minLon ) would be very close to a square. In worst case, this would lead to an error of considering a location 4.24 miles away as being within a 3 miles radius. Or, if you like, on average only 79% of locations that passed the test would be correct. Remaining in the practical realm, I ask myself how significant such differences can be, when one doesn't take into account a practical term like elevation.

 

 

Coming back to Filemaker, the problem with your approach (storing the test results in each location, for the purposes of establishing a relationship) is that it would only work for a single user. The moment another user wanted to view a different location, he/she would ruin it for the first user. Found sets, OTOH, are specific to each user (more precisely, to each window).

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use