Coppermine photos/files in Google Earth

[Tranz]

This mod will generate an xml file so that gallery items can be located in Google Earth. I started this mod before working on the Google Maps mod and didn't do much besides generating the xml file and manually adding coordinates into the database. Since working with Maps, I've added a GUI to make it easy to enter coordinates. If you want to make things easier, too, you'll need to incorporate some aspects of the Maps mod here: http://forum.coppermine-gallery.net/index.php?topic=26571 You don't need to generate the first file if you don't want a regular Map.

This actually started out as a mod for minicms content to display its content in Google Earth. And that was based on work on generating an RSS feed for mincms.

Here's the demo: http://takethu.com/g-earth.kml

I'll separate the instructions into different posts for those of us with short attention spans.

[Tranz]

Maybe too obvious, but you'll need to install http://earth.google.com/ to check out the results. At this time it's not available on all OS's so make sure you can use it.

[Tranz]

Your webhost server will need to be able to know how to handle kml files. Otherwise, it will show a regular text file instead of opening a call to Google Earth on your computer.

In the .htaccess file, add the following lines:

Code Select Expand

AddType text/vnd.google-earth.kml+xml kml
AddType text/vnd.google-earth.kmz kmz

[Tranz]

Create a file with an extension *.kml. In it, put this code:

Code Select Expand

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.google.com/earth/kml/2.0">
<NetworkLink>
<name>Name of Your Gallery</name>
<Url>
<href>http://yourgallery.com/mapping.php</href>
</Url>
<visibility>1</visibility>
</NetworkLink>
</kml>

Make sure to edit the stuff between the name and href tags accordingly.

[Tranz]

Create a mapping.php file. You can call it whatever you want as long as it matches what's in the href tags in the previous step.

Put in this code:

Code Select Expand

<?php
/**************************************************
  This program is free software; you can redistribute it and/or modify
  it under the terms of the GNU General Public License as published by
  the Free Software Foundation; either version 2 of the License, or
  (at your option) any later version.
  *************************************************
  Coppermine version: 1.4.6
  $Revision: 1.3 $
  $Author: tranzndance $
  $Date: 2006/06/02
    
  ************************
    Mod: Coppermine files mapped in Google Earth
For use in conjunction with: Coppermine for Google Maps mod:
http://forum.coppermine-gallery.net/index.php?topic=26571.0
That mod includes changes to the infrastructure to add coordinates.

http://TakeThu.com
***************************************************/
define('IN_COPPERMINE', true);
require('include/init.inc.php');

$query = implode(' AND',$query);

if(count($FORBIDDEN_SET_DATA) > 0 ){
    $forbidden_set_string =" AND {$CONFIG['TABLE_ALBUMS']}.aid NOT IN (".implode(",", $FORBIDDEN_SET_DATA).")";
} else {
    $forbidden_set_string = '';
}

$order = "ORDER BY pic_latitude DESC ";
$query = "SELECT *, pictures.title as title, pictures.caption as content, pictures.filename as filename, ctime as modified, pictures.latitude AS pic_latitude, pictures.longitude AS pic_longitude
FROM {$CONFIG['TABLE_PICTURES']} as pictures, {$CONFIG['TABLE_ALBUMS']} 
WHERE pictures.aid = {$CONFIG['TABLE_ALBUMS']}.aid AND
ABS(pictures.latitude) + ABS(pictures.longitude) > 0 
$query $forbidden_set_string 
$order;";
$result = cpg_db_query($query);

if (!mysql_num_rows($result))
cpg_die(CRITICAL_ERROR, $lang_errors['non_exist_ap'], __FILE__, __LINE__);
$place = mysql_fetch_array($result);
mysql_data_seek($result,0); //put the pointer back to the first entry
 
header('Content-type: text/xml; charset=UTF-8', true);
header("Pragma: public"); 
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    print <<<EOT
<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.google.com/earth/kml/2.0" charset="UTF-8">
<Document>
<name>{$CONFIG['gallery_name']}</name>
<description>{$CONFIG['gallery_description']}</description>
EOT;

while ($place = mysql_fetch_array($result)) {
    $CURRENT_PIC_DATA = $place; //send a copy to get_pic_url it messes with the vars
    $place['thumb_url'] = get_pic_url($CURRENT_PIC_DATA, 'thumb');
    $place['thumb_link'] = 'displayimage.php?pos='.(-$place['pid']);
    $place['caption'] = strtr($place['caption'],$HTML_SUBST_DECODE); //used to reverse Coppermines init.inc.php gpc processing
    $RFC822 = '%a, %d %b %y %T %Z';
    $place['modified'] = localised_date($place['modified'], $RFC822);

if ($place['title'] == NULL) {
$place['title'] = $place['filename'];
}

    print <<<EOT
<Placemark>
<name>{$place['title']}</name>
<description>
<![CDATA[
{$place['content']}
<a href="{$CONFIG['ecards_more_pic_target']}{$place['thumb_link']}">[more]</a>
<br/>
<p><a href="{$CONFIG['ecards_more_pic_target']}{$place['thumb_link']}"><img src="{$CONFIG['ecards_more_pic_target']}{$place['thumb_url']}"/></a></p>
<br/>
{$place['modified']}
]]>
</description>
<Point>
<extrude>1</extrude>
<altitudeMode>relativeToGround</altitudeMode>
<coordinates>{$place['pic_longitude']},{$place['pic_latitude']}</coordinates>
</Point>
</Placemark>
EOT;
}

mysql_free_result($result);
ob_end_flush();
?>
</Document>
</kml>

2006-06-02: Fixed query to account for new coordinates fields in albums table, resulting in ambiguity and mysql error.

[Tranz]

If you can't get your server to handle the kml file, you will need to provide the URL to the mapping.php file (or whatever you named it) to people so they can manually enter it in Google Earth. Because that's what the kml file does... it opens the file from the browser or file explorer. The php file (in this case) does all the work.

[tibu]

I get an error on your demo site as well as my own. I think Google may have changed something, possibly for line 2:
<kml xmlns="http://earth.google.com/kml/2.0">

I tried changing this in both the g-earth.kml file and the mapping.php file but it still gets an error. I can't figure it out.

Error message attached.

[Tranz]

Thanks for bringing it to my attention. It turned out to be a mysql error that arose after some changes I made for the Maps mod. Sorry.

I've updated mapping.php so that it works now.

[tibu]

Thanks, works great!

[javog]

I have a problem since I installed the new version of Google Earth.

Google Earth gives me an error message, which says "Fetch of Networklink failed" and it says that my mapping.php file has missing or bad root element.

If I use your demo link everything works perfectly. 

[Tranz]

Could you post a link to your kml file?

[javog]

I don't want to make the public. I will send you the link by PM.

[Tranz]

Well, when I viewed mapping.php, it said the album didn't exist. Please make sure that you have copied the code exactly as posted above. If you have confirmed it, make sure you have files with valid coordinates values. If you still have trouble, please rename a copy of your mapping.php with .txt extension and attach to your next post.

[javog]

I've solved my problem 

Since all my albums are private, I had to replace the following part in mapping.php:

Code Select Expand

if(count($FORBIDDEN_SET_DATA) > 0 ){
    $forbidden_set_string =" AND {$CONFIG['TABLE_ALBUMS']}.aid NOT IN (".implode(",", $FORBIDDEN_SET_DATA).")";
} else {
    $forbidden_set_string = '';
}

with:

Code Select Expand

//if(count($FORBIDDEN_SET_DATA) > 0 ){
//    $forbidden_set_string =" AND {$CONFIG['TABLE_ALBUMS']}.aid NOT IN (".implode(",", $FORBIDDEN_SET_DATA).")";
//} else {
    $forbidden_set_string = '';
//}


Now, mapping.php doesn't check anymore if the album is private or public.

[PrivatePyle]

First of all, thank you so much for this mod... it's really exactly what I've been looking for...


In the code of thte exportfile for google earth, i had to make 2 modifications to make it work with my album....

first of all, i have over 300 pictures with coordinates now.... so the list in google earth became quite long... I wanted to have the links organised in folders named after my coppermine albums...

the second problem was, that i have a lot of special characters (ä,ö,ü é and so on...) in my picture descriptions....  those are not accepted in the xml file, so i have to replace them with the HTML-codes, before putting them into the file...


here is the adapted version of your mapping.php script, in case somebody is looking for something like that too...


you can see the result here: http://www.hess.li/cgi-bin/pictures/mapping4.php (sorry, but my webserver doesn't know kml-files... yet...)

Code Select Expand

<?php
/**************************************************
  This program is free software; you can redistribute it and/or modify
  it under the terms of the GNU General Public License as published by
  the Free Software Foundation; either version 2 of the License, or
  (at your option) any later version.
  *************************************************
  Coppermine version: 1.4.6
  $Revision: 1.3 $
  $Author: tranzndance $
  $Date: 2006/06/02
    
  ************************
    Mod: Coppermine files mapped in Google Earth
For use in conjunction with: Coppermine for Google Maps mod:
http://forum.coppermine-gallery.net/index.php?topic=26571.0
That mod includes changes to the infrastructure to add coordinates.

http://TakeThu.com
***************************************************/

//Replace Special Characters MOD
//fill in all the special character that need to be replaced to match the xml standard
//Array(Needle, Replace-Text "HTML", Replace-Text "XML-Tag-Title")
$ReplaceArray[] = Array ("ä","&auml;","ae");
$ReplaceArray[] = Array ("ö","&ouml;","oe");
$ReplaceArray[] = Array ("ü","&uuml;","ue");
$ReplaceArray[] = Array ("Ä","&Auml;","Ae");
$ReplaceArray[] = Array ("Ö","&Ouml;","Oe");
$ReplaceArray[] = Array ("Ü","&Uuml;","Ue");
$ReplaceArray[] = Array ("ß","&szlig;","ss");
$ReplaceArray[] = Array ("é","&eacute;","e");
$ReplaceArray[] = Array ("è","&egrave;","e");
$ReplaceArray[] = Array ("É","&Eacute;","E");
$ReplaceArray[] = Array ("È","&Egrave;","E");
$ReplaceArray[] = Array ("ê","&ecirc;","e");
$ReplaceArray[] = Array ("Ê","&Ecirc;","E");
$ReplaceArray[] = Array ("à","&agrave;","a");
$ReplaceArray[] = Array ("À","&Agrave;","A");
$ReplaceArray[] = Array ("â","&acirc;","a");
$ReplaceArray[] = Array ("Â","&Acirc;","A");
$ReplaceArray[] = Array ("ô","&ocirc;","o");
$ReplaceArray[] = Array ("Ô","&Ocirc;","O");
$ReplaceArray[] = Array ("ò","&ograve;","o");
$ReplaceArray[] = Array ("Ò","&Ograve;","O");
$ReplaceArray[] = Array ("û","&ucirc;","u");
$ReplaceArray[] = Array ("Û","&Ucirc;","U");
$ReplaceArray[] = Array ("ç","&ccedil;","c");

function uml($ReplaceString,$ReplaceType) 
    {global $ReplaceArray;
 foreach($ReplaceArray as $Replace)
        {$ReplaceString = ereg_replace($Replace[0],$Replace[$ReplaceType], $ReplaceString);}
 return $ReplaceString;
}

//Replace Special Characters MOD


define('IN_COPPERMINE', true);
require('include/init.inc.php');

$query = implode(' AND',$query);

if(count($FORBIDDEN_SET_DATA) > 0 ){
    $forbidden_set_string =" AND {$CONFIG['TABLE_ALBUMS']}.aid NOT IN (".implode(",", $FORBIDDEN_SET_DATA).")";
} else {
    $forbidden_set_string = '';
}

$order = "ORDER BY foldertitle ASC, pic_latitude DESC ";
$query = "SELECT *, pictures.title as title, pictures.caption as content, pictures.filename as filename, pictures.ctime as modified, pictures.latitude AS pic_latitude, pictures.longitude AS pic_longitude, albums.title AS foldertitle, albums.description  AS folderdesc
FROM {$CONFIG['TABLE_PICTURES']} as pictures, {$CONFIG['TABLE_ALBUMS']} as albums
WHERE pictures.aid = albums.aid AND
ABS(pictures.latitude) + ABS(pictures.longitude) > 0 
$query $forbidden_set_string 
$order;";
$result = cpg_db_query($query);

if (!mysql_num_rows($result))
cpg_die(CRITICAL_ERROR, $lang_errors['non_exist_ap'], __FILE__, __LINE__);
$place = mysql_fetch_array($result);
mysql_data_seek($result,0); //put the pointer back to the first entry
 
header('Content-type: text/xml; charset=UTF-8', true);
header("Pragma: public"); 
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    print <<<EOT
<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.google.com/earth/kml/2.0" charset="UTF-8">
<Document>
<name>{$CONFIG['gallery_name']}</name>
<description>{$CONFIG['gallery_description']}</description>
EOT;

while ($place = mysql_fetch_array($result)) {

//Replace Special Characters MOD
//Replace all special characters in the database data, that will be put in the xml stream
    $place['caption'] = uml($place['caption'],1);
    $place['content'] = uml($place['content'],1);
    $place['title'] = uml($place['title'],2);
    $place['foldertitle'] = uml($place['foldertitle'],1);
    $place['folderdesc'] = uml($place['folderdesc'],1);
    $place['modified'] = uml(localised_date($place['modified'], $RFC822),1); //localised_date will return special characters in some languages (german, french...)
//Replace Special Characters MOD
   
   
    $CURRENT_PIC_DATA = $place; //send a copy to get_pic_url it messes with the vars
    $place['thumb_url'] = get_pic_url($CURRENT_PIC_DATA, 'thumb');
    $place['thumb_link'] = 'displayimage.php?pos='.(-$place['pid']);
    $place['caption'] = strtr($place['caption'],$HTML_SUBST_DECODE); //used to reverse Coppermines init.inc.php gpc processing
    $RFC822 = '%a, %d %b %y %T %Z';
 
     
if ($place['title'] == NULL) {
$place['title'] = $place['filename'];
}

   
//FOLDER-Mod
    if($lastfolder==$place['foldertitle']) //detect change of folder title
      {$folderheader = '';
      }

     else{$folderheader = '<Folder><name>'.$place['foldertitle'].'</name><description>'.$place['folderdesc'].'</description>'; //fill in data for the header of the foldertag
  if ($lastfolder!='') //close the folder after each change of the FOLDERTITLE, but not in the first "while-run"
     {$folderheader = '</Folder>'.$folderheader;
         }    
         }

    $lastfolder=$place['foldertitle']; //remember the name of the folder, so a folderchange can be detected in the next "while run"
//Folder-Mod



    print <<<EOT
{$folderheader}
<Placemark>
<name>{$place['title']}</name>
<description>
<![CDATA[
{$place['content']}
<a href="{$CONFIG['ecards_more_pic_target']}{$place['thumb_link']}">[more]</a>
<br/>
<p><a href="{$CONFIG['ecards_more_pic_target']}{$place['thumb_link']}"><img src="{$CONFIG['ecards_more_pic_target']}{$place['thumb_url']}"/></a></p>
<br/>
{$place['modified']}
]]>
</description>
<Point>
<extrude>1</extrude>
<altitudeMode>relativeToGround</altitudeMode>
<coordinates>{$place['pic_longitude']},{$place['pic_latitude']}</coordinates>
</Point>
</Placemark>
EOT;
}


mysql_free_result($result);
ob_end_flush();

?>
</Folder>
</Document>
</kml>

[Cino]

thanks for another great mod.. got it working right away

[pocketmon]

I use PrivatePyle's version, and it did work. However, something strange happens.
Since google earth cannnot load the file, I tried to open the mapping.php in browser,
I saw "Critical error": error while process database query.
Then, I tried to log in, and after entering user/password, the browser was directed to mapping.php,
which produce correct XML output???
So, I found that after logged in, mapping.php runs correctly, but it would run into trouble without loggin in.

The error message was:

Code Select Expand


hile executing query "SELECT *, pictures.title as title, pictures.caption as content, pictures.filename as filename, pictures.ctime as modified, pictures.latitude AS pic_latitude, pictures.longitude AS pic_longitude, albums.title AS foldertitle, albums.description  AS folderdesc
FROM cpg1410n_pictures as pictures, cpg1410n_albums as albums
WHERE pictures.aid = albums.aid AND
ABS(pictures.latitude) + ABS(pictures.longitude) > 0
  AND cpg1410n_albums.aid NOT IN (3,4,5,6,7,8)
ORDER BY foldertitle ASC, pic_latitude DESC ;" on 0

mySQL error: Unknown column 'cpg1410n_albums.aid' in 'where clause'



and debug message was

Code Select Expand


USER:
------------------
Array
(
    [ID] => 2210868b41b7708f60d71fbf180691af
    [am] => 1
    [lang] => chinese_big5
    [liv] => Array
        (
            [0] => 6
            [1] => 3
            [2] => 727
            [3] => 670
            [4] => 669
        )

)

==========================
USER DATA:
------------------
Array
(
    [user_id] => 0
    [user_name] => Guest
    [groups] => Array
        (
            [0] => 3
        )

    [group_quota] => 0
    [can_rate_pictures] => 1
    [can_send_ecards] => 0
    [can_post_comments] => 0
    [can_upload_pictures] => 0
    [can_create_albums] => 0
    [pub_upl_need_approval] => 1
    [priv_upl_need_approval] => 1
    [upload_form_config] => 0
    [num_file_upload] => 5
    [num_URI_upload] => 3
    [custom_user_upload] => 0
    [disk_max] => 0
    [disk_min] => 0
    [ufc_max] => 0
    [ufc_min] => 0
    [has_admin_access] => 0
    [group_name] => Anonymous
    [can_see_all_albums] => 0
    [group_id] => 3
)

==========================
Queries:
------------------
Array
(
    [0] => SELECT extension, mime, content, player FROM cpg1410n_filetypes; (0.001s)
    [1] => select * from cpg1410n_plugins order by priority asc; (0.001s)
    [2] => delete from `coppermine`.cpg1410n_sessions where time<1166145098 and remember=0; (0.001s)
    [3] => delete from `coppermine`.cpg1410n_sessions where time<1164939098; (0.001s)
    [4] => select user_id from `coppermine`.cpg1410n_sessions where session_id=md5("913b77ab5ecc0f9c62685662fbd5ced2f09879e13abe497d60176b8a70761d3d"); (0.002s)
    [5] => select user_id as id, user_password as password from `coppermine`.cpg1410n_users where user_id=0 (0.001s)
    [6] => SELECT MAX(group_quota) as disk_max, MIN(group_quota) as disk_min, MAX(can_rate_pictures) as can_rate_pictures, MAX(can_send_ecards) as can_send_ecards, MAX(upload_form_config) as ufc_max, MIN(upload_form_config) as ufc_min, MAX(custom_user_upload) as custom_user_upload, MAX(num_file_upload) as num_file_upload, MAX(num_URI_upload) as num_URI_upload, MAX(can_post_comments) as can_post_comments, MAX(can_upload_pictures) as can_upload_pictures, MAX(can_create_albums) as can_create_albums, MAX(has_admin_access) as has_admin_access, MIN(pub_upl_need_approval) as pub_upl_need_approval, MIN( priv_upl_need_approval) as  priv_upl_need_approval FROM cpg1410n_usergroups WHERE group_id in (3) (0.001s)
    [7] => SELECT group_name FROM  cpg1410n_usergroups WHERE group_id= 3 (0.001s)
    [8] => update `coppermine`.cpg1410n_sessions set time='1166148698' where session_id=md5('913b77ab5ecc0f9c62685662fbd5ced2f09879e13abe497d60176b8a70761d3d'); (0.003s)
    [9] => DELETE FROM cpg1410n_banned WHERE expiry < '2006-12-15 02:11:41' (0.001s)
    [10] => SELECT * FROM cpg1410n_banned WHERE (ip_addr='61.217.109.159' OR ip_addr='61.217.109.159' OR user_id=0) AND brute_force=0 (0.001s)
    [11] => SELECT aid FROM cpg1410n_albums WHERE visibility != '0' AND visibility !='10000' AND visibility NOT IN (3) (0.001s)
    [12] => SELECT *, pictures.title as title, pictures.caption as content, pictures.filename as filename, pictures.ctime as modified, pictures.latitude AS pic_latitude, pictures.longitude AS pic_longitude, albums.title AS foldertitle, albums.description  AS folderdesc
FROM cpg1410n_pictures as pictures, cpg1410n_albums as albums
WHERE pictures.aid = albums.aid AND
ABS(pictures.latitude) + ABS(pictures.longitude) > 0
  AND cpg1410n_albums.aid NOT IN (3,4,5,6,7,8)
ORDER BY foldertitle ASC, pic_latitude DESC ; (0.002s)
)

==========================
GET :
------------------
Array
(
)

==========================
POST :
------------------
Array
(
)

==========================
Page generated in 3.543 seconds - 13 queries in 0.017 seconds - Album set : AND aid NOT IN (3,4,5,6,7,8) ; Meta set: ;


[pocketmon]

solved by modifying

Code Select Expand


if(count($FORBIDDEN_SET_DATA) > 0 ){
    $forbidden_set_string =" AND {$CONFIG['TABLE_ALBUMS']}.aid NOT IN (".implode(",", $FORBIDDEN_SET_DATA).")";
} else {
    $forbidden_set_string = '';
}


to

Code Select Expand


if(count($FORBIDDEN_SET_DATA) > 0 ){
    $forbidden_set_string =" AND albums.aid NOT IN (".implode(",", $FORBIDDEN_SET_DATA).")";
} else {
    $forbidden_set_string = '';
}


I don't know much about SQL, but it works.

[Tranz]

Interesting. Do you not have prefixes in your table names? That might be the reason why cpg1410n_albums.aid couldn't be found. If you don't use prefixes, could you delete the setting in include/config.inc.php and see if the previous code still works?

[pocketmon]

Dear TranzNDance,

Your code is fine without any problems. I think this trouble should only happen in PrivatePyle's.

It comes to me when I checking the code below:

Code Select Expand

$query = "SELECT *, pictures.title as title, pictures.caption as content, pictures.filename as filename, pictures.ctime as modified, pictures.latitude AS pic_latitude, pictures.longitude AS pic_longitude, albums.title AS foldertitle, albums.description  AS folderdesc
FROM {$CONFIG['TABLE_PICTURES']} as pictures, {$CONFIG['TABLE_ALBUMS']} as albums
WHERE pictures.aid = albums.aid AND
ABS(pictures.latitude) + ABS(pictures.longitude) > 0
$query $forbidden_set_string
$order;";


The "{$CONFIG['TABLE_ALBUMS']} as albums " should be the cause. I am not familar with SQL, but I read a little English. So I just try and luckily it works.