krsc/krsc-photo-db

Krsc-Photo-Db is a tool to import, store in db and analyse exif data from photos. Takes advantage of oracle pl/sql to make calculations on big amount of data.

v1.0.0 2020-03-30 14:04 UTC

Requires

• php: ^7.1

GPL-3.0

• Krzysztof Ruszczyński

• exif
• oracle
• gps
• gpx
• photos
• plsql

README

Tool to import, store in db and analyse exif data from photos.

Import metadata

In order to import metadata from photos, please use:

php ImportCommand.php /my/directory/with/photos sql_file.sql

Where

/my/directory/with/photos

is folder, which is recursively searched for photos and

sql_file.sql

is dynamically generated file with import. If second parameter is omitted, sql data will be displayed in output.

Oracle db

In Database/Oracle folder are sql files with database for storing photos. Please change schema name "YOURSCHEMA" to your own needs. After importing sql made via ImportCommand.php you are able to create gpx file with photo locations. To decide, which photos are taken into account, you can use two methods:

BEGIN
    photo_pkg.load_gps_data_by_filename('IMG_10282_g.JPG');
END;

Imports only selected photo. Optional second parameter decides, if previous data stays. If stays it should be 0, like in this example:

BEGIN
    photo_pkg.load_gps_data_by_filename('IMG_10282_g.JPG');
    photo_pkg.load_gps_data_by_filename('IMG_10283_g.JPG', 0);
END;

In such case both photos are loaded. To check number of loaded photos you can use pipelined function:

select * from photo_pkg.gps_table();

You can also load all photos matching specified date range (first start date, second finish date; both or one of parameters can be null):

BEGIN
    photo_pkg.load_gps_data_by_date('15/01/01', '20/08/01');
END;

After data are loaded, you can create gpx file with method:

photo_pkg.get_gpx_file_content

with obligatory CLOB parameter. It is possible to write it directly to console (with bigger set of data the only way to avoid errors, please adjust buffer size) or by out variable. Second optional parameter decides about this (by default console, if input: 0 then data is inside out variable, given as first parameter). Example using out variable:

DECLARE
    out_content CLOB;
BEGIN
    photo_pkg.load_gps_data_by_filename('IMG_10282_g.JPG');
    photo_pkg.load_gps_data_by_filename('IMG_10283_g.JPG', 0);
    photo_pkg.get_gpx_file_content(out_content, 0);
    dbms_output.put_line(out_content);
END;

Below example using console (probably bigger set of data):

DECLARE
    out_content CLOB;
BEGIN
    photo_pkg.load_gps_data_by_date('15/01/01');
    photo_pkg.get_gpx_file_content(out_content);
END;

In example above, only start date is provided. If first parameter is set to NULL, only finish date can be provided.

Other functions for working with gps data:

BEGIN
    photo_pkg.load_gps_data_by_filename('IMG_10282_g.JPG');
    dbms_output.put_line(photo_pkg.v_photos_exif_gps_data_array(1).get_altitude);
    dbms_output.put_line(photo_pkg.v_photos_exif_gps_data_array(1).get_google_maps_url);
    dbms_output.put_line(photo_pkg.v_photos_exif_gps_data_array(1).get_trackpoint);
END;

Function get_altitude returns attitude for photo (like "2096 meters above sea level"), get_google_maps_url returns url showing point on google map and get_trackpoint returns gpx element with photo coordinates.