PostGIS — geografik ma'lumotlar, location based queries

~25 daq · PostgreSQL · Extensions

Yaqin atrofdagi restoranlarni ko'rsatish kerak. Foydalanuvchi koordinatalari bor — 41.2995, 69.2401. 2 km doiradagi restoranlar kerak. Oddiy SQL bilan bu murakkab — lekin PostGIS bilan bir so'rov.

PostGIS o'rnatish

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology; -- qo'shimcha

SELECT PostGIS_Version(); -- 3.x.x

Asosiy turlar

POINT      — nuqta (lon, lat)
LINESTRING — chiziq
POLYGON    — ko'pburchak (maydon)
GEOMETRY   — istalgan geometrik tur
GEOGRAPHY  — yer shari yuzasidagi (metrda hisoblash)

Jadval yaratish

CREATE TABLE restoranlar (
  id       SERIAL PRIMARY KEY,
  nomi     TEXT,
  manzil   TEXT,
  koordinata GEOGRAPHY(POINT, 4326) -- 4326 = WGS84 (GPS)
);

Ma'lumot qo'shish:

INSERT INTO restoranlar (nomi, manzil, koordinata) VALUES
  ('Café Milano',   'Navoiy ko''cha',   ST_MakePoint(69.2401, 41.2995)::geography),
  ('Samarkand Osh', 'Amir Temur xiyob', ST_MakePoint(69.2800, 41.3100)::geography),
  ('Pizza House',   'Chilonzor',        ST_MakePoint(69.2150, 41.2850)::geography);

Yaqin joylar — ST_DWithin

-- Toshkent markazidan 2 km doiradagi restoranlar
SELECT nomi, manzil,
  ST_Distance(koordinata, ST_MakePoint(69.2401, 41.2995)::geography) AS masofa
FROM restoranlar
WHERE ST_DWithin(
  koordinata,
  ST_MakePoint(69.2401, 41.2995)::geography,
  2000  -- 2000 metr = 2 km
)
ORDER BY masofa;

 nomi          | manzil         | masofa
───────────────   ──────────────   ───────
 Café Milano   | Navoiy ko'cha  | 0
 Samarkand Osh | Amir Temur     | 1823

Masofa hisoblash — ST_Distance

SELECT
  nomi,
  round(ST_Distance(
    koordinata,
    ST_MakePoint(69.2401, 41.2995)::geography
  )::numeric) AS masofa_metr
FROM restoranlar
ORDER BY masofa_metr;

GiST Index — tez qidiruv

Geografik qidiruv uchun GiST index:

CREATE INDEX idx_restoranlar_geo ON restoranlar USING GIST (koordinata);

Indexsiz million qatorda ST_DWithin juda sekin. Index bilan — millisekundlar.

Boshqa foydali funksiyalar

-- Koordinatadan matn
ST_AsText(koordinata)           -- 'POINT(69.24 41.30)'
ST_AsGeoJSON(koordinata)        -- GeoJSON format

-- Maydon hisoblash
ST_Area(polygon::geography)     -- metr kvadrat

-- Chegarada ekanligini tekshirish
ST_Contains(shahar_chegarasi, nuqta)

-- Masofaga qarab tartiblash
ORDER BY koordinata <-> ST_MakePoint(69.24, 41.30)::geography

Tez-tez uchraydigan xatolar

lon va lat tartibini almashtirish:

-- ❌ — (lat, lon) — noto'g'ri
ST_MakePoint(41.2995, 69.2401)

-- ✅ — (lon, lat) — to'g'ri tartib
ST_MakePoint(69.2401, 41.2995)

PostGIS da: ST_MakePoint(longitude, latitude) — uzunlik avval, kenglik keyin.

GEOMETRY vs GEOGRAPHY:

GEOMETRY  — tekis koordinat tizimi, daraja hisoblaydi, tez
GEOGRAPHY — yer shari yuzasi, metr hisoblaydi, to'g'ri

Odatda GEOGRAPHY ishlatilsin — metr bilan ishlash mantiqli

Xulosa

-- O'rnatish
CREATE EXTENSION postgis;

-- Jadval
koordinata GEOGRAPHY(POINT, 4326)

-- Ma'lumot
ST_MakePoint(lon, lat)::geography

-- Qidiruv
ST_DWithin(koordinata, markaz, metr)  -- doirada
ST_Distance(a, b)                      -- masofa
ST_Contains(maydon, nuqta)             -- ichida

-- Index
CREATE INDEX ON jadval USING GIST (koordinata);

Keyingi dars

60-dars: pg_trgm — fuzzy search, ILIKE tezlashtirish