Re: Need a Math Wiz
Yeap: http://dev.mysql.com/tech-resources/articles/4.1/gis-with-mysql.html Regards, LelandJ Mark Stanton wrote: > Last time I looked there was a whole load of stuff at the end of the > MySQL (4.01 I think) manual about GIS functions. > > Mark > > > > [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com 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.
Re: Need a Math Wiz
Last time I looked there was a whole load of stuff at the end of the MySQL (4.01 I think) manual about GIS functions. Mark ___ Post Messages to: ProFox@leafe.com 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.
Re: Need a Math Wiz
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. > > 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
Re: Need a Math Wiz
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. 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. 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) >> *
RE: Need a Math Wiz
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. 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(&
Re: Need a Math Wiz
Another advantage of using the join method I described is you would be
using the Google API, or other application's, formulas to do all the
difficult math. They would probably have features to deal with
triangle, rectangle, polygrams, zip code, etc. This would save you the
trouble of coming up with all the math for the different spatial areas.
Regards,
LelandJ
Leland F. Jackson, CPA wrote:
> 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. 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)
>>
>>
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. 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_mi
RE: Need a Math Wiz
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. 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)^
RE: Need a Math Wiz
I used google's api to get the lat and long and now thanks to Dave Bernard, who I met at FoxForward last year, I think I have a solution. I'll check with him to see if he minds me posting his code. John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Leland F. Jackson, CPA Sent: Friday, April 27, 2007 11:13 PM To: ProFox Email List Subject: Re: Need a Math Wiz Well, why couldn't you use MapPro to build a geocoded file of all address within a fifty mile radius of your center point. Then import the geocoded file into a foxpro database. Then run a join on your warrant database against the geocoded database to see what you get. Regards, LelandJ john harvey wrote: > I can do that using MS MapPoint, MapInfo, or even Google, but I need to > select the records first. > > > JH > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf > Of Leland F. Jackson, CPA > Sent: Friday, April 27, 2007 10:51 PM > To: ProFox Email List > Subject: Re: Need a Math Wiz > > This looks interesting: > > http://www.mapproinc.com/docs/how_to_use_mappro_with_databases.pdf > > http://www.mapproinc.com/ > > Regards, > > LelandJ > > > john harvey wrote: > >> Aight, I know some o use guyz are math wizzes and I ain't. I am persistent >> though, and I will gladly use stolen/purloined/borrowed/copied/etc code. >> >> I have a table of warrants 55,000+ which I have geocoded. I am testing a >> system that will push out information, via email, to the neighborhood >> > watch > >> groups, a table of crimes within a 1/2 mile radius, over the past 30 days. >> That data is already available via a system the MPD has and I'm pushing it >> to some test users. >> >> I want to also add into those emails a listing of warrants within that >> radius and push it out as well. Therein lies the problem. (I'm afraid I >> > must > >> have been distracted when those classes were taught). Does anyone have a >> clue how to do a select from a table given a lat and long and select all >> records that fall within the 1/2 mile radius? >> >> Help!! >> >> John Harvey >> >> >> >> [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com 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.
Re: Need a Math Wiz
Well, why couldn't you use MapPro to build a geocoded file of all address within a fifty mile radius of your center point. Then import the geocoded file into a foxpro database. Then run a join on your warrant database against the geocoded database to see what you get. Regards, LelandJ john harvey wrote: > I can do that using MS MapPoint, MapInfo, or even Google, but I need to > select the records first. > > > JH > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf > Of Leland F. Jackson, CPA > Sent: Friday, April 27, 2007 10:51 PM > To: ProFox Email List > Subject: Re: Need a Math Wiz > > This looks interesting: > > http://www.mapproinc.com/docs/how_to_use_mappro_with_databases.pdf > > http://www.mapproinc.com/ > > Regards, > > LelandJ > > > john harvey wrote: > >> Aight, I know some o use guyz are math wizzes and I ain't. I am persistent >> though, and I will gladly use stolen/purloined/borrowed/copied/etc code. >> >> I have a table of warrants 55,000+ which I have geocoded. I am testing a >> system that will push out information, via email, to the neighborhood >> > watch > >> groups, a table of crimes within a 1/2 mile radius, over the past 30 days. >> That data is already available via a system the MPD has and I'm pushing it >> to some test users. >> >> I want to also add into those emails a listing of warrants within that >> radius and push it out as well. Therein lies the problem. (I'm afraid I >> > must > >> have been distracted when those classes were taught). Does anyone have a >> clue how to do a select from a table given a lat and long and select all >> records that fall within the 1/2 mile radius? >> >> Help!! >> >> John Harvey >> >> >> >> [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com 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.
RE: Need a Math Wiz
I can do that using MS MapPoint, MapInfo, or even Google, but I need to select the records first. JH -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Leland F. Jackson, CPA Sent: Friday, April 27, 2007 10:51 PM To: ProFox Email List Subject: Re: Need a Math Wiz This looks interesting: http://www.mapproinc.com/docs/how_to_use_mappro_with_databases.pdf http://www.mapproinc.com/ Regards, LelandJ john harvey wrote: > Aight, I know some o use guyz are math wizzes and I ain't. I am persistent > though, and I will gladly use stolen/purloined/borrowed/copied/etc code. > > I have a table of warrants 55,000+ which I have geocoded. I am testing a > system that will push out information, via email, to the neighborhood watch > groups, a table of crimes within a 1/2 mile radius, over the past 30 days. > That data is already available via a system the MPD has and I'm pushing it > to some test users. > > I want to also add into those emails a listing of warrants within that > radius and push it out as well. Therein lies the problem. (I'm afraid I must > have been distracted when those classes were taught). Does anyone have a > clue how to do a select from a table given a lat and long and select all > records that fall within the 1/2 mile radius? > > Help!! > > John Harvey > > > [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com 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.
Re: Need a Math Wiz
This looks interesting: http://www.mapproinc.com/docs/how_to_use_mappro_with_databases.pdf http://www.mapproinc.com/ Regards, LelandJ john harvey wrote: > Aight, I know some o use guyz are math wizzes and I ain't. I am persistent > though, and I will gladly use stolen/purloined/borrowed/copied/etc code. > > I have a table of warrants 55,000+ which I have geocoded. I am testing a > system that will push out information, via email, to the neighborhood watch > groups, a table of crimes within a 1/2 mile radius, over the past 30 days. > That data is already available via a system the MPD has and I'm pushing it > to some test users. > > I want to also add into those emails a listing of warrants within that > radius and push it out as well. Therein lies the problem. (I'm afraid I must > have been distracted when those classes were taught). Does anyone have a > clue how to do a select from a table given a lat and long and select all > records that fall within the 1/2 mile radius? > > Help!! > > John Harvey > > > [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com 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.
RE: Need a Math Wiz
I was successful in getting all our warrants on the web, except for the secret indictments while I was with the SCSO. I wrote the original website in Fox and Web Connection and since I left, they have redone it in php using my design. Check it out at www.shelbywarrants.org. This site, coupled with the WASP program I wrote helped reduce the backlog by over 5,000 warrants in one year, even though the current Sheriff (who doesn't know the difference between a tort and a felony) cut the Fugitive Bureau by 25 officers. The website allows anyone to search by name or address, last name only, or street name only. It then allows the user to submit a tip (anonymous or otherwise.) Now, all those same warrants will be getting pushed out so that if someone in the neighborhood watch has a warrant, they will turn themselves in or else hit the road, and other's will turn them in. John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Chet Gardiner Sent: Friday, April 27, 2007 9:37 PM To: profox@leafe.com Subject: Re: Need a Math Wiz Have you checked with your lawyers before "enlisting" civilians to "catch crooks"? Are you fingering a bunch of regular folks who've just missed a traffic court appointment or are these warrants for real "crooks"? Are you trying to pin these "crimes" on folks with outstanding warrants? I wonder how many of the warrants are for folks who are in neighborhood watch groups... Sounds fishy... This sounds kinda' near that gray line that separates legal from unconstitutional... http://www.aclu-tn.org/ http://www.aclu.org/police/gen/14614pub19971201.html john harvey wrote: > Aight, I know some o use guyz are math wizzes and I ain't. I am persistent > though, and I will gladly use stolen/purloined/borrowed/copied/etc code. > Mime-Version: 1.0 > Content-Transfer-Encoding: 7bit > Content-Type: text/plain; charset=us-ascii > > I have a table of warrants 55,000+ which I have geocoded. I am testing a > system that will push out information, via email, to the neighborhood watch > groups, a table of crimes within a 1/2 mile radius, over the past 30 days. > That data is already available via a system the MPD has and I'm pushing it > to some test users. > > I want to also add into those emails a listing of warrants within that > radius and push it out as well. Therein lies the problem. (I'm afraid I must > have been distracted when those classes were taught). Does anyone have a > clue how to do a select from a table given a lat and long and select all > records that fall within the 1/2 mile radius? > > Help!! > > John Harvey > > > [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com 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.
Re: Need a Math Wiz
Have you checked with your lawyers before "enlisting" civilians to "catch crooks"? Are you fingering a bunch of regular folks who've just missed a traffic court appointment or are these warrants for real "crooks"? Are you trying to pin these "crimes" on folks with outstanding warrants? I wonder how many of the warrants are for folks who are in neighborhood watch groups... Sounds fishy... This sounds kinda' near that gray line that separates legal from unconstitutional... http://www.aclu-tn.org/ http://www.aclu.org/police/gen/14614pub19971201.html john harvey wrote: > Aight, I know some o use guyz are math wizzes and I ain't. I am persistent > though, and I will gladly use stolen/purloined/borrowed/copied/etc code. > Mime-Version: 1.0 > Content-Transfer-Encoding: 7bit > Content-Type: text/plain; charset=us-ascii > > I have a table of warrants 55,000+ which I have geocoded. I am testing a > system that will push out information, via email, to the neighborhood watch > groups, a table of crimes within a 1/2 mile radius, over the past 30 days. > That data is already available via a system the MPD has and I'm pushing it > to some test users. > > I want to also add into those emails a listing of warrants within that > radius and push it out as well. Therein lies the problem. (I'm afraid I must > have been distracted when those classes were taught). Does anyone have a > clue how to do a select from a table given a lat and long and select all > records that fall within the 1/2 mile radius? > > Help!! > > John Harvey > > > [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com 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.
RE: Need a Math Wiz
I think I may have a solution. I'll let you know if it works. John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Virgil Bierschwale Sent: Friday, April 27, 2007 8:59 PM To: 'ProFox Email List' Subject: RE: Need a Math Wiz I'd be interested in helping you to map them like I have done here since you have the lat and lng http://www.virgilslist.com/bsolutions_ff1059.asp?state=VA If it will help you and it's not confidential, that is. Virgil Bierschwale Armstrong and Skipper Real Estate (830) 329-6774 Cell (830) 864-4799 Fax (830) 864-4726 Home http://www.bierschwale.com http://www.bierschwalesolutions.com http://www.JobsForOurFuture.com/index.php -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of john harvey Sent: Friday, April 27, 2007 8:57 PM To: 'ProFox Email List' Subject: RE: Need a Math Wiz Aight, I know some o use guyz are math wizzes and I ain't. I am persistent though, and I will gladly use stolen/purloined/borrowed/copied/etc code. I have a table of warrants 55,000+ which I have geocoded. I am testing a system that will push out information, via email, to the neighborhood watch groups, a table of crimes within a 1/2 mile radius, over the past 30 days. That data is already available via a system the MPD has and I'm pushing it to some test users. I want to also add into those emails a listing of warrants within that radius and push it out as well. Therein lies the problem. (I'm afraid I must have been distracted when those classes were taught). Does anyone have a clue how to do a select from a table given a lat and long and select all records that fall within the 1/2 mile radius? Help!! John Harvey [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com 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.
RE: Need a Math Wiz
I'd be interested in helping you to map them like I have done here since you have the lat and lng http://www.virgilslist.com/bsolutions_ff1059.asp?state=VA If it will help you and it's not confidential, that is. Virgil Bierschwale Armstrong and Skipper Real Estate (830) 329-6774 Cell (830) 864-4799 Fax (830) 864-4726 Home http://www.bierschwale.com http://www.bierschwalesolutions.com http://www.JobsForOurFuture.com/index.php -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of john harvey Sent: Friday, April 27, 2007 8:57 PM To: 'ProFox Email List' Subject: RE: Need a Math Wiz Aight, I know some o use guyz are math wizzes and I ain't. I am persistent though, and I will gladly use stolen/purloined/borrowed/copied/etc code. I have a table of warrants 55,000+ which I have geocoded. I am testing a system that will push out information, via email, to the neighborhood watch groups, a table of crimes within a 1/2 mile radius, over the past 30 days. That data is already available via a system the MPD has and I'm pushing it to some test users. I want to also add into those emails a listing of warrants within that radius and push it out as well. Therein lies the problem. (I'm afraid I must have been distracted when those classes were taught). Does anyone have a clue how to do a select from a table given a lat and long and select all records that fall within the 1/2 mile radius? Help!! John Harvey [excessive quoting removed by server] ___ Post Messages to: ProFox@leafe.com 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.
RE: Need a Math Wiz
Aight, I know some o use guyz are math wizzes and I ain't. I am persistent though, and I will gladly use stolen/purloined/borrowed/copied/etc code. I have a table of warrants 55,000+ which I have geocoded. I am testing a system that will push out information, via email, to the neighborhood watch groups, a table of crimes within a 1/2 mile radius, over the past 30 days. That data is already available via a system the MPD has and I'm pushing it to some test users. I want to also add into those emails a listing of warrants within that radius and push it out as well. Therein lies the problem. (I'm afraid I must have been distracted when those classes were taught). Does anyone have a clue how to do a select from a table given a lat and long and select all records that fall within the 1/2 mile radius? Help!! John Harvey ___ Post Messages to: ProFox@leafe.com 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.
