I'd like to create a database of pictures. Where to begin?

Question

I'd like to create a searchable database of images.

I'd like something a little expandable, meaning I'd like to define my own fields and be able to search on them, but I'd also like to be able to insert keywords for broader searches.

I am a little concerned about encountering too steep a learning curve, but am willing to put in some work. :-)

A few more items that may help: Although I am open to hosting such a thing online, it's probably not my first choice. Also: I think we talking about a few dozen images initially but more over time.

Solution 1

My first thought is that you don't... place pictures in a directory and you can store links to the files, tags, etc in the database.

Solution 2

If you were using Windows you could use picasa from picasa.google.com. Pictures can be tagged with whatever you want and you can search based on those tags.

Option 2:

galleryproject.org

It is web based and has a mysql database, but the gui is simple. You can tag pictures and search on those tags. There are addons/extension for additional functionality.

Option 3:

A wiki such as mediawiki might also work.

Solution 3

Since you don't mention where do you want to implement your database, I assume you're doing this for a website, the approach I've been using is as follows:

Let's say that I have a directory with 100 pictures, each one named in order:

1.png
2.png
3.png
…
100.png

So I construct a database which stores such images information in the colums as follows:

id, name, size, keywords, …

So I'd construct a query that retrieves them based on the pertinent information provided:

"SELECT * FROM `images` WHERE NAME LIKE ?"

And so I can access them since their id's correspond to their filenames, e.g.:

echo '<img src="'.$queryResult->id.'png">';

But again, it depends on your implementation, is this for a website, a mobile app or a desktop app?

Update:

Since you say this is for a website, and if the images are not going to be uploaded by the clients, but only by the admin, then this is most certainly the best approach. But you may want to take a look at BLOBs too.

The main advantage of this approach (storing them on the filesystem rather than inside a db) is that you can easily access your images and edit them, resize them, change them and do whatever you can do to a normal image, without having to retrieve them from the db everytime you have to do a change. In fact, many websites that host users' OC use this approach of storing the images, e.g: 9GAG, 4Chan.

Comments

[Jon]

Or imgur. Imgur is (in my opinion) the best option out there.

[MaQleod]

Actually, you can use the BLOB type to store images, and in many cases, this is actually preferable (it depends on the BLOB size). Here is a good reference for sqlite, different database engines will perform differently: sqlite.org/intern-v-extern-blob.html

[tcv]

The pictures are an inventory of art items. We will want to include items such as location of artwork, whether it's on-loan, the provenance, etc. So, the database as I call it would be stored on the a local workstation, but it would be ideal to be able to somehow access that data on an iPad.

[arielnmz]

If you make a (properly configured) website, it could be displayed on every device you want. And I've also made a gallery (but for games) with information pertaining publisher, title, year, and an image and I used this method. I've never had any problem with this.

[Jakke]

You are right, in case you want to store tiny little files, blobs can be a really good solution. Most people who ask this question though are not aware of these specifications and so my first advise will be to store the pictures outside the database. When they get the time to read up more about them, they can make a justified decision about what's best for them. For small sites, either way is not going to pose any problems and dealing with image links instead of blobs will be easier on the learning curve as well. For heavy duty sites, you may want to consider both for optimal performance.

[tcv]

Well, not having much in the way of website creation experience, are there any web platforms that could be used for something like this?

[Jason C]

+100 I was just going to type the same answer (in fact I was going to recommend Picasa). There are plenty of good pre-existing services with access available over many platforms (Picasa, for example, has the web interface, an Android app, an iPad app, all of which support searching tags). There's not much reason to start from scratch.

[Jason C]

Don't forget Flickr as well, which lets you search descriptions, and descriptions can contain any arbitrary text. Here's Lifehacker's top 5.

[tcv]

But isn't Picasa heavily invested in Google Plus these days? And I am concerned about the lack of fields that can be defined. I will take a look at both options, though. Thanks!

[cybernard]

Picasa can be used with or without google plus. You can add numerous custom/user defined tags.

[cybernard]

Responding to below about accessing on iPad. Galleryproject is web based you can use any web browser to access it. You can add comments to each individual picture.

[arielnmz]

You may want to look more into web and mobile development. How complex or how pretty-formatted should the system be? Is it for personal use?