PrepBro
← Назад к вопросам

Справочник организаций с геолокацией

3.0 Senior🔥 71 комментариев
#API и веб-протоколы#Базы данных и SQL#Фреймворки

Условие

Реализовать мини-справочник с адресами, компаниями и зданиями.

Структура данных

  • Здания: адрес, координаты (lat, lng)
  • Компании: название, телефон, часы работы
  • Связь: компания может находиться в нескольких зданиях

API требования

  • CRUD для зданий и компаний
  • Поиск компаний по названию
  • Выборка компаний по радиусу от заданной точки
  • Выборка компаний в заданном квадрате координат

Пример запроса

GET /api/companies?lat=55.75&lng=37.62&radius=1000
GET /api/companies?lat1=55.7&lng1=37.5&lat2=55.8&lng2=37.7

Технологии

PHP 8+, Laravel, PostgreSQL с PostGIS или MySQL

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI23 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

Решение

1. Миграции для таблиц

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration {
    public function up(): void {
        // Таблица зданий
        Schema::create("buildings", function (Blueprint $table) {
            $table->id();
            $table->string("address", 500);
            $table->string("city", 100);
            $table->string("postal_code", 20)->nullable();
            $table->decimal("latitude", 10, 8);
            $table->decimal("longitude", 11, 8);
            $table->text("description")->nullable();
            $table->timestamps();
            // Индекс для быстрого поиска по координатам
            $table->spatialIndex(["latitude", "longitude"]);
        });

        // Таблица компаний
        Schema::create("companies", function (Blueprint $table) {
            $table->id();
            $table->string("name", 255);
            $table->string("phone", 20)->nullable();
            $table->string("email", 255)->nullable();
            $table->time("opening_time")->nullable();
            $table->time("closing_time")->nullable();
            $table->text("description")->nullable();
            $table->boolean("is_active")->default(true);
            $table->timestamps();
            $table->index("name");
            $table->index("is_active");
        });

        // Таблица связи компаний и зданий (many-to-many)
        Schema::create("company_building", function (Blueprint $table) {
            $table->foreignId("company_id")->constrained("companies")->cascadeOnDelete();
            $table->foreignId("building_id")->constrained("buildings")->cascadeOnDelete();
            $table->string("office_number", 50)->nullable();
            $table->timestamps();
            $table->primary(["company_id", "building_id"]);
        });
    }

    public function down(): void {
        Schema::dropIfExists("company_building");
        Schema::dropIfExists("companies");
        Schema::dropIfExists("buildings");
    }
};

2. Модели

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;

class Building extends Model {
    protected $fillable = [
        "address",
        "city",
        "postal_code",
        "latitude",
        "longitude",
        "description",
    ];

    protected $casts = [
        "latitude" => "float",
        "longitude" => "float",
    ];

    public function companies(): BelongsToMany {
        return $this->belongsToMany(Company::class, "company_building")
            ->withPivot("office_number")
            ->withTimestamps();
    }
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;

class Company extends Model {
    protected $fillable = [
        "name",
        "phone",
        "email",
        "opening_time",
        "closing_time",
        "description",
        "is_active",
    ];

    protected $casts = [
        "opening_time" => "datetime:H:i",
        "closing_time" => "datetime:H:i",
        "is_active" => "boolean",
    ];

    public function buildings(): BelongsToMany {
        return $this->belongsToMany(Building::class, "company_building")
            ->withPivot("office_number")
            ->withTimestamps();
    }
}

3. Service для геолокации

<?php

namespace App\Services;

use App\Models\Building;
use App\Models\Company;
use Illuminate\Database\Query\Builder;

class GeoLocationService {
    /**
     * Константа для конвертирования координат в метры на экваторе
     */
    const EARTH_RADIUS_M = 6371000;

    /**
     * Вычисляет расстояние между двумя точками в метрах
     * Использует формулу Хаверсина
     */
    public function getDistance(float $lat1, float $lon1, float $lat2, float $lon2): float {
        $dLat = deg2rad($lat2 - $lat1);
        $dLon = deg2rad($lon2 - $lon1);

        $a = sin($dLat / 2) * sin($dLat / 2) +
             cos(deg2rad($lat1)) * cos(deg2rad($lat2)) *
             sin($dLon / 2) * sin($dLon / 2);

        $c = 2 * atan2(sqrt($a), sqrt(1 - $a));
        return self::EARTH_RADIUS_M * $c;
    }

    /**
     * Поиск компаний в радиусе от заданной точки
     *
     * @param float $lat Широта
     * @param float $lng Долгота
     * @param int $radiusM Радиус в метрах
     * @return \Illuminate\Database\Eloquent\Collection
     */
    public function findCompaniesByRadius(float $lat, float $lng, int $radiusM = 1000) {
        // Примерный расчёт границ для предварительной фильтрации
        $latOffset = ($radiusM / self::EARTH_RADIUS_M) * (180 / M_PI);
        $lngOffset = ($radiusM / self::EARTH_RADIUS_M) * (180 / M_PI) / cos(deg2rad($lat));

        $companies = Company::where("is_active", true)
            ->with("buildings")
            ->get()
            ->filter(function (Company $company) use ($lat, $lng, $radiusM) {
                // Проверяем каждое здание компании
                foreach ($company->buildings as $building) {
                    $distance = $this->getDistance(
                        $lat,
                        $lng,
                        $building->latitude,
                        $building->longitude
                    );

                    if ($distance <= $radiusM) {
                        return true;
                    }
                }

                return false;
            })
            ->values();

        return $companies;
    }

    /**
     * Поиск компаний в квадрате координат
     *
     * @param float $lat1 Широта верхнего левого угла
     * @param float $lng1 Долгота верхнего левого угла
     * @param float $lat2 Широта нижнего правого угла
     * @param float $lng2 Долгота нижнего правого угла
     * @return \Illuminate\Database\Eloquent\Collection
     */
    public function findCompaniesByBoundingBox(float $lat1, float $lng1, float $lat2, float $lng2) {
        $minLat = min($lat1, $lat2);
        $maxLat = max($lat1, $lat2);
        $minLng = min($lng1, $lng2);
        $maxLng = max($lng1, $lng2);

        $companies = Company::where("is_active", true)
            ->with([
                "buildings" => function ($query) use ($minLat, $maxLat, $minLng, $maxLng) {
                    $query->whereBetween("latitude", [$minLat, $maxLat])
                          ->whereBetween("longitude", [$minLng, $maxLng]);
                }
            ])
            ->get()
            ->filter(fn($c) => $c->buildings->count() > 0)
            ->values();

        return $companies;
    }

    /**
     * Поиск компаний по названию
     */
    public function searchCompaniesByName(string $query) {
        return Company::where("is_active", true)
            ->where("name", "like", "%{$query}%")
            ->with("buildings")
            ->get();
    }
}

4. API контроллеры

<?php

namespace App\Http\Controllers\Api;

use App\Models\Building;
use App\Models\Company;
use App\Services\GeoLocationService;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;

class CompanyController extends Controller {
    protected $geoService;

    public function __construct(GeoLocationService $geoService) {
        $this->geoService = $geoService;
    }

    /**
     * Поиск компаний по разным критериям
     */
    public function index(Request $request): JsonResponse {
        // Поиск по радиусу
        if ($request->has(["lat", "lng"])) {
            $radius = $request->get("radius", 1000);
            $companies = $this->geoService->findCompaniesByRadius(
                (float) $request->get("lat"),
                (float) $request->get("lng"),
                (int) $radius
            );

            return response()->json([
                "type" => "radius_search",
                "lat" => $request->get("lat"),
                "lng" => $request->get("lng"),
                "radius_m" => $radius,
                "count" => $companies->count(),
                "data" => $companies,
            ]);
        }

        // Поиск по квадрату координат
        if ($request->has(["lat1", "lng1", "lat2", "lng2"])) {
            $companies = $this->geoService->findCompaniesByBoundingBox(
                (float) $request->get("lat1"),
                (float) $request->get("lng1"),
                (float) $request->get("lat2"),
                (float) $request->get("lng2")
            );

            return response()->json([
                "type" => "bounding_box_search",
                "bounds" => [
                    "lat1" => $request->get("lat1"),
                    "lng1" => $request->get("lng1"),
                    "lat2" => $request->get("lat2"),
                    "lng2" => $request->get("lng2"),
                ],
                "count" => $companies->count(),
                "data" => $companies,
            ]);
        }

        // Поиск по названию
        if ($request->has("search")) {
            $companies = $this->geoService->searchCompaniesByName($request->get("search"));
            return response()->json([
                "type" => "name_search",
                "query" => $request->get("search"),
                "count" => $companies->count(),
                "data" => $companies,
            ]);
        }

        // Все компании
        $companies = Company::where("is_active", true)->with("buildings")->paginate(20);
        return response()->json($companies);
    }

    public function show(Company $company): JsonResponse {
        return response()->json($company->load("buildings"));
    }

    public function store(Request $request): JsonResponse {
        $validated = $request->validate([
            "name" => "required|string|max:255",
            "phone" => "nullable|string|max:20",
            "email" => "nullable|email",
            "opening_time" => "nullable|date_format:H:i",
            "closing_time" => "nullable|date_format:H:i",
            "description" => "nullable|string",
        ]);

        $company = Company::create($validated);
        return response()->json($company, 201);
    }

    public function update(Request $request, Company $company): JsonResponse {
        $validated = $request->validate([
            "name" => "string|max:255",
            "phone" => "nullable|string|max:20",
            "email" => "nullable|email",
            "opening_time" => "nullable|date_format:H:i",
            "closing_time" => "nullable|date_format:H:i",
            "description" => "nullable|string",
        ]);

        $company->update($validated);
        return response()->json($company);
    }

    public function destroy(Company $company): JsonResponse {
        $company->delete();
        return response()->json(["message" => "Company deleted"]);
    }
}

<?php

namespace App\Http\Controllers\Api;

use App\Models\Building;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;

class BuildingController extends Controller {
    public function index(): JsonResponse {
        $buildings = Building::with("companies")->paginate(20);
        return response()->json($buildings);
    }

    public function show(Building $building): JsonResponse {
        return response()->json($building->load("companies"));
    }

    public function store(Request $request): JsonResponse {
        $validated = $request->validate([
            "address" => "required|string|max:500",
            "city" => "required|string|max:100",
            "postal_code" => "nullable|string|max:20",
            "latitude" => "required|numeric|between:-90,90",
            "longitude" => "required|numeric|between:-180,180",
            "description" => "nullable|string",
        ]);

        $building = Building::create($validated);
        return response()->json($building, 201);
    }

    public function update(Request $request, Building $building): JsonResponse {
        $validated = $request->validate([
            "address" => "string|max:500",
            "city" => "string|max:100",
            "postal_code" => "nullable|string|max:20",
            "latitude" => "numeric|between:-90,90",
            "longitude" => "numeric|between:-180,180",
            "description" => "nullable|string",
        ]);

        $building->update($validated);
        return response()->json($building);
    }

    public function destroy(Building $building): JsonResponse {
        $building->delete();
        return response()->json(["message" => "Building deleted"]);
    }

    public function attachCompany(Request $request, Building $building): JsonResponse {
        $validated = $request->validate([
            "company_id" => "required|exists:companies,id",
            "office_number" => "nullable|string|max:50",
        ]);

        $building->companies()->attach($validated["company_id"], [
            "office_number" => $validated["office_number"] ?? null,
        ]);

        return response()->json(["message" => "Company attached"]);
    }
}

5. Маршруты API

<?php

use App\Http\Controllers\Api\BuildingController;
use App\Http\Controllers\Api\CompanyController;
use Illuminate\Support\Facades\Route;

Route::get("/companies", [CompanyController::class, "index"]);
Route::get("/companies/{company}", [CompanyController::class, "show"]);
Route::post("/companies", [CompanyController::class, "store"]);
Route::put("/companies/{company}", [CompanyController::class, "update"]);
Route::delete("/companies/{company}", [CompanyController::class, "destroy"]);

Route::get("/buildings", [BuildingController::class, "index"]);
Route::get("/buildings/{building}", [BuildingController::class, "show"]);
Route::post("/buildings", [BuildingController::class, "store"]);
Route::put("/buildings/{building}", [BuildingController::class, "update"]);
Route::delete("/buildings/{building}", [BuildingController::class, "destroy"]);
Route::post("/buildings/{building}/attach-company", [BuildingController::class, "attachCompany"]);

6. Примеры API запросов

# Поиск компаний в радиусе 1000 метров от точки (55.75, 37.62)
GET /api/companies?lat=55.75&lng=37.62&radius=1000

# Поиск компаний в квадрате координат
GET /api/companies?lat1=55.7&lng1=37.5&lat2=55.8&lng2=37.7

# Поиск по названию
GET /api/companies?search=Москва

# Создание здания
POST /api/buildings
{"address": "Красная площадь, 1", "city": "Москва", "latitude": 55.7539, "longitude": 37.6208}

# Присоединение компании к зданию
POST /api/buildings/1/attach-company
{"company_id": 1, "office_number": "101"}

Это полное решение с геолокацией, поиском по радиусу, по квадрату координат и по названию.