Buy our books! https://postgis.us/page_buy_book

Email: lr@pcorp.us

pgRouting (a practical guide) 2nd Ed (Locate Press) in Progress. |

The Book of PostgreSQL (No Starch Press)In Progress |

PostGIS is a set of PostgreSQL extensions to manage and query spatial data in a PostgreSQL database. Spatial data is any data involving space and what inhabits space.

- Post-Jis
- Post G I S
- Post Gees

To make spatial data not special

- OpenGeospatial Consortion Simple Features for SQL (OGC SFSQL)
- SQL/MM - Part of ISO SQL Standard
- Standard output formats - Well-Known Text (WKT), Well-Known Binary (WKB), GML , KML, SVG, GeoJSON, MVT, X3D (W3C standard based on VRML)

**To make spatial data not special**

- Relational
- Spatial (Geo)

What? **Not** Only **Special**

You can use PostGIS anywhere you can use SQL and Postgres. If you know SQL, you know 90% of what you need to know about PostGIS.

.NET, C, Go, Java, NodeJS, PHP, Python, R, Rust, **SQL**, and more

But you can also use fancy tools to look at it in its full glory

What are the 3 closest roads to me?

```
SELECT road_name, s.geog <-> ST_Point(:param_longitude, :param_latitude)::geography AS dist_m
FROM roads
ORDER BY dist_m DESC LIMIT 3;
```

Summarize Crime stats for NYC Boroughs

```
SELECT b.boro_name, count(*), c.type
FROM nyc_boros AS b
INNER JOIN crimes AS c ON ST_Intersects(b.geom, c.geom)
WHERE c.year = 2020
GROUP BY b.boro_name, c.type
ORDER BY b.boro_name, c.type;
```

Flat, Round, Matrix, Relational (Declarative)

`postgis`

Space is a cartesian grid. Supports drawing of linestrings, polygons, 3D polygons, points, 3d points, collections of polygons, points, linestrings, Polyhedral Surfaces, and TINS

Basic geometric types |
Polyhedral Surface |
Triangulated Irregular Network (TIN) |

`postgis`

The Round space model. Space is the surface of a spheroid where you can draw linestrings, polygons, points, and collections of polygons, points, linestrings on the surface.

Basic types |
On the surface of a sphere |

`postgis_raster`

Model of space as a numeric matrix (with cells (called pixels) that have values (on) or don't have values (off)). Within that are things called bands that define the number of matrices you have stacked.

- Elevation
- Soil
- Weather
- Fire
- Aerial

`postgis_topology`

Defined in SQL/MM Topology-Geometry specs. Spatialite has it too. Topology partitions space into **edges**, **nodes**, and **faces**. Then it says this thing called **topogeometry** is space composed of these **edges**, **nodes**, **faces** and other topogeometries (which are again just a bunch of edges, nodes,and faces). If two topogeometries have the same set of (edges,nodes,faces) then they are the same.

(1,1,2,3) -> topology_id, layer_id, id, type

```
SELECT topo::geometry AS geom, (topo).*
FROM some_topo_table;
```

**postgis_sfcgal**- functions for volumetric and 3D surface**postgis_tiger_geocoder**- geocoding and reverse geocoding using US Census TIGER data**address_standardizer, address_standardizer_data_us**Rule-based address standardizer (useful as precursor for geocoding)

`CREATE EXTENSION ...;`

PostGIS is the building block of several other extesions.

You can reach me at:

- Email: lr@pcorp.us
- Mastodon: @robe@mapstodon.space
- LinkedIn: https://www.linkedin.com/in/regina-obe-2000057/
- Twitter: @reginaobe