Spatial Data Types
Points, lines and polygons in SQL.
Spatial Data Types is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What Is Spatial Data?
Spatial data describes real-world locations, shapes, and areas. Unlike a plain number or string, a spatial value carries geometric meaning — a point on a map, a road segment, or the boundary of a country.
PostGIS is a PostgreSQL extension that adds powerful spatial data types and functions, turning your database into a full geographic information system (GIS).
-- Enable the PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;The GEOMETRY Type
The core spatial type in PostGIS is geometry. It stores shapes in a flat (Euclidean) coordinate space. You can specify a subtype such as POINT, LINESTRING, or POLYGON as a constraint when creating a column.
Specifying the SRID (Spatial Reference ID) alongside the subtype tells PostGIS which coordinate system your data uses.
CREATE TABLE locations (
id SERIAL PRIMARY KEY,
name TEXT,
geom geometry(POINT, 4326)
);Inserting a POINT
A POINT represents a single location with X (longitude) and Y (latitude) coordinates. The ST_GeomFromText function converts a Well-Known Text (WKT) string into a geometry value.
SRID 4326 is the WGS 84 coordinate system used by GPS devices worldwide.
INSERT INTO locations (name, geom)
VALUES
('Eiffel Tower', ST_GeomFromText('POINT(2.2945 48.8584)', 4326)),
('Big Ben', ST_GeomFromText('POINT(-0.1246 51.5007)', 4326)),
('Colosseum', ST_GeomFromText('POINT(12.4922 41.8902)', 4326));
SELECT name, ST_AsText(geom) AS wkt FROM locations;The LINESTRING Type
A LINESTRING is an ordered sequence of two or more points connected by straight segments. It is ideal for representing roads, rivers, or any linear feature.
Each pair of numbers in the WKT is one vertex (longitude latitude). PostGIS stores the whole path as a single geometry value.
CREATE TABLE roads (
id SERIAL PRIMARY KEY,
name TEXT,
path geometry(LINESTRING, 4326)
);
INSERT INTO roads (name, path)
VALUES (
'High Street',
ST_GeomFromText(
'LINESTRING(-0.125 51.500, -0.120 51.502, -0.115 51.505)',
4326
)
);
SELECT name, ST_Length(path) AS length_degrees FROM roads;The POLYGON Type
A POLYGON defines an enclosed area. Its outer boundary is a ring — a closed LINESTRING where the first and last points are identical. A polygon can also have inner rings (holes) to represent areas like a lake inside a park.
All rings must be closed: repeat the starting coordinate at the end.
CREATE TABLE zones (
id SERIAL PRIMARY KEY,
name TEXT,
area geometry(POLYGON, 4326)
);
INSERT INTO zones (name, area)
VALUES (
'Central Park Approx',
ST_GeomFromText(
'POLYGON((-73.9817 40.7681, -73.9580 40.7681,
-73.9580 40.7964, -73.9817 40.7964,
-73.9817 40.7681))',
4326
)
);
SELECT name, ST_Area(area) AS area_sq_deg FROM zones;GEOGRAPHY vs GEOMETRY
PostGIS provides two families of spatial types: geometry uses a flat Cartesian plane, while geography models the Earth as a curved spheroid.
Use geography when you need accurate real-world distances in meters across large areas. Use geometry when you are working in a local projected coordinate system or need maximum performance.
CREATE TABLE cities (
id SERIAL PRIMARY KEY,
name TEXT,
geog geography(POINT, 4326)
);
INSERT INTO cities (name, geog) VALUES
('New York', ST_GeogFromText('POINT(-74.0060 40.7128)')),
('London', ST_GeogFromText('POINT(-0.1278 51.5074)'));
-- Distance in metres using geography
SELECT
a.name AS from_city,
b.name AS to_city,
ROUND(ST_Distance(a.geog, b.geog)::NUMERIC) AS distance_m
FROM cities a, cities b
WHERE a.name = 'New York' AND b.name = 'London';Well-Known Text (WKT) and WKB
Spatial data can be serialised in two standard formats. Well-Known Text (WKT) is human-readable: POINT(2.29 48.86). Well-Known Binary (WKB) is a compact binary representation used internally and for data transfer.
PostGIS provides ST_AsText and ST_AsBinary to convert between internal storage and these formats.
SELECT
name,
ST_AsText(geom) AS wkt,
ST_AsEWKT(geom) AS ewkt,
encode(ST_AsBinary(geom), 'hex') AS wkb_hex
FROM locations
LIMIT 2;MULTIPOINT, MULTILINESTRING, MULTIPOLYGON
PostGIS also supports multi-geometry types that group several shapes of the same kind into one value. This is useful when a single real-world feature is made up of disjoint parts — for example, an archipelago modelled as a MULTIPOLYGON.
All standard spatial functions work the same way on multi-geometries.
SELECT ST_AsText(
ST_GeomFromText(
'MULTIPOLYGON(
((0 0, 4 0, 4 4, 0 4, 0 0)),
((10 10, 14 10, 14 14, 10 14, 10 10))
)'
)
) AS multi_poly;
SELECT ST_NumGeometries(
ST_GeomFromText(
'MULTIPOLYGON(
((0 0, 4 0, 4 4, 0 4, 0 0)),
((10 10, 14 10, 14 14, 10 14, 10 10))
)'
)
) AS part_count;GEOMETRYCOLLECTION
A GEOMETRYCOLLECTION is the most general spatial type — it can hold a mix of points, lines, and polygons in a single value. This flexibility is useful when a dataset may contain heterogeneous geometry types stored in the same column.
You can extract individual members with ST_GeometryN.
SELECT ST_AsText(
ST_GeomFromText(
'GEOMETRYCOLLECTION(
POINT(1 1),
LINESTRING(0 0, 1 1, 2 2),
POLYGON((0 0, 3 0, 3 3, 0 3, 0 0))
)'
)
) AS collection;
SELECT ST_AsText(
ST_GeometryN(
ST_GeomFromText(
'GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(0 0, 1 1))'
),
1
)
) AS first_member;Spatial Indexes with GIST
Spatial queries that check containment or proximity must compare every row's geometry — potentially scanning millions of shapes. A GIST index speeds this up dramatically by indexing the bounding boxes of geometries, allowing PostgreSQL to skip rows that cannot possibly match.
Always create a GIST index on geometry or geography columns you query frequently.
-- Create a GIST index on a geometry column
CREATE INDEX idx_locations_geom
ON locations
USING GIST (geom);
-- PostgreSQL will now use the index for spatial operators
EXPLAIN ANALYZE
SELECT name
FROM locations
WHERE ST_DWithin(
geom,
ST_GeomFromText('POINT(2.3 48.9)', 4326),
1.0
);Reading Geometry Properties
PostGIS provides accessor functions to extract properties from any geometry. ST_X and ST_Y return the coordinates of a point, ST_SRID returns the spatial reference ID, and ST_GeometryType returns the subtype name.
These are essential for inspecting and validating spatial data.
SELECT
name,
ST_X(geom) AS longitude,
ST_Y(geom) AS latitude,
ST_SRID(geom) AS srid,
ST_GeometryType(geom) AS geom_type,
ST_IsValid(geom) AS is_valid
FROM locations;Quick Check
Test your understanding of PostGIS spatial data types.
Lesson Recap
In this lesson you explored the core spatial data types provided by PostGIS. You learned that POINT, LINESTRING, and POLYGON are the three fundamental geometry subtypes, and that multi-geometry variants (MULTIPOINT, MULTILINESTRING, MULTIPOLYGON) and the catch-all GEOMETRYCOLLECTION cover more complex shapes.
You saw the difference between the flat geometry type and the spheroidal geography type, and how to choose between them. Well-Known Text (WKT) lets you read and write spatial values as human-readable strings, while GIST indexes keep spatial queries fast at scale. Accessor functions such as ST_X, ST_SRID, and ST_GeometryType let you inspect any geometry value directly in SQL.
Frequently asked questions
Is the “Spatial Data Types” lesson free?
Yes — the full text of “Spatial Data Types” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “Spatial Data Types”?
Points, lines and polygons in SQL. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Spatial Data Types” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.