-
-
Save cquest/7f3d3067f1f572f1cce8 to your computer and use it in GitHub Desktop.
script etat-commune
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <!--break--> | |
| <form method=GET url="./"> | |
| Code INSEE: <input type=text length=5 name="insee"> <input type="submit"> | |
| </form> | |
| <SCRIPT language="Javascript"> | |
| <!-- | |
| var xhr_object; | |
| function josm_load(url) | |
| { | |
| if(window.XMLHttpRequest) // Firefox | |
| xhr_object = new XMLHttpRequest(); | |
| else if(window.ActiveXObject) // Internet Explorer | |
| xhr_object = new ActiveXObject("Microsoft.XMLHTTP"); | |
| else { // XMLHttpRequest non supporté par le navigateur | |
| alert("Votre navigateur ne supporte pas les objets XMLHTTPRequest..."); | |
| return; | |
| } | |
| xhr_object.open("GET", url, true); | |
| xhr_object.send(null); | |
| } | |
| // --> | |
| </SCRIPT> | |
| <?php //-------------------------------------------------------------------------- | |
| if (false) echo "<font color=red><b>La base de données est actuellement en maintenance.</b></font>"; | |
| else | |
| { | |
| $timer = microtime(); | |
| drupal_set_message("Ce formulaire est en construction.","warning"); | |
| $api = "http://api.openstreetmap.fr/api/"; | |
| $insee = "33103"; // commune par défaut | |
| $id=""; // osm_id d'un arrondissement, quartier ou autre | |
| $curgeofla="ign_geofla2013"; | |
| // connexion à la base postgresql sur osm7 via tunnel ssh local | |
| $pg = pg_pconnect("host=localhost port=54327 dbname=osm user=www-drupal password=xxx"); | |
| if (!$pg) | |
| { | |
| echo t("Connexion bdd impossible, désolé."); exit(); | |
| } | |
| pg_query("set search_path to cquest,fred,public,osmosis,osm2pgsql;"); | |
| if (isset($_GET["insee"])) | |
| $insee = pg_escape_string($_GET["insee"]); // code INSEE de la commune | |
| if (isset($_GET["id"])) | |
| $id = pg_escape_string($_GET["id"]); // osm_id dans planet_osm_polygon | |
| // ---------------- on récupère le polygone de la commune dans OSM et des infos de base | |
| if ($id=="") | |
| $sql = "SELECT name, ST_AsEWKT(way) as poly, ST_AsEWKT(ST_transform(way,4326)) as poly_wgs, ST_area(geography(ST_transform(way,4326)))/1000000 as km2, ST_Xmax(ST_transform(ST_Centroid(way),4326)) as lon, ST_Ymax(ST_transform(ST_Centroid(way),4326)) as lat , ST_xmin(ST_transform(way,4326)) as bbleft, ST_ymin(ST_transform(way,4326)) as bbbot, ST_xmax(ST_transform(way,4326)) as bbright, ST_ymax(ST_transform(way,4326)) as bbtop, ST_xmin(way) as bbleftM, ST_ymin(way) as bbbotM, ST_xmax(way) as bbrightM, ST_ymax(way) as bbtopM FROM planet_osm_polygon WHERE \"ref:INSEE\"='$insee' AND admin_level IN ('8','9')"; | |
| else | |
| $sql = "SELECT name, ST_AsEWKT(way) as poly, ST_AsEWKT(ST_transform(way,4326)) as poly_wgs, ST_area(geography(ST_transform(way,4326)))/1000000 as km2, ST_Xmax(ST_transform(ST_Centroid(way),4326)) as lon, ST_Ymax(ST_transform(ST_Centroid(way),4326)) as lat , ST_xmin(ST_transform(way,4326)) as bbleft, ST_ymin(ST_transform(way,4326)) as bbbot, ST_xmax(ST_transform(way,4326)) as bbright, ST_ymax(ST_transform(way,4326)) as bbtop, ST_xmin(way) as bbleftM, ST_ymin(way) as bbbotM, ST_xmax(way) as bbrightM, ST_ymax(way) as bbtopM FROM planet_osm_polygon WHERE osm_id=$id"; | |
| $r=pg_query($sql); | |
| $ville = pg_fetch_array($r); | |
| if ($ville==false) | |
| { | |
| $sql = "SELECT nom_comm as name, ST_AsEWKT(st_transform(the_geom,900913)) as poly, ST_AsEWKT(ST_transform(the_geom,4326)) as poly_wgs, ST_area(geography(ST_transform(way,4326)))/1000000 as km2 , ST_Xmax(ST_transform(ST_Centroid(the_geom),4326)) as lon, ST_Ymax(ST_transform(ST_Centroid(the_geom),4326)) as lat , ST_xmin(ST_transform(the_geom,4326)) as bbleft, ST_ymin(ST_transform(the_geom,4326)) as bbbot, ST_xmax(ST_transform(the_geom,4326)) as bbright, ST_ymax(ST_transform(the_geom,4326)) as bbtop, ST_xmin(ST_transform(the_geom,900913)) as bbleftM, ST_ymin(ST_transform(the_geom,900913)) as bbbotM, ST_xmax(ST_transform(the_geom,900913)) as bbrightM, ST_ymax(ST_transform(the_geom,900913)) as bbtopM FROM $curgeofla r WHERE insee_com='$insee' ;"; | |
| $r=pg_query($sql); | |
| $ville = pg_fetch_array($r); | |
| $geofla=true; | |
| drupal_set_message( t("Les limites de cette commune ne sont pas encore présentes dans OSM. Les infos ci-dessous seront approximatives."),"warning"); | |
| } | |
| else | |
| $geofla=false; | |
| $bboxm = $ville[10].",".$ville[11].",".$ville[12].",".$ville[13]; | |
| $lat = $ville["lat"]; | |
| $lon = $ville["lon"]; | |
| $latlon= "&lat=$lat&lon=$lon"; | |
| $bing_date=""; | |
| // -------------- Couverture Bing Aerials -------------- | |
| $resolution = 5; // environ 5cm/pixel au zoom 21 à une latitude de 45° | |
| for ($zoom = 21; $zoom>=14; $zoom--) | |
| { | |
| $bing= simplexml_load_file("http://dev.virtualearth.net/REST/v1/Imagery/Metadata/Aerial/$lat,$lon?zl=$zoom&o=xml&key=Av48uG1dlRVRDWGbpmSYo4DlXGCuFOyEPNeEmfTVtLcv1olpEM1LvGkAplbo0-qk"); | |
| echo "<!-- start=".$bing->ResourceSets->ResourceSet->Resources->ImageryMetadata->VintageStart." bing_date=$bing_date -->\r\n"; | |
| if ((string) $bing->ResourceSets->ResourceSet->Resources->ImageryMetadata->VintageStart > (string) $bing_date) | |
| { | |
| if ($bing_date=="" ) | |
| { | |
| $meta = $bing->ResourceSets->ResourceSet->Resources->ImageryMetadata; | |
| $meta_last = $meta; | |
| $meta_res = $resolution; | |
| $meta_zoom = $zoom; | |
| echo "<!-- "; print_r($meta); echo " -->"; | |
| } | |
| else | |
| { | |
| $meta_last = $bing->ResourceSets->ResourceSet->Resources->ImageryMetadata; | |
| $meta_last_res = $resolution; | |
| $meta_last_zoom = $zoom; | |
| echo "<!-- "; print_r($meta_last); echo " -->"; | |
| } | |
| $bing_date = $bing->ResourceSets->ResourceSet->Resources->ImageryMetadata->VintageStart; | |
| } | |
| $resolution = $resolution *2; | |
| } | |
| echo "<h2>".$ville["name"]." (insee: $insee)</h2>"; | |
| echo "<img src='$meta->ImageUrl' width=256 height=256 align='right' alt='Exemple vue aérienne Bing'>"; | |
| echo"Surface: ".round($ville["km2"]*100,3)." ha soit ".round($ville["km2"],1)."km2<br>Position (WGS84): <a href='http://www.openstreetmap.org/?$latlon&zoom=14&layers=M'>".round(abs($ville["lat"]),5)."° ".($ville["lat"]>0 ? "N / " : "S / ").round(abs($ville["lon"]),5)."°".($ville["lon"]>0 ? "E" : "O")."</a> - <a href= 'http://www.openstreetmap.org/edit?bbox=".round(abs($ville["bbleft"]),5).",".round(abs($ville["bbbot"]),5).",".round(abs($ville["bbright"]),5).",".round(abs($ville["bbtop"]),5)." | |
| '>Editer la zone</a><p>"; | |
| echo "Bounding-box (left, bottom, right, top): [".round(abs($ville["bbleft"]),5).",".round(abs($ville["bbbot"]),5).",".round(abs($ville["bbright"]),5).",".round(abs($ville["bbtop"]),5)."]<p>"; | |
| echo "<h3>Sources</h3><ul>"; | |
| // ------------------ Cadastre vectoriel ou raster ? ----------- | |
| $f = fopen("http://suivi.openstreetmap.fr/communes/stats-cadastre/".substr($insee,0,2).".csv","r"); | |
| while ($d = fgets($f)) | |
| { | |
| if (preg_match ( "/(.*),(.*),..".substr($insee,2,3).",(.*)/" , $d, $found )) | |
| { | |
| echo "<li>Le cadastre de cette commune est <b>".($found[3]=='VECT' ? "<a href='http://cadastre.openstreetmap.fr/'>vectoriel</a>" : "au format image" )."</b>.</li>"; | |
| break; | |
| } | |
| } | |
| fclose($f); | |
| // ------------------- Bing ? | |
| echo "<li>Images aériennes Bing datant de ".$meta->VintageStart." à ".$meta->VintageEnd." disponibles au zoom $meta_zoom soit environ $meta_res cm/pixel (voir ci-contre).</li>"; | |
| if ($meta_last->VintageStart != $meta->Vintagestart) | |
| echo "<li>Images aériennes Bing <b>la plus récente</b> datant de ".$meta_last->VintageStart." à ".$meta_last->VintageEnd." disponibles au zoom $meta_last_zoom soit environ $meta_last_res cm/pixel.</li>"; | |
| echo "</ul>"; | |
| // ---------------- Liens vers osmose et Cie... | |
| echo "<h3>Outils de contrôle qualité</h3><ul> | |
| <li><a href='http://osmose.openstreetmap.fr/map/?zoom=14$latlon&layers=B0FF00FFFFFFFFFFFFT' targer='_blank'>Osmose</a> - détection d'erreurs</li> | |
| <li><a href='http://tools.geofabrik.de/osmi/?zoom=14$latlon'>OSM Inspector</a>- encore de la détection d'erreurs</li> | |
| <li><a href='http://keepright.ipax.at/report_map.php?zoom=14$latlon'>keep right!</a> - et encore de la détection d'erreurs</li> | |
| <li><a href='http://www.itoworld.com/product/data/ito_map/main?view=0$latlon&zoom=14'>ITO map</a> - cartes spécialisées (réseau électrique, etc)</li> | |
| <!-- | |
| <li><a href='http://cleanmap.poole.ch/?zoom=14$latlon&layers=00B'>ODBL cleamap/badmap</a> - pour visualiser les données qui vont disparaitre suite au changement de licence.</li> | |
| --> | |
| </ul> | |
| <h3>Information sur les contributeurs...</h3> | |
| <ul> | |
| <li><a href='http://resultmaps.neis-one.org/oooc?zoom=12&$latlon&layers=00BTFFT'>Carte des contributeurs actifs dans cette zone</a> | |
| <!-- | |
| <li><a href='http://reporter.fluv.io/?bbox=".round(abs($ville["bbleft"]),5).",".round(abs($ville["bbbot"]),5).",".round(abs($ville["bbright"]),5).",".round(abs($ville["bbtop"]),5)."'>OSM-Reporter:</a> qui a contribué le plus sur cette zone ? | |
| --> | |
| <li><a href='http://zverik.osm.rambler.ru/whodidit/?zoom=14&$latlon&layers=BTT'>WHODIDIT</a> : Les dernières contributions.. | |
| </ul>"; | |
| $poly = $ville["poly"]; | |
| $poly_wgs = $ville["poly_wgs"]; | |
| echo "<br clear=all>"; | |
| // -------------- Adresses -------------- | |
| $sql ="select count(*) as nb from nodes n WHERE ST_contains(ST_GeomFromEWKT('$poly_wgs'),n.geom) and tags ? 'addr:housenumber'; "; | |
| $addr=pg_fetch_array(pg_query($sql)); | |
| $sql ="select count(*) as nb from ways WHERE ST_contains(ST_GeomFromEWKT('$poly_wgs'),linestring) and tags ? 'addr:interpolation'; "; | |
| $addr2=pg_fetch_array(pg_query($sql)); | |
| echo "<h3>Adresses</h3>"; | |
| if ($addr["nb"]>0) | |
| { | |
| echo $addr["nb"]." adresses géolocalisées dans cette commune"; | |
| if ($addr2["nb"]>0) echo " ainsi que ".$addr2["nb"]." interpolations"; | |
| echo ".<p><a href='adresses?insee=$insee'>Plus de détails sur ces adresses...</a>"; | |
| } | |
| else | |
| echo "Il n'y a aucune adresse géolocalisée dans cette commune."; | |
| // ------------- Quartiers | |
| if($id=="") | |
| { | |
| $sql = "select r.name, r.osm_id, r.admin_level from planet_osm_polygon r WHERE ST_contains(ST_GeomFromEWKT('$poly'),r.way) AND r.admin_level in ('9','10','11','12') order by r.admin_level, r.name;"; | |
| $r=pg_query($sql); | |
| echo "<h3>Quartiers</h3>"; | |
| if (pg_num_rows($r)>0) | |
| { | |
| echo "<ul>"; | |
| while ($d = pg_fetch_array($r)) | |
| { | |
| echo "<li><a href='".$_SERVER["REDIRECT_URL"]."?insee=$insee.&id=".$d["osm_id"]."'>".$d["name"]." (admin_level=".$d["admin_level"].")</a></li>"; | |
| } | |
| echo "</ul>"; | |
| } | |
| else | |
| echo "Aucun quartier défini."; | |
| } | |
| // ------------- état du réseau routier -------------------------- | |
| $sql = "SELECT l.highway, count(*) AS nb_way, COUNT(DISTINCT(l.name)) AS nb_name, COUNT(DISTINCT(l.ref)) AS nb_ref, SUM(ST_length(geography(ST_transform(st_intersection(l.way,ST_GeomFromEWKT('$poly')),4326))))/1000 AS km FROM planet_osm_line l WHERE ST_Intersects(ST_GeomFromEWKT('$poly'),l.way) AND l.highway!='' group by l.highway order by km desc;"; | |
| $r=pg_query($sql); | |
| array($rows); | |
| while ($d = pg_fetch_array($r)) | |
| { | |
| $rows[] = array(t($d["highway"]),$d["nb_way"],$d["nb_name"],$d["nb_ref"],round($d["km"],3)." km"); | |
| $total_way += $d["nb_way"]; | |
| $total_name += $d["nb_name"]; | |
| $total_ref += $d["nb_ref"]; | |
| $total_km += $d["km"]; | |
| } | |
| $rows[] = array("TOTAL",$total_way, $total_name, $total_ref, round($total_km,3)." km"); | |
| $header = array("Type","Nombre *","Noms **","Ref ***","Longueur"); | |
| echo "<h3>Voirie</h3>"; | |
| print theme('table', array('header' => $header, 'rows' => $rows)); | |
| echo "* nombre total de segments trouvés<br>** noms différents trouvés<br>*** ref différents trouvés<p>"; | |
| echo "<a href='http://layers.openstreetmap.fr/?zoom=14&layers=B0000TFTFFFFFFFFFFFFFF$latlon'>Voir sur une carte les routes sans nom (rouge) ni ref (bleu)</a><p>"; | |
| unset($rows); | |
| $f = fopen("http://beta.letuffe.org/cron/etat-voirie-communes/stats.csv","r"); | |
| while ($d = fgets($f)) | |
| { | |
| if (preg_match ( "/$insee,(.*)/" , $d, $found )) | |
| { | |
| echo "<a href='http://beta.letuffe.org/cron/etat-voirie-communes/'>Total de voirie estimé à partir de l'analyse du cadastre vectoriel: </a>: ".round($found[1],3)." km<p>"; | |
| break; | |
| } | |
| } | |
| fclose($f); | |
| // ------------- occupation des sols -------------------------- | |
| $sql ="select sum(st_area(geography(st_transform(st_intersection(l.way,ST_GeomFromEWKT('$poly')),4326))))/10000 as landuse_Ha, l.landuse, count(*) FROM planet_osm_polygon l WHERE ST_isvalid(l.way) AND (l.way && ST_GeomFromEWKT('$poly')) AND l.landuse!='' GROUP BY l.landuse ORDER BY landuse_Ha DESC;"; | |
| $r=pg_query($sql); | |
| array($rows); | |
| while ($d = pg_fetch_array($r)) | |
| { | |
| $rows[] = array(t($d["landuse"])." (<a href='http://wiki.openstreetmap.org/wiki/Tag:landuse%3D".$d["landuse"]."'>wiki</a>)",round($d["landuse_ha"],3)." ha (".round($d["landuse_ha"]/$ville["km2"],2)."%)"); | |
| } | |
| $header = array("Type","Surface"); | |
| echo "<h3>Occupation du sol</h3>"; | |
| print theme('table', array('header' => $header, 'rows' => $rows)); | |
| unset($rows); | |
| // -------------- Base Permanente Equipements de l'INSEE ------------- | |
| $bpe=pg_fetch_array(pg_query("select * from equip_serv where codgeo='$insee'")); | |
| // ------------- état des POI -------------------------- | |
| echo "<h3>POI</h3>"; | |
| // lien vers LizPOI | |
| echo "<a href='http://lizpoi.3liz.com/demo/index.php/lizpoi/map/?tree_id=1&selected=57,1&bbox=$bboxm&zoom=15'>Visualisation à l'aide d'OSM Interest</a>"; | |
| $tag="amenity"; | |
| // select v as val, sum(nb) as nb, max(id) as id from (select count(*) as nb, tags->'amenity' as v, max(id) as id from nodes WHERE st_contains(st_geomfromewkt('$poly_wgs'),geom) AND tags ? 'amenity' group by tags->'amenity' UNION select count(*) as nb, tags->'amenity' as v, max(id) as id from ways WHERE st_contains(st_geomfromewkt('$poly_wgs'), linestring) AND tags ? 'amenity' group by tags->'amenity') as poi group by v order by v; | |
| $sql = "select count(*) as nb, tags->'$tag' as val, max(id) from nodes where st_contains(st_geomfromewkt('$poly_wgs'), geom) AND tags ? '$tag' group by tags->'$tag' order by tags->'$tag';"; | |
| $sql = "SELECT v AS val, sum(nb) AS nb, max(id) AS id FROM (SELECT count(*) as nb, tags->'$tag' as v, max(id) as id from nodes WHERE st_contains(st_geomfromewkt('$poly_wgs'),geom) AND tags ? '$tag' group by tags->'$tag' UNION select count(*) as nb, tags->'$tag' as v, max(id) as id from ways WHERE st_contains(st_geomfromewkt('$poly_wgs'), linestring) AND tags ? '$tag' group by tags->'$tag') as poi group by v order by v;"; | |
| $r=pg_query($sql); | |
| array($rows); | |
| while ($d = pg_fetch_array($r)) | |
| { | |
| $nb=""; | |
| switch ($d["val"]) { | |
| case "police": | |
| $nb = $bpe["nb_a101"]+$bpe["nb_a104"]; // police + gendarmerie | |
| break; | |
| case "bank": | |
| $nb = $bpe["nb_a203"]; // banque + caisse d'épargne | |
| break; | |
| case "post_office": | |
| $nb = $bpe["nb_a206"]+$bpe["nb_a207"]+$bpe["nb_a208"]; // poste + relais poste + agence communale | |
| break; | |
| case "court_house": | |
| $nb = $bpe["nb_a105"]+$bpe["nb_a106"]+$bpe["nb_a107"]; // Cours d'appel + TGI + Tribunal d'Instance | |
| break; | |
| case "car_rental": | |
| $nb = $bpe["nb_a303"]; | |
| break; | |
| case "restaurant": | |
| $nb = $bpe["nb_a504"]; | |
| break; | |
| case "veterinary": | |
| $nb = $bpe["nb_a502"]; | |
| break; | |
| case "pharmacy": | |
| $nb = $bpe["nb_d301"]; | |
| break; | |
| case "kindergarten": | |
| $nb = $bpe["nb_c101"]; // ecoles maternelles | |
| break; | |
| case "school": | |
| $nb = $bpe["nb_c104"]+$bpe["nb_c201"]+$bpe["nb_c301"]; // écoles primaires + collèges + lycées | |
| break; | |
| } | |
| if ($nb>0) | |
| $rows[] = array(t($d["val"])." (<a href='http://wiki.openstreetmap.org/wiki/Tag:$tag%3D".$d["val"]."'>wiki</a>)", $d["nb"]." / $nb (".round(100*$d["nb"]/$nb,0)."%)"); | |
| else | |
| $rows[] = array(t($d["val"])." (<a href='http://wiki.openstreetmap.org/wiki/Tag:$tag%3D".$d["val"]."'>wiki</a>)", $d["nb"]); | |
| } | |
| $header = array("Type","Nombre *"); | |
| echo "<h4>".t($tag)."</h4>"; | |
| print theme('table', array('header' => $header, 'rows' => $rows)); | |
| echo "* le total est issu des données de l'INSEE (Base Permanente des Equipements 2011)."; | |
| unset($rows); | |
| $tag="shop"; | |
| // select v as val, sum(nb) as nb, max(id) as id from (select count(*) as nb, tags->'amenity' as v, max(id) as id from nodes WHERE st_contains(st_geomfromewkt('$poly_wgs'),geom) AND tags ? 'amenity' group by tags->'amenity' UNION select count(*) as nb, tags->'amenity' as v, max(id) as id from ways WHERE st_contains(st_geomfromewkt('$poly_wgs'), linestring) AND tags ? 'amenity' group by tags->'amenity') as poi group by v order by v; | |
| $sql = "select count(*) as nb, tags->'$tag' as val, max(id) from nodes where st_contains(st_geomfromewkt('$poly_wgs'), geom) AND tags ? '$tag' group by tags->'$tag' order by tags->'$tag';"; | |
| $sql = "SELECT v AS val, sum(nb) AS nb, max(id) AS id FROM (SELECT count(*) as nb, tags->'$tag' as v, max(id) as id from nodes WHERE st_contains(st_geomfromewkt('$poly_wgs'),geom) AND tags ? '$tag' group by tags->'$tag' UNION select count(*) as nb, tags->'$tag' as v, max(id) as id from ways WHERE st_contains(st_geomfromewkt('$poly_wgs'), linestring) AND tags ? '$tag' group by tags->'$tag') as poi group by v order by v;"; | |
| $r=pg_query($sql); | |
| array($rows); | |
| while ($d = pg_fetch_array($r)) | |
| { | |
| $nb=""; | |
| switch ($d["val"]) { | |
| case "supermarket": | |
| $nb = $bpe["nb_b101"]+$bpe["nb_b102"]; // hypermarché+supermarché | |
| break; | |
| case "doityourself": | |
| $nb = $bpe["nb_b103"]+$bpe["nb_b309"]; // bricolage + droguerie/quincaillerie | |
| break; | |
| case "convenience": | |
| $nb = $bpe["nb_b201"]+$bpe["nb_b202"]; // supérette+épicerie | |
| break; | |
| case "bakery": | |
| $nb = $bpe["nb_b203"]; | |
| break; | |
| case "butcher": | |
| $nb = $bpe["nb_b204"]; | |
| break; | |
| case "newsagent": | |
| $nb = $bpe["nb_b301"]; | |
| break; | |
| case "clothes": | |
| $nb = $bpe["nb_b302"]; | |
| break; | |
| case "shoes": | |
| $nb = $bpe["nb_b304"]; | |
| break; | |
| case "electronics": | |
| $nb = $bpe["nb_b305"]; | |
| break; | |
| case "furniture": | |
| $nb = $bpe["nb_b306"]; | |
| break; | |
| case "jewelry": | |
| $nb = $bpe["nb_b311"]; | |
| break; | |
| case "florist": | |
| $nb = $bpe["nb_b312"]; | |
| break; | |
| case "estate_agent": | |
| $nb = $bpe["nb_a505"]; | |
| break; | |
| case "laundry": | |
| $nb = $bpe["nb_a506"]; | |
| break; | |
| case "hair_dresser": | |
| $nb = $bpe["nb_a501"]; | |
| break; | |
| case "car_repair": | |
| $nb = $bpe["nb_a302"]; | |
| break; | |
| case "driving_school": | |
| $nb = $bpe["nb_a304"]; | |
| break; | |
| } | |
| if ($nb>0) | |
| $rows[] = array(t($d["val"])." (<a href='http://wiki.openstreetmap.org/wiki/Tag:$tag%3D".$d["val"]."'>wiki</a>)", $d["nb"]." / $nb (".round(100*$d["nb"]/$nb,0)."%)"); | |
| else | |
| $rows[] = array(t($d["val"])." (<a href='http://wiki.openstreetmap.org/wiki/Tag:$tag%3D".$d["val"]."'>wiki</a>)", $d["nb"]); | |
| } | |
| $header = array("Type","Nombre"); | |
| echo "<h4>".t($tag)."</h4>"; | |
| print theme('table', array('header' => $header, 'rows' => $rows)); | |
| unset($rows); | |
| $tag="leisure"; | |
| $sql = "select count(*) as nb, tags->'$tag' as val, max(id) from nodes where st_contains(st_geomfromewkt('$poly_wgs'), geom) AND tags ? '$tag' group by tags->'$tag' order by tags->'$tag';"; | |
| $sql = "SELECT v AS val, sum(nb) AS nb, max(id) AS id FROM (SELECT count(*) as nb, tags->'$tag' as v, max(id) as id from nodes WHERE st_contains(st_geomfromewkt('$poly_wgs'),geom) AND tags ? '$tag' group by tags->'$tag' UNION select count(*) as nb, tags->'$tag' as v, max(id) as id from ways WHERE st_contains(st_geomfromewkt('$poly_wgs'), linestring) AND tags ? '$tag' group by tags->'$tag') as poi group by v order by v;"; | |
| $r=pg_query($sql); | |
| array($rows); | |
| while ($d = pg_fetch_array($r)) | |
| { | |
| $rows[] = array(t($d["val"])." (<a href='http://wiki.openstreetmap.org/wiki/Tag:$tag%3D".$d["val"]."'>wiki</a>)", $d["nb"]); | |
| } | |
| $header = array("Type","Nombre"); | |
| echo "<h4>".t($tag)."</h4>"; | |
| print theme('table', array('header' => $header, 'rows' => $rows)); | |
| unset($rows); | |
| $tag="wheelchair"; | |
| $sql = "select count(*) as nb, tags->'$tag' as val, max(id) from ways where st_contains(st_geomfromewkt('$poly_wgs'), linestring) AND tags ? '$tag' group by tags->'$tag' order by tags->'$tag';"; | |
| $sql = "SELECT v AS val, sum(nb) AS nb, max(id) AS id FROM (SELECT count(*) as nb, tags->'$tag' as v, max(id) as id from nodes WHERE st_contains(st_geomfromewkt('$poly_wgs'),geom) AND tags ? '$tag' group by tags->'$tag' UNION select count(*) as nb, tags->'$tag' as v, max(id) as id from ways WHERE st_contains(st_geomfromewkt('$poly_wgs'), linestring) AND tags ? '$tag' group by tags->'$tag') as poi group by v order by v;"; | |
| $r=pg_query($sql); | |
| array($rows); | |
| while ($d = pg_fetch_array($r)) | |
| { | |
| $rows[] = array(t($d["val"])." (<a href='http://wiki.openstreetmap.org/wiki/Tag:$tag%3D".$d["val"]."'>wiki</a>)", $d["nb"]); | |
| } | |
| $header = array("Type","Nombre"); | |
| echo "<h4>Accessibilité</h4>"; | |
| print theme('table', array('header' => $header, 'rows' => $rows)); | |
| unset($rows); | |
| $tag="waterway"; | |
| $sql = "select count(*) as nb, tags->'$tag' as val, max(id) from ways where st_intersects(st_geomfromewkt('$poly_wgs'), linestring) AND tags ? '$tag' group by tags->'$tag' order by tags->'$tag';"; | |
| $sql = "SELECT v AS val, sum(nb) AS nb, max(id) AS id FROM (SELECT count(*) as nb, tags->'$tag' as v, max(id) as id from nodes WHERE st_contains(st_geomfromewkt('$poly_wgs'),geom) AND tags ? '$tag' group by tags->'$tag' UNION select count(*) as nb, tags->'$tag' as v, max(id) as id from ways WHERE st_contains(st_geomfromewkt('$poly_wgs'), linestring) AND tags ? '$tag' group by tags->'$tag') as poi group by v order by v;"; | |
| $r=pg_query($sql); | |
| array($rows); | |
| while ($d = pg_fetch_array($r)) | |
| { | |
| $rows[] = array(t($d["val"])." (<a href='http://wiki.openstreetmap.org/wiki/Tag:$tag%3D".$d["val"]."'>wiki</a>)", $d["nb"]); | |
| } | |
| $header = array("Type","Nombre"); | |
| echo "<h4>".t($tag)."</h4>"; | |
| print theme('table', array('header' => $header, 'rows' => $rows)); | |
| unset($rows); | |
| $tag="railway"; | |
| $sql = "select count(*) as nb, tags->'$tag' as val, max(id) from ways where st_intersects(st_geomfromewkt('$poly_wgs'), linestring) AND tags ? '$tag' group by tags->'$tag' order by tags->'$tag';"; | |
| $sql = "SELECT v AS val, sum(nb) AS nb, max(id) AS id FROM (SELECT count(*) as nb, tags->'$tag' as v, max(id) as id from nodes WHERE st_contains(st_geomfromewkt('$poly_wgs'),geom) AND tags ? '$tag' group by tags->'$tag' UNION select count(*) as nb, tags->'$tag' as v, max(id) as id from ways WHERE st_contains(st_geomfromewkt('$poly_wgs'), linestring) AND tags ? '$tag' group by tags->'$tag') as poi group by v order by v;"; | |
| $r=pg_query($sql); | |
| array($rows); | |
| while ($d = pg_fetch_array($r)) | |
| { | |
| $rows[] = array(t($d["val"])." (<a href='http://wiki.openstreetmap.org/wiki/Tag:$tag%3D".$d["val"]."'>wiki</a>)", $d["nb"]); | |
| } | |
| $header = array("Type","Nombre"); | |
| echo "<h4>".t($tag)."</h4>"; | |
| print theme('table', array('header' => $header, 'rows' => $rows)); | |
| unset($rows); | |
| $tag="historic"; | |
| $sql = "select count(*) as nb, tags->'$tag' as val, max(id) from ways where st_contains(st_geomfromewkt('$poly_wgs'), linestring) AND tags ? '$tag' group by tags->'$tag' order by tags->'$tag';"; | |
| $sql = "SELECT v AS val, sum(nb) AS nb, max(id) AS id FROM (SELECT count(*) as nb, tags->'$tag' as v, max(id) as id from nodes WHERE st_contains(st_geomfromewkt('$poly_wgs'),geom) AND tags ? '$tag' group by tags->'$tag' UNION select count(*) as nb, tags->'$tag' as v, max(id) as id from ways WHERE st_contains(st_geomfromewkt('$poly_wgs'), linestring) AND tags ? '$tag' group by tags->'$tag') as poi group by v order by v;"; | |
| $r=pg_query($sql); | |
| array($rows); | |
| while ($d = pg_fetch_array($r)) | |
| { | |
| $rows[] = array(t($d["val"])." (<a href='http://wiki.openstreetmap.org/wiki/Tag:$tag%3D".$d["val"]."'>wiki</a>)", $d["nb"]); | |
| } | |
| $header = array("Type","Nombre"); | |
| echo "<h4>".t($tag)."</h4>"; | |
| print theme('table', array('header' => $header, 'rows' => $rows)); | |
| unset($rows); | |
| $tag="tourism"; | |
| $sql = "select count(*) as nb, tags->'$tag' as val, max(id) from ways where st_contains(st_geomfromewkt('$poly_wgs'), linestring) AND tags ? '$tag' group by tags->'$tag' order by tags->'$tag';"; | |
| $sql = "SELECT v AS val, sum(nb) AS nb, max(id) AS id FROM (SELECT count(*) as nb, tags->'$tag' as v, max(id) as id from nodes WHERE st_contains(st_geomfromewkt('$poly_wgs'),geom) AND tags ? '$tag' group by tags->'$tag' UNION select count(*) as nb, tags->'$tag' as v, max(id) as id from ways WHERE st_contains(st_geomfromewkt('$poly_wgs'), linestring) AND tags ? '$tag' group by tags->'$tag') as poi group by v order by v;"; | |
| $r=pg_query($sql); | |
| array($rows); | |
| while ($d = pg_fetch_array($r)) | |
| { | |
| $rows[] = array(t($d["val"])." (<a href='http://wiki.openstreetmap.org/wiki/Tag:$tag%3D".$d["val"]."'>wiki</a>)", $d["nb"]); | |
| } | |
| $header = array("Type","Nombre"); | |
| echo "<h4>".t($tag)."</h4>"; | |
| print theme('table', array('header' => $header, 'rows' => $rows)); | |
| unset($rows); | |
| $tag="man_made"; | |
| $sql = "select count(*) as nb, tags->'$tag' as val, max(id) from ways where st_contains(st_geomfromewkt('$poly_wgs'), linestring) AND tags ? '$tag' group by tags->'$tag' order by tags->'$tag';"; | |
| $sql = "SELECT v AS val, sum(nb) AS nb, max(id) AS id FROM (SELECT count(*) as nb, tags->'$tag' as v, max(id) as id from nodes WHERE st_contains(st_geomfromewkt('$poly_wgs'),geom) AND tags ? '$tag' group by tags->'$tag' UNION select count(*) as nb, tags->'$tag' as v, max(id) as id from ways WHERE st_contains(st_geomfromewkt('$poly_wgs'), linestring) AND tags ? '$tag' group by tags->'$tag') as poi group by v order by v;"; | |
| $r=pg_query($sql); | |
| array($rows); | |
| while ($d = pg_fetch_array($r)) | |
| { | |
| $rows[] = array(t($d["val"])." (<a href='http://wiki.openstreetmap.org/wiki/Tag:$tag%3D".$d["val"]."'>wiki</a>)", $d["nb"]); | |
| } | |
| $header = array("Type","Nombre"); | |
| echo "<h4>".t($tag)."</h4>"; | |
| print theme('table', array('header' => $header, 'rows' => $rows)); | |
| unset($rows); | |
| // --------- communes limitrophes --------------- | |
| /* | |
| $sql = "select l.name, l.\"ref:INSEE\" as insee from planet_osm_polygon l JOIN planet_osm_polygon v ON (st_touches(l.way,v.way)) WHERE l.admin_level='8' and v.admin_level='8' and v.\"ref:INSEE\"='$insee' order by name;"; | |
| $r=pg_query($sql); | |
| echo "<h3>Communes limitrophes présentes dans OSM</h3>"; | |
| echo "<ul>"; | |
| while ($d = pg_fetch_array($r)) | |
| { | |
| echo "<li><a href='".$_SERVER["REDIRECT_URL"]."?insee=".$d["insee"]."'>".$d["name"]." (".$d["insee"].")</a></li>"; | |
| } | |
| echo "</ul>"; | |
| */ | |
| $sql = "select l.nom_comm as name, l.insee_com as insee from $curgeofla l JOIN $curgeofla v ON (st_touches(l.wkb_geometry,v.wkb_geometry)) WHERE v.insee_com='$insee' order by name;"; | |
| $r=pg_query($sql); | |
| echo "<h3>Communes limitrophes</h3>"; | |
| echo "<ul>"; | |
| while ($d = pg_fetch_array($r)) | |
| { | |
| echo "<li><a href='".$_SERVER["REDIRECT_URL"]."?insee=".$d["insee"]."'>".$d["name"]." (insee: ".$d["insee"].")</a></li>"; | |
| } | |
| echo "</ul>"; | |
| } | |
| $timing .= round((microtime()-$timer)*1000,1)."ms "; | |
| ?> | |
| <p> | |
| <i>Ces informations sont mises à jour quotidiennement.</i> - <?= $timing ?><p> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment