liveBook · Manning

Geometric data type

As explained in Just Use Postgres!

A geometry is a mathematical representation of a spatial object, including its coordinates and shape.

A geometric data type represents a spatial object with coordinates and shape. Postgres provides built-in 2D types such as point, line, lseg, box, path, polygon, and circle; PostGIS extends this model with typed geometry values such as Point, LineString, Polygon, and MultiPolygon. section 10.1.1 section 10.1.2

Use geometry values to model and query spatial data:

  • Store points of interest, city centers, restaurants, roads, rivers, buildings, parks, lakes, and city boundaries. section 10.1.1 section 10.3.2
  • Associate a subtype and spatial reference identifier with a column, such as geometry(Point, 3857), where 3857 denotes Web Mercator. section 10.1.2 section 10.3.1
  • Perform planar calculations and spatial predicates, including distance, area, containment, intersection, equality, and coordinate transformation. section 10.5.1 section 10.5.2
  • Remember that geometry uses Cartesian mathematics on a Euclidean plane, making calculations fast but potentially less accurate over large distances because it does not model Earth’s curvature. section 10.1.2 section 10.7

Find nearby restaurants

A PostGIS query can search within 500 meters of a hotel, calculate distances, and order the results: JOIN florida.planet_osm_point AS p ON ST_DWithin(h.way, p.way, 500) WHERE p.amenity = 'restaurant' ORDER BY ST_Distance(h.way, p.way) LIMIT 5. The sample output starts with Wagamama at 68 meters and Boulon Brasserie at 132 meters. section 10.5.1

Inspect and transform a point

A point stored in Web Mercator can be displayed as WKT with ST_AsText and transformed to longitude and latitude with ST_Transform(way, 4326). Tampa’s sample point becomes POINT(-82.458444 27.947759499952074), which can then be inserted into an OpenStreetMap URL. section 10.5.1 section 10.3.1

Table 10.1 Postgres geometric data types

Type name

Description

Input format

point

A point on a 2D plane

(x, y)

line

An infinite straight line in 2D space

{A, B, C} where Ax + By + C = 0

lseg

A finite line segment between two points

[(x1, y1), (x2, y2)]

box

A rectangle defined by two opposite corners

((x1, y1), (x2, y2))

path

An open or closed path defined as a sequence of connected points

open path: [(x1, y1), (x2, y2), ...]

closed path: ((x1, y1), (x2, y2), ...)

polygon

A closed polygon with three or more points

((x1, y1), (x2, y2), ...)

circle

A circle with a center and a radius

<(x, y), r>

Browse the built-in Postgres geometric types, their spatial meanings, and accepted input formats. section 10.1.1

How Just Use Postgres! covers it

How deep it goes
Explains subtypes, SRIDs, planar calculations, functions, representations, and indexes, while deferring Earth-curvature modeling to geography.
Examples
Demonstrates points, LineStrings, polygons, theme parks, nearby restaurants, coordinate transformations, WKT, WKB, and OpenStreetMap URLs.
What it assumes
Assumes SQL and Postgres familiarity while introducing spatial objects, geometry subtypes, coordinate systems, SRIDs, and spatial predicates.
What you can do after
Apply it via PostGIS to store geometries, transform coordinates, measure shapes, test containment, and query nearby or enclosed objects.
How it connects
Connects geometry with PostGIS, geography data type, spatial reference systems, WKT, WKB, GIS, and spatial indexes.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.