Eyesis Panorama Database

MySQL DB Structure

Nodes Table:

ID
Name
Description
OriginalDataLongitude
OriginalDataLatitude
OriginalDataAltitude
OriginalDataHeading
OriginalDataTilt
OriginalDataRoll
Longitude
Latitude
Timestamp
TimeStampMilliseconds //  Since MySQL's own timestamp format is accurate only down to 1 second we store Milliseconds in a separate field
Altitude
Heading
Tilt
Roll
Panorama URL
Visibility3D -  list of ranges [from,to] - which nodes are visible from the current one. from, to are both relative to the current node, so merging several segments should not break visibility (not so easy in the map that is not linear path, but we'll think of something  - adding new nodes (importing KML) should not change the relative sequence of indices (kml "name" tag).

Routes Table:

ID
Name
Description

Nodes_Routes Table:

Nodes
Routes
Order

PHP Methods

Implemented

Array GetNodeData (int ID)

Returns all database fields of a specific Node ID as array

Array GetRouteData (int ID)

Returns all database fields of a specific Route ID as array

AddNode (Array Data)

Save a new node to the DB supplying all database field

AddRoute (Array Data)

Save a new node to the DB supplying all database field

GetNodeCount(RouteID = null)

Returns the number of nodes stored in the DB, if you supply a RouteID you can get the number of nodes associated with a specific route.

void AddNodeToRoute (int NodeID, int RouteID)

Adds one Node to a Route. Both have to exist already.

void ImportKML (String KMLfile, int RouteID)

Works just like AddNode but can import a high number of nodes with a single function - read from a KML file, if you supply a RouteID all new Nodes will automatically be added to an existing route.

Float GetNodeDistance (int Node1ID, int Node1ID)

Returns the distance between 2 nodes in metres (for math see notes below)

TODO

Array GetNodesAt (Lat, Long, MaxDistance, Limit)

Find an array of nodes that are not further away from the supplied coordinates (Lat, Long) than the MaxDistance. To prevent huge number of results being return I would add a limit parameter here. If the results reach the limit count it will just stop searching and return the results found so far. Use "BETWEEN value1 AND value2" mysql queries for fast checks -> http://www.w3schools.com/sql/sql_between.asp This will not give a radial distance but a rectangular distance but should not matter for our case.

int GetNextNodeinRoute (int NodeID)

Routes are a sequence of nodes, Next means recorded later = higher timestamp

int GetPreviousNodeinRoute (int NodeID)

Routes are a sequence of nodes, Previous means recorded earlier = lower timestamp

Glossary

Node One full 360 degree panorama with metadata.

Route Sequence of multiple panoramas

Tile A panorama image is split up into multiple tiles for performance reasons.

Process Definitions

Upload of panorama images

1. upload all images to an "upload" directory via FTP/SSH/etc.
2. you start the import script by providing a KML
3. the PHP script moves one image after the other to a different folder with NodeID as folder name for example and adds it to the DB
4. the PHP script deletes the KML or does not save it at all in the first place

Notes

function distance ($lat1,$long1,$lat2,$long2) {
 $earthRadius=6378100; //meters
 $dlat= $lat2- $lat1;
 $dlong=$long2-$long1;
 $lat=($lat1+$lat2)/2;
 $dlong*= cos(deg2rad($lat));
 return pi()*$earthRadius/180* sqrt($dlat*$dlat+$dlong*$dlong);
}

KML file format example

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.2">
<Document>
<PhotoOverlay> 
	<name>0</name>
	<shape>rectangle</shape>
	<TimeStamp>
		<when>2011-04-22T20:55:09.926681Z</when>
	</TimeStamp>
	<Camera>
		<longitude>-110.80748065628902</longitude>
		<latitude>38.59026617490507</latitude>
		<altitude>1536</altitude>
		<heading>162.60471534016946</heading>
		<tilt>71.2006112797243</tilt>
		<roll>14.082961141415383</roll>
	</Camera>
	<Icon>
		<href>http://community.elphel.com/files/eyesis/webgl-pano/3/panos_lwhc/result_1303527309_926681-000001.jpeg</href>
	</Icon>
	<ExtendedData>
		<OriginalData>
			<longitude>-110.817908</longitude>
			<latitude>38.58143</latitude>
			<altitude>1516.2</altitude>
			<heading>0</heading>
			<tilt>90</tilt>
			<roll>0</roll>
		</OriginalData>
               <Visibility3d>	
                        <v3Range><to>15</to></v3Range> // (no "from") means "from -infinity to +35" - this is not a distance but means from all nodes before until +15 nodes in the sequence
                        <v3Range><from>21</from><to>21</to></v3Range>
                        <v3Range><from>24</from><to>25</to></v3Range>
                        <v3Range><from>27</from><to>41</to></v3Range>
               </Visibility3d>
       </ExtendedData>
       <description>Start</description>
       <visibility>1</visibility>
</PhotoOverlay>
<PhotoOverlay>
...
</PhotoOverlay>
...
</Document>
</kml>