My Database Tables
So you've seen how I import and process EXIF data, and produce Google Earth KML files. Here are some tech details.
First, the two main database tables:
| {pk1_master} | One row per folder | |
| folder | text | The folder number |
| dated | text | The date the pics were taken |
| manypics | integer | How many pics are in this folder |
| location | text | The location of the session |
| lens | text | The lens used |
| iso | integer | The ISO - I rarely change midstream |
| notes | text | Eg, a lovely day, great scenery |
| rangef | text | The first picture number |
| ranget | text | The last picture number |
| keywords | TEXT | A series of one-to-many space-separated keywords |
| {photodata} | One row per picture. All data derived from the EXIF except the decimalised lat and long | |
| folder | TEXT | The folder number |
| picture | TEXT | The picture number |
| aperture | TEXT | Aperture |
| shutter | TEXT | Shutter speed |
| iso | TEXT | ISO - will match ISO in the other table |
| lens | TEXT | Ditto |
| dated | TEXT | The GPS-derived date |
| timed | TEXT | The GPS-derived time of the shutter click |
| rawlat | TEXT | Latitude in the form 51 deg 24' 8.96" N |
| rawlong | TEXT | Longitude in the form 1 deg 18' 45.63" W |
| rawalt | TEXT | Altitude as recorded in the EXIF, eg, 98.4 m Above Sea Level |
| declat | TEXT | Latitude translated to decimal, eg, 51.402475 |
| declong | TEXT | Longitude translated to decimal, eg, -1.312675 |
| decalt | TEXT | Altitude 'sanitised', eg, 98.4 |
| satellites | TEXT | Number of satellites visible when picture taken, usually around 7 to 11 |
| heading | TEXT | The direction the camera was pointing at the time the pic was taken |
| cameratemp | TEXT | The camera temperature when the picture was taken |
And a sample KML file, in my latest version:
<?xml version='1.0' encoding='UTF-8'?>
<kml xmlns='http://www.opengis.net/kml/2.2'>
<Document>
<Style id="master">
<IconStyle>
<scale>0.8</scale>
<Icon>
<href>http://maps.google.com/mapfiles/kml/paddle/purple-blank.png</href>
</Icon>
</IconStyle>
<LabelStyle>
<scale>0.7</scale>
<color>ccf0bbb1</color>
</LabelStyle>
<BalloonStyle>
<text>$[description]</text>
</BalloonStyle>
</Style>
<Placemark>
<styleUrl>#master</styleUrl>
<description><![CDATA[<span style={background-color:white}><font face=Verdana size=4><font color=maroon>05/08/2016 at 09:25:11</font><br><br>ISO 250 f/5.6 @ 1/1600<br><br><font color=green>50 mm lens</font><br><br>Facing 264.47 °</font></span>]]></description>
<name>0045-1593</name>
<Point>
<coordinates>-1.25423611111111,51.3788805555556</coordinates>
</Point>
</Placemark>
<Placemark>
<styleUrl>#master</styleUrl>
<description><![CDATA[<span style={background-color:white}><font face=Verdana size=4><font color=maroon>05/08/2016 at 09:25:45</font><br><br>ISO 250 f/5.6 @ 1/1250<br><br><font color=green>50 mm lens</font><br><br>Facing 333.86 °</font></span>]]></description>
<name>0045-1594</name>
<Point>
<coordinates>-1.25398055555556,51.3790166666667</coordinates>
</Point>
</Placemark>
</Document>
</kml>