Float/FloatAppGate
1
0
Fork 0
Code Issues Pull Requests Releases Activity
Files
master
FloatAppGate/public/core1/SAVVY/trips/Geocode.php
T
dev-chiefworks 47f4fad75c Added Other AP
2022-04-26 11:30:34 -04:00

327 lines
13 KiB
PHP
Raw Permalink Blame History

<?php
class Geocode
{
const GPS_BOX_DECIMAL = 10;
const COUNTRY_LIMITS = [
'SG' => null, /* no limit! */
'US' => [
[
'name' => 'San Francisco',
'latitude' => 37.7126152,
'longitude' => -122.1754642,
'radius' => 60000, /* in meters */
],
],
];
public function getAddressById($db, $id)
{
$result = array();
$q = "SELECT a.*,b.timezone AS \"timeZoneId\" FROM address a LEFT JOIN address_timezone b ON (b.id=a.timezone) ";
$q.= " WHERE a.id=".((int)$id);
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
$f["address"] = html_entity_decode ($f["address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
$result = $f;
}
return $result;
}
public function save($db, $result)
{
return Address::save($db, $result);
}
public function checkLatLngByAddress($db, $address, $country = GeocodeApi::DEFAULT_COUNTRY_CODE)
{
syslog(LOG_WARNING, "Geocode::checkLatLngByAddress(\$db,'${address}',$country)");
$db_address = pg_escape_string(strtolower($address));
$db_country = pg_escape_string($country);
$q = "SELECT a.address, a.latitude AS lat, a.longitude AS lng, b.timezone AS location_start_tz, a.postal, a.timezone, a.id, a.country, b.timezone AS \"timeZoneId\", plus_code ";
$q .= " FROM address a LEFT JOIN address_timezone b ON b.id=a.timezone LEFT JOIN address_alias c ON a.id=c.address_id ";
$q .= " WHERE (lower(a.address) = '${db_address}' OR lower(a.plus_code) = '${db_address}' OR lower(c.name) = '${db_address}') ";
$q .= " AND a.country='${db_country}' AND a.geocoding_date IS NOT NULL ORDER BY a.geocoding_date DESC LIMIT 1";
$r = pg_query($db, $q);
syslog(LOG_WARNING, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
$f["timeZoneId"] = $f["location_start_tz"]; // Case is lost on pg_query()
unset($f["location_start_tz"]);
$f["address"] = html_entity_decode ($f["address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
return [$f, null];
}
return [null, pg_last_error($db)];
}
public function getTimezone($db, $timezone)
{
if (is_int($timezone) && $timezone > 0) {
return $timezone;
}
$db_timezone = pg_escape_string($timezone);
$q = "SELECT id FROM address_timezone WHERE timezone='${db_timezone}'";
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
return $f[0];
}
$q = "INSERT INTO address_timezone (timezone) VALUES('${db_timezone}') RETURNING id";
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
return $f[0];
}
return null;
}
public function checkDistanceCache($db, $fromLat, $fromLng, $toLat, $toLng)
{
$db_fromLat = (float) $fromLat;
$db_fromLng = (float) $fromLng;
$db_toLat = (float) $toLat;
$db_toLng = (float) $toLng;
$q = "SELECT distance,duration FROM address_distance_cache WHERE ";
$q .= "start_lat=${db_fromLat} AND start_lng=${db_fromLng} AND end_lat=${db_toLat} AND end_lng=${db_toLng}";
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
return array($f, null);
}
return array(null, pg_last_error($db));
}
public function saveDistanceCache($db, $fromLat, $fromLng, $toLat, $toLng, $data)
{
$db_fromLat = (float) $fromLat;
$db_fromLng = (float) $fromLng;
$db_toLat = (float) $toLat;
$db_toLng = (float) $toLng;
$distance = $data["distance"];
$duration = $data["duration"];
$q = "INSERT INTO address_distance_cache (start_lat,start_lng,end_lat,end_lng,distance,duration) VALUES (";
$q .= "${db_fromLat},${db_fromLng},${db_toLat},${db_toLng},${distance},${duration}) RETURNING id";
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
return array($f, null);
}
return array(null, pg_last_error($db));
}
public function getTransportProvidersByCountry($db, $country)
{
$db_country = pg_escape_string($country);
$q = "SELECT a.id as app_id,a.transport_provider_id,b.name as transport_provider_name,a.country,a.ios_app_id,a.android_app_id,b.timeout,b.retries
FROM transport_provider_apps a, transport_providers b
WHERE a.country='${db_country}' AND b.id=a.transport_provider_id AND b.active=1";
$r = pg_query($db, $q);
if ($r && pg_num_rows($r)) {
$result = [];
while ($f = pg_fetch_assoc($r)) {
$result[] = $f;
}
return array($result, null);
}
return array(null, pg_last_error($db));
}
public function reverseGeocodeCache($db, $address, $lat, $lng, $postal = null, $country = "SG", $timezone = null,$plus_code="")
{
syslog(LOG_WARNING, "Geocode::reverseGeocodeCache(\$db, '$address', $lat, $lng, $postal, $country, $timezone,$plus_code)");
$result = [
"lat" => $lat,
"lng" => $lng,
"address" => $address,
"postal" => $postal,
"country" => $country,
"timeZoneId" => $timezone,
"plus_code" => $plus_code,
];
$address = Address::save($db, $result);
if (!empty($address)) {
syslog(LOG_WARNING, 'Address cached!');
} else {
syslog(LOG_WARNING, 'Failed to cache address!');
}
}
public function reverseGeocode($db, $lat, $lng)
{
syslog(LOG_WARNING, "Geocode::reverseGeocode(\$db, $lat, $lng)");
$db_lat = floatval($lat);
$db_lng = floatval($lng);
// Check our local address table with precisions 5 to 3
for ($i = 5; $i > 2; $i--) {
$q = "SELECT * FROM address WHERE round(latitude,${i})=round(${db_lat},${i}) AND round(longitude,${i})=round(${db_lng},${i}) ";
$q .= " AND latitude<>0 AND longitude<>0 ORDER BY geocoding_date DESC LIMIT 1";
syslog(LOG_WARNING, $q);
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
$f["address"] = html_entity_decode ($f["address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
return [$f, null];
}
}
// Check our local Singapore table with precisions 5 to 3
for ($i = 5; $i > 2; $i--) {
$q = "SELECT * FROM singapore_buildings WHERE round(latitude,${i})=round(${db_lat},${i}) AND round(longitude,${i})=round(${db_lng},${i})";
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
$tz = 1; // Asia/Singapore - since this is "singapore_buildings" table
self::reverseGeocodeCache($db, $f['address'], $db_lat, $db_lng, $f['postal'], 'SG', $tz);
return [$f, null];
}
}
return [null, pg_last_error($db)];
}
public function reverseGeocodeGps($db, $gpsdb, $lat, $lng, $radius = 100)
{
$db_lat = floatval($lat);
$db_lng = floatval($lng);
// Check our gps address table with radius
$r = pg_query_params($gpsdb, self::SELECT_ADDRESS_IN_RADIUS, [
$db_lng,
$db_lat,
$radius,
]);
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
$f["address"] = html_entity_decode ($f["address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
return [$f, null];
}
// Check our gps Singapore table with radius
$r = pg_query_params($gpsdb, self::SELECT_SINGAPORE_BUILDINGS_IN_RADIUS, [
$db_lng,
$db_lat,
$radius,
]);
if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
$f["address"] = html_entity_decode ($f["address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
$q = "SELECT * FROM singapore_buildings WHERE id=" . $f["id"];
$r = pg_query($db, $q);
$d = pg_fetch_assoc($r);
$tz = 1; // Asia/Singapore - since this is "singapore_buildings" table
self::reverseGeocodeCache($db, $f['address'], $db_lat, $db_lng, $d['postal'], 'SG', $tz);
return [$f, null];
}
return [null, pg_last_error($gpsdb)];
}
public function mockGPSLocation($db, $member_id, $lat, $lng, $location = 'default')
{
syslog(LOG_WARNING, "Geocode::mockGPSLocation(\$db, $member_id, $lat, $lng, $location)");
$db_member_id = (int) $member_id;
$db_location = pg_escape_string($location);
$q = "SELECT lat,lng FROM mock_gps_location WHERE member_id=${db_member_id} AND location='${db_location}'";
syslog(LOG_WARNING, $q);
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
$lat = $f[0];
$lng = $f[1];
} else if ($lat == 0 && $lng == 0) {
$lat = 1.2966426;
$lng = 103.7763939;
}
return [$lat, $lng];
}
public function mockGPSCountry($db, $member_id, $country, $location = 'default')
{
syslog(LOG_WARNING, "Geocode::mockGPSCountry(\$db, $member_id, $country, $location)");
$db_member_id = (int) $member_id;
$db_location = pg_escape_string($location);
$q = "SELECT country FROM mock_gps_location WHERE member_id=${db_member_id} AND location='${db_location}'";
syslog(LOG_WARNING, $q);
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
$country = $f[0];
} else if ($country == '') {
$country = 'SG';
}
return $country;
}
public function getCountry($db, $country)
{
$db_country = pg_escape_string(strtolower($country));
$q = "SELECT * FROM country WHERE lower(code)=lower('${db_country}') OR lower(country)=lower('${db_country}') OR lower(short_name)=lower('${db_country}')";
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f = pg_fetch_array($r)) {
return $f;
}
return null;
}
public function getCountryByGPS($db, $location, $country, $defaultCountry) {
syslog(LOG_WARNING,"Geocode::getCountryByGPS(\$db, \$location, $country, $defaultCountry)");
if ($country!=null && strlen(trim($country))==2) {
return $country;
}
if (is_array($location) && array_key_exists('lat',$location) && array_key_exists('lng',$location)
&& $location['lat']!=0 && $location['lat']!=0) {
$fromLat = $location['lat'] - Geocode::GPS_BOX_DECIMAL;
$fromLng = $location['lng'] - Geocode::GPS_BOX_DECIMAL;
$toLat = $location['lat'] + Geocode::GPS_BOX_DECIMAL;
$toLng = $location['lng'] + Geocode::GPS_BOX_DECIMAL;
// First we try address
$baseQuery = "SELECT country FROM %s WHERE ";
$baseQuery.= "(latitude BETWEEN ${fromLat} AND ${toLat}) AND (longitude BETWEEN ${fromLng} AND ${toLng})";
$baseQuery.= " ORDER BY ST_DistanceSphere(%s::geometry, ST_SetSRID(ST_MakePoint(".$location["lng"].",".$location["lat"]."),4326))";
$q = sprintf($baseQuery, "address", "geometry");
$q.= " LIMIT 1";
syslog(LOG_WARNING,$q);
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
$country = $f[0];
} else {
// Then we try geoname
$q = sprintf($baseQuery, "geoname", "location");
$q.= " LIMIT 1";
syslog(LOG_WARNING,$q);
$r = pg_query($db, $q);
if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
$country = $f[0];
}
}
} else {
syslog(LOG_WARNING,"No GPS coordinates supplied!");
}
if ($country==null || strlen(trim($country))!=2) {
return $defaultCountry;
}
return $country;
}
public static function saveGeocodingLog($db, $address, $latitude, $longitude, $address_output,$duration, $client_ip, $client_id)
{
$i = "INSERT INTO geocoding_logs (address_input,latitude,longitude,call_dated,address_output, duration, client_ip,client_id) VALUES ('${address}'," . (!empty($latitude)?$latitude: "NULL") . "," . (!empty($longitude)?$longitude: "NULL") . ",NOW(),'${address_output}',${duration},'${client_ip}','${client_id}') RETURNING id";
$r = pg_query($db, $i);
syslog(LOG_WARNING,$i);
if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
$log_id = $f[0];
}
return $log_id;
}
const SELECT_ADDRESS_IN_RADIUS = "SELECT id,
address,
latitude,
longitude,
ST_DistanceSphere(location, ST_SetSRID(ST_MakePoint($1, $2),4326)) AS distance
FROM address
WHERE
ST_DistanceSphere(location, ST_SetSRID(ST_MakePoint($1, $2),4326)) BETWEEN 0 AND $3
ORDER BY distance";
const SELECT_SINGAPORE_BUILDINGS_IN_RADIUS = "SELECT id,
address,
latitude,
longitude,
ST_DistanceSphere(location, ST_SetSRID(ST_MakePoint($1, $2),4326)) AS distance
FROM singapore_buildings
WHERE
ST_DistanceSphere(location, ST_SetSRID(ST_MakePoint($1, $2),4326)) BETWEEN 0 AND $3
ORDER BY distance";
}
// vi:ts=2
Reference in New Issue View Git Blame Copy Permalink