FloatAppGate/public/core1/SAVVY/geofencearea/AddressToArea.php

<?php

class AddressToArea
{

    public static function get($db, $address_id, $city, $country)
    {
        $message = null;
        $address_id = (int) $address_id;
        $city_id = (int) $city;
        $country_code = pg_escape_string($country);

        $result_areas = [];
        $postal_areas = [];
        $radius_areas = [];
        $plygon_areas = [];

        // Step 1: Load address
        $q = "SELECT * FROM address WHERE id=${address_id}";
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r)) {
            $address = pg_fetch_assoc($r);
        } else {
            return [null, "Address was not found"];
        }

        // Step 2: Load areas for the city and country
        $q = "SELECT * 
              FROM geofence_area 
              WHERE "
                . (!empty($city_id) ? "city_id = ${city_id} AND" : '') . 
                " country='${country_code}'";

        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r)) {
            while ($f = pg_fetch_assoc($r)) {
                if ($f["type"] == "postal") {
                    $postal_areas[] = $f;
                }
                if ($f["type"] == "radius") {
                    $radius_areas[] = $f;
                }
                if ($f["type"] == "polygon") {
                    $plygon_areas[] = $f;
                }
                $raw_areas[] = $f;
            }
        } else {
            return [null, pg_last_error()];
        }
        if (count($postal_areas) < 1 && count($radius_areas) < 1 && count($plygon_areas) < 1) {
            return [null, "No areas defined for the city/country"];
        }

        $result_areas = AddressToArea::getAreasForAddress($db, $address, $postal_areas, $radius_areas, $plygon_areas);

        return [$result_areas, $message];
    }

    public static function getAreasForAddress($db, $address, $postal_areas, $radius_areas, $plygon_areas)
    {
        $result_areas = [];

        // Process postal codes
        if (count($postal_areas) > 0) {
			foreach ($postal_areas as $area) {
				$boundaries = json_decode($area["boundaries"], true);
				$codes = $boundaries["postal_code"];
				// if postal is not empty
				if (!empty($address["postal"])) {
					foreach ($codes as $code) {
						if ($code != "" && $code == substr($address["postal"], 0, strlen($code))) {
							$result_areas[] = $area;
							break;
						}
					}
				
				// if postal is empty
				} else if ($address["latitude"] != 0 && $address["longitude"] != 0) {
					$radius = 10; // 10Km
                    $postals = self::getPostalFromLatLng($db, $address["latitude"], $address["longitude"], $radius);

					$flag = false;
					foreach ($postals as $postal) {
						if ($flag) {
							break;
						}
						foreach ($codes as $code) {
							if ($code != "" && $code == substr($postal, 0, strlen($code))) {
								$result_areas[] = $area;
								$flag = true;
								break;
							}
						}
					}
				}
			}
		}

        // Process radius
        if ($address["latitude"] != 0 && $address["longitude"] != 0 && count($radius_areas) > 0) {
            foreach ($radius_areas as $area) {
                $boundaries = json_decode($area["boundaries"], true);
                if (isset($boundaries['radius'])) {
                    $radius = $boundaries['radius'];

                    // query to check if belongs to a radius area
                    $query = "SELECT ST_DWithin('" . $area['location'] . "', ST_SetSRID(ST_Point (" . $address["longitude"] . ", " . $address["latitude"] . "), 4326)::geography, ${radius}) as iscorrect";
                    $data = pg_fetch_all(pg_query($db, $query));

                    if ($data[0]['iscorrect'] == "t") {
                        $result_areas[] = $area;
                    }
                }
            }
        }

        // Process polygon
        if ($address["latitude"] != 0 && $address["longitude"] != 0 && count($plygon_areas) > 0) {
            foreach ($plygon_areas as $area) {
                $boundaries = json_decode($area["boundaries"], true);
                if (isset($boundaries['polygon'])) {
                    $polygon = $boundaries['polygon'];
                    $polygonCoords = [];
                    foreach ($polygon as $coord) {
                        $polygonCoords[] = "$coord[0] $coord[1]";
                    }
                    $polygonCoordsString = implode(",", $polygonCoords);

                    // query to check if belongs to a polygon area
                    $query = "SELECT ST_Contains(ST_GeomFromText('POLYGON((${polygonCoordsString}))', 4326), ST_SetSRID(ST_Point (" . $address["longitude"] . ", " . $address["latitude"] . "), 4326)) as iscorrect";
                    $data = pg_fetch_all(pg_query($db, $query));

                    if ($data[0]['iscorrect'] == "t") {
                        $result_areas[] = $area;
                    }
                }
            }
        }

        return $result_areas;
	}

	/**
	 * get postal array from latitude, longitude
	 */
    public static function getPostalFromLatLng($db, float $latitude, float $longitude, int $distance_in_km): array
    {
        $earth_radius = 6371;
        $km_per_degree_lat = 111.2;
        $pi = 3.14 / 180;

        $query = " SELECT postal FROM (
                    SELECT 
                      CASE
                        WHEN
                          $latitude = address.latitude AND $longitude = address.longitude THEN 0
                        ELSE
                          $earth_radius * acos(
                            cos( radians( {$latitude}) )
                          * cos( radians( address.latitude  ) )
                          * cos( radians( address.longitude ) - radians( {$longitude} ) )
                          + sin( radians( {$latitude}) )
                          * sin( radians( address.latitude  ) ) )
                        END AS distance
                      , *
                  FROM address
                  WHERE 
                    address.postal IS NOT NULL
                    AND address.latitude BETWEEN " . ($latitude - ($distance_in_km / $km_per_degree_lat)) .
                " AND " . ($latitude + ($distance_in_km / $km_per_degree_lat));

        $delta = round($distance_in_km / ($km_per_degree_lat * COS(deg2rad($longitude))), 10);

        // Bounding box for speed - latitude within range (longtitude to km not linear)
        if (cos($longitude * $pi) > 0) {

            $from = $longitude - $delta;
            $to = $longitude + $delta;

            $query .=
                " AND address.longitude" .
                " BETWEEN " . $from .
                " AND " . $to;

        } else {

            $from = $longitude + $delta;
            $to = $longitude - $delta;

            $query .=
                " AND address.longitude" .
                " BETWEEN " . $from .
                " AND " . $to;

        }

        // distance limit for circle
        $query .= " ) address
                WHERE distance < " . $distance_in_km;

		$rs = pg_query($db, $query);

		if (!$rs) {
      throw new Exception(pg_last_error($db));
		}
	
		return pg_fetch_all($rs) ? array_column(pg_fetch_all($rs), 'postal') : [];
  }
}
// vi:ts=2