Hello!
I need help. FM pro Newbie.
Goal: create a search function that returns all records within a 1000 feet from initial record using longitude and attitude only.
I have one location. I need to find all records within a 1000 feet of that location and display them.
Sounds easy right? I cannot seem to figure it out.
Please help Obi Won Kinobi
Thanks
Jeff
Hello,
I want to go ahead and move your post over to the Discussions area. The Community Feedback and Information is more for updates and information for the Community itself. No worries though, in Discussions you will get more views and helpful tips and advice.
TSPlatypus
FileMaker, Inc.
Had a similar problem. Ended up using global fields for the fixed position and having a calculation field for the distance.
The distance field is searchable.
My example file is calculating the distance in meters, youll have to convert the earth's radius 6371000 meters to feet.
Got the formula from https://www.movable-type.co.uk/scripts/latlong.html
Happy Filemaking
Kim
1000 feet works out to roughly 0.003 degrees. Obviously that will vary, depending on your distance from the equator.
If you're looking for a ballpark match, a search (or a relationship filter) for lats and longs +/- 0.0015 would get you started. Not sure what level of accuracy you're needing.
Part of the answer also depends on what you mean by "and display them". On a map? Google Maps and similar APIs will let you feed in an array of coordinates and return any points in a defined radius.
For example, one of my solutions uses a popover map to show nearby locations. I don't do a find request, but I can open the map on any record and see other locations within ~2 mile radius. Clicking any of those map markers goes to that record in the database. That's more complicated than doing a +/- search, but that's how I usually do these. What kind of result are you looking for? Do you need a found set of records specifically? Do you want to see nearby location records in a portal? Or do you just want to see them on a map?
Your solution is exactly what I have been needing. Can you elaborate how you do this? I would love to implement it in my system.
Is there much lag if you have all records open? Say 1000 records versus 5000?
Thanks in advance,
Michael
It's a little involved but I can explain how I've put mine together. Basically I use a map.php file with the Google Maps API, and a .js file of exported location data (name, lat, lng). Both files are on my web server. In FileMaker, I have a web viewer set to use "map.php" url, and I feed it the lat and lng of whatever record I'm on. "lat" and "lng" are just fields from my database.
So, in FileMaker, I have a url calc field in my Locations table that looks like this:
"http://your.server/map.php?lat=" & lat & "&lng=" & lng
That's the url for the web viewer.
On the webserver, "map.php" makes a simple Google Maps API call, like this (I've edited this down. My actual file is more complex, but these ~25 lines are all you need):
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8" />
<script src="http://maps.google.com/maps/api/js?sensor=false" type="text/javascript"></script>
<style>
html, body, #map { height: 100%; width: 100%; margin: 0px }
</style>
</head>
<body>
<div id="map"></div>
<script src="locations.js" type="text/javascript"></script> <!-- locations array -->
<script>
var lat = <?php echo $_GET['lat']; ?>;
var lng = <?php echo $_GET['lng']; ?>;
// map
var map = new google.maps.Map( document.getElementById('map'), {
center : { lat: lat, lng: lng },
mapTypeId : google.maps.MapTypeId.ROADMAP,
zoom : 13 // adjust this to your liking
});
// markers
var i, marker;
for ( i = 0; i < locations.length; i++ ) {
marker = new google.maps.Marker({
title : locations[i][0],
position : { lat: locations[i][1], lng: locations[i][2] },
map : map
});
// ...
</script></body></html>
The "locations.js" file is the array I exported from FileMaker. I have about 800 locations in my array. Having these in a static file makes the map fast to load, and is better for data separation. My location data doesn't change very often, so I just export a new file as needed.
var locations = [
["Location Name", 32.9750282, -96.9079804],
["Another Location", 32.0000000, -96.0000000],
// ...
];
So in summary, when I'm on a location record, I click a popover to see a map... the web viewer loads the url calc... and map.php draws the map using the current record lat/lng as the map center. The 'for loop' near the end is just looking at each row in the locations.js file, and using [0] for the marker title, [1] for the lat, and [2] for the lng. That's to show the other markers.
Hopefully that all makes sense. You mention being new to FileMaker, but all FileMaker is really doing here is loading a url, so I went ahead and posted all of that mess above. Lol. Apologies if it seems overwhelming. If you've done any php or js work at all, you'll be able to follow along. The main benefit of doing maps this way is you get a fast, clean Google Map without a lot of overlay nonsense.
*Edit: included closing html tags for "map.php", and fixed a javascript comment typo, <!-- --> to //
Note: my answer above doesn't include the 'click to go to another record' behavior I mentioned previously. I wanted to focus on what was necessary to get you up and running. If you're comfortable with everything I posted above, let me know and I can include the eventListeners for marker clicks and hovers.
That's fantastic, I get most of it and will send it to my web developer to do the web server part. We are a service company and this can really help.
Thank you for taking the time to list everything!
You are appreciated,
Michael McDonell / Senior Operations
Protecting You Is What We Do
(sent from mobile, sorry if there are typos)
You're welcome. Brainstorm about other things that might be useful to include in the .js file, beyond just a location name and coordinates. Maybe a hex color to denote different location categories, which could allow you to color-code your markers. Or include a location "-recid" field... or anything else that might be interesting to pass back as a script parameter. I'll leave that part to you.
Here is a simple to implement custom function that will get the distance between two GPS points:
FileMaker Custom Function:DistanceBetweenPoints ( Lat1; Lon1; Lat2; Lon2; Units )
Hello,
This is great! I think I understand most of it. I may need some help with the setup in FM16.
each record in the FMDB has two fields for longitude and latitude. These fields represent a location within San Francisco, CA
Step 1
I want to find a location in the FMDB
click a button to display ALL records that are within 1000 feet of the initial query. ( the calculation goes to the php file and js ? )
display the found records in a portal.
have the found locations pinned on the google map. aka Webviewer
I hope this make sense.
Best,
Jeff
Do you want to see nearby location records in a portal? Or do you just want to see them on a map?
YES and YES!
I kinda have this setup already. I just don't have the " show nearby locations" piece working.
Hello,
Thanks for moving it. I am getting some great feedback!
Best,
Jeff
is there a typo in the url?
"http://your.server/map.php?lat=" & lat & "&lng=" & lng
When I try to configure the web viewer it keeps asking for a table for lat?
To calculate the great circle distance between any two points, from two lat / lon coordinates you have in your database, you can easily do this using the simple Haversine formula:
I wrote a micro-service library you could call from FileMaker which does this exact calculation.
Here's an example:
The URL make look a bit busy, but the idea is you could wire this call into FileMaker or call this same function from Terminal, the Browser, any GET-enabled application. Plus, the micro-service can be ANYWHERE. It's free and easy to write. Best yet, it's extremely FAST.
The code I wrote accepts "M" (Miles), "K" (Kilometers), or "N" (Nautical Miles) as the last parameter on the URL line (or wired in from your FMP form).
The result is just using the Haversine formula, however, so that's directly implementable in FMP if you only want an FMP solution.
HOPE THIS HELPS.
Hi,
my OS X server is not happy. error Undefined index lng on line 17 of map.php
<script>
var lat = <?php echo $_GET['lat']; ?>;
Line 17 var lng = <?php echo $_GET['lng']; ?>;
any ideas why this is kicking this error out?
thanks
Jeff
mvstech wrote:
is there a typo in the url?
"http://your.server/map.php?lat=" & lat & "&lng=" & lng
When I try to configure the web viewer it keeps asking for a table for lat?
The lat and lng (in blue) represent whatever fields you're using in your database. So if your table occurrence is LocationDetail, you would use LocationDetail::lat and LocationDetail::lng
For a portal, I might create calc fields in Locations for
latStartRange lat - 0.0015
latEndRange lat + 0.0015
lngStartRange lng - 0.0015
lngEndRange lng + 0.0015
and use those for the portal relationship. Something like:
LocationID "X" LocationID AND
lat > latStartRange AND
lat < latEndRange AND
lng > lngStartRange AND
lng < lngEndRange
If you base your portal relationship on that, it will only show locations within ~1000 feet.
My map example above doesn't show /only/ nearby locations, it reads /all/ marker data and zooms in to ~2 mile radius. After loading the map, it can be dragged around or zoomed in/out to view other markers. That was a feature request from my client. If you want only the nearby location markers to appear, you could compute the distance from center (with the Google Maps API) and check the distance for each marker in the array inside the for loop.
https://developers.google.com/maps/documentation/javascript/reference#spherical
Doah!
Interesting, I thought I missed something. Using your example it would be:
"http://your.server/map.php?lat=" & LocationDetail::lat & "&lng=" & LocationDetail::lng
Correct?
Then, update the map.php?
<script>
var lat = <?php echo $_GET['lat']; ?>;
var lng = <?php echo $_GET['lng']; ?>;
I am learning a lot by doing, so this is very helpful.
Thanks
Jeff
That's a good idea. I like that approach. One question, how would you manage creating a more dynamic solution for the lat and lng location records? As opposed to updating the web file reguarly.
I have been studying that google API for days. I'm having a lot of fun developing this DB leveraging Google's data.
I will test the call functions again in the morning.
Thanks
Jeff
That's right.
The $_GET['lat'] and $_GET['lng'] will read the coordinate values passed in the url and use them for your map center.
Thanks for your help.
Jeff
The easy way would be to use a Static Map. That's an entirely different animal, though. The Static Map API returns a map /image/, it's not a map object you can interact with. At all. It's an option, but don't start over with a static map unless you need to. Get a better handle on the non-static API first and see if you can get it to do what you want.
What's easy about the Static Map API is that passing an array of marker coordinates is really straightforward. So you could parse, say, a List( ) of coordinates from your portal and pass that calc field into the url. Google has some examples for that:
Google Static Maps Developer Guide | Google Static Maps API | Google Developers https://developers.google.com/maps/documentation/static-maps/intro
Strategies using a non-static API and still keeping everything fully dynamic gets more involved and is probably outside the scope of a forum discussion.
My user has an export button scripted to save a "locations.js" file to their desktop. I also created a Transmit (FTP Mac application) shortcut in the Dock. So, they click export, drag "locations.js" onto the Transmit shortcut, and it overwrites the existing array on the webserver. It's not dynamic, but it's just a single click and drag, and they can do it whenever they want.
Thanks. Yea, static is not ideal for my project. Good info though.
Thanks
Jeff
Are you using WebDirect at all? Just one caveat with webdirect and Google maps is that WebDirect displays WebViewers in an iFrame and you need to use the Google Maps Embed API to make it work Google Maps Embed API | Google Developers
Hi Carl,
Thanks for the reply. I am not. My only challenge at this time is:
find a record
run a calculation that displays all records within 1000 feet of found record. Display those in a portal.
Cheers! Mate
Jeff
Did you not like my idea of using the Haversine formula in FMP?
It computes the great circle distance. I included a screenshot showing how I use it -- from any GET-enabled application (including FMP) that can call my little micro-service.
But you could just as easily create a CF with that logic. Basically a one-liner.
Hi,
I kinda do, but I want to try the a CF for that application.
Best,
Jeff
A CF is what I suggested above.
Did you read my posting?
Look at the last line.
All you have to do is put the formula in a CF and you're done. It shouldn't take more than 10 minutes.
mvstech wrote:
Hi Carl,
Thanks for the reply. I am not. My only challenge at this time is:
find a record
run a calculation that displays all records within 1000 feet of found record. Display those in a portal.
Cheers! Mate
Jeff
How many records do you have? A simple calculation using the CF could get you that answer but it will take time if there are lots of records.
Hi,
I'm a newbie to FM pro so, what is CF? Calculation Field? With what logic piece? I do want to centralize all actions inside FM. Do you have an example of the CF so I can figure it out? I appreciate your efforts.
Thanks
Jeff
Hi,
Yes a lot of records but the data in each record is minimal. No Graphics just a few fields. What is a CF exactly?
Thanks
Jeff
CF = custom function.
The two ways that I know to return a list of points that are a specified distance away are:
1) for each point, calculate its distance away, and only display those within the limit;
2) calculate a boundary box of lat and lon coordinates, and display only the points that fit within the box.
The first algorithm requires that the distance calculation be run on every point. This will be slow as the number of points increase (and on a slow network) and it's necessarily an unstored calculation.
The second algorithm requires calculating the boundary box once, then displaying the points that fit using indexed data (the lat and Lon of each point). This is much, much faster with large data sets.
A custom function exists to calculate the boundary box given a lat, lon and radius:
FileMaker Custom Function:GeoBoxBounds ( latitude ; longitude ; radiusInMeters )
Note that calculating a boundary "box" is computationally easy, and makes it simple to implement in FMP with a multi-predicate relationship. The boundary box geometry means that points in the corners are actually further away than the specified radius. For most applications this error is not a problem.
We are using several maps with dynamically created sets of objects to be shown as markers. We have event listeners added to open popups with additional informations. But we didn't yet manage to give information back to FileMaker from the map.
Would you mind to give us a hint about how you made this? Would be highly appreciated.
Why not just use the simple Haversine formula? It will let you compute the feet between any two lat/lon points.
There are lots of ways to make this code extremely fast, but the basic formula is high-school math. Below is the Java implementation.
If you look at this page: https://rosettacode.org/wiki/Haversine_formula, you can find implementations of this formula in 96 computer languages (sorry, no FMP).
--------
public class Haversine {
public static final double R = 6372.8; // In kilometers
public static double haversine(double lat1, double lon1, double lat2, double lon2) {
double dLat = Math.toRadians(lat2 - lat1);
double dLon = Math.toRadians(lon2 - lon1);
lat1 = Math.toRadians(lat1);
lat2 = Math.toRadians(lat2);
double a = Math.pow(Math.sin(dLat / 2),2) + Math.pow(Math.sin(dLon / 2),2) * Math.cos(lat1) * Math.cos(lat2);
double c = 2 * Math.asin(Math.sqrt(a));
return R * c;
}
public static void main(String[] args) {
System.out.println(haversine(36.12, -86.67, 33.94, -118.40));
}
}
If you've already got a mouseover event for an info window, it works exactly like that.
In your locations array (along with the location name and coordinates), include a recID, uuid, or something you can pass as a script parameter. Then call that as part of a click event handler. And create a FileMaker script that accepts the parameter in a find request.
So, here's the return function for my click event. The $ is what tells the url to load using the current FileMaker file. I'm passing a -recID value as my [4] value, to an "openRecord" script.
return function() {
window.location = 'fmp://$/filename?script=openRecord¶m=' + locations[i][4]
}
Made a script for the search and a script to fill in some testdata.
Wow! That easy! And it works. Maybe worth to note that extended privileges must be set for 'fmurlscript'.
Thanks a lot.
How is it possible to find out such finesses? By studying the training series? - or only from somebody who already knows?
You're welcome.
Figuring out these kinds of things? 20 years of FileMaker + web development (starting with CDML), and nice folks on this Community.
Good catch on the fmurlscript privilege. It's been a while since I set one of these up from scratch.
Hi Kim,
You made one or asking me to make one?
Thanks
Jeff
AKA Hypercard! For those of us who have been in the Macintosh game since the beginning!
Jeff
You made one or asking me to make one?
Hehe - well I probably should write longer explanations in my replies.
I assume youre refering to my last reply: "Made a script for the search and a script to fill in some testdata."
I made thoose two scripts in my Distance2.fmp file attached in my previous post.
If thats not what you asked about, maybe you need to make longer replies ;-)
Happy Filemaking
Kim
Hi Kim. Found it!
I will test and ask questions after.
Thanks
Jeff
Had a similar problem. Ended up using global fields for the fixed position and having a calculation field for the distance.
The distance field is searchable.
My example file is calculating the distance in meters, youll have to convert the earth's radius 6371000 meters to feet.
Got the formula from https://www.movable-type.co.uk/scripts/latlong.html
Happy Filemaking
Kim