Geometry Is a Type, Not Two Numbers
One value, several spellings — and the label that says what the numbers mean
What this lesson covers
- Geometry in the wild: 107 vertices and three courtyards
- WKT, EWKT, GeoJSON and the hex you did not ask for
- Why a bare literal has SRID 0 and nothing warns you
- Asking a geometry about itself: type, dimension, vertices
- What two float columns cost you
- The same spellings on a file, not a table
What a real geometry looks like
SELECT ST_GeometryType(geom) AS type,
ST_NPoints(geom) AS vertices,
ST_NumInteriorRings(ST_GeometryN(geom, 1)) AS holes,
length(ST_AsText(geom)) AS wkt_characters,
geom
FROM osm.building_polygon
WHERE geom && ST_MakeEnvelope(139.75, 35.67, 139.78, 35.69, 4326)
ORDER BY ST_NPoints(geom) DESC
LIMIT 25;
Twenty-five buildings from central Tokyo, ordered by how complicated they are. The biggest carries 107 vertices and one of them has three interior rings — courtyards, holes punched through the polygon. That is 2,461 characters of WKT for a single building, and it is one value in one column: not a list of coordinates you assemble yourself, not two float columns, one geometry.
One value, several spellings
A geometry is one column of one type, not a pair of float columns. Postgres stores it in a binary format and hands it to you in whatever spelling you ask for:
ST_AsText(geom) -- MULTIPOLYGON(((139.7676 35.6790, … human-readable, no SRID
ST_AsEWKT(geom) -- SRID=4326;MULTIPOLYGON(((139.7676 35.6790… WKT plus the SRID
ST_AsGeoJSON(geom) -- {"type":"MultiPolygon","coordinates":[[[… what a web map wants
geom::text -- 0106000020E6100000010000000103000000… EWKB, hexadecimal
The last one is the default. Cast a geometry to text — or let a client do it for you — and you get hex: a faithful, complete, unreadable encoding of the same shape.
This editor spares you that. Every query is wrapped in to_jsonb(…), which renders geometry as GeoJSON with its CRS attached:
{"geom": {"crs": {"type": "name", "properties": {"name": "EPSG:4326"}},
"type": "Point", "coordinates": [139.7671, 35.6812]}}
That is why selecting geom here draws a map instead of printing hex — and why, the first time you query this data from a script of your own, you should expect the hex and reach for ST_AsGeoJSON deliberately.
Ask for the spelling you want. A geometry column is not a string, and the shape you see is a rendering choice, not a property of the value.
The same building, four ways
WITH one_building AS (
SELECT geom
FROM osm.building_polygon
WHERE geom && ST_MakeEnvelope(139.767, 35.680, 139.768, 35.681, 4326)
ORDER BY ST_NPoints(geom)
LIMIT 1
)
SELECT left(ST_AsText(geom), 60) AS wkt,
left(ST_AsEWKT(geom), 60) AS ewkt,
left(ST_AsGeoJSON(geom), 60) AS geojson,
left(geom::text, 60) AS cast_to_text,
ST_SRID(geom) AS srid
FROM one_building;
One geometry rendered four times. WKT is readable and drops the SRID; EWKT keeps it; GeoJSON is what a web map consumes; and ::text — the default if you do nothing — is EWKB in hexadecimal. Each column is the same shape. The difference is entirely in how you asked, which is why this course always projects geometry explicitly.
The same four spellings, on the other engine
SELECT ST_GeometryType(geometry) AS geom_type,
ST_SRID(geometry) AS srid,
ST_NPoints(geometry) AS vertices,
ST_AsText(geometry) AS wkt,
ST_AsGeoJSON(geometry) AS geojson,
geometry AS as_the_file_stores_it
FROM 'data/sedonadb/tokyo_buildings.parquet'
ORDER BY ST_NPoints(geometry)
LIMIT 3
A Parquet file instead of a table, and the value behaves identically: one geometry, tagged SRID 4326, that ST_AsText renders as WKT and ST_AsGeoJSON renders as GeoJSON. The last column is the geometry as the file stores it — the same EWKB hex a PostGIS ::text cast produces. The representations are a standard, not a PostGIS feature, which is exactly why a file written by one tool opens in another.
One difference worth noting: SUBSTR around any of these fails here — "Substring could not be planned" — so there is no truncating the output. The engine's SQL surface is smaller in places you do not expect.
Why not just two float columns?
SELECT a.name,
round(ST_X(a.geom)::numeric, 5) AS lon,
round(ST_Y(a.geom)::numeric, 5) AS lat,
round(sqrt(power(ST_X(a.geom) - 139.7671, 2)
+ power(ST_Y(a.geom) - 35.6812, 2))::numeric, 6) AS pythagoras_on_degrees,
round(ST_Distance(a.geom::geography,
ST_SetSRID(ST_MakePoint(139.7671, 35.6812), 4326)::geography)::numeric, 1) AS real_metres
FROM osm.amenity_point AS a
WHERE a.geom && ST_MakeEnvelope(139.75, 35.67, 139.78, 35.69, 4326)
AND a.name <> ''
ORDER BY real_metres
LIMIT 20;
The obvious alternative to a geometry type is a lon and a lat column, and this is what it costs you. Pythagoras on degrees gives a number that sorts almost right — and then does not: 0.001128 degrees away is 107.7 metres, while 0.001089 degrees away is 108.9. A degree of longitude and a degree of latitude are different lengths, so the plane you are measuring on is not flat, and the ordering quietly disagrees with reality.
SRID is a label, and a bare literal has none
Every geometry carries an SRID — a number saying which coordinate system its numbers are in. 4326 means longitude and latitude in degrees; 3857 means Web Mercator metres. The coordinates alone cannot tell you which, and neither can PostGIS.
Write a literal and you get zero: unknown, unstated, unchecked.
'POINT(139.7671 35.6812)'::geometry -- SRID 0. Nothing warns you.
Zero is not a coordinate system. It is PostGIS saying "you never told me." Everything still works — the point has coordinates, functions accept it, output looks fine — right up to the moment you compare it with real data and get an error, or worse, silently wrong distances.
Two functions look similar and do opposite things:
ST_SetSRID(geom, 4326) -- relabels: same numbers, now declared as degrees
ST_Transform(geom, 3857) -- reprojects: new numbers, computed for a new system
ST_SetSRID moves nothing. It is a promise about what the numbers already mean, and if the promise is wrong the geometry lands in the wrong hemisphere. ST_Transform does the arithmetic — and needs to know where it is starting from, which is why it fails on SRID 0.
Everything in this course is 4326, because that is how the OpenStreetMap extract is stored. Lesson 04 is about what that costs you.
A literal has no coordinate system
SELECT ST_SRID('POINT(139.7671 35.6812)'::geometry) AS bare_literal,
ST_SRID(ST_SetSRID('POINT(139.7671 35.6812)'::geometry, 4326)) AS after_setsrid,
ST_AsEWKT('POINT(139.7671 35.6812)'::geometry) AS ewkt_bare,
ST_AsEWKT(ST_SetSRID('POINT(139.7671 35.6812)'::geometry, 4326)) AS ewkt_labelled;
The one place a made-up geometry belongs, because the literal is the subject. Those coordinates are plainly Tokyo in degrees — and PostGIS reports SRID 0, meaning it was never told. ST_SetSRID fixes the label without touching a single number, which you can confirm by comparing the two EWKT columns: identical coordinates, one now carrying SRID=4326.
Ask a geometry about itself
WITH shapes AS (
(SELECT 'building' AS source, geom
FROM osm.building_polygon
WHERE geom && ST_MakeEnvelope(139.767, 35.680, 139.769, 35.682, 4326) LIMIT 5)
UNION ALL
(SELECT 'road', geom
FROM osm.road_line
WHERE geom && ST_MakeEnvelope(139.767, 35.680, 139.769, 35.682, 4326) LIMIT 5)
UNION ALL
(SELECT 'amenity', geom
FROM osm.amenity_point
WHERE geom && ST_MakeEnvelope(139.767, 35.680, 139.769, 35.682, 4326) LIMIT 5)
)
SELECT source,
ST_GeometryType(geom) AS type,
ST_Dimension(geom) AS dimension,
ST_NPoints(geom) AS vertices,
ST_IsClosed(geom) AS closed
FROM shapes
ORDER BY dimension DESC, source;
Five buildings, five roads and five amenities put through the same four accessors. Dimension separates them cleanly — 2 for areas, 1 for lines, 0 for points — while vertex counts show how much geometry each shape is actually carrying. Watch ST_IsClosed on the points: a point is trivially closed, which is true, useless, and exactly the kind of answer that catches you if you assume closed means "a ring".
Rules of thumb
- A bare WKT literal carries SRID 0 — unknown, not "probably 4326".
ST_SetSRIDrelabels and moves nothing.ST_Transformrecomputes the coordinates.- Cast a geometry to text and you get EWKB hex. Ask for the spelling you want instead.
- Dimension is a property of the shape: 2 for polygons, 1 for lines, 0 for points.
Run these queries against a live PostGIS. Open the lesson in the app — the same SQL, an editor, and your own data.
← Where Your SQL Runs · Degrees Are Not Metres → · All lessons