The google api is fine for geocoding, but the center point changes with every query, so a join is not practical. This solution allows me to geocode the new warrants only, after the initial geocoding of the database, then run the queries. It works very well.
Kudos to Dave, the guy who probably sat in the front row in math class. (I was either in the back, or suspended.) John -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Leland F. Jackson, CPA Sent: Saturday, April 28, 2007 9:33 AM To: ProFox Email List Subject: Re: Need a Math Wiz Google's API look really good and I'm glad you found a solution. It looks like trigonometry is used to calculate the distance for each record to see if it fall within the circle as defined by the circle's radius. if you used Google, or some other tool like MapPro to create geocoded tables of all address within various distances from your point of reference, you could eliminate the need to calculate the distance of each record each time you ran a SQL, which would great;u speed up the SQL. The distance calculation would only need to be done once by the application used to create the geocoded tables. You could create tables of all address that fall with 1 mile, 10 miles, 25 miles, and 50 miles from your point of reference (eg the cricle's center) Then you could use SQL join to these tables to determine which of your table records were within the defined area. You wouldn't even need your tables geocoded; since, you could use the address or some other field common field to do the joins, provided you cleaned up the address or other fields in your tables by using the Google API pharsing feature. Of course, if your circle's center point of reference changed often, then this would not be a good solution; since, you would then need new tables each time the circle's cemter point of reference changed. Anyways, baring any unsatisfactory performance issue, (eg not likely using foxpro), Dave Bernard work look great. Regards, LelandJ john harvey wrote: > Ok, here's Dave's solution which is extremely fast and I would never have > been able to do the math, my dog ate the homework wouldn't even have > worked.... > > Here's my sql select: > > I simply opened the warrant file positioned on a record, closed the browse > window and did a scatter memvar then, > > SELECT * FROM MAPWANTS WHERE > DISTANCE(2,VAL(M.LAT),VAL(M.LONG),VAL(LAT),VAL(LONG),1)<=1 > > The <= 1 is 1 mile. > > Here's his distance function: > http://www.intellectiongroup.com/ > Dave Bernard > [EMAIL PROTECTED] > > *----------------------------------------------------------------------* > * Given a pair of lattitude and longitudes, return the approximate > * distance in nautical miles between points A and B. > * > * P_FUNC 1 = DD:MM:SS format, 2 = DD.MMMM format > * P_LT1 Lattitude of point A > * P_LG1 Longitude of point A > * P_LT1 Lattitude of point B > * P_LG2 Longitude of point B > * P_TYPE 0=Nautical Miles, 1=Statute Miles, 2=Kilometers > * > * All lattitude and longitude values are passed as strings in function 1 > * and as numbers in function 2. > * > * Assumes N lattitude and W longitude > * > * Examples: > * a = Distance(1, "33:57:00", "118:24:00", "40:38:00", "73:47:00", 2) > * a = Distance(2, 33.95, 118.4, 40.6333, 73.7833, 2) > * > * Test with http://www.indo.com/distance > * > *----------------------------------------------------------------------* > function Distance > parameters p_func, p_lt1, p_lg1, p_lt2, p_lg2, p_type > > private p_func, p_lt1, p_lg1, p_lt2, p_lg2, p_svdec, p_pi,; > p1_degN, p1_minN, p1_secN, p1_degW, p1_minW, p1_secW,; > p2_degN, p2_minN, p2_secN, p2_degW, p2_minW, p2_secW,; > p_lat1, p_lon1, p_lat2, p_lon2, p_dist, p_type > > p_svdec = set("decimals") > > set decimals to 9 > > if type("p_type") <> "N" > p_type = 0 > endif > > if p_type > 2 > p_type = 0 > endif > > p_pi = pi() > > do case > case p_func = 1 > p_lt1 = p_lt1 + ":00" > p_lg1 = p_lg1 + ":00" > p_lt2 = p_lt2 + ":00" > p_lg2 = p_lg2 + ":00" > > p1_degN = val(left(p_lt1, at(":", p_lt1) - 1)) > p_lt1 = substr(p_lt1, at(":", p_lt1) + 1) > p1_minN = val(left(p_lt1, at(":", p_lt1) - 1)) > p_lt1 = substr(p_lt1, at(":", p_lt1) + 1) > p1_secN = val(p_lt1) > > p1_degW = val(left(p_lg1, at(":", p_lg1) - 1)) > p_lg1 = substr(p_lg1, at(":", p_lg1) + 1) > p1_minW = val(left(p_lg1, at(":", p_lg1) - 1)) > p_lg1 = substr(p_lg1, at(":", p_lg1) + 1) > p1_secW = val(p_lg1) > > p2_degN = val(left(p_lt2, at(":", p_lt2) - 1)) > p_lt2 = substr(p_lt2, at(":", p_lt2) + 1) > p2_minN = val(left(p_lt2, at(":", p_lt2) - 1)) > p_lt2 = substr(p_lt2, at(":", p_lt2) + 1) > p2_secN = val(p_lt2) > > p2_degW = val(left(p_lg2, at(":", p_lg2) - 1)) > p_lg2 = substr(p_lg2, at(":", p_lg2) + 1) > p2_minW = val(left(p_lg2, at(":", p_lg2) - 1)) > p_lg2 = substr(p_lg2, at(":", p_lg2) + 1) > p2_secW = val(p_lg2) > > p_lat1 = (p1_degN + ((p1_minN + (p1_secN / 60)) / 60)) * p_pi / > 180 > * p_pi * (p1_degN + (p1_minN / 60) + (p1_secN / 3600)) / > 180 > p_lon1 = (p1_degW + ((p1_minW + (p1_secW / 60)) / 60)) * p_pi / > 180 > p_lat2 = (p2_degN + ((p2_minN + (p2_secN / 60)) / 60)) * p_pi / > 180 > p_lon2 = (p2_degW + ((p2_minW + (p2_secW / 60)) / 60)) * p_pi / > 180 > > case p_func = 2 > p1_degN = p_lt1 > p1_minN = 0 > p1_secN = 0 > p1_degW = p_lg1 > p1_minW = 0 > p1_secW = 0 > p2_degN = p_lt2 > p2_minN = 0 > p2_secN = 0 > p2_degW = p_lg2 > p2_minW = 0 > p2_secW = 0 > > p_lat1 = p1_degN * p_pi / 180 > p_lon1 = p1_degW * p_pi / 180 > p_lat2 = p2_degN * p_pi / 180 > p_lon2 = p2_degW * p_pi / 180 > > otherwise > set decimals to &p_svdec > return 0 > endcase > > ***Method 1 > p_dist = acos((sin(p_lat1) * sin(p_lat2)) + (cos(p_lat1) * cos(p_lat2) * > cos(p_lon1 - p_lon2))) > p_dist = int((p_dist * 180 * 60 / p_pi) +.5) && Distance in > Nautical Miles > > ***Method 2, with conversion to UTM (Mercator) coordinates > p_er = 6371.315 && Average radius of the > Earth > > ***Assume N lattitude and W longitude > *** For S lattitude: p_radlat1 = (p_pi / 2) + p_lat1 > *** Others remain the same > p_radlat1 = (p_pi / 2) - p_lat1 > p_radlon1 = (p_pi * 2) - p_lon1 > p_radlat2 = (p_pi / 2) - p_lat2 > p_radlon2 = (p_pi * 2) - p_lon2 > > ***Spherical coordinates: x=r*cos(long)sin(lat), y=r*sin(long)*cos(lat), > z=r*cos(lat) > p_x1 = p_er * cos(p_radlon1) * sin(p_radlat1) > p_y1 = p_er * sin(p_radlon1) * sin(p_radlat1) > p_z1 = p_er * cos(p_radlat1) > > p_x2 = p_er * cos(p_radlon2) * sin(p_radlat2) > p_y2 = p_er * sin(p_radlon2) * sin(p_radlat2) > p_z2 = p_er * cos(p_radlat2) > > p_d = sqrt((p_x1 - p_x2)^2 + (p_y1 - p_y2)^2 + (p_z1 - p_z2)^2) > > ***Side, side, side, law of cosines and arccos > p_theta = acos(((p_er^2 * 2) - (p_d^2)) / (p_er^2 * 2)) > > ***These need to be rounded > p_dist2 = p_theta * p_er && Distance in > Kilometers > p_dist3 = p_dist2 / 1.609344 && Distance in Miles > p_dist4 = p_dist2 / 1.852 && Distance in Nautical > Miles > > set decimals to &p_svdec > > do case > case p_type = 0 > return p_dist > case p_type = 1 > return p_dist3 > case p_type = 2 > return p_dist2 > endcase > > return p_dist > > > > > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]@shelbynet.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
