Importing to Piwigo

Piwigo is pretty good!

I mean, I mostly use Flickr at the moment, because it is quick, easy to recommend to people, and allows photos to be added to Trove. But I’d rather host things myself. Far easier for backups, and so nice to know that if the software doesn’t do a thing then there’s a possibility of modifying it.

To bulk import into Piwigo one must first rsync all photos into the galleries/ directory. Then, rename them all to not have any unwanted characters (such as spaces or accented characters). To do this, first have a look at the files that will fail:

find -regex '.*[^a-zA-Z0-9\-_\.].*'

(The regex is determined by $conf['sync_chars_regex'] in include/ which defaults to ^[a-zA-Z0-9-_.]+$.)

Then you can rename the offending files (replace unwanted characters with underscores) by extending the above command with an exec option:

find -regex '.*[^a-zA-Z0-9\-\._].*' -exec rename -v -n "s/[^a-zA-Z0-9\-\._\/]/_/g" {} \;

(I previously used a more complicated for-loop for this, that didn’t handle directories.)

Once this command is showing what you expect, remove the -n (“no action”) switch and run it for real. Note also that the second regex includes the forward slash, to not replace directory separators. And don’t worry about it overwriting files whose normalized names match; rename will complain if that happens (unless you pass the --force option).

Once all the names are normalized, use the built-in synchronization feature to update Piwigo’s database.

At this point, all photos should be visible in your albums, but there is one last step to take before all is done, for maximum Piwigo-grooviness. This is to use the Virtualize plugin to turn all of these ‘physical’ photos into ‘virtual’ ones (so they can be added to multiple albums etc.). This plugin comes with a warning to ensure that your database is backed up etc. but personally I’ve used it dozens of times on quite large sets of files and never had any trouble. It seems that even if it runs out of memory and crashes halfway, it doesn’t leave anything in an unstable state (of course, you shouldn’t take my word for it…).

Loading spatial data into MySQL with LOAD DATA INFILE

Just a note for my future reference: importing an Excel CSV into MySQL. The WKT column has been constructed by hand to be POINT(lng lat) and the CSV contains headers.

LOAD DATA INFILE '/full_path/to/file-on-server.csv'
INTO TABLE the_table
SET geographic_location = GeomFromText(@geographic_location)