[Devel] r415 - branches/dui
svn at agendadulibre.org
svn at agendadulibre.org
Dim 4 Jan 16:44:02 CET 2009
Author: ldayot
Date: Sun Jan 4 16:44:00 2009
New Revision: 415
Log:
Petites modifications suite du precedent commit.
Principalement : maj du schema sql.
Separation de l'import des epn dans la base des GULs et de la recherche
de coordonnees geospaciales des GULs.
Modif de recherche des noms de ville de name en majname (je ne comprends
pas comment a ete remplie ma base cities avec des donnees partielles
(sans les accents)).
Du coup, un peu plus de carte de localisation pour les lugs.
Added:
branches/dui/findgeogul.php
Modified:
branches/dui/importepn.php
branches/dui/lugtextlist.php
branches/dui/rss.php
branches/dui/schema.sql
branches/dui/showlug.php
Added: branches/dui/findgeogul.php
==============================================================================
--- (empty file)
+++ branches/dui/findgeogul.php Sun Jan 4 16:44:00 2009
@@ -0,0 +1,100 @@
+<?php
+/*
+ * Trouver les coordonnées geospatiale de tous les guls
+*/
+
+include("text.inc.php");
+include("bd.inc.php");
+include("funcs.inc.php");
+include("session.inc.php");
+include("user.inc.php");
+
+$db = new db();
+$user = new user($db);
+
+if (isset($_GET['disconnect']))
+{
+ $user->disconnect();
+ header("Location:" . calendar_absolute_url());
+}
+
+calendar_setlocale();
+
+put_header("Geolocalise les GULs");
+
+echo "<h2>Géolocalise les GULs</h2>";
+
+
+$lug_id = get_safe_integer('id', 0);
+if ($lug_id>0)
+ {
+ $lug_res = $db->query ("SELECT * FROM lugs WHERE id='{$lug_id}'");
+ if ($db->numRows($lug_res)==0)
+ {
+ echo "<p class=\"error\">Pas de GUL avec cet identifiant.</p>";
+ put_footer();
+ exit;
+ }
+ }
+else
+ {
+ $lug_res = $db->query ("SELECT * FROM lugs WHERE latitude=0 OR latitude IS NULL LIMIT 0,500");
+ }
+
+$count_ok = $count_nok = 0;
+
+while($lug = $db->fetchObject($lug_res))
+ {
+ // trouver les coordonnées GPS via Google à partir de l'adresse
+ $url_gps = "http://maps.google.com/maps/geo?q=".
+ $lug->address. " ". $lug->postalcode. " ". $lug->city. " France&output=csv&key=ABQIAAAATndsWAV5Q2y7pRRi-22W_hTxw9fvAnrsiYejTsRxd4b0cj9HKxSNCXUxAANaoACDzXWznNLVPto_jA";
+ $gps_file = file(str_replace(" ", "+", $url_gps), FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
+ if ($lug_id>0)
+ {
+ echo str_replace(" ", "+", $url_gps)."<br/>";
+ print_r($gps_file);
+ }
+ list(,,$lug->latitude, $lug->longitude) = explode(",", $gps_file[0]);
+
+ if ($lug->latitude>0)
+ {
+ echo "+ ";
+ $count_ok++;
+ }
+ else// on prend les coordonnees de la ville si on ne trouve pas
+ {
+ echo "-{$lug->id}\n";
+ $count_nok++;
+ if ($lug->city == "")
+ continue; // no city
+
+ $city = $db->query("select * from cities where majname LIKE '" . addslashes($lug->city) . "'");
+ if (! $city)
+ continue; // error in query
+ if ($db->numRows($city) == 0)
+ continue; // city not found
+ if ($db->numRows($city)>1)
+ {
+ $city = $db->query("select * from cities where majname LIKE '" . addslashes($lug->city) . "' AND postalcode='{$lug->postalcode}'");
+ if (! $city)
+ continue; // error in query
+ if ($db->numRows($city) == 0)
+ continue; // city with postalcode not found
+ }
+
+ $city = $db->fetchObject($city);
+ $lug->longitude = $city->longitude;
+ $lug->latitude = $city->latitude;
+ }
+
+ $sql = "UPDATE lugs SET latitude='{$lug->latitude}', longitude='{$lug->longitude}' WHERE id='{$lug->id}'";
+ $db->query($sql);
+
+ } // end while
+
+ echo "\n\n<h3>Terminé</h3>\n";
+ echo "<p>Ok : $count_ok</p><p>Pas Ok : $count_nok</p>\n";
+
+ echo "<p><a href=\"findgeogul.php\">Relancer le script</a></p>\n";
+
+?>
Modified: branches/dui/importepn.php
==============================================================================
--- branches/dui/importepn.php (original)
+++ branches/dui/importepn.php Sun Jan 4 16:44:00 2009
@@ -1,14 +1,23 @@
<?php
/*
* Importer le répertoire des EPN depuis la base nationale
- * et l'injecter dans la table des gul (ou autre, à voir)
+ * et l'injecter dans la table des GULs
*/
include("text.inc.php");
include("bd.inc.php");
include("funcs.inc.php");
+include("session.inc.php");
+include("user.inc.php");
$db = new db();
+$user = new user($db);
+
+if (isset($_GET['disconnect']))
+{
+ $user->disconnect();
+ header("Location:" . calendar_absolute_url());
+}
put_header("Import des EPN");
@@ -45,7 +54,7 @@
{
if (trim($element)=="</tr>")
{
- echo "<br />$nextIs = ". $epn[$nextIs]. "<br/>\n";
+ //echo "<br />$nextIs = ". $epn[$nextIs]. "<br/>\n";
$nextIs = false;
continue;
}
@@ -79,11 +88,9 @@
continue;
}
}
- // trouver les coordonnées GPS via Google à partir de l'adresse
- $url_gps = "http://maps.google.com/maps/geo?q=".
- $epn['address']. " ". $epn['postalcode']. " ". $epn['city']. " France&output=csv&key=ABQIAAAATndsWAV5Q2y7pRRi-22W_hTxw9fvAnrsiYejTsRxd4b0cj9HKxSNCXUxAANaoACDzXWznNLVPto_jA";
- $gps_file = file(str_replace(" ", "+", $url_gps), FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
- list(,,$epn['latitude'], $epn['longitude']) = explode(",", $gps_file[0]);
+
+ $epn['address'] = str_ireplace($epn['postalcode'], "", $epn['address']);
+ $epn['address'] = str_ireplace($epn['city'], "", $epn['address']);
$epn['comment'] =
($epn['services']>"" ? "Services : ". $epn['services']. "<br/>\n" : "").
@@ -123,5 +130,4 @@
return isset($element) ? $element : false;
}
-
?>
Modified: branches/dui/lugtextlist.php
==============================================================================
--- branches/dui/lugtextlist.php (original)
+++ branches/dui/lugtextlist.php Sun Jan 4 16:44:00 2009
@@ -27,14 +27,27 @@
calendar_setlocale();
+$lug_id = get_safe_integer('id', 0);
+if ($lug_id>0)
+ {
+ $lug_res = $db->query ("SELECT * FROM lugs WHERE id='{$lug_id}'");
+ if ($db->numRows($lug_res)==0)
+ {
+ echo "<p class=\"error\">Pas de GUL avec cet identifiant.</p>";
+ put_footer();
+ exit;
+ }
+ }
+else
+ {
+ $lug_res = $db->query ("SELECT * FROM lugs");
+ }
+
Header("Content-type: text/plain; charset: utf-8");
echo "lon\tlat\ttitle\tdescription\ticon\n";
-$sql = "select * from lugs";
-
-$lugs = $db->query($sql);
-while($lug = mysql_fetch_object($lugs))
+while($lug = $db->fetchObject($lug_res))
{
// has lug coordonates ?
if ($lug->longitude==0)
@@ -42,14 +55,14 @@
if ($lug->city == "")
continue; // no city
- $city = $db->query("select * from cities where name='" . addslashes($lug->city) . "'");
+ $city = $db->query("select * from cities where majname LIKE '" . addslashes($lug->city) . "'");
if (! $city)
continue; // error in query
- if (mysql_num_rows($city) != 1)
+ if ($db->numRows($city) != 1)
continue; // city not found
- $city = mysql_fetch_object($city);
+ $city = $db->fetchObject($city);
$lug->longitude = $city->longitude;
$lug->latitude = $city->latitude;
}
Modified: branches/dui/rss.php
==============================================================================
--- branches/dui/rss.php (original)
+++ branches/dui/rss.php Sun Jan 4 16:44:00 2009
@@ -42,7 +42,7 @@
function get_city_coordinates ($db, $city)
{
- $sql = "SELECT longitude, latitude FROM cities WHERE name='" . $city . "'";
+ $sql = "SELECT longitude, latitude FROM cities WHERE namemaj='" . $city . "'";
$ret = $db->query($sql);
if (mysql_num_rows($ret) == 1)
Modified: branches/dui/schema.sql
==============================================================================
--- branches/dui/schema.sql (original)
+++ branches/dui/schema.sql Sun Jan 4 16:44:00 2009
@@ -4,7 +4,9 @@
`description` text NOT NULL,
`start_time` datetime NOT NULL default '0000-00-00 00:00:00',
`end_time` datetime NOT NULL default '0000-00-00 00:00:00',
+ `address` VARCHAR( 255 ) NULL,
`city` varchar(255) NOT NULL default '',
+ `department` INT NULL,
`region` int(11) NOT NULL default '0',
`locality` int(11) NOT NULL default '0',
`url` varchar(255) NOT NULL default '',
@@ -16,6 +18,7 @@
`moderated` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
INDEX (`region`),
+ INDEX (`department`),
INDEX (`moderated`),
INDEX (`start_time`),
INDEX (`end_time`)
@@ -67,16 +70,6 @@
) TYPE=MyISAM;
CREATE TABLE lugs (
- id int(11) NOT NULL auto_increment,
- region int(11) NOT NULL default '0',
- department int(11) NOT NULL default '0',
- name varchar(255) NOT NULL default '',
- url varchar(255) NOT NULL default '',
- city varchar(255) NOT NULL default '',
- PRIMARY KEY (id)
-) TYPE=MyISAM AUTO_INCREMENT=2 ;
-
-CREATE TABLE IF NOT EXISTS `lugs` (
`id` int(11) NOT NULL auto_increment,
`region` int(11) NOT NULL default '0',
`department` int(11) NOT NULL default '0',
@@ -92,7 +85,8 @@
`longitude` float default NULL,
`postalcode` int(11) default NULL,
PRIMARY KEY (`id`),
- KEY `region` (`region`)
+ KEY `region` (`region`),
+ KEY `department` (`department`)
) TYPE=MyISAM CHARSET=utf8;
-- --------------------------------------------------------
Modified: branches/dui/showlug.php
==============================================================================
--- branches/dui/showlug.php (original)
+++ branches/dui/showlug.php Sun Jan 4 16:44:00 2009
@@ -23,22 +23,68 @@
include("funcs.inc.php");
include("session.inc.php");
+calendar_setlocale();
+
$db = new db();
$session = new session();
-put_header($adl_lug_info);
-
$lug_id = get_safe_integer('id', 0);
$lug_res = $db->query ("SELECT * FROM lugs WHERE id='{$lug_id}'");
$lug = $db->fetchObject($lug_res);
if (! $lug)
{
- echo "<p class=\"error\">Pas d'évènement avec cet identifiant.</p>";
+ echo "<p class=\"error\">Pas de GUL avec cet identifiant.</p>";
put_footer();
exit;
}
+
+// start map
+$jcode = "
+ <script src=\"http://openlayers.org/api/OpenLayers.js\"></script>
+ <script src=\"http://openstreetmap.org/openlayers/OpenStreetMap.js\"></script>
+ <script type=\"text/javascript\">
+ var lat={$lug->latitude}
+ var lon={$lug->longitude}
+ var zoom=11
+ var map;
+ function init() {
+ map = new OpenLayers.Map (\"map\", {
+ controls:[
+ new OpenLayers.Control.Navigation(),
+ new OpenLayers.Control.PanZoomBar(),
+ new OpenLayers.Control.Attribution()],
+ maxResolution: 156543.0399,
+ units: 'm',
+ } );
+
+ map.addControl(new OpenLayers.Control.LayerSwitcher());
+
+ layerTilesAtHome = new OpenLayers.Layer.OSM.Mapnik(\"Mapnik\");
+ map.addLayer(layerTilesAtHome);
+
+ var lonLat = new OpenLayers.LonLat(lon, lat).transform(new OpenLayers.Projection(\"EPSG:4326\"), new OpenLayers.Projection(\"EPSG:900913\"));
+
+ map.setCenter (lonLat, zoom);
+
+ var newl = new OpenLayers.Layer.Text('EPN', {location: '" . calendar_absolute_url("lugtextlist.php?id={$lug_id}") . "'});
+ map.addLayer(newl);
+
+ }
+
+ </script>\n";
+
+// end map
+if ($lug->latitude>0)
+ {
+ put_header($adl_lug_info, $jcode, "init();");
+ }
+else
+ {
+ put_header($adl_lug_info);
+ }
+
function format_lug ($db, $lug)
{
$name = stripslashes($lug->name);
@@ -63,7 +109,7 @@
$result .= "<h3>Informations</h3>\n";
$result .= "<p>Site Web: <a href=\"" . $url . "\">" . $url . "</a></p>\n";
- $result .= "<p>Contact: $contact <a href=\"mailto:" . $mail . "\">" . $mail . "</a> $phone</p>\n";
+ $result .= "<p>Contact: $contact - <a href=\"mailto:" . $mail . "\">" . $mail . "</a> - $phone</p>\n";
return $result;
}
@@ -71,7 +117,10 @@
echo format_lug ($db, $lug);
-//echo return_map_lug($event);
+// start map
+if ($lug->latitude>0)
+ echo "<div style=\"margin: auto; width:500px; height:400px; border: 1px solid black;\" id=\"map\"></div>\n";
+// end map
put_footer();
Plus d'informations sur la liste de diffusion Devel