dev-chiefworks
109 lines
3.2 KiB
PHP
109 lines
3.2 KiB
PHP
<?php
|
|
|
|
class GeofenceAreaCityModel {
|
|
|
|
public static function get($db, $params) {
|
|
$query = "SELECT geofence_city.latitude,
|
|
geofence_city.longitude,
|
|
geofence_city.id,
|
|
geofence_city.location,
|
|
geofence_city.city,
|
|
geofence_city.country,
|
|
geofence_city.radius,
|
|
settings.status,
|
|
settings.geofence_area_city,
|
|
settings.image_url
|
|
FROM geofence_area_city as geofence_city LEFT JOIN geofence_area_city_settings as settings ON settings.geofence_area_city = geofence_city.id";
|
|
|
|
if ($params && count($params) > 0) {
|
|
$lat = $params['lat'] ?? '';
|
|
$lng = $params['lng'] ?? '';
|
|
$radius = $params['radius'] ?? '';
|
|
$whereQuery = [];
|
|
|
|
$city = $params['city'] ?? '';
|
|
$country = $params['country'] ?? '';
|
|
$status = $params['status'] ?? '';
|
|
|
|
if (!empty($status)) {
|
|
$whereQuery[] = "settings.status=$status";
|
|
}
|
|
if (!empty($city)) {
|
|
$whereQuery[] = "LOWER(geofence_city.city)='" . pg_escape_string($city) ."'";
|
|
}
|
|
if (!empty($country)) {
|
|
$whereQuery[] = "LOWER(geofence_city.country)='" . pg_escape_string($country) . "'";
|
|
}
|
|
|
|
$whereQuery = implode(" AND ", $whereQuery);
|
|
|
|
if (!empty($lat) && !empty($lng) && !empty($radius)) {
|
|
$earth_radius = 6371;
|
|
$km_per_degree_lat = 111.2;
|
|
$pi = 3.14/180;
|
|
$latitude = $params["lat"];
|
|
$longitude = $params["lng"];
|
|
$distance_in_km = $params["radius"];
|
|
|
|
$query = " SELECT geofence_city.latitude, geofence_city.longitude, geofence_city.id, geofence_city.location, geofence_city.city, geofence_city.country, geofence_city.radius, settings.status, settings.geofence_area_city, settings.image_url FROM (
|
|
SELECT (
|
|
$earth_radius * acos(
|
|
cos( radians( {$latitude}) )
|
|
* cos( radians( latitude ) )
|
|
* cos( radians( longitude ) - radians( {$longitude} ) )
|
|
+ sin( radians( {$latitude}) )
|
|
* sin( radians( latitude ) ))
|
|
) AS distance,
|
|
id,
|
|
city,
|
|
country,
|
|
latitude,
|
|
longitude,
|
|
location,
|
|
radius
|
|
FROM geofence_area_city
|
|
WHERE 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 longitude" .
|
|
" BETWEEN " . $from .
|
|
" AND " . $to;
|
|
|
|
} else {
|
|
|
|
$from = $longitude + $delta;
|
|
$to = $longitude - $delta;
|
|
|
|
$query .=
|
|
" AND longitude" .
|
|
" BETWEEN " . $from .
|
|
" AND " . $to;
|
|
}
|
|
|
|
// distance limit for circle
|
|
$query .= " ) geofence_city JOIN geofence_area_city_settings AS settings ON geofence_city.id = settings.geofence_area_city WHERE distance < " . $distance_in_km . "AND $whereQuery " . "ORDER BY distance ASC";
|
|
} else {
|
|
$query .= " WHERE $whereQuery";
|
|
}
|
|
}
|
|
|
|
$r = pg_query($db, $query);
|
|
if ($r && pg_num_rows($r)) {
|
|
$result = pg_fetch_all($r);
|
|
} else {
|
|
return [NULL, "City not found"];
|
|
}
|
|
|
|
return [$result, NULL];
|
|
}
|
|
}
|