Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Thinking about a photo database

    Hello members of dBforums,

    I'm reading this great forum since quite a long time, and it has helped me a lot to develop my knowledge of databases in general and MS Access / SQL in particular. This time, I want to present a project myself. I'd be very pleased to read your ideas, suggestions and comments.

    As a matter of fact, I'm an (amateur) photographer of all kinds of things, but mainly public transport (trains, trams and busses) as well as aviation. I should have between 20.000 and 30.000 "unique" pictures by now, and new ones are added every day. Since my collection is growing, I'm starting to loose the overview and i'm looking for an efficient database system to manage it all.

    In order to do it efficiently from the beginning, and not having to re-encode every photo again when there should be some change, I'm looking for something that's complete, reliable, flexible and long-lasting. I'm thinking of an Access database since I'm having quite some (professional) experience with that, but maybe there's an even better solution that suits all my needs. I've had a look at several photo album software packages, but none of them seem to offer what I'm really lookng for. Below I'll list all the things I'd like to have in the best case, and how I expect Access to deal with them.

    A few general notices first: starting from the original picture (raw or jpeg), I'm making two resolutions of every picture with the same filename, using Photoshop actions (900x600 for general (web) use and 1800x1200 for more advanced use). I'd like to have an entry (record) for every picture, with an easy link to the big and small version and all things I need catalogue the pictures: standard data such as date, time and camera settings if possible (automatically extracted from EXIF data), categories and subcategories, standard characteristics, free characteristics using keywords/text, and last but not least: the location / geotagging of the picture.

    Maybe I'm just looking for something that doesn't exist, maybe I should just reconcile myself to the fact that I can only have a basic DB with a lot of manual actions remaining (but even that would be a great help to me), or maybe there is some perfect solution around that I haven't found yet.

    So, this is what I'm - ideally ! - expecting from the database:

    - Complete structure: add whatever category, keyword, characteristic (thus "field") I need.
    => No problem in Access, often limited in other solutions (especially photo album software with closed DB). Another advantage of a regular DB like Access: a category can be a query of every entry with a value between X and Y (like all trains with a number between 1000 and 1500), whereas photo album software often has fixed categories without this flexibility.

    - Long-lasting, easily exportable if needed
    => Again no problem in Access, but very problematic with photo album software (what if it the software is discontinued, if I need to export everything for use in another system etc)

    - Importing list of pictures (not the pictures themselves!) and EXIF data
    => I would have to look around, but I think it would be achievable in Access. In the worst case, I enter the filename and EXIF data I need myself.

    - User-friendly, easy to encode pictures without wasting time and effort
    => Looks very achievable in Access with a couple of good forms.

    - Easily searchable and groupable.
    => One of the main reasons I'd choose Access, everything is possible with queries

    - View images and other Windows commands from the database (such as copying a number of images to a distinct folder, etc)
    => Doesn't look easy in Access; opening a picture using a link or button is easy, but having a preview etc is harder

    - Add location and geotagging to the picture
    => A hard one. I could enter a location in a text field (combined with a link to a Google Maps to look for the value entered), but I'd rather like to geotag the picture. But that looks hard in Access. I could locate the picture in Google Maps and copy the link to Access, but I'd also like to have it in the other way: a geographic representation of my pictures (all of them, or only those found by a query). An option could be to extract the coordinaties from the Google Maps url and continue working with that, but I there still would be quite some work to do.

    - Web gallery: publish all the pictures in a web gallery and use the database to offer similar, but not necessarily all search options
    => I wouldn't quite see how I could achieve this. There are many web gallery tools around, but is there one in which I can use an existing (Access) database, or at least a list exported from this database, to attach keywords etc to pictures in the web gallery?

    Thanks in advance for reading this and thinking with me...

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    and your question is?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2011
    Posts
    2
    Quote Originally Posted by healdem View Post
    and your question is?
    As I wrote in my post, I'd like to know if Access is the best solution for what I'm planning to do, or if there is another solution that would fit my needs even more. And if it's going to be Access, I'd like to have some clues how to attack the problems I'm going to be confronted with (cf the list of my needs and the potential difficulties with Access I've listed).

    Thanks!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I guess I don't understand why you would not use something like Windows Live Photo Gallery, which lets you tag photos in multiple different ways, records the date and geographic location of the photo, allows you to group and search photos, and now stores all of this data in the photo file itself so that you will never need to reenter it again should you copy the photo to a new system.
    What, exactly, do you want to do that you cannot do with this piece of off-the-shelf free software?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Im guessing its the requirment for a 'web gallery'

    the biggest issue with usign Access (JET) databases in a web gallery is that file server type databases such as Access/Jet, Filemaker and so on don't neccesarily scale well. Most peopel would reccomend using a server database for a websit. Whether that server database is MS SQL Server, DB2, Oracle, MySQL, Postgres or whatever doens't amtter that much. if you are doing it on a budget then MySQL, Postgres and a few others are available as cheap or low or no cost options.

    Theres two separate elements to any web application theres the way the data is stored (the back end), and the way the data is presented (the front end). a server db will meet your back end requirments better than a filer server back end and there should be little or no cost implications. hiwever your choiuce of front end (the user interface does have implications. I wouldn't be at all surprised if you found that the exif information could be processed usign thrid party addons. lookign at google suggests there are options withint he PHP world
    Google
    If yoiu expect to search on exif data then you need to hoover up that data into the database so it can be searched easily and quickly, having to read from JPEG's each time will be a perfomance killer.

    theres nothing especially magical about Access, in fact many on this forumn will sneer dismissivcely at Access. however liek all systems developemnt issues if you can cloearly and concisely work out what you need you can usually convert that need into a working application. Access/JET uses its own variant of SQL, so if you are reasonably proficient on Access/JET SQL then you should be OK to convert most of that into standard SQL.

    I guess it depends what you want to do with this app.. is it for your purpsoes, is it for a you and a few mates, is it for any and every web user.

    My guess from the limited info you hgave given is that you will probably better going for a web hosted db, with a front end written in a scripting language such as PHP, PERL, ASP.NET or whatever.

    if its intended to run on a single machine, or intranet with limited users then Access will be fine, if its intended to run on multiple single machines then you mayt want to consider using a traditional development language appraoch such as C, C++, C#, Java, Delphi and so on.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Mar 2011
    Posts
    4
    That actually sounds like a really great idea, and it looks like you're already on a great start. You have a good knowledge and experience in photography, so I think you'll do great!! Go for it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •