Georeferenced Fotos of Switzerland

For the SVG Open 2007 conference

Keywords: Interactive SVG Maps , photos , georeferencing , database driven

Mag. Andreas Neumann
GIS Manager
City of Uster
Office of Survey, Oberlandstr. 78
8610 Uster
Zurich
Switzerland
a.neumann@carto.net

Biography

Andreas Neumann received a masters degree in Geography/Cartography from Vienna University, in 2001. In 1999 he joined the Cartographic Institute of the Swiss Federal Institute of Technology, first as a system administrator and later as a teaching and research assistant, and finally as a PhD student. At the same institute Neumann advised several thesis and student projects in the webmapping domain. He is currently working on a PhD as part of a ETH project called "Distributed Publishing of Cartographic Information on Demand", with the topic "Navigation in Space, Time and Topic". He is also working on a project to develop a geodata server for easy online extraction and download of geodata from databases. From 2001 to 2003 he worked as a GIS specialist at a Zurich based geology consulting company. Neumann was one of the initiators of the SVG.Open 2002 conference in Zurich, the first international SVG developers conference, co-organized by the W3C consortium. In late 2005 he joined the W3C SVG working group to support the work on the upcoming SVG 1.2 mobile and full version. From June 2007 on he is employed by the city of Uster, Kanton Zurich, where he works as a GIS manager.

Dipl-Ing. Daniel Meyer
Traffic Planner
Ernst Winkler + Partner AG
Rikonerstrasse 4
8307 Effretikon
Zurich
Switzerland
daniel.n.meyer@gmail.com

Biography

Daniel Meyer has received a masters degree in Geomatics Engineering at ETH Zurich. He studied from 2001 to 2006 and specialized in geoinformatics and planning. Within his diploma course he wrote two seminar papers. The paper written for the seminar on planning dealt with a functional redesign of a village center. The other paper dealt with the subject of a web-application that presents georeferenced photos of Switzerland. Meyer's diploma thesis was about developing a topographic information system for Switzerland. Currently he is employed as a traffic planner at Ernst Winkler + Partner in Effretikon, Kanton Zurich, Switzerland.


Abstract


There is an increasing number of digital photos produced which can be attached to geographic coordinates. High-end professional cameras as well as smartphones are already equipped with GPS receivers. In the near future it is foreseeable that also cameras in the medium price range will offer the possibility to record positional data. The EXIF standard (Exchangeable Image File Format) already includes tags for the camera and viewpoint locations. Together with the camera's focal length and sensor size (recorded automatically) one can display the camera's position and angle of view upon a topographic base map. This paper presents a workflow and prototype for the display of georeferenced photos in Switzerland. The topographic base map is provided by Swisstopo (Swiss Federal Office of Topography) and displayed in raster format. The current version allows the user to navigate within Switzerland and display the photo locations. Photos may be filtered and searched by keywords and titles. Map extents can be selected by administrative units (cantons, districts, municipalities). The image bar shows image thumbnails filtered by the current map extent and ordered by date. When the user selects an image, the most important EXIF and IPTC metadata is displayed along with the photo position and angle of view in the main map. SVG is used to render the user interface and the map elements. The application uses the SVG widgets provided by the carto.net SVG GUI section. The application does not yet allow the upload of images, this functionality will be provided in a later stage of the project. Currently, images are imported into the database by the use of a perl batch script. The paper summarizes the results of a seminar work realized in the winter term 2005 by Daniel Meyer. He was supervised by Andreas Neumann and Peter Sykora.


Table of Contents


1. Introduction and Motivation
2. Concept
     2.1 Target audience and language
     2.2 Functionality
     2.3 Layout and Design
3. Detailed Functionality
     3.1 Display of Topographic Base Map
     3.2 Spatial Navigation and Spatial Context
     3.3 Display of Photo Standpoint and Angle of View on top of the Map
     3.4 Photo Browser
     3.5 Display of Photo in Higher Resolution in a Separate Window
     3.6 Photo Metadata and Title Display Window
     3.7 Keyword Filtering
     3.8 Geographic Search
4. System Architecture
5. Database Schema
6. Photo Metadata Handling
     6.1 Standard EXIF Tags
          6.1.1 Calculating Focal Length (35mm equivalence) and Angle of View
     6.2 GPS EXIF Tags
     6.3 IPTC Tags
7. Photo Import
8. Display of Photo Locations in the Map
9. Conclusion and Future Developments
     9.1 Conclusion
     9.2 Possible Further Development
Appendix 1. Sourcecode of the Perl Script Importing Photos into the System
Appendix 2. Sourcecode of the ECMAScript function "drawSelectedPhotoDetail()" that generates the pie slice for the viewing angle
Acknowledgements
Bibliography

1. Introduction and Motivation

With the increasingly cheap and easy access to GPS devices, the advent of online image databases (e.g. flickr) as well as online mapping services (Google Maps, Yahoo Maps, Microsoft Virtual Earth or many local online mapping services) people want to tag and georeference their photos, display them on detailed maps and share them with their friends and the rest of the world. Georeferenced photos allow people to visit places virtually during the planning stage of a travel, help cities or regions to promote their tourist spots or landscape features and allow media and news services to look for geo- and timereferenced photo material.

While multiple online offers exist for the display of georeferenced images, many of these online mapping applications for georeferenced photos lack some useful features or only offer photos with limited quality and metadata information. The presented application (Georeferenced Photos of Switzerland) distinguishes itself from alternative offers by providing distinct features:

2. Concept

The goal and main functionality of the application "Georeferenced Photos of Switzerland" is summarized in the abstract and introduction of the paper.

2.1 Target audience and language

The application targets anyone who has an interest in georeferenced images of Switzerland: people interested in cities or regions or media and individuals looking for georeferenced photos of places or events at a given time. The application uses the english language for the user interface in order to reach a broad audience while keeping the development time short. Additional languages can be added later without problems. The application does not require special knowledge in GIS or database management.

2.2 Functionality

If the user moves his mouse cursor over a photo thumbnail in the photo browser window, the location of the photo view point is highlighted in the map, using a cross-hair cursor, and the photo title is displayed in the photo title and metadata window. Once a photo is selected, the photo browser displays only the thumbnail of the selected photo along with additional metadata. The user has the option to display a higher resolution version of the photo in a separate window. To maintain the photographer's copyrights, the photos aren't displayed in the original resolution, but large enough to get a good enough impression of the photo's quality.

The following list summarizes the current functionality of the system:

2.3 Layout and Design

Figure 1 shows the general layout of the application. The appliation is based on several windows, most of them non-movable. The main window displays the map. This window takes up the most room. The photo browser is located at the bottom of the map. This wide, but low window, acts like a film strip. It allows the user to browse through the available photos, filtered by the selected current map extent, using the arrow buttons at both window sides. The right margin of the user interface is subdivided into three smaller windows. The top right window hosts the navigation tools, the middle right window displays the photo title and the more important metadata. The bottom right window allows to filter the photos by keywords or search for administrative units or geographic names. The whole GUI scales with the browser window size, but the width/height ratio stays constant. If the browser window is very wide or very tall, white margins appear around the user interface. A future version might add a more adaptive GUI which actually maximizes screen estate by adapting the windows individually. All the widgets used within the GUI are implemented in SVG and ECMAScript and are used from the carto.net SVG GUI project [Neumann20052006GUI].

figure_1_layout.svg

Figure 1: Layout of the Geophoto Switzerland Application

The color concept uses discreet colors (dimgray) for the user interface (GUI elements) and reserves bold colors for the map topic (darkred). In order to make the topic stand out even more, the user can dim the map using an opacity slider. We used the sans-serif font "Cisalpin", a font specifically designed for cartographic applications by Felix Arnold [Linotype2004].

3. Detailed Functionality

3.1 Display of Topographic Base Map

The application allows browsing through the national map series of Switzerland, including the following map scales:

The system automatically selects the map scale, based on threshold values, defined in the UMN mapserver map file configuration. The maps are displayed in raster format and served in the jpeg or png format, depending on speed and quality. The display of the jpeg format is faster, with inferior quality, the display of the png format is slower, with superior quality. The default is delivery in the jpeg format. In order to improve the saliency of the map topic, the photo locations, the map may be dimmed with an opacity slider.

3.2 Spatial Navigation and Spatial Context

The spatial navigation tools enable an easy navigation within the spatial dimension. The ECMAScripts available at [Neumann20052006Nav] are used for that purpose. Plus/Minus buttons allow discrete zoomin/zoomout steps. A zoom slider allows continuous zoomin/zoomout, a zoom rectangle allows to select a rectangular extent for zoomin, taking the width/height ratio constraints into account. Panning can be handled with the pan-hand (Click and Drag) or recenter tool (Click for new map center). A "full view" button allows the return to a full view of Switzerland. The arrow buttons allow the user to go back and forth in the map extent history. Each zoom or pan adds a new map extent to the extent history. Within the photo browser (explained later), an additional navigation button allows to zoom to the map extent that fully contains the selected photo's camera position and angle of view.

The linked small reference map adds spatial context to the current map extent. The current position and size of the map extent is displayed with a blue, transparent rectangle. After passing a map width treshold value, an additional red cross indicates the current position of the map extent, as the rectangle is too small to be easily detected at this map scale. Harrower and Sheesley [HarrowerSheesley2005] attest the linked reference map and smart scrollbars superior efficiency, since both allow navigation, but provide spatial context as well. Additional spatial context is added with the adaptive scalebar Figure 3. The scalebar varies in width between a minimal and maximal allowed width and ensures that the tick mark labels always show round, meaningful, values. An absolute scale value isn't displayed, since it is impossible to tell how big and with which resolution the map is finally displayed at the target screen. While moving the mouse cursor in the main map, coordinates are displayed in the Swiss National Grid (Schweizer Landeskoordinaten) and in Geographic Coordinates (WGS84). The map data is stored in the Swiss National Grid, the transformation to geographic coordinates happens on the fly, by using a Javascript function provided by Norbert Marwan [Marwan2002]. Figure 2 shows the GUI for the spatial navigation tools, the linked reference map and the coordinate display.

figure_2_spatial_navigation_tools.png

Figure 2: Spatial Navigation Tools, Reference Map and Coordinate Display

3.3 Display of Photo Standpoint and Angle of View on top of the Map

On top of the interactive map, icons display the available photo standpoints, symbolized with small red circles. After passing a certain map scale threshold, a camera symbol replaces the circle symbol, indicating the photo orientation. After clicking on a camera icon, the photo's angle of view is displayed transparently upon the base map. The angle of view is represented by a pie-slice geometry Figure 3. If the current map extent does not fully cover the photo's angle of view, the map automatically adjusts the map extent.

figure_3_display_of_photo_standpoints_and_view_angle.png

Figure 3: Display of the Photo Standpoint with Camera Icons and Angle of View. Data Source: © Swisstopo 2006, Pixelmap 25.000.

3.4 Photo Browser

At the bottom of the application, a photo browser window displays photo thumbnails filtered by the current map extent and ordered by the date and time the photo was taken Figure 4. Newer photos are listed first. Arrows at both sides allow navigating back and forth in time and browsing in the available photo list. Once the user clicks on a photo thumbnail, the map displays the necessary map extent and angle of view of the photo, and a separate detail view shows the following additional metadata next to the photo thumbnail Figure 5:

Three buttons allow opening the photo in higher resolution in a separate window, to zoom to the map extent that fully contains the photo's camera position and angle of view, and to go back to the photo browser view Figure 5.

figure_4_photobrowser.png

Figure 4: The Photo Browser Window

figure_5_photobrowser_detailed_view.png

Figure 5: The Photo Browser Window - Detailed View of a Selected Photo

3.5 Display of Photo in Higher Resolution in a Separate Window

From the detailed view in the photo browser window, a button allows to open a higher resolution version of the selected photo in a separate, movable window Figure 6. The photo title is displayed in the photo window statusbar. The window doesn't display the photo in full resolution, but detailed enough to judge the quality of the photo. It wouldn't be sufficient, though, for reproduction in print or other media.

figure_6_movable_photo_window.png

Figure 6: The Movable Photo Window - A Higher Resolution View of the Selected Photo

3.6 Photo Metadata and Title Display Window

When the map user moves the mouse cursor over a photo icon or after clicking on a specific photo thumbnail in the photo browser window, associated metadata is displayed in the photo metadata window Figure 7. The following metadata is displayed:

figure_7_metadata_window.png

Figure 7: The Photo Metadata and Title Display Window

3.7 Keyword Filtering

Each photo available in the database is tagged with keywords. The user may filter the photos displayed in the map and the photo browser, by activating checkboxes next to the keywords, available in the keyword filter tab Figure 8. The selected keywords can be combined with "logical and" or "logical or". Figure 8 shows the keywords available at the time of writing. Note that the keyword list may be extended in a later version of the application.

figure_8_keyword_filtering.png

Figure 8: The Keyword Filtering Tab within the Filter and Search Window

3.8 Geographic Search

The geographic search tab allows the location of kantons, districts and municipalities Figure 9. Every time a kanton is selected, the district and municipality selection lists are filtered to include only the units available in the selected kanton. The same applies if a district is selected: The system filters the choice of municipalities available in the selected district. Every time an administrative unit is selected, the map zooms to the bounding box of the selected unit, and the polygon representing the selected unit is highlighted in the map.

figure_9_administrative_units_search.png

Figure 9: The Geographic Search Tab within the Filter and Search Window

4. System Architecture

Figure 10 shows a general overview of the system architecture. On the server, a spatial database stores geometry (administrative units, photo positions), photo metadata, keywords and user information. Apache is used as a webserver. The module "mod_deflate" [Apache2005] allows to compress textstreams (such as SVG output from the database or photo metadata) on the fly prior to sending them to the webbrowser. PHP acts as a link to allow communication between the webserver (client) and the PostgreSQL database. It receives the network requests, builds SQL query strings and sends the result back as XML fragments or in JSON format. Network requests are triggered by the getURL() function or XMLHttpRequests. A wrapper function [Neumann2006Network] hides the differences and allows the integration of future, currently unknown, request methods.

Raster data is served by the UMN mapserver [UMNMapserver2006]. Requests are made through the OGC compatible WMS service [OGC2006WMS]. WMS is a serverside application and a well-defined API to generate and access mapping data across the network. After each zoom and pan a new map request is generated. Access parameters, such as map extent, projection, styling, data format, width and height, etc. are encoded in an IRI request. The resulting map is typically delivered as raster data (jpeg, png) or vector data (SVG). [Neumann20052006WMS] explains how to integrate WMS queries in SVG and how to set up the UMN mapserver to efficiently deliver rasterdata.

figure_10_architecture.svg

Figure 10: System Architecture

The clientside GUI and map configuration is defined in an XML file. The application parses the configuration file after loading the initial SVG file and builds the widgets, using the SVG GUI widgets available at [Neumann20052006GUI]. This allows a quick and flexible adaption of the GUI layout and appearance, when necessary. All clientside interactivity is implemented using a combination of events, ECMAScript, DOM, SVG and network requests.

5. Database Schema

The PostgreSQL database with the Postgis extension was chosen for several reasons. It is known as a reliable and reasonably fast database solution, it offers SVG output support, spatial queries, reprojection and is easy to maintain. Additionally, it compiles and installs on any server platform. For the geophoto Switzerland application we use several tables. Figure 11 illustrates the database schema. The central table is the photos table, containing photo title, metadata, coordinates, etc. The photo id links to the keywords_photos table containing all the ids to keywords. The keywords table contains the ids and keywords and is linked to keyword_aliases, to allow several aliases to match to the same keyword. Each photo is also linked to a users table, which is linked to the table user_aliases. The tables kantone, bezirke and gemeinden contain the geometry and names of the swiss administrative units. These tables help automatically detect administrative units, such as country, province, sub-location and city. This matching is done through a point-in-polygon query (Contains()), based on the standpoint_ch1903 coordinates. The two tables spatial_ref_sys and geometry_columns are Postgis helper tables containing information on the spatial tables and spatial reference systems, to be used for data management and reprojection purposes. The photo standpoint and targetpoint coordinates are stored in the swiss national grid (Landeskoordinaten) and geographic coordinates (WGS84). This allows queries on both coordinate system and helps to re-use the photos for a later use in a continental or global scope.

figure_11_database_schema.png

Figure 11: Database Schema

6. Photo Metadata Handling

This application currently does not allow editing and updating of image metadata. It justs imports metadata already present in the photos. A number of excellent tools and applications exist which allow to edit and update image metadata. Probably the most flexible tool is ExifTool by Phil Harvey [Harvey20032006], a perl based commandline tool that allows to read and write almost any tag and fileformat that exists. The fact that it is commandline based and available for all platforms, makes it an excellent choice for batch processing or embedding in scripts or selfwritten software. Other software that proved valuable for reading and writing metadata, is GraphicConverter (Shareware, MacOSX), IrfanView (Freeware, Windows) and AdobeBridge (Commercial, MacOSX, Windows). To store the photo's metadata, we use the standard tags present in the EXIF standard (Exchangeable Image File Format, automatically filled in by the camera), additional GPS Exif tags filled in manually or by a batch tool (e.g. [SykoraNeumann20052006]), and finally the IPTC tags to store additional manual information about the photo, such as the title, keywords, author and copyright information, and geographic information.

6.1 Standard EXIF Tags

Digital Cameras store a wealth of information in the exiftags present in the file header of the photo. Unfortunately, when comparing different camera models during photo import into the system, it was discovered that some models don't closely follow the Exif standard [JEITA2002]. Examples of necessary tags not used in all models are the tags "FocalLengthIn35mmFormat", "FocalPlaneXResolution", "FocalPlaneResolutionUnit" and "ScaleFactor35efl", which are necessary to calculate the angle of view of an image, based on the sensor size and the focal length. Table 1 lists the standard EXIF tags used in the geophoto Switzerland application, with a short explanation and an indication whether this tag is set automatically or manually.

Tag-NameExplanationa (automatic) | m (manual)
MakeCamera Manufactorera
ModelCamera Modela
ExifImageLengthFoto height in pixela
ExifImageWidthFoto width in pixela
DateTimeOriginalDate and Time when the Photo was takena
ExposureTimeExposure Timea
FNumberAperture (f-Value)a
ISOISO valuea
FocalLengthFocal Length in mm (may include a proprietary model factor)a
FocalLengthIn35mmFormatthe standardized focal length in 35mm equivalenta/m
FocalPlaneXResolutionHorizontal Resolution of the Camera Sensora
FocalPlaneYResolutionVertical Resolution of the Camera Sensora
FocalPlaneResolutionUnitCamera Sensor Resolution unit (mm, inches)a
ScaleFactor35eflScale Factor to transform proprietary focal length to the 35mm equivalenta

Table 1: EXIF tags used in the Geophoto Switzerland Application

6.1.1 Calculating Focal Length (35mm equivalence) and Angle of View

The focal length (f) is defined as the distance between the focal point and the center of the photo plane. Zoom lenses can change the focal length. The angle of view (a) of the photo is closely linked to the focal length. The third important parameter is the size (s) of the negative (the exposed area of a photo on the filmstrip) or the CCD sensor in digital cameras. All three parameters are visualized in Figure 12

figure_12_relation_between_sensor_size_focal_length_and_view_angle.svg

Figure 12: Relation between Sensor Size, Focal Length and Angle of View

The relationship of the three parameters can be defined with the following trigonometric equation:

figure_12_a_formula_for_focal_length_sensor_size_and_view_angle.svg

Figure 13: Trigonometric Equation for the Relation between Focal Length, Sensor Size and Angle of View

The variable s (sensor size) can be interpreted differently: as diagonal, width or height of the negative or sensor size, which triggers of course different results for the calculation of the angle of view. In this implementation the width of the sensor was chosen, taking into account the orientation of the photo (portrait or landscape). Next, the focal length has to be converted, if necessary. Most camera manufactorers have different sensor sizes, which means that the specified focal length has to be converted to the 35mm equivalent. Some cameras provide the EXIF tag "FocalLengthIn35mmFormat" or "ScaleFactor35efl". Unfortunately, the latter parameter did not prove to be reliable in all cases. For those camera devices which don't provide either of these values, one can relate the sensor size to the standard negative size (24 x 36 mm) used by traditional anlogue SLR Cameras. If the size of the sensor is known, we can calculate the focal length ratio from the ratio of the sensor size to the standard size of 35mm cameras, resulting in the focal length 35mm equivalence (f35). The size (width) of the sensor is given as the value w, the proprietary focal length as f. The corresponding equation is:

figure_12_b_formula_for_calculating_35mm_focal_length_equivalence_using_sensor_size.svg

Figure 14: Equation for Calculating the 35mm Focal Length Equivalence from the Sensor Size and the Proprietary Focal Length

The size of the sensor (width w) is not provided directly. It has to be calculated from the EXIF tags "FocalPlaneXResolution" (horizontal resolution of the sensor) and "FocalPlaneResolutionUnit" (length units "mm" or "inch"). To make the situation a little more complex, camera vendors don't always provide all necessary parameters in the EXIF tags. In some cases one can investigate the missing values by reading the device specifications. One can supplement the missing values manually by adding them with ExifTool.

6.2 GPS EXIF Tags

Some devices already support the automatic recording of the photo standpoint coordinates in EXIF tags, at the time of writing ([GeoSpatialExperts2005], [Nikon2005]). However, prices for these devices are still very high. On the lower end of the price scale, many mobile phones already combine camera and GPS. These devices are already in use in some Asian countries, esp. Japan. Sony recently announced a GPS device with software for easy synchronization of digital images with GPS coordinates, using time stamps [Sony2006]. A cheaper solution for people already owning consumer GPS devices (such as Garmin or Magellan GPS) exists at [SykoraNeumann20052006]. If the location, where the picture was taken, is known, one can also manually write the GPS metadata to the image EXIF data, by using tools like ExifTool. In any case, there isn't yet an automatic method to determine the targetpoint coordinates. However, these target coordinates are very important, since the targetpoint coordinates are maybe even more important than the standpoint coordinates. After all, people usually look for pictures where a certain targetpoint is reproduced. The recording of the target point is also one of the major differences between our georeferenced photo application and most of the competing systems. Table 2 lists the GPS tags that the system utilizes:

Tag-NameExplanationa (automatic) | m (manual)
GPSLatitudeRefNorthern or Southern Hemisphere of the Photo Standpointa/m
GPSLatitudeLatitude of the Photo Standpoint (WGS84)a/m
GPSLongitudeRefEastern or Western Hemisphere of the Photo Standpointa/m
GPSLongitudeLongitude of the Photo Standpoint (WGS84)a/m
GPSDestLatitudeRefNorthern or Southern Hemisphere of the Photo Targetpointm
GPSDestLatitudeLatitude of the Photo Targetpoint (WGS84)m
GPSDestLongitudeRefEastern or Western Hemisphere of the Photo Targetpointm
GPSDestLongitudeLongitude of the Photo Targetpoint (WGS84)m

Table 2: GPS EXIF tags used in the Geophoto Switzerland Application

6.3 IPTC Tags

"IPTC metadata were employed by Adobe Systems Inc. to describe photos already in the early nineties. A subset of the IPTC "Information Interchange Model - IIM" was adopted as the well known "IPTC Headers" for Photoshop, JPEG and TIFF image files which currently describe millions of professional digital photos" [IPTC2004]. IPTC metadata is usually manually entered. Table 3 lists which metadata is used within the application:

Tag-NameExplanationa (automatic) | m (manual)
ObjectNameTitle of the Photom
KeywordsKeywords to describe the photo content Table 4, separated by semicolonsm
CityCity where the photo was taken; in our case the municipalitya/m
Sub-locationSub-location where the photo was taken; in our case the districta/m
Province-StateProvince/State where the photo was taken; in our case the Kantona/m
Country-PrimaryLocationCodeThe country where the photo was taken; in our case Switzerlanda/m
CreditCredits; in our case the photographerm
CopyrightNoticeCopyright Notem

Table 3: IPTC tags used in the Geophoto Switzerland Application

To improve interoperability of keywords, we suggest a number of pre-defined keywords with several aliases, that link to the same, specific keyword. The keyword list is defined in english, but the alias table can hold the same term in other languages. At the time of writing, 24 predefined keywords are available, listed in Table 4:

IdKeywordAliases
1springspringtimeFrühling
2summersummertimeSommer
3autumnfallHerbst
4winterwintertimeWinter
5buildingbuildingshousehousesstructurestructurescottagecottagescastlefortGebäude
6villagevillageshamletDorf
7citycitiestowntownsurbanStadt
8transportTransporttransportationroadroadsstreetstreetsalleyalleysrailroadrailroadsrailwayrailwaysbridgesquare
9forestwoodwoodstreesWald
10meadowmeadowsgrasslandhayfieldWiese
11lakelakespondpondsSee
12riverriversstreamstreamsFluss
13creekcreeksbrookbrooksBach
14valleyvalleysdaledalesvalevalesglenTal
15mountainmountainsmountpasssummitpeaksummitspeaksBerg
16hillhillsknollHügel
17viewviewsoutlooklook-outvistavistapointviewpointAussicht
18naturenaturalanimalplantvegetationflorafaunaNatur
19atmosphereambiencesunsetsunrisecloudsStimmungWolken
20history and culturehistoryculturepastheritageKulturGeschichte
21snow and icesnowiceglacierGletscherSchneeEisfrozen
22parkParkgardenmunicipal parkGarten
23religionchurchchurcheschapelmonasteryconventcathedralminaretReligiontemplecemetarygraveyardsynagogemosqueshrine
24canyoncanyonsgorgeravineSchlucht

Table 4: IPTC keywords and aliases used in the Geophoto Switzerland Application

Of course, such a keyword list isn't exhaustive. It will be extended over time, when the application is published. If someone has a good source for standardized and comprehensive keyword lists, please let the authors know. To avoid different notations of the geographic keywords (municipality, district, kanton) the entries are replaced with the standardized list of names available in the spatial database. The assignment happens automatically through point in polygon queries, based on the photo standpoints, utilizing Postgis "Contains()" function.

7. Photo Import

Import of photos is done with a self-written perlscript and the ExifTool perl module. For a graphical view of the workflow see Figure 15. The perl script opens a connection to the database, opens the specified directory and reads in all the jpeg photos. Next, it reads all the requested metadata (EXIF and IPTC tags) of the photo currently processed. Some tags have to be further processed, e.g. the DateTime tag, prior to being ready for database insertion. The coordinates, specified in geographic coordinates (WGS84), are translated to the Swiss National Grid allowing the point in polygon query. Using the resulting administrative unit names, the script checks whether a directory is already available for the detected kanton, district and municipality. It creates new directories, if necessary. A new filename is created, using the date and time when the photo was taken, as well as the photo title. In the next step, the script resamples the image to create thumbnails and a medium resolution for display of the photos in the web application. The full resolution is never delivered. If necessary, the script calculates the 35mm equivalent for the field of view tag. The script reads the "Credit" tag and tries to match a photographer already present in the user or user alias table. In the next step, all EXIF and IPTC tags necessary for the system are inserted into the database. Finally, the system processes the keywords. It tries to match existing keywords or alias and ensures that no keyword is stored twice. As a status report, a message is written to the console after processing each photo. The process is repeated for each photo to be imported. The sourcecode of the import script is published in Appendix 1.

figure_13_workflow_of_photo_import.svg

Figure 15: Workflow of the Perl Script importing the Photos

8. Display of Photo Locations in the Map

After each zoom and pan a network request is generated to get the photo information or the current map extent out of the spatial database. This filtering could theoretically be done clientside, but serverside SQL queries are easier to implement and more efficient. When changing the design later to handle thousands of images, serverside aggregation and query capabilities are a requirement. On the server, a php script formats the results as an XML fragment which is parsed on the client. The result is stored in an ECMAScript array. After the array was created, photo symbols are created in the main map. For a map width greater than 10km circle symbols are used, below that threshold small camera symbols are used that already indicate the orientation the photo was taken.

After the user clicks on a photo symbol or a thumbnail in the photo browser, a pie slice is generated indicating the angle of view of the selected photo Figure 3. This pie slice is generated using a path element with an elliptical arc. The path segment is started with a MoveTo command from the center of the pie slice, followed by a LineTo command generating the left side of the slice (clockwise order), followed by an elliptical arc command and a ClosePath command. The details of the elliptical arc command can be read at [FerraioloFujisawaJackson2003]. The start and end coordinates of the elliptical arc is calculated from the distance and view direction of the photo metadata, using the cosine and sine function. The sourcecode of the function "drawSelectedPhotoDetail()", which draws the pieslice of the selected photo, is published in Appendix 2

9. Conclusion and Future Developments

9.1 Conclusion

The system offers an easy to use, web based interface to a database of georeferenced photos. The location where the photo was taken, as well as the angle of view, are displayed on top of a high quality topographic base map, produced by Swisstopo, the Swiss Federal Office of Topography. The user can display metadata for every photo selected. Sophisticated spatial navigation functions allow easy access to any part of Switzerland. The name search for kantons, districts and communities provides quick access to any administrative unit within Switzerland. Filtering by keywords allows to quickly select photos related to a certain topic. The system is implemented with open source components: PostgreSQL, Postgis, UMN mapserver, PHP, ExifTool and perl. The system proves to be reliable and easy to maintain. While the usability and reliability has yet to be further tested, the implemented functionality already goes beyond the offerings of many commercial alternatives.

As the system was developed as part of a semester work (4 months duration, 1.5 days a week work) there wasn't time to develop additional features or to do extensive testing. User testing would almost likely uncover weaknesses in the user interface and would provide hints for improvements. One weakness in the current design is that the system would only work up to a couple thousand images because a symbol is drawn for each single photo. A future version should aggregate photo locations that are close to each other and display an alternative symbol for groups of photos. These groups would be dissolved if the user zooms further into the map. This approach was successfully used in a different project, a report information system for a geology company, where around 20000 report locations where displayed utilizing a spatial database and SVG. Another problem appears where more than one photo was taken from the same location. Currently, multiple camera symbols are drawn on top of each other, which makes it harder to read and harder to pick, using the mouse cursor. The symbol on the top is easy to pick, but the symbols below can only be reached at small parts of the symbol. An alternative symbol with small angles of view should be provided to handle this case. Certain improvements could also be made regarding the spatial navigation. The linked reference map as well as the zoom slider are useless in its current state when the user zoomed in very far other than for getting a spatial context on an overview level. An alternative navigation map should be provided in a separate tab to handle these cases, where the linked reference map zooms and pans with the main map. The zoomslider would also adopt to this alternative reference map.

9.2 Possible Further Development

Next to fixing the weaknesses described in the conclusion, further interesting features could or should be implemented. The following list provides a few ideas:

Acknowledgements

We'd like to thank Peter Sykora, André Winter, Andreas Wipf and Urs Mattle for assisting the project by providing ideas, hints and photos for the prototype. The PostgreSQL/Postgis and PHP developers enabled our serverside framework, which proved to be reliable and easy to maintain. Thomas DeWeese (Apache Batik) and Erik Dahlström (Opera) helped to improve the application, tested it with their SVG UA implementations and fixed bugs where necessary. Finally, we'd like to thank Phil Harvey for his excellent ExifTool to manage image metadata.

Bibliography

[Apache2005]
Apache Foundation (2005): Apache Module mod_deflate, http://httpd.apache.org/docs/2.0/mod/mod_deflate.html
[FerraioloFujisawaJackson2003]
Ferraiolo, Jon, Fujisawa, Jun and Dean Jackson (2006): The elliptical arc curve commands, http://www.w3.org/TR/SVG11/paths.html#PathDataEllipticalArcCommands
[GeoSpatialExperts2005]
Geospatial Experts (2005): Ricoh Pro G3 GPS Camera, http://www.geospatialexperts.com/ricoh.html
[JEITA2002]
Japan Electronics and Information Technology Industries Association (JEITA) (2002): EXIF 2.2 specification, http://www.exif.org/Exif2-2.PDF
[HarrowerSheesley2005]
Harrower, Mark and Benjamin Sheesley (1999): Designing better map interfaces: A framework for panning and zooming, in: Transactions in GIS, Volume 9 (2)
[Harvey20032006]
Harvey, Phil (2003-2006): ExifTool by Phil Harvey, http://www.sno.phy.queensu.ca/~phil/exiftool/
[IPTC2004]
IPTC (2004): IPTC Metadata for XMP - Home of the "IPTC Core" Schema for XMP, http://www.iptc.org/IPTC4XMP/
[Linotype2004]
Linotype (2004): Cisalpin: The ideal typeface for cartography, http://www.linotype.com/2276/cisalpin.html?PHPSESSID=33409b89a2
[Marwan2002]
Marwan, N. (2002): Umrechnung von Schweizer Landeskoordinaten in das WGS84-System, http://www.agnld.uni-potsdam.de/~marwan/isaak/Trafo/trafo.html
[Neumann20052006GUI]
Neumann, A. (2005 - 2006): SVG GUI widgets, http://www.carto.net/papers/svg/gui/
[Neumann20052006Nav]
Neumann, A. (2005 - 2006): Navigation Tools for SVG Maps, http://www.carto.net/papers/svg/navigationTools/
[Neumann2006Network]
Neumann, A. (2006): getData: a wrapper object to do network requests, http://www.carto.net/papers/svg/getData/
[Neumann20052006WMS]
Neumann, A. (2005 - 2006): Integrating OGC WMS Services with SVG mapping applications, http://www.carto.net/papers/svg/ogc_wms_integration/
[Nikon2005]
Nikon Corporation (2005): NikonD2Hs Specification, http://www2.europe-nikon.com/uploads/ngb/Brochures/d2hs_4p.pdf
[OGC2006WMS]
Opengeospatial Consortium (2006): Webmap Service, http://www.opengeospatial.org/standards/wms
[Sony2006]
Sony Corporation (2006): Organize photos by ‘where’ not ‘when’ with global positioning system for Sony Digital Cameras, http://news.sel.sony.com/en/press_room/consumer/digital_imaging/release/23993.html
[SykoraNeumann20052006]
Sykora, Peter and Andreas Neumann (2005 - 2006): gpsPhoto.pl - A commandline tool to synchronize a gps (gpx) tracklog with the date/time stamps of the image exif-data, http://www.carto.net/projects/photoTools/gpsPhoto/
[UMNMapserver2006]
UMN Mapserver Community (2006): UMN Mapserver, http://mapserver.gis.umn.edu/

Appendix 1. Sourcecode of the Perl Script Importing Photos into the System

#!/usr/bin/perl
#program to import photo metadata, detect geographic locations and resample photos
#Copyright: GPL 2.1, Daniel Meyer and Andreas Neumann, 2005
#embed modules
use strict;
use File::Find;
use File::Basename;
use File::Copy;
use File::Spec;
use Math::Round;
use Getopt::Long;
use Image::ExifTool;
use Encode;
use DBI;

#define variables
my ($dir,$photodir,$origdir,$file,$imgCounter,$userid,$mySQL,$SQL_sp_wgs,$SQL_sp_ch,$SQL_tp_wgs,$SQL_tp_ch,$sth,$chcoord,$status,$newfile,$thumbfile,$thumblongside,$factor,$thumbwidth,$thumbheight,$imagelength,$ccd,$w,$pi,$error);
my ($make,$model,$dateTimeOriginal,$objectName,$city,$cityReplaced,$subLocation,$subLocationReplaced,$provinceState,$provinceStateReplaced,$credit,$copyrightNotice,$ImageWidth,$ImageHeight,$exifImageHeight,$exifImageWidth,$iso,$focalLength,$focalLength35,$fNumber,$exposureTime,$lat,$latRef,$lon,$lonRef,$destLat,$destLatRef,$destLon,$destLonRef,$captionAbstract,$imageDir,$imageDirRef,$imageDist,$imageDistRef,$focalPlaneXResolution,$focalPlaneResolutionUnit,$keywords,$keywordId,$value,$scaleFactor);
my (@data,@imgTitles,@imgFilenames,@imgWidths,@imgHeights);
my $CoordFormat = Encode::decode_utf8("%.6f");
my $dateTimeFormat = Encode::decode_utf8("%Y:%m:%d %H:%M:%S");
my $inCoorSystem = 4326;
my $outCoorSystem = 2056;
my $version = "0.1";
my $date = "2005-10-30";
my $program = $0;
$program =~ s/(\.\/)//;
my $usage = "$program (version $version, $date)\nUsage: $program --dir myphotos/dir/";

#get parameters
GetOptions("dir=s" => \$dir);
unless ($dir) {
	die "$usage you have to specify a directory containing the images (--dir)!\n";
}

#treat strings to be correctly utf8 encoded
if ($dir) {
	$dir = Encode::encode("utf8",$dir);
}
$origdir = $dir;

#connect to a PostgreSQL database.
my $db_host = '192.168.1.10';
my $db_user = 'dbuser';
my $db_pass = 'password';
my $db_name = 'dbname';
my $db = "dbi:Pg:dbname=${db_name};host=${db_host}";
my $dbh = DBI->connect($db, $db_user, $db_pass) || die "Error connecting to the database: $DBI::errstr\n";

#read directory and count image files
opendir(DIR, $dir) or die "can't open directory $dir: $!";
$imgCounter = 0;

while (defined($file = readdir(DIR))) {
	$file = File::Spec->rel2abs($dir.$file);
	#first check if it is a jpeg file
	my ($base, $dir, $ext) = fileparse($file,'\..*');
	if ($ext eq ".jpg" || $ext eq ".JPG" || $ext eq ".jpeg" || $ext eq ".JPEG") {
		print "Infos to image ".($imgCounter + 1)."($file):\n";
		my $exifTool = new Image::ExifTool;
		#set a few parameters
		$exifTool->Options(Charset => 'UTF8', CoordFormat => $CoordFormat, DateFormat => $dateTimeFormat);
		my $imgInfo = $exifTool->ImageInfo($file,"Make","Model","DateTimeOriginal","ObjectName","Credit","CopyrightNotice","ExifImageLength","ExifImageWidth","ImageWidth","ImageHeight","ISO","FocalLength","FocalLengthIn35mmFormat","FNumber","ExposureTime","GPSLatitude","GPSLatitudeRef","GPSLongitude","GPSLongitudeRef","GPSDestLatitude","GPSDestLatitudeRef","GPSDestLongitude","GPSDestLongitudeRef","FocalPlaneXResolution","FocalPlaneResolutionUnit","Keywords","ScaleFactor35efl","Caption-Abstract");
		#get individual parameters
		my @tags = $exifTool->GetRequestedTags();
		$make = Encode::encode("utf8",$exifTool->GetValue($tags[0]));
		$model = Encode::encode("utf8",$exifTool->GetValue($tags[1]));
		$dateTimeOriginal =  Encode::encode("utf8",$exifTool->GetValue($tags[2]));
		$objectName = Encode::encode("utf8",$exifTool->GetValue($tags[3]));
		$credit = Encode::encode("utf8",$exifTool->GetValue($tags[4]));
		$copyrightNotice = Encode::encode("utf8",$exifTool->GetValue($tags[5]));
		$exifImageHeight = Encode::encode("utf8",$exifTool->GetValue($tags[6]));
		$exifImageWidth = Encode::encode("utf8",$exifTool->GetValue($tags[7]));
		$ImageWidth = Encode::encode("utf8",$exifTool->GetValue($tags[8]));
		$ImageHeight = Encode::encode("utf8",$exifTool->GetValue($tags[9]));
		$iso = Encode::encode("utf8",$exifTool->GetValue($tags[10]));
		$focalLength = Encode::encode("utf8",$exifTool->GetValue($tags[11]));
		$focalLength35 = Encode::encode("utf8",$exifTool->GetValue($tags[12]));
		$fNumber = Encode::encode("utf8",$exifTool->GetValue($tags[13]));
		$exposureTime = Encode::encode("utf8",$exifTool->GetValue($tags[14]));
		$lat = Encode::encode("utf8",$exifTool->GetValue($tags[15]));
		$latRef = Encode::encode("utf8",$exifTool->GetValue($tags[16]));
		$lon = Encode::encode("utf8",$exifTool->GetValue($tags[17]));
		$lonRef = Encode::encode("utf8",$exifTool->GetValue($tags[18]));
		$destLat = Encode::encode("utf8",$exifTool->GetValue($tags[19]));
		$destLatRef = Encode::encode("utf8",$exifTool->GetValue($tags[20]));
		$destLon = Encode::encode("utf8",$exifTool->GetValue($tags[21]));
		$destLonRef = Encode::encode("utf8",$exifTool->GetValue($tags[22]));
		$focalPlaneXResolution = Encode::encode("utf8",$exifTool->GetValue($tags[23]));
		$focalPlaneResolutionUnit = Encode::encode("utf8",$exifTool->GetValue($tags[24]));
		$keywords = Encode::encode("utf8",$exifTool->GetValue($tags[25]));
		$scaleFactor = Encode::encode("utf8",$exifTool->GetValue($tags[26]));
		$captionAbstract = Encode::encode("utf8",$exifTool->GetValue($tags[27]));
		
		#check if attributes are available
		$error = 0;
		if (!$make) {$make = "NULL"; print "Make not defined\n"; $error += 1;}
		if (!$model) {$model = "NULL"; print "Model not defined\n"; $error += 1;}
		if (!$dateTimeOriginal) {$dateTimeOriginal = "NULL"; print "DateTimeOriginal not defined\n"; $error += 1;}
		if (!$objectName && !$captionAbstract) {$objectName = "NULL"; print "ObjectName and Caption-Abstract not defined\n"; $error += 1;}
		if (!$credit) {$credit = "NULL"; print "Credit not defined\n"; $error += 1;}
		if (!$copyrightNotice) {$copyrightNotice = "NULL"; print "CopyrightNotice not defined\n"; $error += 1;}
		if (!$exifImageHeight) {
			if ($ImageHeight) {
				$exifImageHeight = $ImageHeight;
			}
			else {
				$exifImageHeight = "NULL";
				print "ExifImageLength or ImageHeight not defined\n";
				$error += 1;
			}
		}
		if (!$exifImageWidth) {
			if ($ImageWidth) {
				$exifImageWidth = $ImageWidth;
			}
			else {
				$exifImageWidth = "NULL";
				print "ExifImageWidth not defined\n";
				$error += 1;
			}
		}
		if (!$iso) {$iso = "NULL"; print "ISO not defined\n"; $error += 1;}
		if (!$focalLength) {$focalLength = "NULL"; print "FocalLength not defined\n"; $error += 1;}
		if (!$fNumber) {$fNumber = "NULL"; print "FNumber not defined\n"; $error += 1;}
		if (!$exposureTime) {$exposureTime = "NULL"; print "ExposureTime not defined\n"; $error += 1;}
		if (!$keywords) {$keywords = "NULL"; print "Keywords not defined\n"; $error += 1;}
		if (!$lat || !$latRef || !$lon || !$lonRef || !$destLat || !$destLatRef || !$destLon || !$destLonRef) {print "Coordinates not correctly defined\n"; $error += 1;}
				
		#get Caption-Abstract instead of ObjectName if longer
		if (length($captionAbstract) > length($objectName)) {
			$objectName = $captionAbstract;
		}
		#mask the ' for the sql-statement
		$objectName =~ s/\'/\\\'/gi;
		
		#extract the timezone appendix from dateTimeOriginal
		my @dateTime = split(/\./,$dateTimeOriginal);
		$dateTimeOriginal = $dateTime[0];
		
		#convert coordinate string to float values		
		my @latCoords = split(/\s+/,$lat);
		$lat = $latCoords[0];
		my @lonCoords = split(/\s+/,$lon);
		$lon = $lonCoords[0];
		my @destLatCoords = split(/\s+/,$destLat);
		$destLat = $destLatCoords[0];
		my @destLonCoords = split(/\s+/,$destLon);
		$destLon = $destLonCoords[0];
		
		#get coords of standpoint in ch1903
		$mySQL = "SELECT AsText(TRANSFORM(SetSRID(GeometryFromText('POINT($lon $lat)'),$inCoorSystem),$outCoorSystem)) AS coord;";
		$sth = $dbh->prepare($mySQL);
		$sth->execute or db_err("Unable to execute query", $dbh->errstr);
		while (@data = $sth->fetchrow_array()) {
			$chcoord = $data[0];	
		}
		
		$city = "undefined";
		$cityReplaced = "undefined";
		$subLocation = "undefined";
		$subLocationReplaced = "undefined";
		$provinceState = "undefined";
		$provinceStateReplaced = "undefined";
		
		#get city, subLocation and provinceState according to coords
		$mySQL = "SELECT gemeinden.gemname,bezirke.name,kantone.name from gemeinden,bezirke,kantone WHERE Contains(gemeinden.the_geom,Transform(setSRID(GeometryFromText('$chcoord'),$outCoorSystem),21781)) AND gemeinden.bezirksnr = bezirke.id AND gemeinden.kantonsnr = kantone.kanton;";
		$sth = $dbh->prepare($mySQL);
		$sth->execute or db_err("Unable to execute query", $dbh->errstr);
		while (@data = $sth->fetchrow_array()) {
			$city = $data[0];
			$cityReplaced = replaceChars($data[0]);
			$subLocation = $data[1];
			$subLocationReplaced = replaceChars($data[1]);	
			$provinceState = $data[2];
			$provinceStateReplaced = replaceChars($data[2]);	
		}
		
		#test if directories exist or create new ones
		$photodir = $dir;
		$photodir =~ s/$origdir/photos\/Switzerland\//;
		my $kantondir = $photodir.$provinceStateReplaced."/";
		unless (-d $kantondir) { #see if directory exists
			mkdir($kantondir);
		}
		my $bezirksdir = $kantondir.$subLocationReplaced."/";
		unless (-d $bezirksdir) { #see if directory exists
			mkdir($bezirksdir);
		}
		my $gemeindedir = $bezirksdir.$cityReplaced."/";
		unless (-d $gemeindedir) { #see if directory exists
			mkdir($gemeindedir);
		}
		
		if ($city eq "undefined") {
			print "Could not match the coordinates to a municipality !!!!!!!!!!!!!!!!!!\n";
			$error += 1;
		}
		
		#copy file to specified directory
		$newfile = $gemeindedir.replaceChars($dateTimeOriginal).'_'.replaceChars($objectName).$ext;
		copy($file,$newfile) or die "could not copy $file";	
		
		#create thumbnails
		$thumblongside = 200;
		while ($thumblongside != 1000) {
			if ($exifImageWidth > $exifImageHeight) {
				$factor = $exifImageHeight / $exifImageWidth;
				$thumbwidth = $thumblongside;
				$thumbheight = nearest(1,($thumbwidth * $factor));	#round to integer
			}
			else {
				$factor = $exifImageWidth / $exifImageHeight;
				$thumbheight = $thumblongside;
				$thumbwidth = nearest(1,($thumbheight * $factor));	#round to integer
			}
			$thumbfile = $gemeindedir.replaceChars($dateTimeOriginal).'_'.replaceChars($objectName)."_thumb$thumblongside".$ext;
			$status = system("convert '".$file."' -resize ".$thumbwidth."x".$thumbheight." ".$thumbfile);
			die "could not create thumbnail $thumbfile" if $status == 1;
			$thumblongside += 400;
		}	

		#extract the mm from focalLength
		$focalLength =~ s/mm//gi;
		
		#compute focalLength35 if not available
		#problem: other necessary attributes are maybe neither available!
		#problem: EXIF-Tag ScaleFactor35efl is not always reliable!
		if ($focalLength != "NULL" && !$focalLength35) {
			if ($make == "RICOH" && $model == "Caplio R2") {$focalLength35 = nearest(1,($focalLength*6));}		#scalefactor for RICOH Caplio R2 = 6
			elsif ($make == "OLYMPUS OPTICAL CO.,LTD" && $model == "X-2,C50Z") {$focalLength35 = nearest(1,($focalLength*4.9));}		#scalefactor for Olympus C-50Z = 4.9
			elsif ($scaleFactor) {$focalLength35 = nearest(1,($focalLength*$scaleFactor));}
			else {
				$imagelength = $exifImageWidth;
				if ($exifImageHeight > $exifImageWidth) {$imagelength = $exifImageHeight;}	#get the longer side of the image according to focalPlaneXResoluiton
				if ($focalPlaneXResolution) {
					$ccd = $imagelength/$focalPlaneXResolution;		#size of ccd = pixels/resolution (resolution = pixels/size)
					if ($focalPlaneResolutionUnit eq "inches") {$ccd = $ccd*25.4;}	#convert inches to mm
					$focalLength35 = nearest(1,($focalLength*36/$ccd));		# f : ccd = f35 : 36 (36 = length of a 35mm negative)
				}
				else {
					print "Could not detect or compute FocalLengthIn35mmFormat and therefore neither Angle of View\n";
					$error += 1;
				}
			}
		}
		
		#compute angle of view
		if (!$focalLength35) {$w=0}
		else {
			if ($exifImageHeight > $exifImageWidth) {$w = 2*atan2(12,$focalLength35);}
			else {$w = 2*atan2(18,$focalLength35);}		#the values 12 and 18 are the half of the width or height of a 35mm negative (24x36mm)
			$pi = atan2(1,1)*4;
			$w = $w*180/$pi;
		}
				
		#try to match user id
		$mySQL = "SELECT id from user_aliases WHERE alias LIKE '%$credit%';";
		$sth = $dbh->prepare($mySQL);
		$sth->execute or db_err("Unable to select user alias data", $dbh->errstr);
		$userid = -99;
		while (@data = $sth->fetchrow_array()) {
			$userid = $data[0];
		}
		
		#print out camera/photo info
		print "Make: $make\nModel: $model\nDateTimeOriginal: $dateTimeOriginal\nObjectName: $objectName\nProvince-State: $provinceState\nSub-location: $subLocation\nCity: $city\nCredit: $credit\nCopyrightNotice: $copyrightNotice\nImageHeight: $exifImageHeight\nImageWidth: $exifImageWidth\nISO: $iso\nFNumber: $fNumber\nFocalLength: $focalLength\nFocalLengthIn35mmFormat: $focalLength35\nExposureTime: $exposureTime\nStandpoint: lat: $lat, lon: $lon\nTargetpoint: destLat: $destLat, destLon: $destLon\n";
		
		#define SQL-statements for coords
		$SQL_sp_wgs = qq(SetSRID(GeometryFromText('POINT($lon $lat)'),$inCoorSystem));
		$SQL_sp_ch = qq(Transform(SetSRID(GeometryFromText('POINT($lon $lat)'),$inCoorSystem),$outCoorSystem));
		$SQL_tp_wgs = qq(SetSRID(GeometryFromText('POINT($destLon $destLat)'),$inCoorSystem));
		$SQL_tp_ch = qq(Transform(SetSRID(GeometryFromText('POINT($destLon $destLat)'),$inCoorSystem),$outCoorSystem));
				
		#write exif data into db
		$mySQL = qq(INSERT INTO photos ("Make","Model","DateTimeOriginal","ObjectName","City","Sub-location","Province-State","Credit","CopyrightNotice","ExifImageLength","ExifImageWidth","ISO","FocalLength","FNumber","FocalLengthIn35mmFormat","UserID","ExposureTime","AngleOfView","standpoint_wgs84","standpoint_ch1903","targetpoint_wgs84","targetpoint_ch1903","FileName") VALUES ('$make','$model',to_timestamp('$dateTimeOriginal','YYYY:MM:DD HH24:MI:SS'),'$objectName','$city','$subLocation','$provinceState','$credit','$copyrightNotice',$exifImageHeight,$exifImageWidth,$iso,$focalLength,$fNumber,$focalLength35,$userid,'$exposureTime',$w,$SQL_sp_wgs,$SQL_sp_ch,$SQL_tp_wgs,$SQL_tp_ch,'$newfile'););
		$sth = $dbh->prepare($mySQL);
		$sth->execute or db_err("Unable to insert foto data", $dbh->errstr);
		
		#get current photo id
		$mySQL = qq(SELECT currval('create_photo_id'));
		$sth = $dbh->prepare($mySQL);
		$sth->execute or db_err("Unable to query current foto id", $dbh->errstr);
		@data = $sth->fetchrow_array();
		my $photoId = @data[0];
		
		#split keywords
		my @Keywords = split(/\s*,\s*/,$keywords);
		my @KeywordIDs;
		my $KeywordID;
		my $unique;
		print "Keywords: @Keywords\n";
		foreach $value (@Keywords) {
			$keywordId = -99;
			$unique = 1;
			$mySQL = "SELECT id FROM keyword_aliases WHERE alias = '$value';";
			$sth = $dbh->prepare($mySQL);
			$sth->execute or db_err("Unable to select keyword alias data", $dbh->errstr);
			while (@data = $sth->fetchrow_array()) {
				$keywordId = $data[0];
			}
			foreach $KeywordID (@KeywordIDs) {
				if ($keywordId == $KeywordID) {
					$unique = 0;	
				}
			}
			if ($keywordId != -99 && $unique == 1) {
				$mySQL = qq(INSERT INTO keywords_photos VALUES ($photoId,$keywordId));
				$sth = $dbh->prepare($mySQL);
				$sth->execute or db_err("Unable to input keyword id", $dbh->errstr);
			}
			push(@KeywordIDs,$keywordId);
		}
		print "All informations have been written into database. $error error(s) occured.\n";		
		print "Processing photo ".($imgCounter + 1)." completed.\n\n\n"; 
		
		$imgCounter++;
	}
}			

close(DIR);
$sth->finish;
$dbh->disconnect;

#subroutine for special characters
sub replaceChars {
	my $string = shift;
	$string =~ s/\/|\(|\)|\s+/_/g;	
	$string =~ s/ä/ae/g;	
	$string =~ s/Ä/Ae/g;	
	$string =~ s/ö/oe/g;	
	$string =~ s/Ö/Oe/g;	
	$string =~ s/ü/ue/g;	
	$string =~ s/Ü/Ue/g;
	$string =~ s/é|è|ê/e/g;
	$string =~ s/â|à/a/g;
	$string =~ s/ô/o/g;
	$string =~ s/[^a-zA-Z0-9_]//g;
	return $string;	
}		
		

Appendix 2. Sourcecode of the ECMAScript function "drawSelectedPhotoDetail()" that generates the pie slice for the viewing angle

//this function draws the angle of view to the selected photo
function drawSelectedPhotoDetail() {
	var id = myMapApp.photoSelectedId;
	var photoSymbolDetailGroup = document.getElementById("photoSymbolDetail");
	//check if we have to remove old geometry
	if (photoSymbolDetailGroup.hasChildNodes()) {
		photoSymbolDetailGroup.removeChild(photoSymbolDetailGroup.firstChild);
	}
	//only draw if the data is available
	if (myMapApp.photoData[id]) {
		//create temporary group for photo symbols
		var curPhoto = myMapApp.photoData[id].value;
		var photoSymbolDetailTempGroup = document.createElementNS(svgNS,"g");
		photoSymbolDetailTempGroup.setAttributeNS(null,"id","photoSymbolDetailTemp");
		photoSymbolDetailTempGroup.setAttributeNS(null,"pointer-events","none");
		photoSymbolDetailGroup.appendChild(photoSymbolDetailTempGroup);
			
		var viewAngle = document.createElementNS(svgNS,"path");
		viewAngle.setAttributeNS(null,"stroke","darkred");
		viewAngle.setAttributeNS(null,"stroke-width",(myMainMap.curWidth * 0.001));
		viewAngle.setAttributeNS(null,"fill","darkred");
		viewAngle.setAttributeNS(null,"fill-opacity","0.2");
		var d = "M"+curPhoto.StandPointX+","+(curPhoto.StandPointY*-1)+"L"+myMapApp.selPhotoPointX1+","+myMapApp.selPhotoPointY1+"A"+myMapApp.selPhotoDistance+","+myMapApp.selPhotoDistance+" 0 0,1 "+myMapApp.selPhotoPointX2+" "+myMapApp.selPhotoPointY2+"z";
		viewAngle.setAttributeNS(null,"d",d);
		photoSymbolDetailTempGroup.appendChild(viewAngle);
			
		var connectLine = document.createElementNS(svgNS,"line");
		connectLine.setAttributeNS(null,"stroke","darkred");
		connectLine.setAttributeNS(null,"id","connectLine");
		connectLine.setAttributeNS(null,"stroke-width",(myMainMap.curWidth * 0.002));
		connectLine.setAttributeNS(null,"x1",curPhoto.StandPointX);
		connectLine.setAttributeNS(null,"y1",curPhoto.StandPointY * -1);
		connectLine.setAttributeNS(null,"x2",curPhoto.TargetPointX);
		connectLine.setAttributeNS(null,"y2",curPhoto.TargetPointY * -1);
		photoSymbolDetailTempGroup.appendChild(connectLine);
			
		var photoSymbol = document.createElementNS(svgNS,"use");
		photoSymbol.setAttributeNS(xlinkNS,"href","#camera");
		photoSymbol.setAttributeNS(null,"x",curPhoto.StandPointX);
		photoSymbol.setAttributeNS(null,"y",curPhoto.StandPointY * -1);
		photoSymbol.setAttributeNS(null,"id","photoDetailSymbol_"+curPhoto.id);
		photoSymbol.setAttributeNS(null,"transform","rotate("+(myMapApp.selPhotoDirection*-1)+","+curPhoto.StandPointX+","+(curPhoto.StandPointY * -1)+")");
		photoSymbolDetailTempGroup.appendChild(photoSymbol);
	}
	else {
		myMapApp.photoSelectStatus = false;
		displayPhotosSmall("start",undefined);
		showInfosEmpty();
	}
}	

XHTML rendition made possible by SchemaSoft's Document Interpreter™ technology.