exec($createQuery)) {
die("Failed to create the 'projects' table.");
}
$db->close();
} else {
$db = new SQLite3($dbFile);
if (!$db) {
die("Database connection failed: " . $db->lastErrorMsg());
}
}
// Function to fetch and display data
function displayData($db) {
$results = '';
$addresses = [];
$query = "SELECT id, erfno, allotment, projectname FROM projects";
$result = $db->query($query);
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$erfNumber = $row['erfno'];
$allotmentArea = $row['allotment'];
$projectname = $row['projectname'];
$province = "";
if ($allotmentArea === "Plettenberg Bay" || $allotmentArea === "Knysna" || $allotmentArea === "Cape Town" || $allotmentArea === "George") {
$province = "WESTERN%20CAPE";
} else {
$province = "EASTERN%20CAPE";
}
if (empty($erfNumber) || empty($allotmentArea)) {
$results = 'ERF number and allotment area are required.';
} else {
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 . "
";
$projectname = str_replace(" ", "|", $projectname);
// Collect address data for map
$addresses[] = $xCoord . ' ' . $yCoord . ' ' . $projectname;
}
} 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 . "
";
$projectname = str_replace(" ", "|", $projectname);
// Collect address data for map
$addresses[] = $xCoord . ' ' . $yCoord . ' ' . $projectname;
}
} else {
// $results .= "No results found:" . $projectname . "
";
}
}
}
}
}
return [$results, $addresses];
}
// Get data and results
list($results, $addresses) = displayData($db);
?>
ERF Number Query