PostGIS
2 min read
PostGIS is a spatial database extender for PostgreSQL. It adds support for geographic objects allowing it to be used as a spatial database for geographic information systems (GIS). With PostGIS, PostgreSQL becomes a powerful database for managing spatial data and performing complex geographic operations.
PostGIS offers the following key features:
• Geometry and Geography Types: PostGIS supports two primary types of spatial objects: Geometry (for Cartesian coordinates) and Geography (for geodetic coordinates).
• Spatial Indexing: Support for R-tree-based spatial indexing using GiST (Generalized Search Tree) indexes.
• Spatial Relationships and Measurements: Functions to perform spatial analysis, including distance calculations, intersections, unions, and more.
• 3D and 4D Coordinates: Support for 3D geometries (with Z values) and 4D (with M values for measures).
• Raster and Vector Data: PostGIS allows for the handling of both raster (pixel-based) and vector (coordinate-based) spatial data.
• WKT, WKB, GeoJSON Support: PostGIS supports common geographic data formats like Well-Known Text (WKT), Well-Known Binary (WKB), and GeoJSON.
Enable PostGIS in a PostgreSQL Database
After installation, to enable PostGIS on a specific database, run the following SQL commands:
sql
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
Spatial Data Types
PostGIS introduces several spatial data types. The two most commonly used types are:
1. Geometry
Represents geometric shapes in a Cartesian (planar) coordinate system.
sql
CREATE TABLE my_table (
id SERIAL PRIMARY KEY,
geom GEOMETRY(Point, 4326)
);
2. Geography
Represents geographic shapes in a spherical coordinate system (uses latitudes and longitudes).
sql
CREATE TABLE my_geo_table (
id SERIAL PRIMARY KEY,
geom GEOGRAPHY(POINT, 4326)
);
PostGIS also supports other geometry types, such as:
• POINT
• LINESTRING
• POLYGON
• MULTIPOINT
• MULTILINESTRING
• MULTIPOLYGON
Each of these types can be used in both GEOMETRY and GEOGRAPHY contexts.
Spatial Functions
PostGIS provides a vast library of spatial functions for querying and manipulating spatial data. Some important functions include:
Distance
Calculates the distance between two geometries.
sql
SELECT ST_Distance(
ST_GeomFromText('POINT(0 0)', 4326),
ST_GeomFromText('POINT(1 1)', 4326)
);
Intersection
Returns the intersection of two geometries.
sql
SELECT ST_Intersection(
ST_GeomFromText('LINESTRING(0 0, 2 2)', 4326),
ST_GeomFromText('LINESTRING(0 2, 2 0)', 4326)
);
Contains
Checks if one geometry contains another.
sql
SELECT ST_Contains(
ST_GeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 4326),
ST_GeomFromText('POINT(1 1)', 4326)
);
Area
Calculates the area of a polygon.
sql
SELECT ST_Area(
ST_GeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 4326)
);