GeoDjango va PostGIS: «eng yaqin stansiya» so'rovi qanday quriladi

Tashkent Metro ilovasini ochgan foydalanuvchiga birinchi ko'rinadigan narsa — unga eng yaqin stansiya. Telefon koordinatani yuboradi, backend esa millisekundlarda javob qaytarishi kerak. Bu maqolada o'sha so'rov production'da qanday qurilganini noldan, ishlaydigan kod bilan ko'ramiz.

Nega oddiy lat/lng ustunlari yetmaydi#

Eng sodda yo'l — jadvalga latitude va longitude (ikkita FloatField) qo'yish, keyin Python'da haversine formula bilan masofa hisoblash:

# YOMON yechim: har so'rovda BUTUN jadval Python'ga tortiladi
stations = Station.objects.all()  # full table scan
nearest = min(stations, key=lambda s: haversine(user_lat, user_lng, s.lat, s.lng))

45 ta stansiyada bu "ishlaydi". Lekin bu yondashuv o'lchamga qarab sinadi: indeks yo'q, hisob bazada emas — 10 000 nuqtada har so'rov to'liq skan bo'ladi. To'g'ri joy — hisobni bazaning ichiga olib kirish. PostgreSQL'ning PostGIS kengaytmasi aynan shu uchun: geo-tiplar, geo-funksiyalar va ularga mos GiST indeks.

Eslatma

GiST (Generalized Search Tree) — B-tree "kattaroq-kichikroq" solishtirishga mos kelmaydigan ma'lumotlar (nuqta, poligon, oraliq) uchun indeks turi. "Shu radius ichida nima bor?" degan savolga jadvalni to'liq skan qilmasdan javob beradi.

1-qadam: muhit — PostGIS va geo-kutubxonalar#

PostGIS — PostgreSQL kengaytmasi, GeoDjango esa uning ustidan ishlash uchun tizimda GDAL/GEOS kutubxonalarini talab qiladi.

# Ubuntu/Debian serverda:
sudo apt install postgresql-16-postgis-3 gdal-bin libgdal-dev

# macOS (lokal ishlab chiqish):
brew install postgis gdal

Lokal ishlab chiqishda eng qulayi — tayyor Docker image:

# docker-compose.yml
services:
  db:
    image: postgis/postgis:16-3.4   # oddiy postgres emas — postgis image
    environment:
      POSTGRES_DB: metro
      POSTGRES_USER: metro
      POSTGRES_PASSWORD: metro
    ports:
      - "5432:5432"

Django sozlamalarida ikki narsa o'zgaradi — engine va app:

# config/settings.py
INSTALLED_APPS = [
    # ...
    "django.contrib.gis",  # GeoDjango: geo-maydonlar va geo-so'rovlar
    "apps.metro",
]

DATABASES = {
    "default": {
        # postgresql emas — postgis backend (postgresql'ning ustki qatlami)
        "ENGINE": "django.contrib.gis.db.backends.postgis",
        "NAME": "metro",
        "USER": "metro",
        "PASSWORD": "metro",
        "HOST": "127.0.0.1",
        "PORT": "5432",
    }
}

Bazada kengaytmani bir marta yoqib qo'yamiz — buni migratsiya qilib yozgan to'g'ri (yangi muhitda esdan chiqmaydi):

# apps/metro/migrations/0001_postgis.py
from django.contrib.postgres.operations import CreateExtension
from django.db import migrations


class Migration(migrations.Migration):
    initial = True
    dependencies = []
    operations = [CreateExtension("postgis")]

2-qadam: model — PointField va uning indeksi#

Loyihaning fayl tuzilishi (seriyada shu skeletga qaytamiz):

apps/metro/
├── migrations/
│   └── 0001_postgis.py
├── __init__.py
├── admin.py
├── models.py        ← hozir shu yerdamiz
├── serializers.py
├── views.py
└── tests.py

# apps/metro/models.py
from django.contrib.gis.db import models  # DIQQAT: django.db emas — gis.db


class Line(models.Model):
    name = models.CharField(max_length=64)
    color = models.CharField(max_length=7)  # xaritada chiziq rangi: "#E8442D"

    def __str__(self):
        return self.name


class Station(models.Model):
    class Status(models.TextChoices):
        OPEN = "open", "Ochiq"
        PARTIAL = "partial", "Qisman"
        CLOSED = "closed", "Yopiq"

    line = models.ForeignKey(Line, on_delete=models.PROTECT, related_name="stations")
    name = models.CharField(max_length=128)
    status = models.CharField(max_length=8, choices=Status.choices, default=Status.OPEN)

    # geography=True — masofalar METRDA va Yer sharining egriligi hisobda.
    # srid=4326 — GPS'ning standart koordinata tizimi (WGS 84).
    # spatial_index default True — migratsiya GiST indeksni O'ZI yaratadi.
    location = models.PointField(geography=True, srid=4326)

    def __str__(self):
        return self.name

Eng ko'p uchraydigan xato

Point(x, y) tartibi — (longitude, latitude), ya'ni (uzunlik, kenglik). Kundalik hayotda "lat, lng" deb o'rganganmiz, geo-dunyoda esa teskari: avval x (lng), keyin y (lat). Chalkashsangiz stansiyalaringiz okeanga "ko'chib ketadi" — va hech qanday xato chiqmaydi.

Migratsiya qilamiz va indeks haqiqatan yaratilganini tekshiramiz:

python manage.py makemigrations metro && python manage.py migrate

# psql ichida:
\d metro_station
# ...
# "metro_station_location_id" gist (location)   ← GiST indeks joyida

3-qadam: ma'lumot kiritish#

Stansiyalarni fixture yoki management command bilan yuklaymiz:

# apps/metro/management/commands/seed_stations.py
from django.contrib.gis.geos import Point
from django.core.management.base import BaseCommand

from apps.metro.models import Line, Station

STATIONS = [
    # (liniya, nomi, latitude, longitude) — odatiy "lat, lng" tartibida saqlaymiz,
    # Point yasashda joyida almashtiramiz
    ("Chilonzor", "Mustaqillik maydoni", 41.3123, 69.2787),
    ("Chilonzor", "Amir Temur xiyoboni", 41.3110, 69.2797),
    ("O'zbekiston", "Alisher Navoiy", 41.3230, 69.2278),
]


class Command(BaseCommand):
    def handle(self, *args, **options):
        for line_name, name, lat, lng in STATIONS:
            line, _ = Line.objects.get_or_create(name=line_name, defaults={"color": "#888"})
            Station.objects.update_or_create(
                line=line,
                name=name,
                # mana shu joy: Point(lng, lat) — x birinchi!
                defaults={"location": Point(lng, lat, srid=4326)},
            )
        self.stdout.write(self.style.SUCCESS(f"{len(STATIONS)} ta stansiya yuklandi"))

4-qadam: «eng yaqin stansiya» so'rovi#

Butun maqolaning yuragi — uch qatorlik queryset:

# apps/metro/services.py
from django.contrib.gis.db.models.functions import Distance
from django.contrib.gis.geos import Point
from django.contrib.gis.measure import D

from .models import Station


def nearest_stations(lat: float, lng: float, limit: int = 3, radius_km: float = 5):
    user_point = Point(lng, lat, srid=4326)
    return (
        Station.objects.filter(
            status=Station.Status.OPEN,
            # ST_DWithin'ga aylanadi — GiST indeks ishlaydigan qism shu:
            # radius tashqarisidagi stansiyalar indeksdanoq chiqib ketadi
            location__distance_lte=(user_point, D(km=radius_km)),
        )
        # har qatorga hisoblangan masofa ustuni qo'shiladi (metrda)
        .annotate(distance=Distance("location", user_point))
        .order_by("distance")[:limit]
    )

Bu yerda ikki bosqichli hiyla bor, tushunish muhim:

distance_lte (SQL'da ST_DWithin) — filtr. GiST indeks bilan ishlaydi, 5 km radius tashqarisidagi hamma narsani arzon usulda tashlab yuboradi;
Distance + order_by — saralash. Aniq masofa faqat radius ichida qolgan ozgina nomzod uchun hisoblanadi.

order_by(Distance(...))ni filtrsiz yolg'iz ishlatsangiz, baza baribir har qator uchun masofa hisoblaydi — indeksning foydasi yo'qoladi. Avval toraytirish, keyin saralash.

geography=True nima berdi

geography tipida Distance natijasi to'g'ridan-to'g'ri metrda va hisob Yer sferasi bo'yicha to'g'ri. geometry tipida (default) natija gradusda chiqadi — Toshkent kengligida 1 gradus ≈ 84 km, ya'ni son ma'nosiz. Shahar masshtabidagi masofalar uchun har doim geography=True.

5-qadam: DRF endpoint#

# apps/metro/serializers.py
from rest_framework import serializers

from .models import Station


class NearestStationSerializer(serializers.ModelSerializer):
    line = serializers.CharField(source="line.name")
    distance_m = serializers.SerializerMethodField()

    class Meta:
        model = Station
        fields = ["id", "name", "line", "status", "distance_m"]

    def get_distance_m(self, obj) -> int:
        # annotate'dan kelgan Distance obyekti; mijozga yaxlit metr yetarli
        return round(obj.distance.m)

# apps/metro/views.py
from rest_framework.exceptions import ValidationError
from rest_framework.generics import ListAPIView

from .serializers import NearestStationSerializer
from .services import nearest_stations


class NearestStationsView(ListAPIView):
    serializer_class = NearestStationSerializer

    def get_queryset(self):
        try:
            lat = float(self.request.query_params["lat"])
            lng = float(self.request.query_params["lng"])
        except (KeyError, ValueError):
            raise ValidationError({"detail": "lat va lng son bo'lishi shart"})
        # koordinata chegaralarini ham tekshiramiz — chunki bu tashqi input
        if not (-90 <= lat <= 90 and -180 <= lng <= 180):
            raise ValidationError({"detail": "koordinata chegaradan tashqarida"})
        return nearest_stations(lat, lng)

# apps/metro/urls.py
from django.urls import path

from .views import NearestStationsView

urlpatterns = [
    path("stations/nearest/", NearestStationsView.as_view()),
]

Sinab ko'ramiz:

curl "http://127.0.0.1:8000/api/stations/nearest/?lat=41.3115&lng=69.2790"

[
  {"id": 2, "name": "Amir Temur xiyoboni", "line": "Chilonzor", "status": "open", "distance_m": 88},
  {"id": 1, "name": "Mustaqillik maydoni", "line": "Chilonzor", "status": "open", "distance_m": 96}
]

Indeks ishlayotganini isbotlash#

Ishonch — EXPLAINdan keladi (o'qish usulini PostgreSQL indekslar va EXPLAIN ANALYZE maqolasida batafsil yozganman):

EXPLAIN ANALYZE
SELECT * FROM metro_station
WHERE ST_DWithin(location, ST_MakePoint(69.2790, 41.3115)::geography, 5000);

--  Index Scan using metro_station_location_id on metro_station
--  ^^^^^^^^^^ "Seq Scan" emas — GiST indeks ishlayapti

Test#

# apps/metro/tests.py
import pytest
from django.contrib.gis.geos import Point

from apps.metro.models import Line, Station
from apps.metro.services import nearest_stations


@pytest.fixture
def stations(db):
    line = Line.objects.create(name="Chilonzor", color="#E8442D")
    make = lambda name, lat, lng, **kw: Station.objects.create(  # noqa: E731
        line=line, name=name, location=Point(lng, lat, srid=4326), **kw
    )
    make("Yaqin", 41.3115, 69.2790)
    make("Uzoq", 41.3400, 69.3350)
    make("Yopiq", 41.3116, 69.2791, status=Station.Status.CLOSED)


def test_nearest_sorts_by_distance_and_skips_closed(stations):
    result = list(nearest_stations(41.3115, 69.2790))
    assert [s.name for s in result] == ["Yaqin", "Uzoq"]  # masofa tartibida
    assert "Yopiq" not in {s.name for s in result}  # yopiq chiqmaydi
    assert result[0].distance.m < 50  # deyarli ustida

Xulosa#

Koordinata — FloatField juftligi emas, PointField(geography=True);
«Yaqinlik» so'rovi ikki bosqich: distance_lte (indeks bilan toraytirish) + Distance/order_by (aniq saralash);
Point(lng, lat) — x birinchi; adashsangiz jimgina noto'g'ri ishlaydi;
Indeks bor deb ishonmang — EXPLAIN ANALYZE bilan isbotlang.

Seriyaning keyingi qismida shu stansiyalarga jonli ogohlantirishlarni ulaymiz: Django Channels bilan WebSocket arxitekturasi — ilova ochiq turgan foydalanuvchiga xabar bir soniyada yetib boradi.