Intermediate LevelPostgreSQL
What is PostGIS and how does it transform PostgreSQL into a spatial database?
❓ Interview Prompt: Explain PostGIS extension, spatial data types (GEOMETRY, GEOGRAPHY), Spatial Reference System Identifiers (SRID 4326 vs 3857), and spatial functions.
PostGIS is an extension that turns PostgreSQL into a spatial database:
Key Concepts:
•
'GEOMETRY': Planar spatial type (fast Cartesian Euclidean calculations).
•
'GEOGRAPHY': Spherical spatial type (accurate Earth curvature calculations on lat/lng).
•
SRID 4326: Standard WGS84 GPS coordinate system (Latitude / Longitude).
•
Spatial Functions: 'ST_Distance', 'ST_Contains', 'ST_Buffer', 'ST_Intersects'.
Code Implementation Example
sql
-- Enable PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE TABLE delivery_zones (
id SERIAL PRIMARY KEY,
name TEXT,
polygon GEOMETRY(Polygon, 4326)
);
-- Query if target point is contained inside delivery zone
SELECT name FROM delivery_zones
WHERE ST_Contains(polygon, ST_SetSRID(ST_MakePoint(-73.985, 40.748), 4326));💡Executing spatial point-in-polygon containment query with PostGIS.
🎯 Interview Key Takeaway
Use PostGIS GEOGRAPHY for accurate global distance calculations and GEOMETRY for fast local planar calculations.
#PostGIS#GEOMETRY#SRID#Spatial Database#Intermediate
Back to all PostgreSQL Questions→