PostGIS teaser

PostGIS extends PostgreSQL with geographic types and spatial functions—maps, distance queries, and geofencing for logistics and location apps.

Enable PostGIS

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE stores (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  location GEOGRAPHY(POINT, 4326) NOT NULL
);

INSERT INTO stores (name, location)
VALUES ('Downtown', ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326)::geography);

Practice: Review prior lessons in psql, then attempt interview-style queries without peeking.

Distance query

SELECT name,
       ST_Distance(location, ST_MakePoint(-122.4, 37.77)::geography) AS meters
FROM stores
ORDER BY meters
LIMIT 5;

When to adopt

If queries are mostly points and distances, PostGIS beats storing lat/lng in plain floats. Heavy map tile serving may still pair with specialized services—but keep authoritative geometry in Postgres.

Important interview questions and answers

1. Q: GEOGRAPHY vs GEOMETRY?
   A: GEOGRAPHY uses earth spheroid for distance; GEOMETRY is planar—pick for your accuracy needs.
2. Q: SRID 4326?
   A: WGS 84 latitude/longitude—common GPS coordinate system.

Self-check

1. Which extension adds geographic types?
2. What function creates a point from lon/lat?

Tip: Use GEOGRAPHY for meter distances on Earth; verify SRID matches your data source.