Bar Charts in Google Earth from FileMaker

One of our graduate students here at UC is investigating the movement of artifacts from the origin to archaeological find spot. She had been gathering her data in a FileMaker Pro database and wanted to be able to visualize the quantity of material either sourced or found in various cities. Since she already had a data table of cities with coordinates gathered from Google Earth, I decided to see if I could get FileMaker to talk directly to Google Earth. This database is the result.

The database consists of a table of cities, and a table of objects.

The objects have a field for Source City and a field for Find City. They also have a quantity. You can limit your query to anything: source city, find city, material, object type, dates, etc. You can view the summary results by either source or find city and export the result into a kml file that can be viewed in Google Earth.

This will work on an iPad as well, with FileMaker Go and Google Earth, but you will need to use an intermediary file manager (such as GoodReader) to change the extension from .txt to .kml.

Specifics from the Read Me file:

G Earth Bar Charts database made by John Wallrodt (john.wallrodt@classics.uc.edu) for general use. Use it as you wish.

This database was written in FileMaker Pro 11. It can be converted to the FileMaker Pro 12 format and should work with that program as well.

The database is supplied with geographical data and some object data. The object data has been randomized and is not useful archaeological data. The geographic location data is more accurate but has not been thoroughly reviewed.

The database illustrates a way to export quantitative FileMaker Pro data into a KML file viewable by Google Earth.
[Note that FileMaker 11 and 12 handle the last step of the scripts differently with the MacOS. FM11 will let you give a full name to the file, including the extension. FM12 will only let you create the file name. It will force you to keep the .txt extension. You will have to use the Finder to change the .txt extension to .kml.]
Cities table

This shows a simple method of creating KML files that have simple location dots. By entering a city name (that name must be unique) and both X and Y coordinates the database will make a place mark record for that site. At the bottom of the screen is a Show Locations button. That will start the process of assembling the KML file that will be exported. At the end of the script you will be reminded to give the file a name and a .kml file extension. This file extension is necessary for both Mac and Windows to open Google Earth. If you keep all 165 records in the database and hit the Show Locations button you will get all 165 locations. Adding fields to the cities table will let you group the cities into various criteria that you can use to isolate your data further.

Objects table

This table has some random data describing 1945 objects. The most important fields for this illustration are the source city (SourceCitySR) and a find city (FindCitySR) fields. Each object also has to have a quantity. By default, if you make a new record, the database supplies the number 1 so that the quantity field is not empty.

You can search for data any way you want. Look for objects from a particular source and map the find spots of that object. Find all coins, or terracottas, or objects by date. Or leave the database alone and map all 1945 objects. If you want to map items by source city click the Summarize by Source City. If you want to map objects by find city click Summarize by Find City. Each button will take you to a different layout in the database, both both layouts act the same.

Objects by Source City

If you click the Summarize by Source City you are taken here. This layout shows all of the objects in your found set and summarizes them by the city from which they came. The cities appear above the object data along with a total quantity count from the city.

At the bottom of the screen you should select what color you want your bar to be. You can select a zFactor as well. This number will be multiplied to the total quantity for the city. Google Earth expects a number in meters, so if your quantity is 10, the bar will only be 10 meters above the earth, and almost invisible. So adding a zFactor of 500 will increase that bar to 5000 meters. If you have a large quantity of material, this number should get lower. If you don’t have many objects, this number should be higher.

Click on the Map It! button to create the KML file. Some consideration should be given to the name of the file. You might end up with a lot of these files in Google Earth so you will want to recall what the chart represents.

Once you have the file with a KML extension, double click it to open in Google Earth. Click on a site name to see the total number of objects belonging to that site.

Objects by Find City

Works just as by Source City above.

Color

There is an additional table named color. This holds the hex values that Google Earth uses to show the colors of the bars. You can add to this table to create more colors.

Most of the work done with this database is in the scripts. I tried to put the calculations into fields in a table but they required too much customization. If you open up the Manage Scripts window you will see three important scripts (checked) and two additional ones to make sure that the two Objects by (x) layouts sort correctly.

Assemble Simple KML Points

This script essentially does three things: create an xml header for the document, add the PlacemarkKML data for each city in the found set, and then adds an xml footer.

Assemble KML Count by Source City

This creates the bar chart KML document and is slightly more complicated. It creates a slightly more complex xml header for the document (with a camera view to make the bars obvious when opening). Then it has to do two things: create a polygon whose elevation value is correct with the quantity and zFactor exaggeration supplied; and then create a point for the site that floats at the top of the bar and includes the html code needed to show the total quantity.

Polygon

The polygon is derived from the point data in the Cities table. The math is found in the Set Variable [$polygonwithz… script step and looks like this:

SourceCity::YCoord – .031305 & “,” & SourceCity::XCoord – .024081 & “,” & $total* Object::zFactor& ” ” & SourceCity::YCoord + .031304 & “,” &SourceCity::XCoord – .024081 & “,” & $total* Object::zFactor& ” ” & SourceCity::YCoord + .031304 & “,” & SourceCity::XCoord + .024081 & “,” & $total* Object::zFactor& ” ” & SourceCity::YCoord – .031305 & “,” & SourceCity::XCoord + .02408 & “,” &$total* Object::zFactor& ” ” & SourceCity::YCoord – .031305 & “,” & SourceCity::XCoord – .024081 & “,” & $total* Object::zFactor

You can adjust the +/- to change the size of the square.

That script step also grabs the code for the color from the color table.

Floating point

The floating point is created in the Set Variable [$placemarkkmlwithz… script step. That is where the total number of objects is added.

Assemble KML Count by Find City

Same as above.