Asosiy kontentga o‘tish

Maqolalar 17 daqiqa 213 ko‘rish

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

Tashkent Metro backend'ida ishlatilgan yondashuv: koordinatalarni PostGIS'da saqlash, GiST indeks bilan millisekundlarda «eng yaqin stansiya»ni topish va buni DRF endpoint qilib berish — to'liq kod bilan.

database api django

Mundarija

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:

Python
# 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.

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

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

macOS'da (ayniqsa Apple Silicon) bitta qo'shimcha qadam bor: Django GDAL'ni Homebrew papkasidan o'zi topolmaydi va Could not find the GDAL library deb yiqiladi. Yo'lni sozlamada aniq ko'rsating (ls $(brew --prefix)/lib/libgdal.dylib bilan fayl joyida ekanini tekshirib oling):

Python
# config/settings.py — faqat macOS lokal muhitda
GDAL_LIBRARY_PATH = "/opt/homebrew/lib/libgdal.dylib"
GEOS_LIBRARY_PATH = "/opt/homebrew/lib/libgeos_c.dylib"

Lokal ishlab chiqishda baza uchun eng qulayi — tayyor Docker image:

YAML
# 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"

Loyiha skeletini yaratamiz va bazani ko'taramiz:

Terminal
uv init --bare
uv add django djangorestframework "psycopg[binary]"
uv add --dev pytest pytest-django
uv run django-admin startproject config .

mkdir -p apps/metro/migrations apps/metro/management/commands
touch apps/__init__.py apps/metro/__init__.py apps/metro/migrations/__init__.py \
      apps/metro/management/__init__.py apps/metro/management/commands/__init__.py

docker compose up -d

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

Python
# config/settings.py
INSTALLED_APPS = [
    # ...
    "django.contrib.gis",  # GeoDjango: geo-maydonlar va geo-so'rovlar
    "rest_framework",
    "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):

Python
# 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):

Fayl tuzilishi
apps/metro/
├── management/
│   └── commands/
│       └── seed_stations.py
├── migrations/
│   └── 0001_postgis.py
├── __init__.py
├── models.py        ← hozir shu yerdamiz
├── services.py
├── serializers.py
├── views.py
├── urls.py
└── tests.py
Python
# 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:

Terminal
uv run python manage.py makemigrations metro && uv run python manage.py migrate

# bazaning psql konsoli (Docker'dagi baza uchun):
docker compose exec db psql -U metro metro
SQL
\d metro_station
--  ...
--  "metro_station_location_19d5697d_id" gist (location)   ← GiST indeks joyida

Indeks nomidagi 19d5697d — Django qo'shadigan xesh, sizda boshqacha bo'lishi mumkin. Muhimi gist (location) qismi.

3-qadam: ma'lumot kiritish#

Stansiyalarni fixture yoki management command bilan yuklaymiz:

Python
# 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"))
Terminal
uv run python manage.py seed_stations
# 3 ta stansiya yuklandi

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

Butun maqolaning yuragi — bitta queryset:

Python
# 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__dwithin=(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:

  1. dwithin (SQL'da ST_DWithin) — filtr. GiST indeks bilan ishlaydi, 5 km radius tashqarisidagi hamma narsani arzon usulda tashlab yuboradi;
  2. 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.

distance_lte emas, dwithin

Nomi o'xshash location__distance_lte=(user_point, D(km=5)) ham xuddi shu stansiyalarni qaytaradi — lekin SQL'da ST_DWithin ga emas, ST_Distance(...) <= 5000 ga aylanadi. Bu ifoda GiST indeksdan foydalana olmaydi: baza har bir qator uchun masofani hisoblab chiqadi. 20 000 stansiyali jadvalda sinab ko'rdim: distance_lte — Seq Scan, 62 ms; dwithin — indeks bilan, 1 ms. Natija bir xil bo'lgani uchun bu xato testda ham, kichik jadvalda ham sezilmaydi.

geography=True nima berdi

geography tipida masofa metrda va Yer shakli hisobga olingan holda o'lchanadi, shuning uchun dwithin ga D(km=5) ni bemalol berasiz va u indeks bilan ishlaydi. geometry tipida (default) SRID 4326 birliklari — gradus: Django dwithin ga metr berishga ruxsat bermaydi (Only numeric values of degree units are allowed...), gradus bilan esa radius buziladi — Toshkent kengligida 1 gradus shimol-janub bo'yicha ≈ 111 km, sharq-g'arb bo'yicha ≈ 84 km. Shahar masshtabidagi "radius ichida eng yaqin" so'rovlari uchun geography=True.

5-qadam: DRF endpoint#

Python
# 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)
Python
# 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)
Python
# apps/metro/urls.py
from django.urls import path

from .views import NearestStationsView

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

Sinab ko'ramiz:

Endpoint api/ ostida ochilishi uchun app URL'larini loyihaga ulaymiz:

Python
# config/urls.py
from django.contrib import admin
from django.urls import include, path

urlpatterns = [
    path("admin/", admin.site.urls),
    path("api/", include("apps.metro.urls")),
]
Terminal
uv run python manage.py runserver

# boshqa terminalda:
curl "http://127.0.0.1:8000/api/stations/nearest/?lat=41.3115&lng=69.2790"
JSON
[
  {"id": 2, "name": "Amir Temur xiyoboni", "line": "Chilonzor", "status": "open", "distance_m": 81},
  {"id": 1, "name": "Mustaqillik maydoni", "line": "Chilonzor", "status": "open", "distance_m": 92},
  {"id": 3, "name": "Alisher Navoiy", "line": "O'zbekiston", "status": "open", "distance_m": 4473}
]

Alisher Navoiy ham ro'yxatda: u 4,5 km uzoqlikda, ya'ni 5 km radius ichida.

Indeks ishlayotganini isbotlash#

Ishonch — EXPLAINdan keladi (o'qish usulini PostgreSQL indekslar va EXPLAIN ANALYZE maqolasida batafsil yozganman). SQL'ni qo'lda yozib tekshirmang — Django aslida qanday so'rov yuborayotganini ko'rish kerak, yuqoridagi distance_lte tuzog'i aynan shu farqda yashiringan. QuerySet'ning o'zidan so'raymiz:

Terminal
uv run python manage.py shell -c "
from apps.metro.services import nearest_stations
print(nearest_stations(41.3115, 69.2790).explain(analyze=True))
"
Natija
Limit  (... rows=3 loops=1)
  ->  Sort  (...)
        Sort Key: (st_distance(location, '0101...'::geography, true))
        ->  Index Scan using metro_station_location_19d5697d_id on metro_station  (...)
              Index Cond: (location && _st_expand('0101...'::geography, '5000'::double precision))
              Filter: (((status)::text = 'open'::text) AND st_dwithin(location, '0101...'::geography, '5000'::double precision, true))

Index Scan — GiST indeks ishlayapti. Stansiyalar ko'paygach reja Bitmap Index Scan ga o'zgarishi mumkin — bu ham indeks. Seq Scan on metro_station chiqsa — indeks ishlatilmayapti.

Test#

pytest'ga Django sozlamalarini va tests.py nomli fayllarni ko'rsatamiz (standart holatda pytest faqat test_*.py ni yig'adi):

TOML
# pyproject.toml
[tool.pytest.ini_options]
DJANGO_SETTINGS_MODULE = "config.settings"
python_files = ["tests.py", "test_*.py"]
Python
# 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.3300, 69.3000)  # ~2,7 km
    make("Yopiq", 41.3116, 69.2791, status=Station.Status.CLOSED)
    make("Radiusdan tashqarida", 41.3400, 69.3350)  # ~5,6 km — 5 km ga kirmaydi


def test_nearest_sorts_by_distance_skips_closed_and_far(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
Terminal
uv run pytest apps/metro/ -q

Xulosa#

  • Koordinata — FloatField juftligi emas, PointField(geography=True);

  • «Yaqinlik» so'rovi ikki bosqich: dwithin (indeks bilan toraytirish) + Distance/order_by (aniq saralash); distance_lte indeksni chetlab o'tadi;

  • Point(lng, lat) — x birinchi; adashsangiz jimgina noto'g'ri ishlaydi;

  • Indeks bor deb ishonmang — QuerySet'ning .explain(analyze=True) bilan isbotlang.

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

17 daqiqa 213 marta o‘qildi