geographic physician search

[markdo]

I want to create an online database that will find a physician (from an unposted list) based upon city and state. or better yet all physicians within 100 miles of a specified zip code... Any suggestions?

It should be a fairly simple solution.

Thanks...

Mark

[danjacoby]

Not sure what an "unposted list" is, but I'll barge ahead anyway...

Searching based on city/state is a simple find. Enter the city in the City field and the state in the State field, et voila.

Searching within 100 miles is much trickier. Basically, each zip code needs to be associated with a pair of numbers (say, latitude and longitude measured in seconds only, figuring 60 seconds per minute and 60 minutes per degree -- seriously!). Best way is to create a separate file with this list (3 fields, ZipCode, Latitude, Longitude).

Once that is done, create two calc fields in the main file, Longitude & Latitude, which return the numbers from the separate file based on the ZipCode entered. Next, create a Global ZipCodeSearch field. Then a couple of global fields, returning the Lat. & Long. for the global ZipCode. Finally, a calc field which returns the square root of the sum of the squares of the differences between the global Lat/Long fields and the individual Lat/Long fields (ah, Pythagoras!), i.e.:

Sqrt (((gLat - Lat) * (gLat - Lat)) + ((gLong - Long) * (gLong - Long)))

Now do the search for any record where this final calc field is below a certain number.

It's not perfectly accurate, since the earth isn't flat, but it comes pretty darn close.

Of course, the question remains, is it worth it?

[slstrother]

I am doing something similar with latitude and longitude for a directory. I am using the calc that danjacoby posted, but with theAbs function added (see below). This will do away with negative numbers, which you are likely to get otherwise. The result of the calc will be in degrees which you will need to convert. The number of miles per degree changes the farther from the equator you are. Also, the calc (below) becomes more and more inaccurate the farther from the equator you are. Here in the Los Angeles area, when I calculate 20 miles using this formula, the distance is actually 21 miles. This is close enough for my purposes. You will have to decide if this margin of error is acceptable. Do a search on "latitude and longitude" at askjeeves.com. You will find resources for converting from hours, minutes, seconds to a decimal that can be used in calcs. Also, you will find more accurate ways to calculate distance, but harder to implement in Filemaker.

Sqrt ((Abs(gLat - Lat) * Abs(gLat - Lat)) + (Abs(gLong - Long) * Abs(gLong - Long)))

[danjacoby]

Don't need the Abs function, since any number multiplied by itself is a positive number.

[slstrother]

I stand corrected. The things one forgets as they age.

Thanks

[markdo]

Thank you all for your input. I am very grateful for your generosity..

Mark

[Denethor]

OK, I'm a complete noob when comes to FileMaker on the Mac but I have lots of PC SQL experience.

How in detail, please, do I do these steps.

Where is the user defining the radius (ie 100 miles) No where in that math do I see that.

I have my contact management screen created.

I have all the data in the database.

I have a table that has LAT LONG ZIP CITY STATE COUNTY (if any body wants it let me know)

I just don't understand how all this works.

I've done this same thing in ASP script with two SQL queries and similar math to above (except I cheated and used a bounding box instead of a true radius) and it works great, but for the life of me I can't figure out FM8. Maybe its because I'm not a native Mac user. But I need to get this working soon.

Thanks for any help

Edited February 16, 2006 by Guest

[Ender]

What specifically did you want to do?

[Denethor]

I want the user to go into the find screen and type in a zipcode and a radius in miles and have it return all records that match.

Trying to find all schools with in x miles of y zip code.

Could this all be done in a calc feild and used as similar. I was looking at the "productivity Solution" example that came with FM8. They have a tab called "related Contacts" on that tab you can click a radio button for Name, City, or Company.

I'd like to add another option "AREA" and have it pop up little box that asks the distance to search and returns all contacts with in x miles of the curent zip.

Here are the parts of the original post I don't quite follow...

1. [color:gray]Best way is to create a separate file with this list (3 fields, ZipCode, Latitude, Longitude).

Did that and related ZIP to the Zip in my contacts table and I can show the Lat and Lon for each record.

2.[color:gray]Once that is done, create two calc fields in the main file, Longitude & Latitude, which return the numbers from the separate file based on the ZipCode entered.

OK done that was pretty easy I guess as the relationship takes care of the ZIP match I simply put =Lat in the Lat feild and =Long in the Long feild.

3.[color:gray]Next, create a Global ZipCodeSearch field.

OK, easy enough I guess, made a new field in my contacts table called GlobalZipCodeSearch and went in to options and made it a global (one instance).

4.[color:gray]Then a couple of global fields, returning the Lat. & Long. for the global ZipCode.

Did same as above gLat and gLong, but can't figure how to make them display that lat/long for

Global zip

5.[color:gray]Finally, a calc field which returns the square root of the sum of the squares of the differences between the global Lat/Long fields and the individual Lat/Long fields (ah, Pythagoras!), i.e.:

Sqrt (((gLat - Lat) * (gLat - Lat)) + ((gLong - Long) * (gLong - Long)))

OK, I made a field called AreaRadius, chose Calc as the type, and put that function in. It does the math on the Lat and Long but since the gLat and gLong aren't being populated then it doesn't mean much.

6. [color:gray]Now do the search for any record where this final calc field is below a certain number.

How is this final step accomplished?

Again sorry for being such a noob

Edited February 16, 2006 by Guest

[Denethor]

Is your name from "Ender's Game" by Orson Scott Card?

I met him, he lived not far from me and I've read the whole series of books. It was a long time ago though, must have been 10 years at least.

[Ender]

Well Denethor, I think you've found yourself a rather complex problem. Before we get into possible solutions, it's important that you consider the limitations of the technique outlined in this thread (I haven't seen the original posters around lately, but I'll give it a go.)

There are two different problems, and each is significant depending on how spread out the source and destination addresses are. If your addresses are spread out (like in different cities,) or far from the equator, you may have problems with the provided calc as it doesn't factor in the longitude scaling difference at various latitudes. If your addresses are fairly close (within a few miles of each other,) you will instead have problems with the use of one set of latitude/longitude points representing an entire zip code.

The first problem could be compensated for by adding a small scalar factor in to the distance calc (if your addresses are pretty close to each other, but not near the equator,) or finding a more general calc that correctly computes the distance between latitude/longitude points on a globe.

The second problem results from a zip code possibly encompassing a large area, but the latitude/longitude points can only show one point in the zip code. This would result in errors of several miles, as some zip codes could be miles on each side. Or, if the source and destination are at opposite ends of the same zip code, they would both lookup the same latitude/longitude, and the distance would incorrectly show zero. I'm afraid this is harder to deal with. Instead of looking up the latitude/longitude points from the zip code, you would instead need to look them up by street address. I have build such a solution (in a limited service area,) but there may be better methods now that GPS stuff is more common.

Let us know which way you're going with this, and we may be able to help further.

[Genx]

... subscribing...

[Denethor]

I don't care that much about acuracy as it's more of a I want to see schools that are in like suburbs of major cities, the actual distance I could less how acurate it is. I was just trying to allow a defined distance from x.

I had a table of zips,lats, and longs from a prior project that I wrote in ASP script for web page. it looks like this:(I've filled in variables with test for example)

sqlstr = "SELECT Longitude,Latitude FROM LIVE_ZipCodes WHERE ZipCode='07020'"

set ZRS = db.execute(sqlstr) [color:red]executes the above SQL command where the zipcode is filled in from a user supplied variable

if not ZRS.eof then [color:red]Make sure the zip supplied is in the table

miles = 50 [color:red]again supplied by the user in a variable usually

lon = ZRS.fields("Longitude") [color:red]Read the Lon that matches the input zip center

lat = ZRS.fields("Latitude") [color:red]Read the Lat that matches the input zip center

[color:red]The next four lines compute a box around the zip center.

lon1 = lon - (miles * 0.008092753623188405797101449275362)

lon2 = lon + (miles * 0.008092753623188405797101449275362)

lat1 = lat + (miles * 0.013830769230769230769230769230769)

lat2 = lat - (miles * 0.013830769230769230769230769230769)

rangestr = "Latitude <= " & lat1 & " AND Latitude >= " & lat2 & " AND Longitude >= " & lon1 & " AND Longitude <= " & lon2

findstr = findstr & "(City IN (SELECT City FROM LIVE_ZipCodes WHERE " & rangestr & "))"

sqlstr = "SELECT * FROM " & dbprefix & "School WHERE Status = " & ACTIVE & " AND " & findstr

else

searchprompt = "Could not identify zip code " & request("Zip")

sqlstr = "SELECT * FROM " & dbprefix & "School WHERE ID IS NULL"

end if

[color:red]So after computing the farthest lat and lon in four direction from the center, I run another query to show all records that fall in betweeen those ranges. works like a champ, I just can't figure out how to do this in FileMaker as I've used it for all of 1 day now.

[Ender]

Ho Denethor!

I have prepared a sample showing one way to do this with relationship filters rather than Finds. Take a look at the relationship graph (double-click the relationship lines to Student to see the multi-criteria relationship that makes this possible.)

LatLong.fp7.zip

[Denethor]

Thanks I really appreciate all the help.

I'm still at a loss here, I'm missing the basic knowledge as to "why" this works and how to apply it to my data set.

Why are there three zipcode and three school tables (base, All, & in Bounds)?

What do they get populated with?

And I want to show the School name not the zip in the results and it be clickable to bring up that schools info in the contact manager.

I'd send you my tables so you could see what I'm doing, but they are almost 100MB.

[Denethor]

I'm going to try to take out most of the data to get it under the limit for this board and upload what I have

[Denethor]

Here's my Contact Manager mostly stolen from business productivity pack that is free on FM site.

You might get script errors where I removed as much a possible to squeeze it down.

Pull up school in anchorage (record #4 is one)

Then down at the bottom click the "Related Contacts" tab, see how you can choose related City etc...

If you click the city radio button, it will show all the schools in Anchorage.

Idealy I would like to have another button called "Area" or something that would show all contacts with in range defined else where or have multiple buttons for 10,20,30 miles. Like I said I'm not going for acuracy just general area.

BPS_1.0.zip

Edited February 17, 2006 by Guest

[Ender]

Why are there three zipcode and three school tables (base, All, & in Bounds)?

What do they get populated with?

There is only one table of each. But there are multiple table occurences, representing different relationships to those tables.

And I want to show the School name not the zip in the results and it be clickable to bring up that schools info in the contact manager.

If you have the School Name defined in the School table, you can show that in the portal instead.

I had set this up to show which schools are within range of the Student's Zipcode, but if you wanted to instead see the Students that are within range of a specified School, you could do the same setup through the School table instead.

[Denethor]

This may sound dumb, but how do you make another occueance of a table and does it automatically stay updated when you update any other occurance?

[Denethor]

Take a look at my newest incarnation.

The bounds box is generated fine, now there must be some way to use that data in that "similar" fashion. Take a look at the DB design and the calc feild called "Similar Area Key" Why does that return nothing?

BPS_1.1.zip

[Ender]

This may sound dumb, but how do you make another occueance of a table and does it automatically stay updated when you update any other occurance?

There's a button in the Edit Relationship dialog to add a table occurence. The data in a table is the same for all occurences of that table.

Take a look at my newest incarnation.

The bounds box is generated fine, now there must be some way to use that data in that "similar" fashion. Take a look at the DB design and the calc feild called "Similar Area Key" Why does that return nothing?

It's not clear to me what you're trying to accomplish here. What is the goal--to show those Contacts that are within a specified distance to another Contact? What is a Contact--a school or a person?

Your Similar Area Key cannot be used to replace the functionality of the relationship I showed you earlier. If you wish to use a relationship to filter the latitude/longitude points, then you must use all four of the latitude/longitude criteria in the relationship that shows your filter results. If you want this filter to be on or off based on a user choice, then have them calculated to allow the entire range of latitude/longitude points if the user has chosen NOT to filter by Area, otherwise use the correct latitude/longitude point of that record.

[Denethor]

A contact is a school (show all schools in the area of the school your looking at currently).

There should only ever be one person we ever care to contact at any given school that's why I didn't make a seperate teachers table and just left it as a name tied to the school.

I did leave two adresses feilds because I figured there's the main address of the school and then that particular teacher could have another address or prefer to be contacted at home or something.

Let me play with the table example you gave earlier dome more and see if I can't figure out how to adapt that to my setup.

Thank you for all your help.

[Denethor]

I found what was making my life a living hell...

In my zip code table, Latitude and Longitude were defined as TEXT and not indexed.

After fixing that it all started working for me.

I was really getting frustrated because I had added a few feilds to you example and imported all my data into it and changed the lists to show what I wanted to see and all was working great.

But when I would go back and do the eact same thing on my real tables it wouldn't do anything.

Finally I went through every deatail of every feild until I found the only differnces.

Thanks again for all your help.

If I want to attempt your more exact method later by using the actual address, is there a thread on that already?

[Ender]

You can look at this thread:

http://fmforums.com/forum/showtopic.php?tid/100771

The demo was for FM5/6, but the technique could be optimized for FM7/8.

[Denethor]

Ender, thanks for all your help so far, but can you help me once again?

Or anyone...

The area code radius works but it always looks at the entire DB. How can it be made to only look in the records resulting from a find? or be used as a find criteria (i.e. within x miles of zip zzzzz)

Now if I go into find and say show me all schools with a certain criteria it finds all those schools.

Now I want say narrow that list down to only records that meet the above criteria and are within x miles of zipcode zzzzzz.

thanks

Edited March 9, 2006 by Guest

[BFrost]

Ender, thanks for all your help so far, but can you help me once again?

Or anyone...

The area code radius works but it always looks at the entire DB. How can it be made to only look in the records resulting from a find? or be used as a find criteria (i.e. within x miles of zip zzzzz)

Now if I go into find and say show me all schools with a certain criteria it finds all those schools.

Now I want say narrow that list down to only records that meet the above criteria and are within x miles of zipcode zzzzzz.

thanks

A State designation can narrow it down for you. The first 3 digits of an area code can narrow it down within a state. The last two digits (of a 5-digit Zip) even further reduction.

Find a Post Office

Wickipedia has a Explanation of ZIP Codes.