What's the best way to store EXIF data from photos in a Database (MySQL in my case). This is for a photo sharing site.
What are the most important Tags, and what are discardable?
+7
A:
It's hard for us to determine what is important for you. One approach might be to store all the properties in a table created something like this (approximate SQL syntax):
create table exif_info (
photo_id integer,
name varchar,
value varchar
);
Each row in this table associates one EXIF property with one photo. So you would need a whole bunch of rows to hold all the EXIF properties for a single photo, but this is exactly what relational databases are good at.
In this way, you can store all the available information without having to decide now what might be important later.
Greg Hewgill
2009-07-01 01:41:04
Agree that OP must decide which information is important for them. However, disagree with storing the data in name/value pairs. In practice, it's very difficult to create an efficient query like "select all of the photos and their attributes taken with camera X at iso setting Y and focal length between A and B". You're better off with discrete columns for each piece of important information. Name/value pairs will only work for simple queries like "select all of the photo attributes for photo number X".
Convict
2009-07-01 04:06:57
But different cameras have different EXIF data and can add new data in the future. Can you guess all the future digital camera features that will be tracked by EXIF?
Martin Beckett
2009-11-10 18:43:42
@mgb: No, and the point is you don't need to know all the possible properties ahead of time. Each individual photo can have its own set of properties, independently of any other photo.
Greg Hewgill
2009-11-10 20:19:52
+2
A:
For your second question...
Picasa
- Date and time that the photo was taken
- Camera make & model
- Resolution
- Orientation
- Focal length
- Aperture
- ISO speed
- GPS latitude and longitude
Flickr
- Camera
- Exposure
- Aperture
- Focal Length
- ISO Speed
- Exposure Bias
- Flash
Jorge
2009-07-01 02:19:44
A:
Actually, I would recommend storing EXIF as a blob (json?) since there is great variability across devices that generate EXIF. It may be easier to pull that as a chuck and then operate on it.
If this is for a photo sharing site, you probably want to store the IPTC info too, probably in the same manner.
Jauder Ho
2009-07-01 04:23:38
only if you don't want to search on the EXIF info using SQL. If you want to search on ISO, then you need it in a field of its own.
Val
2009-09-27 00:49:11
It was not listed as a requirement to search via SQL. I would think for a sufficiently large site that search would be handled by something like Lucene so this would not be an issue. But then again, maybe the OP wants to do selects on ISO...
Jauder Ho
2009-10-01 03:11:48
A:
EXIF is metadata. Why not use a data format designed from the ground up to deal with metadata? EXIF could be easily put into RDF, which has tons of tools, libraries and storage options.
Fenugreek Femtosecond
2009-11-20 21:38:40