Home
Welcome to the photo2sql wiki!
photo2sql is a simple perl script that reads EXIF data of your photos (aperture,shutter speed etc) to sql database for further analysis. Unlike other similar programs like ExposurePlot, photo2sql works with photos in RAW format too.
- perl
- Image::ExifTool module for perl (
apt-get install libimage-exiftool-perl
on Debian or get it from CPAN ) - DBI and DBD::SQLite perl modules (
apt-get install libdbd-sqlite3-perl
)
The script could work in 2 modes:
- put EXIF data directly into SQLite database
photo2sql [--init] --path <directory>
- create SQL file that creates a table called shots and populates it with the exif data from your photos. Could be handy if your photo collection located in one place and you like to analyze it in some other place.
photo2sql --dumpsql --path <directory>
photo2sql creates (if asked to) a table called shots in SQL database (SQLite is used by default). The table contains the following columns :
- lens Camera lens
- camera Camera model, Nikon D70, D200 etc
- make _Camera make, Nikon, Canon etc _
- shutter_speed Shutter speed
- aperture Aperture
- iso ISO
- focal_len Lens focal length
- crop_factor Scale factor to 35mm
- time_stamp When the photo was taken
List of my cameras by number of photos taken in descending order:
sqlite> select make,camera,count(*) from shots group by make,camera order by count(*) desc;
NIKON CORPORATION | NIKON D200 | 13292 |
NIKON CORPORATION | NIKON D70 | 12226 |
Canon | Canon PowerShot G5 | 8566 |
NIKON CORPORATION | NIKON D7000 | 7425 |
Canon | Canon PowerShot S90 | 3259 |
Sony Ericsson | K750i | 523 |
OLYMPUS CORPORATION | X250,D560Z,C350Z | 391 |
Number of shots vs focal length in 35mm format distribution:
SELECT
ROUND(focal_len * crop_factor / 10) * 10 AS fffl,
COUNT(*) AS cnt
FROM shots
WHERE
fffl BETWEEN 1 AND 800
GROUP BY fffl;
Focal length range | Number of shots |
10.0 | 2 |
20.0 | 1603 |
30.0 | 3826 |
40.0 | 7630 |
50.0 | 3920 |
60.0 | 1849 |
70.0 | 1616 |
80.0 | 5521 |
90.0 | 1397 |
100.0 | 1426 |
110.0 | 4057 |
120.0 | 1484 |
130.0 | 4132 |
140.0 | 3106 |
150.0 | 318 |
160.0 | 25 |
... | ... |
These data could be visualised as shown on picture on top of this page using gnuplot or similar tool.