--- path: docs/spatial-sql-book/03-geometry-as-a-type title: Geometry Is a Type, Not Two Numbers desc: One value, several spellings — and the label that says what the numbers mean image: /images/spatial-sql-book/03-geometry-as-a-type/cover.webp --- # 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 ```sql 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. *[Run this query yourself](https://app.gisowl.com/app/register?source=site)* ## 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: ```sql 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: ```json {"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 ```sql 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. *[Run this query yourself](https://app.gisowl.com/app/register?source=site)* #### The same four spellings, on the other engine ```sql 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. *[Run this query yourself](https://app.gisowl.com/app/register?source=site)* #### Why not just two float columns? ```sql 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. *[Run this query yourself](https://app.gisowl.com/app/register?source=site)* ## 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. ```sql '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: ```sql 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 ```sql 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`. *[Run this query yourself](https://app.gisowl.com/app/register?source=site)* #### Ask a geometry about itself ```sql 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". *[Run this query yourself](https://app.gisowl.com/app/register?source=site)* ## Rules of thumb - A bare WKT literal carries SRID 0 — unknown, not "probably 4326". - `ST_SetSRID` relabels and moves nothing. `ST_Transform` recomputes 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](https://app.gisowl.com/app/register?source=site) — the same SQL, an editor, and your own data. ← [Where Your SQL Runs](/docs/spatial-sql-book/02-where-your-sql-runs) · [Degrees Are Not Metres](/docs/spatial-sql-book/04-srid-degrees-metres) → · [All lessons](/docs/spatial-sql-book)