setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Select all rows from the erfnumbers table
// $query2 = "SELECT * FROM erfnumbers";
// $stmt2 = $db2->prepare($query2);
// $stmt2->execute();
// $rows2 = $stmt2->fetchAll(PDO::FETCH_ASSOC);
// Loop through each row in the erfnumbers table
// foreach ($rows2 as $row2) {
// $erfNumber = $row2['erfno'];
// $allotmentArea = $row2['allotment'];
// $projectname = $row2['projectname'];
// $tagX = $row2['TAG_X'];
// $tagY = $row2['TAG_Y'];
// }
//} catch (PDOException $e) {
// echo "Error: " . $e->getMessage();
//}
//}
function displayData2($db1Path, $db2Path) {
try {
// Connect to the 1st database (projects.db)
$db1 = new PDO("sqlite:" . $db1Path);
$db1->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Connect to the 2nd database (erfmapping.db)
$db2 = new PDO("sqlite:" . $db2Path);
$db2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Select all rows from the 'projects' table in projects.db
$query1 = "SELECT erfno, allotment, projectname FROM projects";
$stmt1 = $db1->prepare($query1);
$stmt1->execute();
$rows1 = $stmt1->fetchAll(PDO::FETCH_ASSOC);
$results = '';
$addresses = [];
// Loop through each row of projects.db
foreach ($rows1 as $row1) {
// Prepare a query to check if the row exists in erfnumbers table in erfmapping.db
$query2 = "SELECT COUNT(*) FROM erfnumbers WHERE erfno = :erfno AND allotment = :allotment AND projectname = :projectname";
$stmt2 = $db2->prepare($query2);
$stmt2->bindParam(':erfno', $row1['erfno']);
$stmt2->bindParam(':allotment', $row1['allotment']);
$stmt2->bindParam(':projectname', $row1['projectname']);
$stmt2->execute();
$exists = $stmt2->fetchColumn();
// if the entry doesnt exist in erfmapping.db
if ($exists == 0) {
$erfNumber = $row1['erfno'];
$allotmentArea = $row1['allotment'];
$projectname = $row1['projectname'];
$province = "";
if ($allotmentArea === "Plettenberg Bay" || $allotmentArea === "Knysna") {
$province = "WESTERN%20CAPE";
} else {
$province = "EASTERN%20CAPE";
}
if (empty($erfNumber) || empty($allotmentArea)) {
$results = 'ERF number and allotment area are required.';
} else {
// echo "";
if (strpos($erfNumber, "&") !== false) {
//echo "The string contains an ampersand (&).";
$trimmedString = trim($erfNumber);
// Split the string into parts at each "&"
$parts = explode("&", $trimmedString);
// Trim spaces around each part
$parts = array_map('trim', $parts);
// Loop through each part
foreach ($parts as $part) {
$lowercaseAlotment = strtoupper($allotmentArea);
$noSpaceAllotment = str_replace(" ", "%20", $lowercaseAlotment);
$url = 'https://csggis.drdlr.gov.za/server/rest/services/CSGSearch/MapServer/2/query?f=json&where=(UPPER(PROVINCE)%20%3D%20%27'. $province. '%27)%20AND%20(UPPER(MIN_REGION)%20%3D%20%27' . $noSpaceAllotment . '%27)%20AND%20(PARCEL_NO%20%3D%20' . $part . ')&returnGeometry=true&spatialRel=esriSpatialRelIntersects&outFields=OBJECTID%2CGID%2CPRCL_KEY%2CPRCL_TYPE%2CLSTATUS%2CWSTATUS%2CGEOM_AREA%2CCOMMENTS%2CTAG_X%2CTAG_Y%2CTAG_VALUE%2CTAG_SIZE%2CTAG_ANGLE%2CTAG_JUST%2CID%2CDATE_STAMP%2CDATE_PROCESSED%2CSHAPE_Length%2CSHAPE_Area%2CPROVINCE%2CMAJ_REGION%2CMAJ_CODE%2CMIN_REGION%2CMIN_CODE%2CPARCEL_NO%2CPORTION%2CSS_NAME%2CDSG_NO%2CSR_NO%2CSS_NO&outSR=102100&resultOffset=0&resultRecordCount=1000';
// Fetch the JSON response
$json = file_get_contents($url);
// Decode the JSON into a PHP array
$data = json_decode($json, true);
// Check if the JSON decoded properly
if ($data === null) {
// $results .= 'Error parsing JSON
';
} else {
// Extract specific data (based on the structure of the JSON)
if (isset($data['features'])) {
foreach ($data['features'] as $feature) {
// Print out relevant fields
$xCoord = $feature['attributes']['TAG_X'];
$xCoord = str_replace(",", "", $xCoord);
$yCoord = $feature['attributes']['TAG_Y'];
$yCoord = str_replace(",", "", $yCoord);
//$results .= "Erf Number:" . $part . ", " . $allotmentArea . "
";
$allotmentArea = str_replace(" ", "|", $allotmentArea);
$projectname = str_replace(" ", "|", $projectname);
// Collect address data for map
$addresses[] = $xCoord . ' ' . $yCoord . ' ' . $projectname . ' ' . $allotmentArea . ' ' . $part;
}
} else {
// $results .= "No results found:" . $projectname . "
";
}
}
}
} else {
$lowercaseAlotment = strtoupper($allotmentArea);
$noSpaceAllotment = str_replace(" ", "%20", $lowercaseAlotment);
$url = 'https://csggis.drdlr.gov.za/server/rest/services/CSGSearch/MapServer/2/query?f=json&where=(UPPER(PROVINCE)%20%3D%20%27'. $province. '%27)%20AND%20(UPPER(MIN_REGION)%20%3D%20%27' . $noSpaceAllotment . '%27)%20AND%20(PARCEL_NO%20%3D%20' . $erfNumber . ')&returnGeometry=true&spatialRel=esriSpatialRelIntersects&outFields=OBJECTID%2CGID%2CPRCL_KEY%2CPRCL_TYPE%2CLSTATUS%2CWSTATUS%2CGEOM_AREA%2CCOMMENTS%2CTAG_X%2CTAG_Y%2CTAG_VALUE%2CTAG_SIZE%2CTAG_ANGLE%2CTAG_JUST%2CID%2CDATE_STAMP%2CDATE_PROCESSED%2CSHAPE_Length%2CSHAPE_Area%2CPROVINCE%2CMAJ_REGION%2CMAJ_CODE%2CMIN_REGION%2CMIN_CODE%2CPARCEL_NO%2CPORTION%2CSS_NAME%2CDSG_NO%2CSR_NO%2CSS_NO&outSR=102100&resultOffset=0&resultRecordCount=1000';
// Fetch the JSON response
$json = file_get_contents($url);
// Decode the JSON into a PHP array
$data = json_decode($json, true);
// Check if the JSON decoded properly
if ($data === null) {
// $results .= 'Error parsing JSON
';
} else {
// Extract specific data (based on the structure of the JSON)
if (isset($data['features'])) {
foreach ($data['features'] as $feature) {
// Print out relevant fields
// $area = number_format($feature['attributes']['Shape_Area'], 2);
// $zoning = $feature['attributes']['ZoningScheme'];
// $zoning = str_replace(" Scheme", "", $zoning);
$xCoord = $feature['attributes']['TAG_X'];
$xCoord = str_replace(",", "", $xCoord);
$yCoord = $feature['attributes']['TAG_Y'];
$yCoord = str_replace(",", "", $yCoord);
// $results .= "projectname:" . $projectname . "
";
$allotmentArea = str_replace(" ", "|", $allotmentArea);
$projectname = str_replace(" ", "|", $projectname);
// Collect address data for map
$addresses[] = $xCoord . ' ' . $yCoord . ' ' . $projectname . ' ' . $allotmentArea . ' ' . $erfNumber;
}
} else {
// $results .= "No results found:" . $projectname . "
";
}
}
}
}
}
}
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
return [$results, $addresses];
}
// Get data and results
list($results, $addresses) = displayData2($db1Path, $db2Path);
?>
ERF Number Query
ERF Number Query