eligibilite/ajax/db.php

<?php
  ini_set('display_errors', 1);
  ini_set('display_startup_errors', 1);
  ini_set('extension', 'pgsql');
  error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE);

  $db = "host=clusterpg.linux.infra.cloud.local port=5432 dbname=sig50 user=sigr password=T38Msh2R4q";

  // Connexion au service
  $conn = pg_connect($db) or die('Connexion impossible : ' . pg_last_error());

  $in_srid = "4326";
  $pg_srid = "3949";
  
  // Execute query
  function executeQuery($query) {
    $res = [];
    $r = pg_query($query) or die('Échec de la requête : ' . pg_last_error());

    while ($row = pg_fetch_array($r, null, PGSQL_ASSOC)) {
        $res[] = $row;
    }
    pg_free_result($r);
    return $res;
  }

  // Get parameters
  if(isset($_POST["x"]) && isset($_POST["y"])) {
    $x = floatval($_POST["x"]);
    $y = floatval($_POST["y"]);
  }
  else if(isset($_GET["x"]) && isset($_GET["y"])) {
    $x = floatval($_GET["x"]);
    $y = floatval($_GET["y"]);
  }
  else {
    // $x = -1.109669152209201;
    // $y = 49.102353729303125;
    echo 'Missing parameters (x and/or y)';
  }

  // Append X,Y to results
  // NB: CRS is EPSG:3857
  $results['x'] = $x;
  $results['y'] = $y;

  // Get FTTH results
  $queryFtth = "Select
                  ftth.id_zapm_partielle,
                  ftth.phase_trav,
                  ftth.date_deb_trav_prev,
                  ftth.date_deb_trav_reel,
                  ftth.date_ouv_bul_preracco,
                  ftth.date_ferm_bul_preracco,
                  ftth.date_abonnement_prev,
                  ftth.date_ouv_commercialisation
                FROM
                  sig_ant.v_ftth_smmn_eligibilite ftth
                WHERE
                  ST_Contains(ftth.geom, ST_Transform(ST_SetSRID(ST_MakePoint($x, $y), $in_srid), $pg_srid)) = 'true';";
  $results['ftth'] = executeQuery($queryFtth);

  // Get ADSL results
  $queryAdsl = "Select adsl.id_num , adsl.classedebi, adsl.classe_legende
                From sig_ant.adsl_mthd_opt_no_3mo adsl 
                Where ST_Contains(adsl.geom, ST_Transform(ST_SetSRID(ST_MakePoint($x, $y), $in_srid), $pg_srid)) = 'true';";
  $results['adsl'] = executeQuery($queryAdsl);

  // Get MIMO results
  $queryMimo = "Select mimo.id_num AS id_site_mimo, mimo.date_ouv_service_reel, mimo.date_ouv_service_prev 
                From sig_ant.mimo_antc_couverture_6km mimo
                Where ST_Contains(mimo.geom, ST_Transform(ST_SetSRID(ST_MakePoint($x, $y), $in_srid), $pg_srid)) = 'true';";
  $results['mimo'] = executeQuery($queryMimo);

  // Ferme la connexion
  pg_close($conn);

  echo json_encode($results);

  /*
  les requetes pour FTTH et ADSL passent sur des vues
  un boolean dans la view dit si zone ouverte ou pas au preraco
  date d'abonnement previsionel au semestre (ald mois)

  1) regarder adresse.gouv.data
  2) adresse gouville 150 points avec même X, Y
  3) Bug IE11 5 rue des croix, saint come du mont => 50500 ca marche!
  */
?>