Whoops, see below for the correct link to postGIS, a free geometric application for PostgreSQL. Also there is a link below showing an example of how postGIS can be used:
http://unserializableone.blogspot.com/2007/02/using-postgis-to-find-points-of.html http://postgis.refractions.net/ Regards, LelandJ Leland F. Jackson, CPA wrote: > O/K, one last thing I will mention, so you're aware of it. PostgreSQL, > and probably some other databases, have special geometric data types and > functions that do all the number crunching within the database itself; > although, I've never worked with the PostgreSQL geometric features. > Also, PostgreSQL will scale to huge databases and tables. It would be > possible to use VFP as a front end to query stored procedure in > PostgresSQLto take advantage of PostgreSQL built in capabilities, either > in a desktop or web based interface. It would be a matter of moving > Dave's work into the PostgreSQL database language itself. I thought I > would mention this, as it's something you might want to look at in the > future. > > Also, if you ever want to compete with the Google in providing a map > service to others, you could take a look at postGIS. I'm just funning > you a little here. <g> > > http://www.smvfp.com/pgsqlhtml/functions-math.html > > http://www.smvfp.com/pgsqlhtml/datatype-geometric.html > > http://postgis.refractions.net/S > > Regards, > > LelandJ > > john harvey wrote: > >> 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] ** 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.
