#!/usr/bin/perl use Image::Magick; use File::Basename; #use warnings; #use strict; use utf8; no utf8; use Time::Local; use Time::Piece; use DBI; my $db_host = 'localhost'; my $db_user = 'robert'; my $db_pass = 'wqlfl3'; my $db_name = 'train_db'; my $db = "dbi:Pg:dbname=${db_name};host=${db_host}"; my $dbh = DBI->connect($db, $db_user, $db_pass,{ RaiseError => 1, AutoCommit => 0 }) || die "Error connecting to the database: $DBI::errstr\n"; print "This loop finds all pictures given in STDIN, rescales them and sorts them into\n"; print "a yyyy/mm/dd structure below the current directory.\n"; while (<>) { my $fn=$_; chop $fn; print "Processing $fn "; my $image = Image::Magick->new(); $image->Read($fn); my $f = basename($fn); $f =~ s/[A-Za-z_\.]//g; my $t = $image->Get('format', '%[EXIF:DateTime]'); # two arguments $t =~ s/://g; $t =~ s/ /_/g; $dy = substr($t,0,4); $dm = substr($t,4,2); $dd = substr($t,6,2); my $m = uc(substr($image->Get('format', '%[EXIF:Model]'),0,1)); my $h = $image->Get('format', '%[EXIF:PixelYDimension]'); # two arguments my $w = $image->Get('format', '%[EXIF:PixelXDimension]'); # two arguments my $geo = "1280"; if ($h>$w) { print "Portrait: Rescaling h to 1920 : -resize x1920 \n"; $geo = "x1280"; } mkdir ("f/".$dy); mkdir ("f/".$dy."/".$dm); mkdir ("f/".$dy."/".$dm."/".$dd); $image->Write(filename => "f/".$dy."/".$dm."/".$dd."/".$t."_".$m.$f.".jpg"); mkdir ($dy); mkdir ($dy."/".$dm); mkdir ($dy."/".$dm."/".$dd); $image->Resize(geometry => $geo); $image->Write(filename => $dy."/".$dm."/".$dd."/".$t."_".$m.$f.".jpg"); $geo = "192"; if ($h>$w) { $geo = "x192"; } mkdir ("p/".$dy); mkdir ("p/".$dy."/".$dm); mkdir ("p/".$dy."/".$dm."/".$dd); $image->Resize(geometry => $geo); $image->Write(filename => "p/".$dy."/".$dm."/".$dd."/".$t."_".$m.$f.".jpg"); # Now database file name format needed $f = $t."_".$m.$f.".jpg"; #print " - unique filename: $f ...\n"; print "\n"; #print "Querying db for $f ...\n"; my @rows = @{$dbh -> selectall_arrayref("SELECT * FROM public.pictures WHERE picture_id = '$f' ORDER BY picture_id ASC LIMIT 3;")}; my $res = 0+@rows; if ($res>0) { print "\033[33;1;1m$f already exists in db, will not add it\033[0m "; my $r = @rows[0]; print "- id: $r->[0] \n"; #print "\"picture_id\": $r->[1], "; #print "time: ",$r->[2]; #print "location: ",$r->[3]; #my $exif_aperture = $image->Get('format', '%[EXIF:ApertureValue]'); #print "RAW: $exif_aperture\n"; } else { print "\033[32;1;1mImporting $f into database.\033[0m\n"; my $t = $image->Get('format', '%[EXIF:DateTime]'); # two arguments $t =~ s/://g; $t =~ s/ /_/g; $dy = substr($t,0,4); $dm = substr($t,4,2); $dd = substr($t,6,2); print "\033[2mFile location: ".$dy."/".$dm."/".$dd."/$f\n"; print "File basename: $f\n"; my $exif_datetime = $image->Get('format', '%[EXIF:DateTime]'); my $exif_offset = $image->Get('format', '%[EXIF:OffsetTime]'); my $exif_aperture = $image->Get('format', '%[EXIF:ApertureValue]'); print "RAW: $exif_aperture\n"; if ($exif_aperture) { @aper = split(/\//,$exif_aperture); $num_aper = int(@aper[0])/int(@aper[1]); } else { $num_aper = "NULL"; } my $exif_exptime = $image->Get('format', '%[EXIF:ExposureTime]'); if ($exif_exptime) { @expt = split(/\//,$exif_exptime); $num_expt = int(@expt[1])/int(@expt[0]); } else { $num_expt = "NULL"; } my $exif_flen = $image->Get('format', '%[EXIF:FocalLengthIn35mm]'); $exif_flen = $image->Get('format', '%[EXIF:FocalLength]') unless ($exif_flen); if ($exif_flen) { @flen = split(/\//,$exif_flen); $num_flen = int(@flen[0])/int(@flen[1]); } else { $num_flen = "NULL"; } my $exif_cammodel = $image->Get('format', '%[EXIF:Model]'); my $exif_gpsalt = $image->Get('format', '%[EXIF:GPSAltitude]'); if ($exif_gpsalt) { @gpsalt = split(/\//,$exif_gpsalt); $num_gpsalt = int(@gpsalt[0])/int(@gpsalt[1]); } else { $num_gpsalt = "NULL"; } my $exif_gpslat = $image->Get('format', '%[EXIF:GPSLatitude]'); if ($exif_gpslat) { $exif_gpslat =~ s/\//,/g; $exif_gpslat =~ s/ //g; @gpslat = split(/,/,$exif_gpslat); $num_gpslat = int(@gpslat[0])/int(@gpslat[1]) + int(@gpslat[2])/int(@gpslat[3])/60. + int(@gpslat[4])/int(@gpslat[5])/3600.; $num_gpslat = -1.*$num_gpslat if ($image->Get('format', '%[EXIF:GPSLatitudeRef]') eq "S"); } else { $num_gpslat = "NULL"; } my $exif_gpslon = $image->Get('format', '%[EXIF:GPSLongitude]'); if ($exif_gpslon) { $exif_gpslon =~ s/\//,/g; $exif_gpslon =~ s/ //g; @gpslon = split(/,/,$exif_gpslon); $num_gpslon = int(@gpslon[0])/int(@gpslon[1]) + int(@gpslon[2])/int(@gpslon[3])/60. + int(@gpslon[4])/int(@gpslon[5])/3600.; $num_gpslon = -1.*$num_gpslon if ($image->Get('format', '%[EXIF:GPSLatitudeRef]') eq "E"); } else { $num_gpslon = "NULL"; } my $exif_subsec = $image->Get('format', '%[EXIF:SubSecTimeDigitized]'); #print "RAW: $exif_subsec\n"; my $t = $image->Get('format', '%[EXIF:DateTime]'); # two arguments $t =~ s/://g; $t =~ s/ /_/g; $dy = substr($t,0,4); $dm = substr($t,4,2); $dd = substr($t,6,2); my $exif_datetime = $image->Get('format', '%[EXIF:DateTime]'); my $exif_offset = $image->Get('format', '%[EXIF:OffsetTime]'); $exif_datetime =~ s/:/-/; $exif_datetime =~ s/:/-/; $exif_offset =~ s/:00$//; print "Time: $exif_datetime.$exif_subsec"."$exif_offset\n"; $newtimestamp = "$exif_datetime.$exif_subsec"."$exif_offset"; # print "NEW subsec time: $exif_subsec\n"; # $db_string = "UPDATE public.pictures SET time = \'$newtimestamp\' WHERE sampleid = $sid;"; print "Time: $newtimestamp\n"; print "Camera: $exif_cammodel\n"; printf "Settings: Aperture: f/%2.1f, Exposure: 1/%2.0f, Focal length: %2.2f mm\n", $num_aper, $num_expt, $num_flen; printf "GPS: %3.6f, %3.6f, %3.2f\n", $num_gpslat, $num_gpslon, $num_gpsalt; print "\033[0m"; # #INSERT INTO public.pictures (sampleid, picture_id, "time", "location", eva, lat, lon, vehicle, camera_id, aperture, exposure, focal_length, author, journey_id, user_data, rating, is_visible, alt) VALUES (DEFAULT, '19860000-02.jpg', NULL, NULL, NULL, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, NULL); # unless ($num_gpslat eq "NULL") { $num_gpslat = sprintf("%3.6f", $num_gpslat); } unless ($num_gpslon eq "NULL") { $num_gpslon = sprintf("%3.6f", $num_gpslon); } unless ($num_gpsalt eq "NULL") { $num_gpsalt = sprintf("%3.2f", $num_gpsalt); } unless ($num_aper eq "NULL") { $num_aper = sprintf("f/%.0f", $num_aper); } unless ($num_expt eq "NULL") { $num_expt = sprintf("1/%.0f", $num_expt); } $db_string = "INSERT INTO public.pictures (sampleid, picture_id, \"time\", \"location\", eva, lat, lon, vehicle, camera_id, aperture, exposure, focal_length, author, journey_id, user_data, rating, is_visible, alt) VALUES (DEFAULT, '$f', '$newtimestamp', NULL, NULL, $num_gpslat, $num_gpslon, DEFAULT, '$exif_cammodel', '$num_aper', '$num_expt', '$num_flen', 'Robert Wagner', DEFAULT, DEFAULT, DEFAULT, DEFAULT, $num_gpsalt);"; #print "$db_string\n"; my $res = $dbh->do($db_string); #print "$res\n"; $dbh->commit(); } } $dbh->disconnect(); exit 0;