Finding a property in my database, closest to a zip code?

[JTSmith]

Most all of my Finds/Searches, I need to find a property that is closest to an address or zip code, and I'd like to either search something like "All properties within 30 miles of zip code 12345", or even "all properties within 30 miles of City Name". The problem is, I may need to look for something in Portland, Oregon, and come up with 1 or 2 properties, but I have 5 more just 5 miles away in Vancouver, Washington.

Any ideas? Thanks.

[Russell Barlow]

Most all of my Finds/Searches, I need to find a property that is closest to an address or zip code, and I'd like to either search something like "All properties within 30 miles of zip code 12345", or even "all properties within 30 miles of City Name". The problem is, I may need to look for something in Portland, Oregon, and come up with 1 or 2 properties, but I have 5 more just 5 miles away in Vancouver, Washington.

Any ideas? Thanks.

Geolocational data lookups. Use a web service to return a list of Zip Codes that fulfill your requirements, then parse that list for matching properties in those Zips. Few different ways of doing this.

[Vaughan]

Ditto the geo-coding advice.

Note that it is computationally intensive. You can reduce this by adding some restrictions using some bounding boxes (boxes can be done with multi-predicate relationships, circles need unsorted calculations) for example by initially specifying properties within 100 miles. This bit is relatively easy, both programatically and computationally.

The intensive part is getting the n records within 100 miles and then working out their exact distance from the origin point, then sorting them.

Note that geo-coding by zip code has some limitations. Several cities can share the same zip code, so they will all appear to be the same physical place. Also the geocode is often the post office which is not necessarily the centre of the zip code area.

As long as your needs can tolerate the inaccuracies that these limitations impose, it's all pretty easy to do.

It is possible through Google maps to get geocode data for specific addresses, this may help.

[nleon307]

I did this using Access a few years ago using the Haversine formula. It is "as the crow flies" (straight line from point a to point b ) instead of driving distance but it worked fine for what we were doing. You'll need a list of zip code longitude/latitudes, which you can pay for or find online for free. The calculation is done using the longitude/latitude of one zip code to another. It looks like there's a custom function for it here.

I've got to say, this looks so much simpler than what I had to do in Access, I'm jealous.

[No_access]

if you have the lat and lon of the zip, then you can use this formula, it works great on my database....

so you need 6 fields minimun, I use 10 to get area code, city, state, county..

1 zipcode

2 lat1

3 lon1

4 lat2

5 lon2

6 distance

lat2 and lon2 are the starting location that you want to measure from distance is the calc field that tells you how far lat1 and lon1 are from lat2 and lon2

Distance is in miles for my application, but can be made into whatever you need. note their is a sample dataset you can use below.. your distances should = mine..

 

If(Lat1 ="" or Lon1="" or lat2="" or Lon2="";"";

   If (Sin((Lat1/57.29577951))*Sin(lat2/57.29577951)+Cos(Lat1/57.29577951)*Cos(lat2/57.29577951)*Cos(Lon1/57.29577951-Lon2/57.29577951) > 1

;   3963.1*Acos(1);   3963.1*Acos(Sin(Lat1/57.29577951)*Sin(lat2/57.29577951)+Cos(Lat1/57.29577951)*Cos(lat2/57.29577951)*Cos(Lon1/57.29577951-Lon2/57.29577951))))

Hope this helps.

SAMPLE DATE:

SAVE AS A "FILENAME.CSV" OPEN IN EXCEL OR DRAG TO FILMAKER TO SEE.... YOU CAN USE THIS AS A SAMPLE DATASET TO THES THE FORMULA BELOW...

Area code,City,County,distance,Lat1,lat2,Lon1,Lon2,PostalCode,State

304,Iaeger,Mcdowell,229.3260268, +37.375246,40.322,-081.653889,-79.7022,24844,WV

304,Ikes Fork,Wyoming,212.4276639, +37.603344,40.322,-081.539980,-79.7022,24845,WV

304,Isaban,Mcdowell,229.3260268, +37.375246,40.322,-081.653889,-79.7022,24846,WV

304,Itmann,Wyoming,212.4276639, +37.603344,40.322,-081.539980,-79.7022,24847,WV

304,Indore,Clay,147.2379336, +38.465777,40.322,-081.050662,-79.7022,25111,WV

304,Institute,Kanawha,172.7424696, +38.282497,40.322,-081.565140,-79.7022,25112,WV

304,Ivydale,Clay,147.2379336, +38.465777,40.322,-081.050662,-79.7022,25113,WV

304,Inwood,Berkeley,110.7391261, +39.362373,40.322,-078.033065,-79.7022,25428,WV

304,Independence,Preston,58.48967339, +39.482740,40.322,-079.836968,-79.7022,26374,WV

304,Industrial,Harrison,85.72867403, +39.279818,40.322,-080.575379,-79.7022,26375,WV

304,Ireland,Lewis,104.5593849, +38.945847,40.322,-080.514490,-79.7022,26376,WV

304,Idamay,Marion,65.01247741, +39.487740,40.322,-080.266588,-79.7022,26576,WV

[Vaughan]

Please use IsEmpty() instead of ="".

[Lee Smith]

And some spaces too,

Also, if you use the Code format icon, it does add some color formatting for better readability.

If (

	Lat1 = "" or Lon1 = "" or Lat2 = "" or Lon2 = "" ;

	"" ;

	If (

		Sin ( ( Lat1 / 57.29577951 ) ) * Sin ( Lat2 / 57.29577951 ) +

		Cos ( Lat1 / 57.29577951 ) * Cos ( Lat2 / 57.29577951 ) *

		Cos ( Lon1 / 57.29577951 - Lon2 / 57.29577951 ) > 1 ;

		3963.1 * Acos ( 1 ) ;

		3963.1 *

		Acos (

			   Sin ( Lat1 / 57.29577951 ) * Sin ( Lat2 / 57.29577951 ) +

			   Cos ( Lat1 / 57.29577951 ) * Cos ( Lat2 / 57.29577951 ) *

			   Cos ( Lon1 / 57.29577951 - Lon2 / 57.29577951 )

		  )

	)

)