Bring up listing of employees within x-mile radius of a zip code?

  • 0
  • 1
  • Question
  • Updated 6 years ago
  • Answered
Does QB have a method to search our employee records by zipcode, and bring up a listing of employees within a x-mile radius of that zipcode?
We have a db of 5000+ records. Need a way to search them by entering a zip code.
If not, does Intuit have plans to include this, and if so, is there an ETA?
Photo of James Leahy

James Leahy

  • 0 Points

Posted 6 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
You need to geocode your data first - that means obtaining the latitude and longitude of each address or entity. In the case of zip code rather than addresses you can probably get away with just using the centroid of the zip code region which the post office publishes.

The algorithm to find all entities (in your case employees) within some distance of an origin (lat, lon) is to first defined the bounding box of latmin, latmax, lonmin and lonmax and then prune those entries that are are a greater distance away than the threshold. Distance is computing using the glorious Haversine function after which my business is named. See:

http://www.movable-type.co.uk/scripts/latlong.html

So in terms of implementing this in QuickBase you have to use script. You take the distance and original lat and lon as inputs. You then perform a query for all records within the bouding box fromed by latmin, latmax, lonmin and longmax, calculate the haversine formula to get the actual distance and prune those few corner cases where the calculated distance might be greater than the input distance. If you are not to fussy you can skip the pruning step.
Photo of Mark_Shnier

Mark_Shnier

  • 700 Points 500 badge 2x thumb
Just to add to Dan's post above, If you have a lot of employees, and needed an automated solution, there are 3rd party tools available from Intuit Developer partners like Juiced who can geocode your addresses, or geocode your target zip code on request.

If there are not too many employees hen you would just look them up manually and enter them as part of your employee setup. Then I would set up a special table with just 1 record and relate it to all of your employees. Then bring in the targeted zip code down to the employee record n a lookup and find a formula to calculate the distance between the Employee zip and the target zip.

In terms of using native QuickBase I would have to look for a formula which would not use sin or cos, as I don't see those in the native set of QuickBase formuals. But there is probably a way around that obstacle if you wanted to pursue this direction.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,224 Points 20k badge 2x thumb
Actually if you are locating your entities based on zip code you do not have to geocode addresses because there are freely available zip code databases that represent the latitude and longitude of the zip code zone centroid. For example this free database was compiled by the Census Bureau and undergone various crowd sourced updates:

http://www.boutell.com/zipcodes/

While QuickBase does not have trigonometric functions you don't really need them as we are probably only dealing with US locations and the trigonometric functions could probably be approximated using the QuickBase formula language or fully evaluated using JavaScript. This is especially true if you don't need 100% absolutely correct results like you would for calculating orbits of satellites.

So if you look at the attached image you will see that all points on the surface of a sphere of radius R fit pretty tightly with a quadrilateral bounded by minlat, maxlat, minlon, and maxlon. So if you calculate the bounding quadrilateral formed by minlat, maxlat, minlon, and maxlon based circle of radius R that just fits within it there are four only small corner sections that are father away from the center than R. When the radius R is small (say 25, 50 or 100 miles) and you are between the Artic and Antartic circles you don't have to worry about these corners as they are quite small. So you could simply calculate the minlat, maxlat, minlon, and maxlon associated with a given centerpoint and radius R and query QuickBase for those records within:

minlat < lat < maxlat
minlon < lon < maxlon

For pedestrian usage like store locators you could probably not even worry about pruning the results to exclude location within the four corners.