Eyesis Panorama Database

About

This project contains a set of PHP functions and requires a MySQL database. It is a framework for storing, accessing and altering a large set of geotagged panorama images in a database. This simplifies the process of building Streetview-like panorama viewers.

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

The following functions are already working. The return value is put in front of the function (C-like Systax, even though it does not exist in that way in PHP which this framework is written in)

Array GetNodeData (int $ID)

Returns all database fields of a specific Node ID as array

Return value

On success: Returns the Node data as array with the following fields:

$return['ID']
$return['Name']
$return['Description']
$return['OriginalDataLongitude']
$return['OriginalDataLatitude']
$return['OriginalDataAltitude']
$return['OriginalDataHeading']
$return['OriginalDataTilt']
$return['OriginalDataRoll']
$return['Longitude']
$return['Latitude']
$return['Timestamp']
$return['TimeStampMilliseconds']
$return['Altitude']
$return['Heading']
$return['Tilt']
$return['Roll']
$return['PanoramaURL']
$return['Visibility3D']

On an error:

$return['error'] = "ID not found";

Array GetRouteData (int $ID)

Returns all database fields of a specific Route ID as array

Return value

On success: Returns the Route data as array with the following fields:

$return['ID']
$return['Name']
$return['Description']

On an error:

$return['error'] = "ID not found";

AddNode (Array $Data)

Save a new Node to the DB supplying the following database fields:

$Data['Name']
$Data['Description']
$Data['OriginalDataLongitude']
$Data['OriginalDataLatitude']
$Data['OriginalDataAltitude']
$Data['OriginalDataHeading']
$Data['OriginalDataTilt']
$Data['OriginalDataRoll']
$Data['Longitude']
$Data['Latitude']
$Data['Timestamp']
$Data['TimeStampMilliseconds']
$Data['Altitude']
$Data['Heading']
$Data['Tilt']
$Data['Roll']
$Data['PanoramaURL']
$Data['Visibility3D']

Return value

On success:

Returns the ID of the newly created Node

On an error:

$return['error'] = "Entry with same Coordinates already exists";

AddRoute (Array $Data)

Save a new Route to the DB supplying the following database fields:

Return value

On success:

Returns the ID of the newly created Route

On an error:

$return['error'] = "Entry already exists";

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 ($Node1ID, $Node1ID)

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

Array GetNodesAt ($LatMin, $LatMax, $LongMin, $LongMax, $Limit)

Find an array of nodes that are in the area of the supplied coordinates (LatMin, LatMax, LongMin, LongMax). To prevent a huge number of results there is the limit parameter with a default value of 100. the results are not returned in a particular order

Array GetNodesByRoute($RouteID)

Returns all Nodes associated with a Route ordered by the routes_nodes 'order' column in a multidimensional array

void DeleteRoute($RouteID)

Delete Route and all Nodes associated with it

Array DeleteNode($NodeID)

Delete a single Node

Return value

On success:

$return['success'] = "done";

On an error:

$return['error'] = "Entry with NodeID: ".$NodeID." does not exist";

void RemoveNodeFromRoute($NodeID, $RouteID)

Removes a Node from a Route, does not delete the actual Node, just removes the relationship between Route and this Node.

void UpdateNode($Parameters[])

This function can be used to edit existing node data. The node has to exist already.

The parameter has to be an array with the following fields:

$Parameters['ID'] = 1;

The ID field is mandatory to identify which Node to edit, all the following fields are optional:

$Parameters['Name'] = "changed";
$Parameters['Description'] = "changed";
$Parameters['Longitude'] = "0";
$Parameters['Latitude'] = "0";
$Parameters['Timestamp'] = "0";
$Parameters['TimeStampMilliseconds'] = "0";
$Parameters['Altitude'] = "0";
$Parameters['Heading'] = "0";
$Parameters['Tilt'] = "0";
$Parameters['Roll'] = "0";
$Parameters['PanoramaURL'] = "changed";
$Parameters['Visibility3D'] = "changed";

UpdateNode($Parameters));

Note that fields like "OriginalDataLongitude" or "OriginalDataRoll" cannot be overwritten.

TODO

int GetNextNodeinRoute (int $NodeID)

Routes are a sequence of nodes, The order is set by an "order" field in the routes_nodes table. Next node means higher order value.

int GetPreviousNodeinRoute (int $NodeID)

Routes are a sequence of nodes, The order is set by an "order" field in the routes_nodes table. Previous node means lower order value.

void UpdateRoute($Parameters[])

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>