fixStationsWithFCC.php

<?php
    
// Use this program to compare NCM.stations with fcc_amateur.en and to 
// update NCM.stations when there is a difference
// Written: 2021-09-13 

require_once "dbConnectDtls.php";
require_once "geocode.php";     /* added 2017-09-03 */
require_once "GridSquare.php";  /* added 2017-09-03 */

$sql = "
SELECT n.callsign as callsign, 
       n.id, 
       n.Fname, 
       n.Lname,
       n.fccid as ncmfccid,
       n.state as ncmstate,
       f.callsign as fcccallsign,     
       f.fccid as fccfccid,
       f.state as fccstate,
       f.first as first,
       f.last  as last,
       (SELECT MAX(fccid) FROM fcc_amateur.en WHERE callsign = n.callsign) as maxfccid,        
       CONCAT_WS(' ', f.address1, f.city, f.state, f.zip) as address
  FROM ncm.stations n
      ,fcc_amateur.en f
 WHERE n.callsign = f.callsign
  AND (SELECT MAX(fccid) FROM fcc_amateur.en WHERE callsign = n.callsign) 
  AND f.fccid > n.fccid 
  AND n.id < 8000
ORDER BY `n`.`callsign` ASC
LIMIT 20
";

//echo "$sql"; 


$count = 0;		
foreach($db_found->query($sql) as $row) {
$count++;

	$address = $row[address];
	 
	$koords  = geocode("$address");
		$latitude  = $koords[0];
		$longitude = $koords[1];
	
		$county	   = $koords[2];
		$state	   = $koords[3];
			if ($state == '') {
				$state = $row[state];
			}
		$gridd 	   = gridsquare($latitude, $longitude);
		$grid      = "$gridd[0]$gridd[1]$gridd[2]$gridd[3]$gridd[4]$gridd[5]"; 

//echo "<br>$count";
//UPDATE stations SET latlng = GeomFromText(POINT(39.791869,-93.549968)) WHERE callsign = 'kf0evg';

// to update all the latlng values do this
// UPDATE stations SET latlng = GeomFromText(CONCAT('POINT(',latitude,' ',longitude,')'));

// to update only one latlng value tod this
// UPDATE stations SET latlng = POINT(latitude, longitude) WHERE id = 'xxxxxxx';

$sql2 = "UPDATE stations SET Fname      = \"$row[first]\" ,
                             Lname      = \"$row[last]\" ,
                             grid       = '$grid' ,
                             county     = '$county' ,
                             state      = '$state' ,
                             home       = '$latitude,$longitude,$grid,$county,$state' ,
                             fccid      = $row[fccfccid] ,
                             dttm       = NOW() ,
                             latitude   = $latitude , 
                             longitude  = $longitude ,
                             latlng     = GeomFromText('POINT($latitude $longitude)')    
          WHERE id = $row[id];         
";


echo("<br><br>$sql2");


//$db_found->exec($sql2); 


} // End foreach
echo "<br><br>Done --> Count= $count";
?>