Usługi wyszukiwania i lokalizacji geoprzestrzennej w PostGIS
Wyszukiwanie według lokalizacji to serce każdej platformy nieruchomości: 73% kupujących wskazuje lokalizację jako czynnik decydujący numer jeden. To znaczy zaimplementuj wyszukiwanie geoprzestrzenne w tym samym czasie szybko, dokładny e elastyczny wymaga znacznie więcej niż zwykły filtr miejski. W tym artykule zbudujemy system usług lokalizacyjnych całkowicie używać PocztaGIS na PostgreSQL, z zaawansowanym indeksowaniem przestrzennym i wyszukiwaniem bliskości poprzez H3, zapytania wielokątne o niestandardowe dzielnice i integrację z interaktywnymi mapami.
Dzięki zoptymalizowanemu indeksowaniu przestrzennemu PostGIS osiąga imponującą wydajność: 15-25 ms dla zapytań o punkt w promieniu, 8-12 ms w przypadku wyszukiwania najbliższego sąsiada, np 30-50 ms dla złożonych przecięć wielokątów w zbiorach danych milionów właściwości.
Czego się nauczysz
- Instalacja i konfiguracja PostGIS w PostgreSQL dla danych dotyczących nieruchomości
- Modelowanie geoprzestrzenne: punkty, wielokąty, geometrie osiedli i obszarów
- Indeksowanie przestrzenne za pomocą GIST i BRIN dla optymalnej wydajności
- Wyszukiwanie w pobliżu: ST_DWithin, ST_Distance, K-najbliższy sąsiad
- Zapytania wielokątne dla niestandardowych wyszukiwań „w mojej okolicy”.
- H3 (sześciokątny hierarchiczny indeks przestrzenny Ubera) dla agregacji
- Integracja z MapLibre GL JS i OpenStreetMap
- RESTful API z Express.js dla frontendu nieruchomości
PostGIS: Geoprzestrzenne rozszerzenie PostgreSQL
PocztaGIS dodaje typy danych, funkcje i operatory geoprzestrzenne do PostgreSQL, przekształcając go w kompletny System Informacji Geograficznej (GIS). Dla platform nieruchomości, PostGIS i wyboru standard: zarządza zarówno geometrią płaską (współrzędne rzutowane w metrach), jak i geometrią geograficzną (szerokość/długość geograficzna) z krzywizną ziemi), z funkcjami, które obejmują każdy przypadek użycia nieruchomości.
-- Abilitazione PostGIS e estensioni correlate
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
CREATE EXTENSION IF NOT EXISTS address_standardizer;
CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder;
-- Verifica installazione
SELECT PostGIS_Version();
-- OUTPUT: 3.4.2 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
-- Schema per dati immobiliari geospaziali
CREATE TABLE properties (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
title TEXT NOT NULL,
price NUMERIC(15,2) NOT NULL,
price_sqm NUMERIC(10,2) GENERATED ALWAYS AS (price / square_meters) STORED,
square_meters NUMERIC(8,2) NOT NULL,
rooms SMALLINT NOT NULL,
bathrooms SMALLINT NOT NULL,
property_type VARCHAR(50) NOT NULL, -- 'apartment', 'house', 'villa', 'commercial'
listing_type VARCHAR(20) NOT NULL, -- 'sale', 'rent'
status VARCHAR(20) NOT NULL DEFAULT 'active', -- 'active', 'sold', 'rented'
-- Dati geospaziali - GEOMETRY(Point, 4326) usa coordinate lat/lon standard
location GEOMETRY(Point, 4326) NOT NULL,
address JSONB NOT NULL, -- {"street": "...", "city": "...", "zip": "..."}
agent_id UUID REFERENCES agents(id),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Indice spaziale GIST (obbligatorio per performance)
CREATE INDEX idx_properties_location
ON properties USING GIST(location);
-- Indici aggiuntivi per query composite
CREATE INDEX idx_properties_type_status
ON properties(property_type, status, listing_type);
CREATE INDEX idx_properties_price
ON properties(price) WHERE status = 'active';
Wyszukiwanie w pobliżu: znajdowanie nieruchomości w promieniu
Najczęstszy przypadek użycia: „pokaż mi apartamenty w promieniu 2 km od tej lokalizacji”.
ST_DWithin w kolumnach GEOGRAFIA (nie GEOMETRIA) automatycznie używa metrów jako jednostek
i uwzględnia krzywiznę Ziemi, zapewniając dokładność nawet na dużych dystansach.
-- Ricerca proprietà nel raggio con filtri multipli
-- ST_DWithin con GEOGRAPHY usa metri (più accurata di GEOMETRY per distanze reali)
-- Converti la colonna location in GEOGRAPHY per calcolo corretto
-- Oppure usa ST_DWithin con distanza in gradi (approx) su GEOMETRY
-- Esempio 1: Appartamenti in vendita entro 2km da Piazza Navona (Roma)
SELECT
p.id,
p.title,
p.price,
p.rooms,
p.square_meters,
ST_Distance(
p.location::geography,
ST_MakePoint(12.4731, 41.8991)::geography
) AS distance_meters,
ST_AsGeoJSON(p.location) AS geojson
FROM properties p
WHERE
p.status = 'active'
AND p.listing_type = 'sale'
AND p.property_type = 'apartment'
AND ST_DWithin(
p.location::geography,
ST_MakePoint(12.4731, 41.8991)::geography, -- lon, lat
2000 -- 2000 metri = 2km
)
AND p.rooms >= 2
AND p.price BETWEEN 200000 AND 500000
ORDER BY distance_meters ASC
LIMIT 50;
-- Esempio 2: K-Nearest Neighbor (le 10 più vicine)
-- Usa l'operatore <-> per KNN che sfrutta l'indice GIST in modo efficiente
SELECT
p.id,
p.title,
p.price,
p.location <-> ST_MakePoint(12.4731, 41.8991)::geometry AS distance_deg
FROM properties p
WHERE p.status = 'active'
ORDER BY distance_deg ASC
LIMIT 10;
Zapytania wielokątne: sąsiedztwa i strefy niestandardowe
Wyszukiwanie po zdefiniowanym obszarze (narysowanym przez użytkownika na mapie lub predefiniowanym jako okolica) wymaga
ST_Within o ST_Intersects. Realizujemy także strefy zainteresowań
(szkoły, parki, transport) z zapytaniami do bufora.
-- Tabella quartieri con poligoni geospaziali
CREATE TABLE neighborhoods (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(200) NOT NULL,
city VARCHAR(100) NOT NULL,
slug VARCHAR(200) UNIQUE NOT NULL,
boundary GEOMETRY(Polygon, 4326) NOT NULL, -- poligono del quartiere
metadata JSONB, -- {"population": 15000, "avg_price_sqm": 3500}
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_neighborhoods_boundary
ON neighborhoods USING GIST(boundary);
-- Funzione: trova proprietà in un quartiere specifico
CREATE OR REPLACE FUNCTION get_properties_in_neighborhood(
p_neighborhood_slug VARCHAR,
p_listing_type VARCHAR DEFAULT NULL,
p_min_price NUMERIC DEFAULT NULL,
p_max_price NUMERIC DEFAULT NULL
)
RETURNS TABLE(
property_id UUID,
title TEXT,
price NUMERIC,
rooms SMALLINT,
distance_to_center_m FLOAT
) AS $
DECLARE
v_centroid GEOMETRY;
BEGIN
SELECT ST_Centroid(boundary) INTO v_centroid
FROM neighborhoods WHERE slug = p_neighborhood_slug;
RETURN QUERY
SELECT
p.id,
p.title,
p.price,
p.rooms,
ST_Distance(p.location::geography, v_centroid::geography) AS dist
FROM properties p
JOIN neighborhoods n ON ST_Within(p.location, n.boundary)
WHERE
n.slug = p_neighborhood_slug
AND p.status = 'active'
AND (p_listing_type IS NULL OR p.listing_type = p_listing_type)
AND (p_min_price IS NULL OR p.price >= p_min_price)
AND (p_max_price IS NULL OR p.price <= p_max_price)
ORDER BY dist ASC;
END;
$ LANGUAGE plpgsql STABLE;
-- Ricerca per area disegnata dall'utente (polygon from frontend)
SELECT
p.id,
p.title,
p.price,
p.rooms
FROM properties p
WHERE
p.status = 'active'
AND ST_Within(
p.location,
ST_GeomFromGeoJSON(
-- GeoJSON inviato dal frontend (area disegnata su mappa)
'{"type":"Polygon","coordinates":[[[12.46,41.89],[12.49,41.89],[12.49,41.91],[12.46,41.91],[12.46,41.89]]]}'
)
);
-- Proprietà vicine a scuole (join spaziale)
SELECT DISTINCT
p.id,
p.title,
s.name AS nearest_school,
ST_Distance(p.location::geography, s.location::geography) AS school_distance_m
FROM properties p
JOIN schools s ON ST_DWithin(
p.location::geography,
s.location::geography,
500 -- entro 500 metri da una scuola
)
WHERE p.status = 'active'
ORDER BY p.id, school_distance_m;
H3: Sześciokątny hierarchiczny indeks przestrzenny
H3 przez firmę Uber i pozwalający na to sześciokątny system indeksowania geoprzestrzennego
efektywne agregacje danych rynkowych. Jest idealny do map cieplnych cen i gęstości reklam
i analiza rynku według obszaru. Rozszerzenie h3-pg zintegruj H3 bezpośrednio z PostgreSQL.
-- Installazione estensione H3
CREATE EXTENSION IF NOT EXISTS h3;
CREATE EXTENSION IF NOT EXISTS h3_postgis CASCADE;
-- Aggiungi colonna H3 alla tabella properties (risoluzione 9 = ~174m di lato)
ALTER TABLE properties
ADD COLUMN h3_index H3INDEX GENERATED ALWAYS AS (
h3_lat_lng_to_cell(
ST_Y(location), -- latitudine
ST_X(location), -- longitudine
9 -- risoluzione: 0 (globale) - 15 (edificio)
)
) STORED;
CREATE INDEX idx_properties_h3 ON properties(h3_index);
-- Aggregazione prezzi medi per cella H3 (heatmap)
SELECT
h3_index,
COUNT(*) AS property_count,
ROUND(AVG(price)::numeric, 0) AS avg_price,
ROUND(AVG(price_sqm)::numeric, 0) AS avg_price_sqm,
h3_cell_to_boundary_wkt(h3_index) AS cell_boundary_wkt
FROM properties
WHERE
status = 'active'
AND listing_type = 'sale'
AND property_type = 'apartment'
GROUP BY h3_index
HAVING COUNT(*) >= 3 -- minimo 3 proprietà per cella (privacy)
ORDER BY avg_price_sqm DESC;
-- Cerchia intorno a un punto usando celle H3 adiacenti
SELECT DISTINCT p.*
FROM properties p
JOIN (
SELECT unnest(h3_grid_disk(
h3_lat_lng_to_cell(41.8991, 12.4731, 9),
2 -- 2 ring = cerchio di circa 500m
)) AS h3_cell
) cells ON p.h3_index = cells.h3_cell
WHERE p.status = 'active';
RESTful API z Express.js
Udostępniamy możliwości geoprzestrzenne poprzez RESTful API napisane w Express.js i TypeScript, z walidacją danych wejściowych przez Zoda i połączeniem z bazą danych poprzez pg (node-postgres).
import express from 'express';
import { Pool } from 'pg';
import { z } from 'zod';
const router = express.Router();
const db = new Pool({ connectionString: process.env['DATABASE_URL'] });
// Schema validazione con Zod
const ProximitySearchSchema = z.object({
lat: z.number().min(-90).max(90),
lon: z.number().min(-180).max(180),
radiusMeters: z.number().min(100).max(50000).default(2000),
listingType: z.enum(['sale', 'rent']).optional(),
propertyType: z.enum(['apartment', 'house', 'villa', 'commercial']).optional(),
minPrice: z.number().positive().optional(),
maxPrice: z.number().positive().optional(),
minRooms: z.number().int().min(1).optional(),
limit: z.number().int().min(1).max(200).default(50),
});
// GET /api/properties/nearby
router.get('/nearby', async (req, res) => {
const parsed = ProximitySearchSchema.safeParse({
...req.query,
lat: Number(req.query['lat']),
lon: Number(req.query['lon']),
radiusMeters: req.query['radiusMeters'] ? Number(req.query['radiusMeters']) : 2000,
minPrice: req.query['minPrice'] ? Number(req.query['minPrice']) : undefined,
maxPrice: req.query['maxPrice'] ? Number(req.query['maxPrice']) : undefined,
minRooms: req.query['minRooms'] ? Number(req.query['minRooms']) : undefined,
limit: req.query['limit'] ? Number(req.query['limit']) : 50,
});
if (!parsed.success) {
return res.status(400).json({ error: 'Invalid parameters', details: parsed.error.issues });
}
const params = parsed.data;
try {
const result = await db.query(
`SELECT
p.id, p.title, p.price, p.rooms, p.square_meters,
p.property_type, p.listing_type,
ST_AsGeoJSON(p.location)::json AS location,
ST_Distance(p.location::geography, ST_MakePoint($2, $1)::geography) AS distance_m
FROM properties p
WHERE
p.status = 'active'
AND ST_DWithin(p.location::geography, ST_MakePoint($2, $1)::geography, $3)
AND ($4::text IS NULL OR p.listing_type = $4)
AND ($5::text IS NULL OR p.property_type = $5)
AND ($6::numeric IS NULL OR p.price >= $6)
AND ($7::numeric IS NULL OR p.price <= $7)
AND ($8::int IS NULL OR p.rooms >= $8)
ORDER BY distance_m ASC
LIMIT $9`,
[
params.lat, params.lon, params.radiusMeters,
params.listingType ?? null,
params.propertyType ?? null,
params.minPrice ?? null,
params.maxPrice ?? null,
params.minRooms ?? null,
params.limit,
]
);
return res.json({
count: result.rows.length,
center: { lat: params.lat, lon: params.lon },
radiusMeters: params.radiusMeters,
properties: result.rows,
});
} catch (err) {
console.error('Geospatial query error:', err);
return res.status(500).json({ error: 'Internal server error' });
}
});
// GET /api/properties/neighborhood/:slug
router.get('/neighborhood/:slug', async (req, res) => {
const { slug } = req.params;
const { listingType, minPrice, maxPrice } = req.query;
try {
const result = await db.query(
`SELECT * FROM get_properties_in_neighborhood($1, $2, $3, $4)`,
[slug, listingType ?? null, minPrice ?? null, maxPrice ?? null]
);
return res.json({ count: result.rows.length, properties: result.rows });
} catch (err) {
console.error('Neighborhood query error:', err);
return res.status(500).json({ error: 'Internal server error' });
}
});
export default router;
Integracja z MapLibre GL JS
MapLibre GL JS oraz rozwidlenie open source Mapbox GL JS, idealne do integracji z OpenStreetMap i niestandardowym serwerem kafelków. Integrujemy API geoprzestrzenne z interaktywną mapą który aktualizuje wyniki w czasie rzeczywistym, gdy użytkownik przesuwa lub zmienia rozmiar rzutni.
import maplibregl from 'maplibre-gl';
export class PropertyMapManager {
private map: maplibregl.Map;
private searchTimeout: ReturnType<typeof setTimeout> | null = null;
constructor(container: HTMLElement) {
this.map = new maplibregl.Map({
container,
style: 'https://demotiles.maplibre.org/style.json',
center: [12.4731, 41.8991], // Roma
zoom: 13,
});
this.map.on('load', () => this.initializeSources());
// Aggiorna risultati al termine del movimento mappa (debounced)
this.map.on('moveend', () => {
if (this.searchTimeout) clearTimeout(this.searchTimeout);
this.searchTimeout = setTimeout(() => this.fetchPropertiesInView(), 300);
});
}
private initializeSources(): void {
// Source GeoJSON per proprietà (aggiornato dinamicamente)
this.map.addSource('properties', {
type: 'geojson',
data: { type: 'FeatureCollection', features: [] },
cluster: true,
clusterMaxZoom: 14,
clusterRadius: 50,
});
// Layer cluster circles
this.map.addLayer({
id: 'clusters',
type: 'circle',
source: 'properties',
filter: ['has', 'point_count'],
paint: {
'circle-color': [
'step', ['get', 'point_count'],
'#51bbd6', 10, '#f1f075', 30, '#f28cb1'
],
'circle-radius': ['step', ['get', 'point_count'], 20, 10, 30, 30, 40],
},
});
// Layer singoli punti
this.map.addLayer({
id: 'unclustered-point',
type: 'circle',
source: 'properties',
filter: ['!', ['has', 'point_count']],
paint: {
'circle-color': '#0066cc',
'circle-radius': 8,
'circle-stroke-width': 2,
'circle-stroke-color': '#fff',
},
});
// Click su punto singolo: mostra popup
this.map.on('click', 'unclustered-point', (e) => {
const feature = e.features?.[0];
if (!feature?.geometry || feature.geometry.type !== 'Point') return;
const props = feature.properties as {
title: string;
price: number;
rooms: number;
id: string;
};
new maplibregl.Popup()
.setLngLat(feature.geometry.coordinates as [number, number])
.setHTML(`
<div class="map-popup">
<h4>${props['title']}</h4>
<p>€${props['price'].toLocaleString('it-IT')} | ${props['rooms']} locali</p>
<a href="/property/${props['id']}">Dettagli →</a>
</div>
`)
.addTo(this.map);
});
}
private async fetchPropertiesInView(): Promise<void> {
const bounds = this.map.getBounds();
const center = this.map.getCenter();
const zoom = this.map.getZoom();
// Calcola raggio approssimativo dalla viewport
const radiusMeters = Math.min(
Math.pow(2, 15 - zoom) * 500,
50000
);
const response = await fetch(
`/api/properties/nearby?lat=${center.lat}&lon=${center.lng}&radiusMeters=${radiusMeters}&limit=200`
);
const data = await response.json();
// Converti in GeoJSON FeatureCollection
const geojson: GeoJSON.FeatureCollection = {
type: 'FeatureCollection',
features: data.properties.map((p: any) => ({
type: 'Feature',
geometry: p.location,
properties: {
id: p.id,
title: p.title,
price: p.price,
rooms: p.rooms,
},
})),
};
(this.map.getSource('properties') as maplibregl.GeoJSONSource)
.setData(geojson);
}
}
Testy wydajności i optymalizacje
| Typ zapytania | Zbiory danych | Bez indeksu | Z indeksem GIST | Z H3 |
|---|---|---|---|---|
| Punkt w promieniu (2 km) | Nieruchomość 1M | 2,3 s | 18 ms | 12 ms |
| K-najbliższy sąsiad (10) | Nieruchomość 1M | 4,1 s | 9 ms | 7 ms |
| Przecięcie wielokąta | Majątek za 500 tys | 1,8 s | 35 ms | 20 ms |
| Agregacja mapy cieplnej | Nieruchomość 1M | 8,5 s | 420 ms | 45 ms |
Zaawansowane optymalizacje
- Zmaterializowane poglądy: Wstępnie obliczaj agregacje H3 dla map cieplnych i aktualizuj je co 15 minut
- Indeksy częściowe: Indeks GIST tylko dla aktywnych właściwości (WHERE status='active')
- Pula połączeń: użyj PgBouncer w trybie transakcyjnym, aby zmniejszyć opóźnienia połączenia
- Przeczytaj odpowiedzi: Kieruj zapytania odczytu do replikacji PostgreSQL, aby skalować w poziomie
- Buforowanie wyników: Pamięć podręczna Redis do częstych wyszukiwań (ta sama ramka ograniczająca, te same filtry)
Współrzędne: lon, lat (nie lat, lon)
Kolejność użycia PostGIS i GeoJSON długość geograficzna, szerokość geograficzna (x, y) do tyłu
bardziej znanego porządku lat/lon. ST_MakePoint(lon, lat). To jest powszechne źródło
błąd: odwrócone współrzędne dają punkty na Pacyfiku zamiast w Europie. Zawsze dokumentuj
koordynuj kolejność w interfejsie API i dodaj testy poprawności, które sprawdzają pola ograniczające wyników.
Geokodowanie i odwrotne geokodowanie
Geokodowanie (adres tekstowy -> współrzędne) i geokodowanie odwrotne (współrzędne -> adres) są to operacje podstawowe. W przypadku produkcji najbardziej niezawodnymi dostawcami są:
// Geocoding con Nominatim (OpenStreetMap, gratuito con rate limiting)
export async function geocodeAddress(address: string): Promise<{ lat: number; lon: number } | null> {
const encoded = encodeURIComponent(address);
const url = `https://nominatim.openstreetmap.org/search?q=${encoded}&format=json&limit=1`;
const response = await fetch(url, {
headers: { 'User-Agent': 'RealEstatePlatform/1.0 (contact@example.com)' },
});
if (!response.ok) return null;
const data = await response.json();
if (!data.length) return null;
return {
lat: parseFloat(data[0].lat),
lon: parseFloat(data[0].lon),
};
}
// Geocoding con Google Maps Platform (commerciale, alta qualità per indirizzi italiani)
export async function geocodeWithGoogle(address: string): Promise<{ lat: number; lon: number } | null> {
const apiKey = process.env['GOOGLE_MAPS_API_KEY'];
const url = `https://maps.googleapis.com/maps/api/geocode/json?address=${encodeURIComponent(address)}&key=${apiKey}®ion=it`;
const response = await fetch(url);
const data = await response.json();
if (data.status !== 'OK' || !data.results.length) return null;
const location = data.results[0].geometry.location;
return { lat: location.lat, lon: location.lng };
}
// Inserimento proprietà con geocoding automatico
async function insertPropertyWithGeocoding(
pool: Pool,
property: Omit<PropertyInsert, 'location'>,
address: string
): Promise<string> {
const coords = await geocodeWithGoogle(address);
if (!coords) throw new Error(`Geocoding failed for: ${address}`);
const result = await pool.query(
`INSERT INTO properties (title, price, square_meters, rooms, bathrooms,
property_type, listing_type, location, address)
VALUES ($1, $2, $3, $4, $5, $6, $7,
ST_MakePoint($9, $8)::geometry,
$10::jsonb)
RETURNING id`,
[
property.title, property.price, property.squareMeters, property.rooms,
property.bathrooms, property.propertyType, property.listingType,
coords.lat, coords.lon,
JSON.stringify({ street: address, city: property.city, zip: property.zip }),
]
);
return result.rows[0].id;
}
Wnioski
PostGIS przekształca PostgreSQL w korporacyjną platformę GIS zdolną do zarządzania dowolnymi potrzebami geoprzestrzenna sektora nieruchomości: od błyskawicznego wyszukiwania bliskości po agregacje H3 dla heatmap, od zapytań wielokątnych po połączenia przestrzenne z POI. Połączenie z MapLibre GL JS i Nominatim/Google Maps tworzy kompleksowy, skalowalny system inteligencji lokalizacyjnej typu open source.