SpatiaLite EXIF GPS

SpatiaLite 2.3.1 supporting EXIF GPS

SpatiaLite directly supports EXIF GPS. The EXchangeable Image file Format represents the de facto standard widely supported by the photographic industry; quite any digital camera implements this standard.
You can get some useful information about EXIF at:
http://en.wikipedia.org/wiki/Exchangeable_image_file_format

EXIF defines a large set of metadata encapsulating a picture in the popular JPEG format; not all JPEGs supports the EXIF metadata, but you can see any EXIF picture using a plain JPEG viewer.
The vast majority of EXIF metadata is finalized to describe the photographic settings [i.e. lens focal length, aperture, shutter speed ...].
Anyway [and this is most exciting in a GIS context], EXIF metadata can contain a GPS position as well, if the camera was connected to some GPS device when shooting.
For long time this GPS option was available on expensive high end professional digital SLRs [and this required connecting the camera to some external GPS device using a cable], but in very recent times a large number of inexpensive compact digital cameras are directly mounting a GPS chip on-board.
And that's not all: the i-Phone come first integrating both a GPS chip and a digital camera, and now an increasing number of power cell phone models are able to produce EXIF GPS pictures.

Importing EXIF pictures in a SpatiaLite DB

You can download the EXIF GPS samples required by following examples.
This sample contains an half dozen pictures I've shot in Arezzo using a Nikon P6000 digital camera [a recent model mounting a GPS chip].

exif-1

We can now create an empty DB and the load the EXIF GPS pictures simple using the ad hoc tool supported by spatialite-gui.
After performing this task we can notice that now the database contains several new tables:

the table named ExifPhoto now contains the sample pictures, stored into the DB as BLOB values.
each picture has a POINT Geometry containing its GPS position.
the ExifTags and the ExifValues now contains any EXIF metadata fetched from the sample pictures.
the ExifMetadata simply is a view joining all together for simplicity.

Alternatively, you can use the exif_loader CLI tool to import EXIF pictures into a SpatiaLite DB.

cd exif-gps-samples exif_loader -d exif.sqlite -D . exif_loader: cannot open file './.': Permission denied exif_loader: cannot open file './..': Permission denied file './DSCN0010.JPG' succesfully loaded file './DSCN0012.JPG' succesfully loaded file './DSCN0021.JPG' succesfully loaded file './DSCN0025.JPG' succesfully loaded file './DSCN0027.JPG' succesfully loaded file './DSCN0029.JPG' succesfully loaded file './DSCN0038.JPG' succesfully loaded file './DSCN0040.JPG' succesfully loaded file './DSCN0042.JPG' succesfully loaded file './exif.sqlite' doesn't seems to be a valid EXIF *** 9 EXIF photos where succesfully inserted into the DB

And we'll not quickly explain just two simple examples.
The following are the complete EXIF metadata for one single picture; a really impressive data collection, isn't it ?

PhotoId	TagId	TagName	GpsTag	ValueType	TypeName	CountValues	ValueIndex	ByteValue	StringValue	NumValue	NumValueBis	DoubleValue	HumanReadable
SELECT * FROM ExifMetadata WHERE PhotoId = 1


1	1	GPSLatitudeRef	1	2	STRING	2	0	NULL	N	NULL	NULL	NULL	NULL
1	2	GPSLatitude	1	5	RATIONAL	3	0	NULL	NULL	43	1	43.000000	NULL

1	2	GPSLatitude	1	5	RATIONAL	3	1	NULL	NULL	28	1	28.000000	NULL

1	2	GPSLatitude	1	5	RATIONAL	3	2	NULL	NULL	281400000	100000000	2.814000	NULL

1	3	GPSLongitudeRef	1	2	STRING	2	0	NULL	E	NULL	NULL	NULL	NULL

1	4	GPSLongitude	1	5	RATIONAL	3	0	NULL	NULL	11	1	11.000000	NULL

1	4	GPSLongitude	1	5	RATIONAL	3	1	NULL	NULL	53	1	53.000000	NULL

1	4	GPSLongitude	1	5	RATIONAL	3	2	NULL	NULL	645599999	100000000	6.456000	NULL

1	5	GPSAltitudeRef	1	1	BYTE	1	0	BLOB sz=1 UNKNOWN type	NULL	NULL	NULL	NULL	NULL

1	7	GPSTimeStamp	1	5	RATIONAL	3	0	NULL	NULL	14	1	14.000000	NULL

1	7	GPSTimeStamp	1	5	RATIONAL	3	1	NULL	NULL	27	1	27.000000	NULL

1	7	GPSTimeStamp	1	5	RATIONAL	3	2	NULL	NULL	724	100	7.240000	NULL

1	8	GPSSatellites	1	2	STRING	3	0	NULL	06	NULL	NULL	NULL	NULL

1	16	GPSImgDirectionRef	1	2	STRING	2	0	NULL		NULL	NULL	NULL	NULL

1	18	GPSMapDatum	1	2	STRING	10	0	NULL	WGS-84	NULL	NULL	NULL	NULL

1	29	GPSDateStamp	1	2	STRING	11	0	NULL	2008:10:23	NULL	NULL	NULL	NULL

1	270	ImageDescription	0	2	STRING	32	0	NULL		NULL	NULL	NULL	NULL

1	271	Make	0	2	STRING	6	0	NULL	NIKON	NULL	NULL	NULL	NULL

1	272	Model	0	2	STRING	14	0	NULL	COOLPIX P6000	NULL	NULL	NULL	NULL

1	274	Orientation	0	3	SHORT	1	0	NULL	NULL	1	NULL	NULL	Normal

1	282	XResolution	0	5	RATIONAL	1	0	NULL	NULL	300	1	300.000000	NULL

1	283	YResolution	0	5	RATIONAL	1	0	NULL	NULL	300	1	300.000000	NULL

1	296	ResolutionUnit	0	3	SHORT	1	0	NULL	NULL	2	NULL	NULL	Inches

1	305	Software	0	2	STRING	21	0	NULL	Nikon Transfer 1.1 W	NULL	NULL	NULL	NULL

1	306	DateTime	0	2	STRING	20	0	NULL	2008:11:01 21:15:07	NULL	NULL	NULL	NULL

1	531	YCbCrPositioning	0	3	SHORT	1	0	NULL	NULL	1	NULL	NULL	NULL

1	33434	ExposureTime	0	5	RATIONAL	1	0	NULL	NULL	4	300	0.013333	1/75 sec

1	33437	FNumber	0	5	RATIONAL	1	0	NULL	NULL	59	10	5.900000	F 5.9

1	34665	Exif IFD Pointer	0	4	LONG	1	0	NULL	NULL	268	NULL	NULL	NULL

1	34850	ExposureProgram	0	3	SHORT	1	0	NULL	NULL	2	NULL	NULL	Normal program

1	34853	GPSInfo IFD Pointer	0	4	LONG	1	0	NULL	NULL	926	NULL	NULL	NULL

1	34855	ISOSpeedRatings	0	3	SHORT	1	0	NULL	NULL	64	NULL	NULL	64 ISO

1	36864	ExifVersion	0	7	UNDEFINED	4	0	BLOB sz=4 UNKNOWN type	NULL	NULL	NULL	NULL	NULL

1	36867	DateTimeOriginal	0	2	STRING	20	0	NULL	2008:10:22 16:28:39	NULL	NULL	NULL	NULL

1	36868	DateTimeDigitized	0	2	STRING	20	0	NULL	2008:10:22 16:28:39	NULL	NULL	NULL	NULL

1	37121	ComponentsConfiguration	0	7	UNDEFINED	4	0	BLOB sz=4 UNKNOWN type	NULL	NULL	NULL	NULL	NULL

1	37380	ExposureBiasValue	0	10	SRATIONAL	1	0	NULL	NULL	0	10	0.000000	0.00 EV

1	37381	MaxApertureValue	0	5	RATIONAL	1	0	NULL	NULL	29	10	2.900000	F 2.7

1	37383	MeteringMode	0	3	SHORT	1	0	NULL	NULL	5	NULL	NULL	MultiSegment

1	37384	LightSource	0	3	SHORT	1	0	NULL	NULL	0	NULL	NULL	Unknown

1	37385	Flash	0	3	SHORT	1	0	NULL	NULL	16	NULL	NULL	No Flash

1	37386	FocalLength	0	5	RATIONAL	1	0	NULL	NULL	24	1	24.000000	24.0 mm

1	37500	MakerNote	0	7	UNDEFINED	3298	0	BLOB sz=3298 UNKNOWN type	NULL	NULL	NULL	NULL	NULL

1	37510	UserComment	0	7	UNDEFINED	126	0	BLOB sz=126 UNKNOWN type	NULL	NULL	NULL	NULL	NULL

1	40960	FlashpixVersion	0	7	UNDEFINED	4	0	BLOB sz=4 UNKNOWN type	NULL	NULL	NULL	NULL	NULL

1	40961	ColorSpace	0	3	SHORT	1	0	NULL	NULL	1	NULL	NULL	sRGB

1	40962	ExifImageWidth	0	4	LONG	1	0	NULL	NULL	640	NULL	NULL	640 pixels

1	40963	ExifImageLength	0	4	LONG	1	0	NULL	NULL	480	NULL	NULL	480 pixels

1	40965	Interoperability IFD Pointer	0	4	LONG	1	0	NULL	NULL	896	NULL	NULL	NULL

1	41728	FileSource	0	7	UNDEFINED	1	0	BLOB sz=1 UNKNOWN type	NULL	NULL	NULL	NULL	NULL

1	41729	SceneType	0	7	UNDEFINED	1	0	BLOB sz=1 UNKNOWN type	NULL	NULL	NULL	NULL	NULL

1	41985	CustomRendered	0	3	SHORT	1	0	NULL	NULL	0	NULL	NULL	NULL

1	41986	ExposureMode	0	3	SHORT	1	0	NULL	NULL	0	NULL	NULL	Auto exposure

1	41987	WhiteBalance	0	3	SHORT	1	0	NULL	NULL	0	NULL	NULL	Auto

1	41988	DigitalZoomRatio	0	5	RATIONAL	1	0	NULL	NULL	0	100	0.000000	NULL

1	41989	FocalLengthIn35mmFilm	0	3	SHORT	1	0	NULL	NULL	112	NULL	NULL	112 mm

1	41990	SceneCaptureType	0	3	SHORT	1	0	NULL	NULL	0	NULL	NULL	Standard

1	41991	GainControl	0	3	SHORT	1	0	NULL	NULL	0	NULL	NULL	None

1	41992	Contrast	0	3	SHORT	1	0	NULL	NULL	0	NULL	NULL	Normal

1	41993	Saturation	0	3	SHORT	1	0	NULL	NULL	0	NULL	NULL	Normal

1	41994	Sharpness	0	3	SHORT	1	0	NULL	NULL	0	NULL	NULL	Normal

1	41996	SubjectDistanceRange	0	3	SHORT	1	0	NULL	NULL	0	NULL	NULL	Unknown

And this is an example of the ExifPhoto table content:

PhotoId	Photo	PixelX	PixelY	CameraMake	CameraModel	DateTime (ShotDateTime)	AsText (GpsGeometry)	Srid (GpsGeometry)	GpsDirection	GpsSatellites	DateTime (GpsTimeStamp)	FromPath
SELECT PhotoId, Photo, PixelX, PixelY, CameraMake, CameraModel, DateTime(ShotDateTime), AsText(GpsGeometry), Srid(GpsGeometry), GpsDirection, GpsSatellites, DateTime(GpsTimestamp), FromPath FROM ExifPhoto


1	BLOB sz=161713 JPEG-EXIF-GPS image	640	480	NIKON	COOLPIX P6000	2008-10-22 16:28:39	POINT(11.885127 43.467448)	4326	NULL	06	2008-10-23 14:27:07	./DSCN0010.JPG
2	BLOB sz=159137 JPEG-EXIF-GPS image	640	480	NIKON	COOLPIX P6000	2008-10-22 16:29:49	POINT(11.885395 43.467157)	4326	NULL	06	2008-10-23 14:28:17	./DSCN0012.JPG
3	BLOB sz=157382 JPEG-EXIF-GPS image	640	480	NIKON	COOLPIX P6000	2008-10-22 16:38:20	POINT(11.884538 43.467082)	4326	NULL	06	2008-10-23 14:36:47	./DSCN0021.JPG
4	BLOB sz=150301 JPEG-EXIF-GPS image	640	480	NIKON	COOLPIX P6000	2008-10-22 16:43:21	POINT(11.881635 43.468365)	4326	NULL	05	2008-10-23 14:41:49	./DSCN0025.JPG
5	BLOB sz=157723 JPEG-EXIF-GPS image	640	480	NIKON	COOLPIX P6000	2008-10-22 16:44:01	POINT(11.881515 43.468442)	4326	NULL	05	2008-10-23 14:42:29	./DSCN0027.JPG
6	BLOB sz=150085 JPEG-EXIF-GPS image	640	480	NIKON	COOLPIX P6000	2008-10-22 16:46:53	POINT(11.880172 43.468243)	4326	NULL	05	2008-10-23 14:45:20	./DSCN0029.JPG
7	BLOB sz=157569 JPEG-EXIF-GPS image	640	480	NIKON	COOLPIX P6000	2008-10-22 16:52:15	POINT(11.879213 43.467255)	4326	NULL	04	2008-10-23 14:50:40	./DSCN0038.JPG
8	BLOB sz=152893 JPEG-EXIF-GPS image	640	480	NIKON	COOLPIX P6000	2008-10-22 16:55:37	POINT(11.879112 43.466012)	4326	NULL	04	2008-10-23 14:54:00	./DSCN0040.JPG
9	BLOB sz=156695 JPEG-EXIF-GPS image	640	480	NIKON	COOLPIX P6000	2008-10-22 17:00:07	POINT(11.881478 43.464455)	4326	NULL	04	2008-10-23 14:57:41	./DSCN0042.JPG

back