(to be done in VFP9SP2 existing app)
Customer need: to grab/query towns near a certain zip code or city name, akin to like when you use an app where you're looking for a car and say "show me matches within 50 miles." I'm thinking latitude/longitude and google somehow, but I thought I'd poll the community to see what you'd recommend.
Thanks! --Mike
Yeah, as much location data as you can get (address, Zip, lat & long) and the Google API.
E.g a 'nearby search':
://developers.google.com/places/web-service/search
So I've done some searching on google and found easy pages where I can basically seed the URL with the points and then screen-scrape the distance between the two points, like this: https://www.freemaptools.com/how-far-is-it-between-muhlenberg_-pa,-united-st...
At this point, this client has asked me to query for other locations near their target location where they have had business. So the City, State already exists, giving me the ability to search other locations using the 2 points between each other. HOWEVER, next challenge is the best, most efficient way to do this. I don't have that plan yet. I know my SEARCH city, state. I know other city/states on file in the database. The quick crude thought is to plot that custom URL with the OTHER city/state values where they've conducted business and then show those that are within X miles, defined in the UI (just like when you say "show me all the cars 50 miles within zip code 21117."
I know there's gotta be a better way but at this point I'm polling the list for how to do that EFFICIENTLY. Meanwhile, I'll keep searching google (including API) for alternative help.
Thanks in advance! --Mike
On 2017-03-15 03:32, Alan Bourke wrote:
Yeah, as much location data as you can get (address, Zip, lat & long) and the Google API. -- Alan Bourke alanpbourke (at) fastmail (dot) fm
On Wed, 15 Mar 2017, at 05:23 AM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
(to be done in VFP9SP2 existing app)
Customer need: to grab/query towns near a certain zip code or city name, akin to like when you use an app where you're looking for a car and say "show me matches within 50 miles." I'm thinking latitude/longitude and google somehow, but I thought I'd poll the community to see what you'd recommend.
Thanks! --Mike
[excessive quoting removed by server]
Here's the math I used in VFP using the lat/long of both endpoints to determine the distance:
FUNCTION CalcDistance(StartingLat as Number, StartingLong as Number, EndingLat as Number, EndingLong as Number) as Number RETURN 3956 * 2 * ASIN(SQRT((SIN((StartingLat - EndingLat) * pi()/180 / 2)**2) + COS(StartingLat * pi()/180) * COS(EndingLat * pi()/180) * (SIN((StartingLong - EndingLong) * pi()/180 / 2)**2) )) ENDFUNC && CalcDistance(StartingLat as Number, StartingLong as Number, EndingLat as Number, EndingLong as Number) as Number
It's called from this code:
select distinct destination ; from (; select distinct f1.ccity as city_in, f1.cstatecode as state_in, f1.czipcode as zip_in, ALLTRIM(f2.ccity) + ', ' + ALLTRIM(f2.cstatecode) as destination, ; f2.czipcode as zip_dest, this.CalcDistance(VAL(f1.clatitude), VAL(f1.clongitude), VAL(f2.clatitude), VAL(f2.clongitude)) as Distance; from lucounties f1, lucounties f2; where f1.ccity = pcCity and f1.cstatecode = pcState and f1.ccitytype = 'D' AND f2.ccitytype = 'D' AND f1.czipcode <> f2.czipcode ; having Distance <= piRadius; ) x1 ; order by destination ; INTO CURSOR (lcCursor)
fyi. Thanks for all who chimed in! --Mike
On 2017-03-15 03:32, Alan Bourke wrote:
Yeah, as much location data as you can get (address, Zip, lat & long) and the Google API. -- Alan Bourke alanpbourke (at) fastmail (dot) fm
On Wed, 15 Mar 2017, at 05:23 AM, mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
(to be done in VFP9SP2 existing app)
Customer need: to grab/query towns near a certain zip code or city name, akin to like when you use an app where you're looking for a car and say "show me matches within 50 miles." I'm thinking latitude/longitude and google somehow, but I thought I'd poll the community to see what you'd recommend.
Thanks! --Mike
[excessive quoting removed by server]
This is a mySQL approach. It assumes you know all of the coordinates nearby and are in your data..
I've done something similar with a selling houses app, ordering by distance from a given point, place this in your SQL select statement:
((ACOS(SIN(' . **$search_location['lat']** . ' * PI() / 180) * SIN(**map_lat** * PI() / 180) + COS(' . **$search_location['lat']** . ' * PI() / 180) * COS(**map_lat** * PI() / 180) * COS((' . **$search_location['lng']** . ' - **map_lng**) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS "distance"
Replace $search_location with your relevant lat/lng values and the map_lat/map_lng values are the SQL columns which contain the lat/lng values. You can then order the results by distance and either use a where or having clause to filter our properties within a 50km range.
On Wed, Mar 15, 2017 at 12:23 AM, < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
(to be done in VFP9SP2 existing app)
Customer need: to grab/query towns near a certain zip code or city name, akin to like when you use an app where you're looking for a car and say "show me matches within 50 miles." I'm thinking latitude/longitude and google somehow, but I thought I'd poll the community to see what you'd recommend.
Thanks! --Mike
[excessive quoting removed by server]
-
Alan Bourke -
mbsoftwaresolutions＠mbsoftwaresolutions.com -
Stephen Russell