Piwigo.org

dummer-esel · 2023-08-20 17:58:23

Dear community,

I have been using PWG for a long time now, sharing my travel photos to family and friends.
Now I added a functionality, and I wanted to share it with you, in case you can make use of it.

Caution:
1. I'm awfully out of practice with programming and I just invested a week-end into this. So don't expect good quality, much configuration, integration etc.
2. I'm not familiar with the way how to make PWG plugins, so this is kind of a stand-alone.
3. As always, I can take absolutely NO responsability of your data. It works for my but only you are responsible if you use it. No guarantee!

Said this, you can do whatever you want with this code.
If someone is willing to enhance it or make a "real plugin" out of it: I'm happy! You don't even have to mention my name/domain/whatever. (but if you do enhace it, surely the community would be grateful!)

What does it do?
- it uses OSM leaflet to show markers on a map (via an additional page), but for ALBUMS (categories), not for PHOTOS!
- manage the markers on a php page (with the marker data stored in a mysql database table)
- manage the markers popup template (in a file)

Why?
- I didn't want to use Google, that's why OSM
- I didn't want to use any other external service to create markers (and depend on that service)
- I wanted to link the info to PWG and just add coordinates, and generate the map with markers automatically

How does it look like?

Map: End-Result

Admin: Popup-Template

Admin: custom Popuptext & coordinates

What's still to do? (I probably won't do any of it)
- professional PWG integration (as a plugin)
- make code secure for production (apart from authentification and mysqli_real_escape_strings I didn't do any input checks)
- when 2 markers are at the exact same position, it shows only 1 on the map (overlay)
- popup template could be improved/extended. I only did it for my needs.
- optimize/refactor code (maybe some db query loops are not very efficient? also, its procedural code)

Last edited by dummer-esel (2023-08-20 18:19:38)

dummer-esel · 2023-08-20 18:16:25

Here's the code etc.

Leaflet: I used v. 1.9.4 as a local copy on my server: https://l********.com/download.html
(only the .js and .css file, and the /images/ subfolder).

As I said, no guarantee it works. Feel free to use/improve it.

MySQL Table for the coordinates:

Code:

CREATE TABLE `geotags` (
  `category` int(11) NOT NULL,
  `active` tinyint(1) NOT NULL,
  `popup` text NOT NULL,
  `latitude` float NOT NULL,
  `longitude` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Additional page in PWG (to embed the leaflet map):

Code:

<link rel="stylesheet" href="leaflet.css">
<script src="leaflet.js"></script>

<style>
    html, body {
      height: 100%;
      margin: 0;
    }
    .leaflet-container {
      height: 600px;
      width: 400px;
      max-width: 100%;
      max-height: 100%;
    }
</style>

  
</head>
<body>

<div id="map" style="width: 100%; height: 400px;"></div>
<script>

  var map = L.map('map').setView([21, -25], 2);

  var tiles = L.tileLayer('https://tile.openstreetmap.org/{z}/{x}/{y}.png', {
    maxZoom: 19,
    attribution: ''
  }).addTo(map);

</script>

<script src="../geo_markers.inc">
</script>

Code of the admin.php page (sorry for the German words ^^):
(I removed the header and some sensitive information like exact table names or DB auth.)

Code:

<?

/////////// session, authentication, header etc. removed


?>
<div id="untereinander">

<?
// Popup Template
$template_file = 'popup_template.inc';

print '<form action="'.$PHP_SELF.'" method="POST">
<table><tr><td>
<b>Popup Template</b><br>';

if ($_POST['setPopupTemplate'] and $_POST['popup_template']) { // replace template in file
  file_put_contents($template_file, $_POST['popup_template']);
  print '<br><font color="green"><b>Template successfully saved!</b></font><br><br>';
}

print 'The following placeholders can be used:<br>
<pre>
{CATTITLE} = Category Title<br>
{CATDESCR} = Category Description<br>
{CATURL} = Category URL<br>
{CATPATH} = Category path (of names)</pre>
<br>
If the popup textarea of the gallery is empty, the following template will be applied for the popup:<br>
<textarea name="popup_template" style="width:100%; height=50px">'.file_get_contents($template_file).'</textarea>
<br>
<br>
<input type="submit" name="setPopupTemplate" value="Speichern"></form>

</td></tr>
</table>
';
?>




<?
// Geo-Tagging Table
print '<table><tr><td colspan=7>
<b>Fotoalben Geo-Tagging</b><br>
<br>

<form action="'.$PHP_SELF.'" method="POST">
';

// Connect zur PWG DB:
$db1 = mysqli_connect("xxxxx", "xxxxx", "xxxxx") or die("keine Verbindung möglich: ".mysql_error());
mysqli_set_charset($db1, "utf8mb4");
mysqli_select_db($db1, "xxxxx");

// Connect zur Tagging DB:
$db2 = mysqli_connect("xxxxx", "xxxxx", "xxxxx") or die("keine Verbindung möglich: ".mysql_error());
mysqli_set_charset($db2, "utf8mb4");
mysqli_select_db($db2, "xxxxx");


// Update database and generate new geo markers file
if ($_POST['setGeoData']) {

  $geo_markers_file = 'geo_markers.inc'; // write leaflet markers here
  $popup_template = file_get_contents($template_file); // Popup Template
  $get_markers_text = '';
  
  $category_url_prefix = 'https://xxxxx-my-domain-with-piwigo/index.php?/category/';
  
  // get list of categories from pwg
  $result = mysqli_query($db1, 'SELECT id,name,comment,uppercats FROM `xxxxx`');
  while($row = mysqli_fetch_assoc($result)) {
    $pwg_names[$row['id']] = $row['name'];
    $pwg_comments[$row['id']] = $row['comment'];
    $pwg_granks[$row['id']] = $row['uppercats'];
  }
    
  foreach ($_POST['id'] as $ids => $id) // walk through table
  {
    $new_active = ($_POST['active'][$id]) ? '1' : '0'; // checkbox
    $new_popup = ($_POST['popup'][$id]) ? $_POST['popup'][$id] : ''; // textarea
    $new_lat = ($_POST['latitude'][$id]) ? $_POST['latitude'][$id] : 0; // float
    $new_long = ($_POST['longitude'][$id]) ? $_POST['longitude'][$id] : 0; // float
    
    // update geo db
    if (!mysqli_query($db2, 'REPLACE INTO `geotags` SET category=\''.$id.'\', active=\''.$new_active.'\', popup=\''.($new_popup).'\', latitude=\''.($new_lat).'\', longitude=\''.($new_long).'\'')) {
      printf("Fehlermeldung: %s\n", mysqli_error($db2));
    }
    
    
    //add marker to markers list
    if ($new_active) {

      // Leaflet marker template: var marker = L.marker([51.5, -0.09]).addTo(map).bindPopup('some html text');
      $addtext = 'var marker = L.marker(['.$new_lat.', '.$new_long.']).addTo(map).bindPopup(\'';
      
      if ($new_popup == '')
        $popup_text = $popup_template;

      /*
      {CATTITLE} = Category Title<br>
      {CATDESCR} = Category Description<br>
      {CATURL} = Category URL<br>
      {CATPATH} = Category path (of names)
      */
      $popup_text = str_replace('{CATTITLE}', $pwg_names[$id], $popup_text);
      $popup_text = str_replace('{CATDESCR}', $pwg_comments[$id], $popup_text);

      
      $path_text = ''; // get all the category names in the path
      unset($step);
      foreach(explode(",", $pwg_granks[$id]) as $step)
      {
        if ($step != $id)
          $path_text .= $pwg_names[$step].' &gt; ';
      }
      $popup_text = str_replace('{CATPATH}', $path_text, $popup_text);
      $popup_text = str_replace('\'', '\\\'', $popup_text);
      $popup_text = str_replace('{CATURL}', $category_url_prefix.$id, $popup_text);
      
      $addtext .= $popup_text;
      $addtext .= "');\n";
      
      $get_markers_text .= $addtext; // add marker to markers list
    }
  }
  file_put_contents($geo_markers_file, $get_markers_text);
}


// Tabelle mit Formular ausgeben
print '</td></tr>
    <tr><td><b>Category</b></td><td><b>Path</b></td><td><b>Name</b></td><td><b>Active</b></td><td><b>Popup (HTML)</b></td><td><b>Latitude</b></td><td><b>Longitude</b></td></tr>';
$result = mysqli_query($db1, 'SELECT id,name,global_rank FROM `xxxxx` ORDER BY CAST(`global_rank` AS DOUBLE)');
while($row = mysqli_fetch_assoc($result)) {
  
  $id = $row['id'];
  $name = $row['name'];
  $rank = $row['global_rank'];
  
  // Tagging DB abfragen
  $row2 = mysqli_fetch_assoc(mysqli_query($db2, 'SELECT active,popup,latitude,longitude FROM `geotags` WHERE category='.$id));
  $active = $row2['active'];
  $popup = $row2['popup'];
  $latitude = $row2['latitude'];
  $longitude = $row2['longitude'];


  print '<tr>
  <td>'.$id.'<input type="hidden" name="id['.$id.']" value="'.$id.'"></td>
  <td>'.$rank.'</td>
  <td>'.$name.'</td>
  <td><input type="checkbox" name="active['.$id.']"'.($active?' checked':'').'></td>
  <td><textarea name="popup['.$id.']" style="height:15px; width:160px;">'.$popup.'</textarea></td>
  <td><input type="text" name="latitude['.$id.']" size=5 value="'.$latitude.'"></td>
  <td><input type="text" name="longitude['.$id.']" size=5 value="'.$longitude.'"></td>
  </tr>';
}

print '<tr><td colspan=7 align="right"><input type="submit" name="setGeoData" value="Speichern"></form>
    </td></tr>
  </table>';

include('inc_unten.php');

?>

...also watch out to use the right directories. My admin.php is in the main directory, whereas piwigo is in the subfolder /fotos/.
this script creates the files popup_template.inc and geo_markers.inc in the main directory (not in subfolder /fotos/)

The automatically generated markers file geo_markers.inc (which is included in the map page) looks like this (one line like this for each marker):

Code:

var marker = L.marker([39.488, -0.325]).addTo(map).bindPopup('2023 &gt; Spanien &gt;  <a href="https://xxxxxxxxxxxxxx/fotos/index.php?/category/255">Valencia</a><br>In Stadt und Strand ließen wir es uns gut gehen und genossen das gute Wetter');

...this file with the markers is included in the additional page mentioned above.

The file popup_template.inc is used in the admin.php and could look like this:

Code:

{CATPATH} <a href="{CATURL}">{CATTITLE}</a><br>{CATDESCR}

Last edited by dummer-esel (2023-08-20 19:37:52)

Piwigo.org

Announcement

#1 2023-08-20 17:58:23

Info: Show category/album markers on OSM map (openstreetmap)

#2 2023-08-20 18:16:25

Re: Info: Show category/album markers on OSM map (openstreetmap)

Code:

Code:

Code:

Code:

Code:

Board footer