Maps with Google’s Fusion Tables

Use the WriteSQL tool in conjunction with Google’s FusionTables to produce maps. FusionTables/Maps ignores everything past the first 100,000 rows, so it may be necessary to consolidate data.  In this example we’ll do so by rounding coordinates to the nearest tenth-degree. Note that this is for demonstration purposes only – including the GUID results in one row/specimen.

A map of female MSB Mammal collection shrews. (This is a horrible example; just use specimensearch.)

The SQL:

select

    state_prov,

    round(dec_lat,1) || ‘,’ || round(dec_long,1) coordinates,

    ‘<a href=””http://arctos.database.museum/SpecimenResults.cfm?guid=&#8217; || guid || ‘”” target=””_blank””>[ ‘ || guid || ‘ ]</a>’ link

from

    filtered_flat

where

    dec_lat is not null and

    dec_long is not null and

    scientific_name like ‘Sorex%’ and

    trim(sex)=’female’

This could be done with joins to “real” tables, but it’s often possible to use table FLAT to simplify things. Table FILTERED_FLAT is FLAT with encumbrances, so we don’t have to worry about exposing encumbered data.

|| is the Oracle concatenation operator.

The Oracle function ROUND takes two arguments – a number and precision.

ROUND (12.345,1) is 12.3.

AS aliases a column name, or names the results of function. “… AS coordinates” means “call all this junk to the left ‘coordinates’.” Column names are saved as CSV headers.

Google Maps will display HTML. The “link” column is a link to Arctos which opens in a new window. Doublequotes are doubled, the CSV “standard” (undocumented!) escape required by Fusion.

Reports/WriteSQL, copypaste, select CSV as Result.

download CSV, confirm that it looks reasonable. A plain text editor may be beneficial here – CSV will probably try to open in something like Excel, which may do unexpected things.

Now, go to Google Drive and create a Fusion Table

Click upload and next a few times….

Once the data are imported, click the Map tab. All female MSB Mammals shrews should be displayed. Filter to eg, look for outliers. A map of “Nevada,” for example.

Don’t forget that we rounded the coordinates – this will, at sufficient zoom levels, introduce some error and a wonky grid pattern.

Dusty McDonald, 6 August 2013