While Fulcrum photo fields make attaching pictures to records a ‘snap’, the flexibility of having a single field for multiple photos can be somewhat challenging when dealing with exports. In this short blog post I will describe a few techniques I have found for managing this one-to-many relationship challenge.
Fulcrum exports a zip file that contains all of your photos along with your data in CSV, GeoJSON, or Shapefile format (KML files are handled a bit differently). Upon reviewing your data, you will notice that photo fields contain a comma separated list of the photo files that are associated with that particular field.
Unfortunately this format is not very handy for linking to the photos from within a spreadsheet or GIS program. Fortunately, with minimal scripting effort, we can transform the raw CSV export into a much more user-friendly tool for accessing your data.
Step 1: Exploring your data
You could use a spreadsheet program such as Excel or LibreOffice to explore your data, but I’m going to import the CSV file into a new Google Drive spreadsheet so that I can easily share it. Login to your Google account, navigate to Google Drive and click the big red arrow to upload your file. Make sure the file is converted to Google Spreadsheet format (you may have to adjust your upload settings preferences).
Step 2: Creating PDF Photo Sheets
Now we will deal with our one-to-many photo challenge. Rather than having a bunch of files per photo field, we will tidy things up by merging the associated photos into a single, multi-page PDF photo sheet. The best utility I’ve found for accomplishing this task is this excellent little img2pdf Python script. Simply copy the img2pdf.py file into your folder containing the photos and you are ready to go.
img2pdf is a command line utility that expects the following arguments:
img2pdf.py file1.jpg file2.jpg file2.jpg –output merge.pdf
A complete list of arguments can be found on the README document, and more help is available with the -h or –help option.
In order to help automate the process of creating our photo sheets, we can build a little shell script or batch file by concatenating the key fields in our spreadsheet. If you only have one photo field in your app, you can simply name the resulting PDF file the fulcrum_id, so that it is easy to link back to. If you have several photo fields, you’ll probably want to come up with a more creative naming solution and folder structure. Here’s what my spreadsheet looks like (I’ve hidden all the extra data fields to simplify things).
Add a new column at the end of your spreadsheet and call it img2pdf_cmd. Now we will use the fulcrum_id column (A) and the photos column (B) to build our img2pdf commands for each record. In the new img2pdf_cmd column we want to use the CONCATENATE function to write out our command. Note that img2pdf expects input files to be separated by just spaces (without commas). We can include a SUBSTITUTE function to remove the commas from our photo field. So the img2pdf_cmd column for our first record should look like this:
=CONCATENATE(“img2pdf.py “, SUBSTITUTE(B2, “, “, ” “), ” –output “, A2, “.pdf”)
Which gives us something like this:
img2pdf.py ad0c334e-b57f-471f-89f1-2ca17988b74c.jpg 88e60f5c-15e9-4d68-8e79-4b659f66157b.jpg –output fc4dd641-37cc-4053-a6d5-9e08d77c0a77.pdf
Now we simply copy this formula down for all of the rest of the records and end up with something like this.
If you are on Windows, you can create a batch file by copying the img2pdf_cmd column and pasting it into your favorite text editor. Make sure to remove the first row field heading (img2pdf_cmd) and save it in your photo directory as img2pdf.bat.
img2pdf.py ad0c334e-b57f-471f-89f1-2ca17988b74c.jpg 88e60f5c-15e9-4d68-8e79-4b659f66157b.jpg –output fc4dd641-37cc-4053-a6d5-9e08d77c0a77.pdf img2pdf.py 4da2cd00-d3a7-4468-997e-042303dc7137.jpg bc0edbe2-45b0-4ad5-9b35-a8c3e623634d.jpg –output b157881f-3cf9-4d87-86a3-d82a8133698b.pdf img2pdf.py 9fa41b28-24c8-4f8f-abf5-08604a043d13.jpg e28c7d9c-06af-43bc-8f8c-1862f0bc6642.jpg –output a81a9fa6-c9bc-41a1-afc5-70906025938a.pdf img2pdf.py 95360e82-f6a5-46ce-b33d-58a8f571fe11.jpg –output fb28db08-a223-4765-a0b2-23cdf4a02ff6.pdf img2pdf.py cb39737a-a3ed-4240-91ff-d967c8151ab9.jpg –output bd42aed4-9bf3-449c-98a5-1eda7dab9e95.pdf img2pdf.py 2be31a26-a482-4802-81e9-478e5cc676e9.jpg 4b4aeba8-7d78-4a41-9c61-b5bd60a484b2.jpg –output fa22e53a-b5ce-4c18-8bd2-82ce9050cd32.pdf
Now simply double click on img2pdf.bat to run the script and create the PDF photo sheets.
Step 3: Putting it All Together
Now that we have PDF’s to link to, we can upload these to a web server and point to the path http://yoururl/fulcrum_id.pdf from within our spreadsheet or GIS application. I like to use Google Spreadsheet hyperlink fields to link to my photos, as well as passing the map coordinates to the excellent DualMaps API to link each point to an interactive map that shows Google StreetView linked with Bing Bird’s Eye imagery. The end result is a nice user-friendly spreadsheet.