1<?php 2 3/** 4 * webtrees: online genealogy 5 * Copyright (C) 2023 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\DB; 23use Fisharebest\Webtrees\FlashMessages; 24use Fisharebest\Webtrees\Gedcom; 25use Fisharebest\Webtrees\I18N; 26use Fisharebest\Webtrees\PlaceLocation; 27use Illuminate\Database\Query\Expression; 28use Illuminate\Support\Collection; 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<object>> 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 fn(object $row): string => implode(Gedcom::PLACE_SEPARATOR, array_filter((array) $row))); 128 129 $all_locations = DB::table('place_location AS p0') 130 ->leftJoin('place_location AS p1', 'p1.id', '=', 'p0.parent_id') 131 ->leftJoin('place_location AS p2', 'p2.id', '=', 'p1.parent_id') 132 ->leftJoin('place_location AS p3', 'p3.id', '=', 'p2.parent_id') 133 ->leftJoin('place_location AS p4', 'p4.id', '=', 'p3.parent_id') 134 ->leftJoin('place_location AS p5', 'p5.id', '=', 'p4.parent_id') 135 ->leftJoin('place_location AS p6', 'p6.id', '=', 'p5.parent_id') 136 ->leftJoin('place_location AS p7', 'p7.id', '=', 'p6.parent_id') 137 ->leftJoin('place_location AS p8', 'p8.id', '=', 'p7.parent_id') 138 ->select([ 139 'p0.place AS part_0', 140 'p1.place AS part_1', 141 'p2.place AS part_2', 142 'p3.place AS part_3', 143 'p4.place AS part_4', 144 'p5.place AS part_5', 145 'p6.place AS part_6', 146 'p7.place AS part_7', 147 'p8.place AS part_8', 148 ]) 149 ->get() 150 ->map(static fn(object $row): string => implode(Gedcom::PLACE_SEPARATOR, array_filter((array) $row))); 151 152 $missing = $all_places->diff($all_locations); 153 154 foreach ($missing as $location) { 155 (new PlaceLocation($location))->id(); 156 } 157 } 158 159 /** 160 * @param int $id 161 * 162 * @return void 163 */ 164 public function deleteRecursively(int $id): void 165 { 166 // Uses on-delete-cascade 167 DB::table('place_location') 168 ->where('id', '=', $id) 169 ->delete(); 170 } 171 172 /** 173 * @param int|null $parent_location_id 174 * @param array<int> $parent_place_ids 175 * 176 * @return void 177 */ 178 public function deleteUnusedLocations(int|null $parent_location_id, array $parent_place_ids): void 179 { 180 if ($parent_location_id === null) { 181 $location_query = DB::table('place_location') 182 ->whereNull('parent_id'); 183 } else { 184 $location_query = DB::table('place_location') 185 ->where('parent_id', '=', $parent_location_id); 186 } 187 188 foreach ($location_query->get() as $location) { 189 $places = DB::table('places') 190 ->whereIn('p_parent_id', $parent_place_ids) 191 ->where('p_place', '=', $location->place) 192 ->get(); 193 194 if ($places->isEmpty()) { 195 FlashMessages::addMessage(I18N::translate('“%s” has been deleted.', e($location->place))); 196 197 DB::table('place_location') 198 ->where('id', '=', $location->id) 199 ->delete(); 200 } else { 201 $place_ids = $places->map(static fn (object $place): int => (int) $place->p_id)->all(); 202 $this->deleteUnusedLocations((int) $location->id, $place_ids); 203 } 204 } 205 } 206 207 /** 208 * Find a list of child places. 209 * How many children does each child place have? How many have co-ordinates? 210 * 211 * @param int|null $parent_id 212 * 213 * @return Collection<int,object> 214 */ 215 public function getPlaceListLocation(int|null $parent_id): Collection 216 { 217 $prefix = DB::connection()->getTablePrefix(); 218 219 $expression = 220 $prefix . 'p1.place IS NOT NULL AND ' . $prefix . 'p1.latitude IS NULL OR ' . 221 $prefix . 'p2.place IS NOT NULL AND ' . $prefix . 'p2.latitude IS NULL OR ' . 222 $prefix . 'p3.place IS NOT NULL AND ' . $prefix . 'p3.latitude IS NULL OR ' . 223 $prefix . 'p4.place IS NOT NULL AND ' . $prefix . 'p4.latitude IS NULL OR ' . 224 $prefix . 'p5.place IS NOT NULL AND ' . $prefix . 'p5.latitude IS NULL OR ' . 225 $prefix . 'p6.place IS NOT NULL AND ' . $prefix . 'p6.latitude IS NULL OR ' . 226 $prefix . 'p7.place IS NOT NULL AND ' . $prefix . 'p7.latitude IS NULL OR ' . 227 $prefix . 'p8.place IS NOT NULL AND ' . $prefix . 'p8.latitude IS NULL OR ' . 228 $prefix . 'p9.place IS NOT NULL AND ' . $prefix . 'p9.latitude IS NULL'; 229 230 $expression = 'CASE ' . $expression . ' WHEN TRUE THEN 1 ELSE 0 END'; 231 232 $query = DB::table('place_location AS p0') 233 ->leftJoin('place_location AS p1', 'p1.parent_id', '=', 'p0.id') 234 ->leftJoin('place_location AS p2', 'p2.parent_id', '=', 'p1.id') 235 ->leftJoin('place_location AS p3', 'p3.parent_id', '=', 'p2.id') 236 ->leftJoin('place_location AS p4', 'p4.parent_id', '=', 'p3.id') 237 ->leftJoin('place_location AS p5', 'p5.parent_id', '=', 'p4.id') 238 ->leftJoin('place_location AS p6', 'p6.parent_id', '=', 'p5.id') 239 ->leftJoin('place_location AS p7', 'p7.parent_id', '=', 'p6.id') 240 ->leftJoin('place_location AS p8', 'p8.parent_id', '=', 'p7.id') 241 ->leftJoin('place_location AS p9', 'p9.parent_id', '=', 'p8.id'); 242 243 if ($parent_id === null) { 244 $query->whereNull('p0.parent_id'); 245 } else { 246 $query->where('p0.parent_id', '=', $parent_id); 247 } 248 249 return $query 250 ->groupBy(['p0.id']) 251 ->select([ 252 'p0.*', 253 new Expression('COUNT(' . $prefix . 'p1.id) AS child_count'), 254 new Expression('SUM(' . $expression . ') AS no_coord'), 255 ]) 256 ->get() 257 ->map(static function (object $row): object { 258 $row->child_count = (int) $row->child_count; 259 $row->no_coord = (int) $row->no_coord; 260 $row->key = mb_strtolower($row->place); 261 262 return $row; 263 }) 264 ->sort(static fn (object $x, object $y): int => I18N::comparator()($x->place, $y->place)); 265 } 266 267 /** 268 * @param float $latitude 269 * 270 * @return string 271 */ 272 public function writeLatitude(float $latitude): string 273 { 274 return $this->writeDegrees($latitude, Gedcom::LATITUDE_NORTH, Gedcom::LATITUDE_SOUTH); 275 } 276 277 /** 278 * @param float $longitude 279 * 280 * @return string 281 */ 282 public function writeLongitude(float $longitude): string 283 { 284 return $this->writeDegrees($longitude, Gedcom::LONGITUDE_EAST, Gedcom::LONGITUDE_WEST); 285 } 286 287 /** 288 * Find all active places that match a location 289 * 290 * @param PlaceLocation $location 291 * 292 * @return array<string> 293 */ 294 private function placeIdsForLocation(PlaceLocation $location): array 295 { 296 $hierarchy = []; 297 298 while ($location->id() !== null) { 299 array_unshift($hierarchy, $location->locationName()); 300 $location = $location->parent(); 301 } 302 303 $place_ids = ['0']; 304 305 foreach ($hierarchy as $place_name) { 306 $place_ids = DB::table('places') 307 ->whereIn('p_parent_id', $place_ids) 308 ->where('p_place', '=', $place_name) 309 ->groupBy(['p_id']) 310 ->pluck('p_id') 311 ->all(); 312 } 313 314 return $place_ids; 315 } 316 317 /** 318 * @param float $degrees 319 * @param string $positive 320 * @param string $negative 321 * 322 * @return string 323 */ 324 private function writeDegrees(float $degrees, string $positive, string $negative): string 325 { 326 $degrees = round($degrees, 5); 327 328 if ($degrees < 0.0) { 329 return $negative . abs($degrees); 330 } 331 332 return $positive . $degrees; 333 } 334} 335