Crowd Sourcing House Photos

Our house photos have us in a bit of a pickle. We ran out of money to keep up with them, and the appraisers, who at one time were going to update photos in the field, found that all things considered, they’d rather be appraising. Can’t say I blame them.

So we have a small crowd angry that their photos are old. But we also have an enormous crowd that would skin us alive if the photos went away. So…here’s my plan.

Mark the photos we have with the date when we display them, something we should have been doing anyway. The photos will still be out there, keeping our hides intact.
Let people upload their own photo. That should placate the I repainted my house and it doesn’t show in my photo crowd.
Release an API for the photos. After all, if people are providing data updates, people should get the data (I’m looking at you Google).

First things first - a file upload/processing piece. This afternoon-killing bit of code was so unexpectedly unpleasant I wanted to share it. You’ll need the gd and sqlite/pdo extensions enabled for PHP.

OUCH OUCH OUHC OUCH OUCH

[crayon lang=”php”] <?php

// validate email address function isValidEmail($email){

return preg_match("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$^", $email);

} // validate attribution function isThere($attribution) {

return strlen(trim($attribution)) > 0;

} // validate image type function isImageType(){

if ((($_FILES["file"]["type"] == "image/jpeg")
  || ($_FILES["file"]["type"] == "image/jpg")
  || ($_FILES["file"]["type"] == "image/gif")
  || ($_FILES["file"]["type"] == "image/png"))) {
    return true;
}
else return false;

} // check image size function isImageSize(){

return $_FILES["file"]["size"] < 2000000 ;

} // replace file name extension function replace_extension($filename, $new_extension) {

return preg_replace('/\..+$/', '.' . $new_extension, $filename);

}

/* * Remove spaces and special characters from file names. Swiped this from Drupal. / function sanitize_filename($name) {

$special_chars = array ("#","$","%","^","&amp;","*","!","~","‘","\"","’","'","=","?","/","[","]","(",")","|","<",">",";","\\",",",".");
$name = preg_replace("/^[.]*/","",$name); // remove leading dots
$name = preg_replace("/[.]*$/","",$name); // remove trailing dots

$lastdotpos=strrpos($name, "."); // save last dot position

$name = str_replace($special_chars, "", $name);  // remove special characters

$name = str_replace(' ','_',$name); // replace spaces with _

$afterdot = "";
if ($lastdotpos !== false) { // Split into name and extension, if any.
  if ($lastdotpos < (strlen($name) - 1))
      $afterdot = substr($name, $lastdotpos);

  $extensionlen = strlen($afterdot);

  if ($lastdotpos < (50 - $extensionlen) )
      $beforedot = substr($name, 0, $lastdotpos);
  else
      $beforedot = substr($name, 0, (50 - $extensionlen));
}
else   // no extension
 $beforedot = substr($name,0,50);

if ($afterdot)
  $name = $beforedot . "." . $afterdot;
else
  $name = $beforedot;

return $name;

}

// Holder of ye oh crap information $message = “”;

// form was submitted if ($_POST[‘xsubmit’] == ‘y’) {

// run data checks
if (!isValidEmail($_POST["email"])) $message .= "Invalid email address.<br />";
if (!isThere($_POST["attribution"])) $message .= "Attribution required.<br />";
if (!isset($_POST["license"])) $message .= "You must agree to the terms and conditions.<br />";
if (!isImageType()) $message .= "Image must be GIF, PNG, or JPG/JPEG.<br />";
if (!isImageSize()) $message .= "Image must be less than 2MB in size.<br />";

// run file upload if everything checked out OK
// the uploaded photo is shrunk/expanded to 600px wide
// and written out as date + filename.jpg
if (strlen($message) == 0 ) {
  try {
    // width of the uploaded image
    $newwidth = 600;

    // Set save directory and file name
    $directory = "photos/";
    $target = time().'-'.sanitize_filename($_FILES['file']['name']);

    // Write image to server

    $ext = end(explode(".",strtolower(trim($_FILES["file"]["name"]))));

    // Check is valid extension.
    if($ext == "jpg" || $ext == "jpeg"){
      $image = imagecreatefromjpeg($_FILES["file"]["tmp_name"]);
    }
    else if($ext == "gif"){
      $image = imagecreatefromgif($_FILES["file"]["tmp_name"]);
    }
    else if($ext == "png"){
      $image = imagecreatefrompng($_FILES["file"]["tmp_name"]);
    }

    list($width,$height)=getimagesize($_FILES['file']['tmp_name']);
    $newheight=($height/$width)*$newwidth;
    $tmp=imagecreatetruecolor($newwidth,$newheight);

    imagecopyresampled($tmp,$image,0,0,0,0,$newwidth,$newheight,$width,$height);

    if(!imagejpeg($tmp,$directory . replace_extension($target, "jpg"), 80)){
      $message =  "Gah! Something broke. Please try again later.";
    }
  }
  catch(Exception $e) {
    $message = "Gah! Something broke. Please try again later.";
  }

  /**
  * Upload data to sqlite
  */
  try
    {
      //open the database
      $db = new PDO('sqlite:house_photos.db');

      //insert some data...
      $stm = $db->prepare("INSERT INTO house_photos (pid, name, email, filename, upload_date) " . 
        " VALUES (:pid, :name, :email, :filename, strftime('%s','now') );");
      $stm->bindParam(':pid', $_POST["pid"], PDO::PARAM_STR, 8);
      $stm->bindParam(':name', $_POST["attribution"], PDO::PARAM_STR, 100);
      $stm->bindParam(':email', $_POST["email"], PDO::PARAM_STR, 60);
      $stm->bindParam(':filename', replace_extension($target, "jpg"), PDO::PARAM_STR, 60);

      $count = $stm->execute();

      // close the database connection
      $db = NULL;
    }
    catch(PDOException $e)
    {
      //print 'Exception : '.$e->getMessage();
      $message = "Gah! Something broke. Please try again later.";
    }

}

} ?>

<!-- show form on initial load or if there's a problem with a previous post -->
<?php if (strlen($message) > 0 || !isset($_POST['xsubmit'])) { ?>
  <form action="index.php" method="post" enctype="multipart/form-data">
    <input type="hidden" name="xsubmit" value="y"/>
    <input type="hidden" name="pid" value="<? echo $_GET["pid"] ?>"/>
    <input type="file" name="file" id="file" required /> <br />
    <div class="details">File must be GIF, PNG, or JPEG/JPG and 2MB in size or less.</div>
    <input type="email" name="email" id="email" placeholder="you@place.org" required /><label for="location"> Email Address</label><br />
    <input type="text" name="attribution" id="attribution" placeholder="Name or Company" required /><label for="location" title="name, company, etc."> Attribution</label><br />
    <p class="details">
      <strong>The Fine Print</strong><br />
      By submitting this photo, you agree that you own the copyright on the photo—you photographed it yourself, 
      it is a work for hire, or the copyright was transferred to you via written statement 
      or operation of law (e.g., inheritance). You also agreet to allow the photo to be 
      freely copied and modified by anyone, including third parties not affiliated with 
      Mecklenburg County Government, for any purpose.
    </p>
    <input type="checkbox" name="license" id="license" value="license" required /><label for="license"><strong>I agree</strong> with these terms and conditions.</label>
    <br />
    <input type="submit" name="submit" value="Upload" />
    <?php if (strlen($message) > 0) { ?>
      <p class="warning"><? echo $message ?></p>
    <?php } ?>
  </form>

<!-- show thank you message if everything loaded successfully -->
<?php } else { ?>
  <h2>You're Super!</h2>
  <p>You just helped add data all of our citizens can use and enjoy. Thanks!</p>
  <p>Your photo should be available within the next few days. We'll let you know when it's ready.</p>
<?php } ?>

I'm don't know if I'm off my game today or what, but a little upload process/database write shouldn't have hurt this bad. I exclaimed <em>balls!</em> so loud at one point my 5 year old showed up at my office door with his soccer ball. Here's what's going on, starting from the bottom and working our way back up.

If you've seen PHP, nothing in the HTML part will be unfamiliar. Basically you get a form when you get there, the form again with some red text if you botched something, and a thank you message if everything went well. The image disclaimer is more or less from Wikipedia.

Back to the top and the real meat of things. The functions at the top are all about data validation. Are all of the fields there, is the email address a real looking address, is the file <2MB and gif/png/jpg, etc. I'm doing some HTML5 form validation as well, but you should always check data inputs at the client and the server. The only thing interesting here is the sanitize_filename function, which I swiped from Drupal. This is so file names with odd characters and spaces don't get written that way to disk. 

If the form has been posted and the data all checks out, we're going to process the image and drop it on the server.

php $newwidth = 600;

// Set save directory and file name $directory = “photos/”; $target = time().’-‘.sanitize_filename($_FILES[‘file’][‘name’]);

// Write image to server
$ext = end(explode(“.”,strtolower(trim($_FILES[“file”][“name”]))));

// Check is valid extension. if($ext == “jpg” || $ext == “jpeg”){ $image = imagecreatefromjpeg($FILES[“file”][“tmp_name”]); } else if($ext == “gif”){ $image = imagecreatefromgif($FILES[“file”][“tmp_name”]); } else if($ext == “png”){ $image = imagecreatefrompng($_FILES[“file”][“tmp_name”]); }

list($width,$height)=getimagesize($_FILES[‘file’][‘tmp_name’]); $newheight=($height/$width)*$newwidth; $tmp=imagecreatetruecolor($newwidth,$newheight);

imagecopyresampled($tmp,$image,0,0,0,0,$newwidth,$newheight,$width,$height);

if(!imagejpeg($tmp,$directory . replace_extension($target, “jpg”), 80)){ $message = “Gah! Something broke. Please try again later.”; }

I'm making some executive decisions here. I'm going to store the images as JPG's with 80% quality and I'm going to resize incoming images (proportionally) to a width of 600px. I want some consistency for apps, and I also don't want to store some yoyo's 8MP house photos. We just got a quote for 1TB of SAN space for $30k. The whole "storage is cheap" thing hasn't hit Mecklenburg County yet. So what I'm doing is getting the size of the original image, making a new image with gd, and resampling the uploaded image to the new size. Then I write it out with a time stamp so I don't get file collisions. 

Next I need a database entry to store some information and a pointer to the file. I decided to use SQlite, as I think this crowd isn't going to be too big. I've been moving toward escalating data to something like Postgres when it needs to rather than starting things there. 

php //open the database $db = new PDO(‘sqlite:house_photos.db’);

//insert some data… $stm = $db->prepare(“INSERT INTO house_photos (pid, name, email, filename, upload_date) ” .

  " VALUES (:pid, :name, :email, :filename, strftime('%s','now') );");

$stm->bindParam(‘:pid’, $POST[“pid”], PDO::PARAM_STR, 8); $stm->bindParam(‘:name’, $POST[“attribution”], PDO::PARAM_STR, 100); $stm->bindParam(‘:email’, $_POST[“email”], PDO::PARAM_STR, 60); $stm->bindParam(‘:filename’, replace_extension($target, “jpg”), PDO::PARAM_STR, 60);

$count = $stm->execute();

// close the database connection $db = NULL;

The SQLite table looks like this:

sql CREATE TABLE house_photos (

"pid" TEXT NOT NULL,
"name" TEXT NOT NULL,
"email" TEXT NOT NULL,
"filename" TEXT NOT NULL,
"approved" INTEGER NOT NULL DEFAULT (0),
"upload_date" INTEGER

) “`

This is pretty straight forward with PDO, with one exception. After much swearing, it turns out you can’t write to a SQLite database unless the apache process has write permissions to both the SQLite file and the folder the SQLite file is sitting in. This isn’t as bad as it sounds, as you’re making a filesystem connection - you can (and should) move your SQLite database out of any HTTP shares entirely. But beyond that, it is straight-forward PDO with bound parameters so the script kiddies don’t eat my lunch. With PDO I could move the whole thing to PostgreSQL just by changing the connection string (I think).

Man, that sucked. I allocated an hour for that, and it ate part of my morning and nearly all of my afternoon. But now I have a simple file uploader ready to go. Apps can pull it up in a jQuery UI dialog or whatever, passing it the subject PID when they call it. Next up: a little python script to periodically check for photos not yet approved in the table (I’ll have to ~~reroute the inevitable porn images to a different folder~~ delete the inevitable porn images), a way to complain about an image, and a web API so people can get to it. But that can wait until next week. I’m taking my ball and going home.

Fuzzy Tolerance

Crowd Sourcing House Photos

Comments