🗺️ PostGIS: Turn PostgreSQL into a Geographic Information System

Uber, Lyft, and every delivery app use this. Here's why.

The $1M Question: "Find all restaurants within 1km"
Without PostGIS (nightmare):

-- Haversine formula hell
SELECT *, 
  6371 * acos(
    cos(radians(user_lat)) * cos(radians(restaurant_lat)) * 
    cos(radians(restaurant_lng) - radians(user_lng)) + 
    sin(radians(user_lat)) * sin(radians(restaurant_lat))
  ) AS distance
FROM restaurants
WHERE -- Even more complex math here
ORDER BY distance;
-- Time: 2.3 seconds, inaccurate near poles

With PostGIS (magic):

-- Install once
CREATE EXTENSION postgis;

-- Store locations properly
ALTER TABLE restaurants 
ADD COLUMN location GEOGRAPHY(POINT);

UPDATE restaurants 
SET location = ST_MakePoint(longitude, latitude);

-- Find nearby restaurants
SELECT name, ST_Distance(location, user_location) as distance
FROM restaurants
WHERE ST_DWithin(location, user_location, 1000)  -- 1km
ORDER BY location <-> user_location;
-- Time: 0.03 seconds, accurate everywhere

76x faster. 100% accurate.

🎯 Real-World PostGIS Powers:
Delivery Zone Check:

-- Draw delivery zones
CREATE TABLE delivery_zones (
    id SERIAL PRIMARY KEY,
    name TEXT,
    zone GEOGRAPHY(POLYGON)
);

-- Check if address is in delivery area
SELECT name 
FROM delivery_zones
WHERE ST_Contains(zone, customer_location);

Route Optimization:

-- Find shortest path between points
WITH RECURSIVE route AS (
    SELECT location, 0 as total_distance
    FROM locations WHERE id = start_id
    UNION ALL
    SELECT l.location, 
           r.total_distance + ST_Distance(r.location, l.location)
    FROM locations l, route r
    WHERE l.id = next_stop_id
)
SELECT * FROM route;

Geofencing Alerts:

-- Trigger when user enters area
CREATE OR REPLACE FUNCTION check_geofence()
RETURNS TRIGGER AS $
BEGIN
    IF ST_DWithin(NEW.location, store_location, 100) THEN
        INSERT INTO notifications (user_id, message)
        VALUES (NEW.user_id, 'Welcome! You are near our store!');
    END IF;
    RETURN NEW;
END;
$ LANGUAGE plpgsql;

💡 The Game Changer:

-- Spatial indexes = instant geographic queries
CREATE INDEX idx_restaurants_location 
ON restaurants USING GIST (location);

-- Now this is instant on millions of points
SELECT * FROM restaurants
WHERE ST_DWithin(location, user_point, 5000)
ORDER BY location <-> user_point
LIMIT 10;

PostGIS turned our $5K/year Google Maps API bill into $0.

What geographic problem could PostGIS solve for you? 🌍

#PostgreSQL #PostGIS #Geospatial #LocationData

@postgres