Regular readers of this blog may recognize the demo file. I modified my existing Google Cluster Maps demo database to use with the new system.
This demo file should run fine in either FileMaker Pro or 13 — no special 13 features were employed. In fact, if you really need this to run in earlier versions of FileMaker, it could probably be made to run in FileMaker 7 format. The only thing that would probably need to be redone is the Get Coordinates script, which now employ the Insert from URL step. If you look back on early versions of the Cluster Maps demo, they were done in FileMaker 11 and used a Web Viewer instead.
Limitations of OpenStreetMap
- The OpenStreetMap data is not as complete as Google Maps
- I haven’t yet found a URL method for looking up Map Coordinates for Longitude and Latitude. This capability may be there, but the documentation in WIki format, is a little harder to follow than Google’s well documented API. Accordingly, I am still using the Google URL method to lookup coordinates. These calls, if kept in reason do not seem to require a Google Mapping API Key.
- There may be issues with the FileMaker export displaying Unicode characters — specifically German accent characters. FileMaker is exporting as UTF-8. One beta tester was set to give up but then tried Windows ANSI in the export settings and was able to get the data to display properly.
- Running this solution on a Network, I recommend you add in some Pauses to allow the exports to complete. Without these pauses in place, you may experience a FileMaker Pro crash.
1) To test the demo, I recommend you select a subset of points, for instance all the California addresses.
2) Choose where you would like the files to export to.
- If you want to examine the output, put them on your Desktop.
- If you want to just view the file in a browser, use the Temporary Folder (files get erased when FileMaker quits).
- To run the solution on an iOS device, choose the Documents Folder.
3) Set the Title for the HTML file. This will also be the file name with a .html extension.
4) With a small subset, click the ‘Generate Cluster Map’ button at the top.
5) Click the ‘OK’ button and watch the screen flash around for a bit.
Adding the OpenStreetMap Cluster Mapping functionality to your solution
To add this Cluster Mapping capability to your solution,
- Copy or recreate two Custom Functions to solution:
- Extract – //Extract(source, start, end) Middle ( source; Position ( source; start; 1; 1)+Length(start); Position(source; end; Position ( source; start;1;1); 1 )-(Position(source;start;1;1)+Length(start)))
- GeoBox -Source: http://www.briandunning.com/cf/1144
- Add the Virtual List Utility data table to your solution. No relationship is needed.
- Copy all the Global fields from the Settings table into your settings table
- If necessary, add a set of numeric Longitude and Latitude field to your ‘Data’ data table.
- Add a text Global ‘XML Coordinates Catcher’ field to your Data table. This is used in the Insert from URL step in the Get Coordinates script.
- Copy the set of OpenStreetMap scripts into your solution.
- In the “Generate HTML file” script, modify the Go to Layout step to go to a layout in your solution that you are planning to have the address information come from.
- Go into the script called “Generate HTML file” and set the fields in the $NameAndAddress variable to your data table’s address fields.
- Copy the Get Coordinates scripts in to your solution. Edit the script step that sets the XML Coordinates Catcher to your newly created field.
- If you plan on running the solution in a network situation, add some 3 second pauses to slow down the exports and prevent FileMaker Pro crashes. These steps are already in the script, but should be set to Active.
- Create a Value List for the Export Location popup list. Include:
- Desktop Path
- Documents Folder
- FileMaker Folder
- Temporary Path
- FileMaker Web
- Add the Settings fields to your Settings layout
- Copy the ‘Generate Cluster Map’ button and add it to your layout.
- Import or Copy and Paste all the text from the Settings tab in the demo solution into your solution. If you are HTML-savvy, you can edit the HTML text to display other things you want to show up on your webpage.
- If you do not already have Longitude and Latitude numbers for the addresses in your solution, run the Get Coordinates for All Records script first.
- Test the ‘Generate Cluster Map’ button.
- Test. Fix, Test. Repeat as necessary.
Edit this script: OpenStreetMap – Generate HTML file
Put whatever data you want to display on the map pins in the $NameAndAddress variable. Set the $Long and $Lat variables to your coordinates fields.
Clusters on a Map
If you zoom in and click on the coloured circles, they should break down into blue dots. The number on the cluster indicates how many data points are in the cluster.
Clicking on a blue dot will reveal the name of the location
Multiple locations at a single address — Leaflet displays them
This was one of the limitations of the markercluster.js using Google Maps. It could not handle multiple addresses.
That’s it. Have fun with it. Let me know of limitations you find or suggested improvements. Hope it is useful to your solutions. If you really like it, donate to the OpenStreetMap project.
2014-07-04 Update: Modified the demo file to include scripts that get coordinates from OpenStreetMap instead of using Google’s API. The OpenStreetMap lookup is much more sensitive to anomalies in your addresses, so you may need to edit your addresses to get good results.
2014-05-23 Update: Reader Bruce Robertson suggested a simpler approach to having to reset the serial number each time the script runs. Instead of using the ID as the basis of the numbering scheme in the Virtual List, he recommended using Get(RecordNumber). That way, as long as there are enough records in the Virtual_List_Utility table to match the lines of text in the $$virtual_list Global Variable, it doesn’t matter what the serial number is. What that would look like is changing the Field definition of virtual_list to:
2014-07-08 Update: I tried running the Virtual List technique on FileMaker Server and my observation was that the routine of deleting all records, resetting the serial number to 1 and recreating the correct number of records was quite slow. It also exposed the user to a message saying ‘Deleting records’ which can be unnerving if they don’t know what is going on. Instead, at the suggestion of a clever developer on the Facebook FileMaker Users & Developers Group, I modified the Virtual List field to reference Get(RecordNumber), instead of the ID field, so it doesn’t matter what serial number it is. The virtual_list field definition now looks like this:
Then I changed from the Delete All, reset the serial number and recreate new records approach. Now the Export script tests to see if there are enough records and add more if needed. If there are too many records, the script omits the extra records. Also, I added a button to display the Virtual List layout so you can see what is going on after the export is done. If you downloaded the demo before, I recommend you download a fresh copy. I think it is improved.