1<?php 2 3/** 4 * webtrees: online genealogy 5 * Copyright (C) 2021 webtrees development team 6 * This program is free software: you can redistribute it and/or modify 7 * it under the terms of the GNU General Public License as published by 8 * the Free Software Foundation, either version 3 of the License, or 9 * (at your option) any later version. 10 * This program is distributed in the hope that it will be useful, 11 * but WITHOUT ANY WARRANTY; without even the implied warranty of 12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 13 * GNU General Public License for more details. 14 * You should have received a copy of the GNU General Public License 15 * along with this program. If not, see <https://www.gnu.org/licenses/>. 16 */ 17 18declare(strict_types=1); 19 20namespace Fisharebest\Webtrees\Services; 21 22use Fisharebest\Webtrees\Gedcom; 23use Fisharebest\Webtrees\I18N; 24use Fisharebest\Webtrees\PlaceLocation; 25use Illuminate\Database\Capsule\Manager as DB; 26use Illuminate\Database\Query\Expression; 27use Illuminate\Support\Collection; 28use stdClass; 29 30use function abs; 31use function array_filter; 32use function array_unshift; 33use function implode; 34use function mb_strtolower; 35use function round; 36 37/** 38 * Process geographic data. 39 */ 40class MapDataService 41{ 42 // Location of files to import 43 public const PLACES_FOLDER = 'places/'; 44 45 // Format of CSV files 46 public const CSV_SEPARATOR = ';'; 47 48 /** 49 * @param int $id 50 * 51 * @return PlaceLocation 52 */ 53 public function findById(int $id): PlaceLocation 54 { 55 $hierarchy = []; 56 57 while (true) { 58 $row = DB::table('place_location') 59 ->where('id', '=', $id) 60 ->select(['place', 'parent_id']) 61 ->first(); 62 63 if ($row === null) { 64 return new PlaceLocation(implode(Gedcom::PLACE_SEPARATOR, $hierarchy)); 65 } 66 67 $hierarchy[] = $row->place; 68 $id = $row->parent_id; 69 } 70 } 71 72 /** 73 * Which trees use a particular location? 74 * 75 * @param PlaceLocation $location 76 * 77 * @return array<string,array<stdClass>> 78 */ 79 public function activePlaces(PlaceLocation $location): array 80 { 81 $parents = $this->placeIdsForLocation($location); 82 $children = []; 83 84 $rows = DB::table('places') 85 ->join('gedcom', 'gedcom.gedcom_id', '=', 'p_file') 86 ->join('gedcom_setting', 'gedcom_setting.gedcom_id', '=', 'gedcom.gedcom_id') 87 ->where('setting_name', '=', 'title') 88 ->whereIn('p_parent_id', $parents) 89 ->select(['p_place', 'gedcom_name AS tree_name', 'setting_value AS tree_title', 'p_id']) 90 ->get(); 91 92 foreach ($rows as $row) { 93 $children[mb_strtolower($row->p_place)][] = $row; 94 } 95 96 return $children; 97 } 98 99 /** 100 * Make sure that all places in the genealogy data also exist in the location data. 101 * 102 * @return void 103 */ 104 public function importMissingLocations(): void 105 { 106 $all_places = DB::table('places AS p0') 107 ->leftJoin('places AS p1', 'p1.p_id', '=', 'p0.p_parent_id') 108 ->leftJoin('places AS p2', 'p2.p_id', '=', 'p1.p_parent_id') 109 ->leftJoin('places AS p3', 'p3.p_id', '=', 'p2.p_parent_id') 110 ->leftJoin('places AS p4', 'p4.p_id', '=', 'p3.p_parent_id') 111 ->leftJoin('places AS p5', 'p5.p_id', '=', 'p4.p_parent_id') 112 ->leftJoin('places AS p6', 'p6.p_id', '=', 'p5.p_parent_id') 113 ->leftJoin('places AS p7', 'p7.p_id', '=', 'p6.p_parent_id') 114 ->leftJoin('places AS p8', 'p8.p_id', '=', 'p7.p_parent_id') 115 ->select([ 116 'p0.p_place AS part_0', 117 'p1.p_place AS part_1', 118 'p2.p_place AS part_2', 119 'p3.p_place AS part_3', 120 'p4.p_place AS part_4', 121 'p5.p_place AS part_5', 122 'p6.p_place AS part_6', 123 'p7.p_place AS part_7', 124 'p8.p_place AS part_8', 125 ]) 126 ->get() 127 ->map(static function (stdClass $row): string { 128 return implode(Gedcom::PLACE_SEPARATOR, array_filter((array) $row)); 129 }); 130 131 $all_locations = DB::table('place_location AS p0') 132 ->leftJoin('place_location AS p1', 'p1.id', '=', 'p0.parent_id') 133 ->leftJoin('place_location AS p2', 'p2.id', '=', 'p1.parent_id') 134 ->leftJoin('place_location AS p3', 'p3.id', '=', 'p2.parent_id') 135 ->leftJoin('place_location AS p4', 'p4.id', '=', 'p3.parent_id') 136 ->leftJoin('place_location AS p5', 'p5.id', '=', 'p4.parent_id') 137 ->leftJoin('place_location AS p6', 'p6.id', '=', 'p5.parent_id') 138 ->leftJoin('place_location AS p7', 'p7.id', '=', 'p6.parent_id') 139 ->leftJoin('place_location AS p8', 'p8.id', '=', 'p7.parent_id') 140 ->select([ 141 'p0.place AS part_0', 142 'p1.place AS part_1', 143 'p2.place AS part_2', 144 'p3.place AS part_3', 145 'p4.place AS part_4', 146 'p5.place AS part_5', 147 'p6.place AS part_6', 148 'p7.place AS part_7', 149 'p8.place AS part_8', 150 ]) 151 ->get() 152 ->map(static function (stdClass $row): string { 153 return implode(Gedcom::PLACE_SEPARATOR, array_filter((array) $row)); 154 }); 155 156 $missing = $all_places->diff($all_locations); 157 158 foreach ($missing as $location) { 159 (new PlaceLocation($location))->id(); 160 } 161 } 162 163 /** 164 * Find all active places that match a location 165 * 166 * @param PlaceLocation $location 167 * 168 * @return array<string> 169 */ 170 private function placeIdsForLocation(PlaceLocation $location): array 171 { 172 $hierarchy = []; 173 174 while ($location->id() !== null) { 175 array_unshift($hierarchy, $location->locationName()); 176 $location = $location->parent(); 177 } 178 179 $place_ids = ['0']; 180 181 foreach ($hierarchy as $place_name) { 182 $place_ids = DB::table('places') 183 ->whereIn('p_parent_id', $place_ids) 184 ->where('p_place', '=', $place_name) 185 ->groupBy(['p_id']) 186 ->pluck('p_id') 187 ->all(); 188 } 189 190 return $place_ids; 191 } 192 193 /** 194 * @param int $id 195 */ 196 public function deleteRecursively(int $id): void 197 { 198 // Uses on-delete-cascade 199 DB::table('place_location') 200 ->where('id', '=', $id) 201 ->delete(); 202 } 203 204 /** 205 * Find a list of child places. 206 * How many children does each child place have? How many have co-ordinates? 207 * 208 * @param int|null $parent_id 209 * 210 * @return Collection<object> 211 */ 212 public function getPlaceListLocation(?int $parent_id): Collection 213 { 214 $prefix = DB::connection()->getTablePrefix(); 215 216 $expression = 217 $prefix . 'p1.place IS NOT NULL AND ' . $prefix . 'p1.latitude IS NULL OR ' . 218 $prefix . 'p2.place IS NOT NULL AND ' . $prefix . 'p2.latitude IS NULL OR ' . 219 $prefix . 'p3.place IS NOT NULL AND ' . $prefix . 'p3.latitude IS NULL OR ' . 220 $prefix . 'p4.place IS NOT NULL AND ' . $prefix . 'p4.latitude IS NULL OR ' . 221 $prefix . 'p5.place IS NOT NULL AND ' . $prefix . 'p5.latitude IS NULL OR ' . 222 $prefix . 'p6.place IS NOT NULL AND ' . $prefix . 'p6.latitude IS NULL OR ' . 223 $prefix . 'p7.place IS NOT NULL AND ' . $prefix . 'p7.latitude IS NULL OR ' . 224 $prefix . 'p8.place IS NOT NULL AND ' . $prefix . 'p8.latitude IS NULL OR ' . 225 $prefix . 'p9.place IS NOT NULL AND ' . $prefix . 'p9.latitude IS NULL'; 226 227 $expression = 'CASE ' . $expression . ' WHEN TRUE THEN 1 ELSE 0 END'; 228 229 $query = DB::table('place_location AS p0') 230 ->leftJoin('place_location AS p1', 'p1.parent_id', '=', 'p0.id') 231 ->leftJoin('place_location AS p2', 'p2.parent_id', '=', 'p1.id') 232 ->leftJoin('place_location AS p3', 'p3.parent_id', '=', 'p2.id') 233 ->leftJoin('place_location AS p4', 'p4.parent_id', '=', 'p3.id') 234 ->leftJoin('place_location AS p5', 'p5.parent_id', '=', 'p4.id') 235 ->leftJoin('place_location AS p6', 'p6.parent_id', '=', 'p5.id') 236 ->leftJoin('place_location AS p7', 'p7.parent_id', '=', 'p6.id') 237 ->leftJoin('place_location AS p8', 'p8.parent_id', '=', 'p7.id') 238 ->leftJoin('place_location AS p9', 'p9.parent_id', '=', 'p8.id'); 239 240 if ($parent_id === null) { 241 $query->whereNull('p0.parent_id'); 242 } else { 243 $query->where('p0.parent_id', '=', $parent_id); 244 } 245 246 return $query 247 ->groupBy(['p0.id']) 248 ->orderBy(new Expression($prefix . 'p0.place /*! COLLATE ' . I18N::collation() . ' */')) 249 ->select([ 250 'p0.*', 251 new Expression('COUNT(' . $prefix . 'p1.id) AS child_count'), 252 new Expression('SUM(' . $expression . ') AS no_coord'), 253 ]) 254 ->get() 255 ->map(static function (stdClass $row): stdClass { 256 $row->child_count = (int) $row->child_count; 257 $row->no_coord = (int) $row->no_coord; 258 $row->key = mb_strtolower($row->place); 259 260 return $row; 261 }); 262 } 263 264 /** 265 * @param float $latitude 266 * 267 * @return string 268 */ 269 public function writeLatitude(float $latitude): string 270 { 271 return $this->writeDegrees($latitude, Gedcom::LATITUDE_NORTH, Gedcom::LATITUDE_SOUTH); 272 } 273 274 /** 275 * @param float $longitude 276 * 277 * @return string 278 */ 279 public function writeLongitude(float $longitude): string 280 { 281 return $this->writeDegrees($longitude, Gedcom::LONGITUDE_EAST, Gedcom::LONGITUDE_WEST); 282 } 283 284 /** 285 * @param float $degrees 286 * @param string $positive 287 * @param string $negative 288 * 289 * @return string 290 */ 291 private function writeDegrees(float $degrees, string $positive, string $negative): string 292 { 293 $degrees = round($degrees, 5); 294 295 if ($degrees < 0.0) { 296 return $negative . abs($degrees); 297 } 298 299 return $positive . $degrees; 300 } 301} 302