PostGIS in Action, Third Edition

PostGIS in Action, Third Edition

by Regina O. Obe, Leo S. Hsu
Released September 2021
Publisher(s): Manning Publications
ISBN: 9781617296697

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

In PostGIS in Action, Third Edition you will learn:

  • An introduction to spatial databases
  • Geometry, geography, raster, and topology spatial types, functions, and queries
  • Applying PostGIS to real-world problems
  • Extending PostGIS to web and desktop applications
  • Querying data from external sources using PostgreSQL Foreign Data Wrappers
  • Optimizing queries for maximum speed
  • Simplifying geometries for greater efficiency

PostGIS in Action, Third Edition teaches readers of all levels to write spatial queries for PostgreSQL. You’ll start by exploring vector-, raster-, and topology-based GIS before quickly progressing to analyzing, viewing, and mapping data. This fully updated third edition covers key changes in PostGIS 3.1 and PostgreSQL 13, including parallelization support, partitioned tables, and new JSON functions that help in creating web mapping applications.

About the Technology
PostGIS is a spatial database extender for PostgreSQL. It offers the features and firepower you need to take on nearly any geodata task. PostGIS lets you create location-aware queries with a few lines of SQL code, then build the backend for mapping, raster analysis, or routing application with minimal effort.

About the Book
PostGIS in Action, Third Edition shows you how to solve real-world geodata problems. You’ll go beyond basic mapping, and explore custom functions for your applications. Inside this fully updated edition, you’ll find coverage of new PostGIS features such as PostGIS Window functions, parallelization of queries, and outputting data for applications using JSON and Vector Tile functions.

What's Inside
  • Fully revised for PostGIS version 3.1 and PostgreSQL 13
  • Optimize queries for maximum speed
  • Simplify geometries for greater efficiency
  • Extend PostGIS to web and desktop applications


About the Reader
For readers familiar with relational databases and basic SQL. No prior geodata or GIS experience required.

About the Authors
Regina Obe and Leo Hsu are database consultants and authors. Regina is a member of the PostGIS core development team and the Project Steering Committee.

Quotes
The best introduction I’ve seen for engineers who want to get ramped up quickly and build advanced GIS applications.
- Ikechukwu Okonkwo, Orum.io

A wealth of information that showcases how powerful PostGIS is.
- Luis Moux-Dominguez, EMO

An extraordinary book for the world of GIS. Truly learned a lot!
- DeUndre’ Rushon, DigiDiscover LLC

Gives you insight into how best to provide map services for a wide audience.
- Marcus Brown, Enel Green Power

Publisher resources

View/Submit Errata

Table of contents

  1. From the 2nd edition of PostGIS in Action by Regina O. Obe and Leo S. Hsu
  2. PostGIS in Action
  3. Copyright
  4. dedication
  5. brief contents
  6. contents
  7. front matter
    1. foreword
    2. preface
    3. acknowledgments
    4. about this book
      1. Who should read this book?
      2. How this book is organized: a roadmap
      3. About the code
      4. liveBook discussion forum
      5. About the title
    5. about the Author
    6. about the cover illustration
  8. Part 1. Introduction to PostGIS
  9. 1 What is a spatial database?
    1. 1.1 Thinking spatially
    2. 1.2 Introducing PostGIS
      1. 1.2.1 Why PostGIS
      2. 1.2.2 Standards conformance
      3. 1.2.3 PostGIS is powerful
      4. 1.2.4 Built on top of PostgreSQL
      5. 1.2.5 Free—as in money
      6. 1.2.6 Free—as in freedom
      7. 1.2.7 Alternatives to PostGIS
    3. 1.3 Installing PostGIS
      1. 1.3.1 Verifying versions of PostGIS and PostgreSQL
    4. 1.4 Spatial data types
      1. 1.4.1 Geometry type
      2. 1.4.2 Geography type
      3. 1.4.3 Raster type
      4. 1.4.4 Topology type
    5. 1.5 Hello real world
      1. 1.5.1 Digesting the problem
      2. 1.5.2 Modeling
      3. 1.5.3 Loading data
      4. 1.5.4 Writing the query
      5. 1.5.5 Viewing spatial data with OpenJump
    6. Summary
  10. 2 Spatial data types
    1. 2.1 Type modifiers
      1. 2.1.1 Subtype type modifiers
      2. 2.1.2 Spatial reference identifier
    2. 2.2 Geometry
      1. 2.2.1 Points
      2. 2.2.2 Linestrings
      3. 2.2.3 Polygons
      4. 2.2.4 Collection geometries
      5. 2.2.5 The M coordinate
      6. 2.2.6 The Z coordinate
      7. 2.2.7 Polyhedral surfaces and TINs
      8. 2.2.8 Generating TINs
      9. 2.2.9 Curved geometries
      10. 2.2.10 Spatial catalog for geometry
    3. 2.3 Geography
      1. 2.3.1 Differences between geography and geometry
      2. 2.3.2 Spatial catalogs for geography
    4. 2.4 Raster
      1. 2.4.1 Properties of rasters
      2. 2.4.2 Creating rasters
      3. 2.4.3 Spatial catalog for rasters
    5. Summary
  11. 3 Spatial reference systems
    1. 3.1 Spatial reference systems: What are they?
      1. 3.1.1 Geoids
      2. 3.1.2 Ellipsoids
      3. 3.1.3 Datum
      4. 3.1.4 Coordinate reference system
      5. 3.1.5 Spatial reference system essentials
      6. 3.1.6 Projections
    2. 3.2 Selecting a spatial reference system for storing data
      1. 3.2.1 Pros and cons of using EPSG:4326
      2. 3.2.2 Geography data type for EPSG:4326
      3. 3.2.3 Mapping just for presentation
      4. 3.2.4 Covering the globe when distance is a concern
    3. 3.3 Determining the spatial reference system of source data
      1. 3.3.1 Guessing at a spatial reference system
      2. 3.3.2 When the SRS is missing from the spatial_ref_sys table
    4. 3.4 History of PROJ support in PostGIS
      1. 3.4.1 PROJ 4
      2. 3.4.2 PROJ 5
      3. 3.4.3 PROJ 6
      4. 3.4.4 PROJ 7
      5. 3.4.5 PROJ 8 and beyond
    5. Summary
  12. 4 Working with real data
    1. 4.1 PostgreSQL built-in tools
      1. 4.1.1 Psql
      2. 4.1.2 pgAdmin4
      3. 4.1.3 Pg_dump and pg_restore
    2. 4.2 Downloading files
    3. 4.3 Extracting files
    4. 4.4 Importing and exporting shapefiles
      1. 4.4.1 Importing with shp2pgsql
      2. 4.4.2 Importing and exporting with shp2pgsql-gui
      3. 4.4.3 Exporting with pgsql2shp
    5. 4.5 Importing and exporting vector data with ogr2ogr
      1. 4.5.1 Environment variables in ogr2ogr
      2. 4.5.2 Ogrinfo
      3. 4.5.3 Importing with ogr2ogr
      4. 4.5.4 Exporting with ogr2ogr
    6. 4.6 Querying external data using PostgreSQL foreign data wrappers
      1. 4.6.1 File_fdw foreign data wrapper
      2. 4.6.2 Ogr_fdw foreign data wrapper
      3. 4.6.3 Converting hstore tags to jsonb
    7. 4.7 Importing raster data with raster2pgsql
      1. 4.7.1 Raster2pgsql command-line switches
      2. 4.7.2 Raster2pgsql supported formats
      3. 4.7.3 Loading a single file with raster2pgsql
      4. 4.7.4 Loading multiple files and tiling in shell script
      5. 4.7.5 Using PostgreSQL functions to output raster data
    8. 4.8 Exporting raster data with GDAL
      1. 4.8.1 Using gdalinfo to inspect rasters
      2. 4.8.2 Gdal_translate and gdalwarp
    9. Summary
  13. 5 Using PostGIS on the desktop
    1. 5.1 Desktop viewing tools at a glance
      1. 5.1.1 OpenJUMP
      2. 5.1.2 QGIS
      3. 5.1.3 gvSIG
      4. 5.1.4 Jupyter Notebook and JupyterLab
      5. 5.1.5 Spatial database support
      6. 5.1.6 Format support
      7. 5.1.7 Web services supported
    2. 5.2 OpenJUMP
      1. 5.2.1 OpenJUMP feature summary
      2. 5.2.2 Installing OpenJUMP
      3. 5.2.3 Ease of use
      4. 5.2.4 OpenJUMP plug-ins
      5. 5.2.5 OpenJUMP scripting
      6. 5.2.6 OpenJUMP format support
      7. 5.2.7 PostGIS support
      8. 5.2.8 Registering data sources
      9. 5.2.9 Rendering PostGIS geometries
      10. 5.2.10 Exporting data
    3. 5.3 QGIS
      1. 5.3.1 Installing QGIS
      2. 5.3.2 Using QGIS with PostGIS
    4. 5.4 GvSIG
      1. 5.4.1 Using gvSIG with PostGIS
      2. 5.4.2 Exporting data
    5. 5.5 JupyterLab and Jupyter Notebook
      1. 5.5.1 Installing Jupyter
      2. 5.5.2 Launching Jupyter Notebook
      3. 5.5.3 Launching JupyterLab
      4. 5.5.4 Creating a Python notebook
      5. 5.5.5 Magic commands
      6. 5.5.6 Performing raw queries with Jupyter Notebook
      7. 5.5.7 UTable 5.6 sing GeoPandas, Shapely, and Matplotlib to work with spatial data
      8. 5.5.8 Viewing data on a map with folium
    6. Summary
  14. 6 Geometry and geography functions
    1. 6.1 Output functions
      1. 6.1.1 Well-known text (WKT) and well-known binary (WKB)
      2. 6.1.2 Keyhole Markup Language (KML)
      3. 6.1.3 Geography Markup Language (GML)
      4. 6.1.4 Geometry JavaScript Object Notation (GeoJSON)
      5. 6.1.5 Scalable Vector Graphics (SVG)
      6. 6.1.6 Mapbox Vector Tiles (MVT) and protocol buffers
      7. 6.1.7 Tiny WKB (TWKB)
      8. 6.1.8 Extensible 3D Graphics (X3D)
      9. 6.1.9 Examples of output functions
      10. 6.1.10 Geohash
    2. 6.2 Constructor functions
      1. 6.2.1 Creating geometries from text and binary formats
      2. 6.2.2 Creating geographies from text and binary formats
      3. 6.2.3 Using text or binary representations as function arguments
    3. 6.3 Accessor and setter functions
      1. 6.3.1 Spatial reference identifiers
      2. 6.3.2 Transforming geometry to different spatial references
      3. 6.3.3 Using transformation with the geography type
      4. 6.3.4 Geometry type functions
      5. 6.3.5 Geometry and coordinate dimensions
      6. 6.3.6 Retrieving coordinates
      7. 6.3.7 Checking geometry validity
      8. 6.3.8 Number of points that define a geometry
    4. 6.4 Measurement functions
      1. 6.4.1 Geometry planar measurements
      2. 6.4.2 Geodetic measurements
    5. 6.5 Decomposition functions
      1. 6.5.1 Bounding box of geometries
      2. 6.5.2 Boundaries and converting polygons to linestrings
      3. 6.5.3 Centroid, median, and point on surface
      4. 6.5.4 Returning points defining a geometry
      5. 6.5.5 Decomposing multi-geometries and geometry collections
    6. 6.6 Composition functions
      1. 6.6.1 Making points
      2. 6.6.2 Making polygons
      3. 6.6.3 Promoting single geometries to multi-geometries
    7. 6.7 Simplification functions
      1. 6.7.1 Grid snapping and coordinate rounding
      2. 6.7.2 Simplification
    8. Summary
  15. 7 Raster functions
    1. 7.1 Raster terminology
    2. 7.2 Raster constructors
      1. 7.2.1 Converting geometries to rasters with ST_AsRaster
      2. 7.2.2 Loading rasters with raster2pgsql
      3. 7.2.3 Constructing rasters from scratch: ST_MakeEmptyRaster and ST_AddBand
      4. 7.2.4 Setting pixels: ST_SetValue and ST_SetValues
      5. 7.2.5 Creating rasters from other rasters
      6. 7.2.6 Converting other raster formats with ST_FromGDALRaster
    3. 7.3 Raster output functions
      1. 7.3.1 ST_AsPNG, ST_AsJPEG, and ST_AsTiff
      2. 7.3.2 Output using ST_AsGDALRaster
      3. 7.3.3 Using psql to export rasters
    4. 7.4 Raster accessors and setters
      1. 7.4.1 Basic raster metadata properties
      2. 7.4.2 Pixel statistics
      3. 7.4.3 Pixel value accessors
      4. 7.4.4 Band metadata setters
    5. 7.5 Georeferencing functions
      1. 7.5.1 Metadata setters
      2. 7.5.2 Processing functions
    6. 7.6 Reclassing functions
    7. 7.7 Polygonizing functions
      1. 7.7.1 ST_ConvexHull
      2. 7.7.2 ST_Envelope
      3. 7.7.3 ST_Polygon
      4. 7.7.4 ST_MinConvexHull
    8. Summary
  16. 8 Spatial relationships
    1. 8.1 Bounding box and geometry comparators
      1. 8.1.1 The bounding box
      2. 8.1.2 Bounding box comparators
    2. 8.2 Relating two geometries
      1. 8.2.1 Interior, exterior, and boundary of a geometry
      2. 8.2.2 Intersections
      3. 8.2.3 A house plan model
      4. 8.2.4 Contains and within
      5. 8.2.5 Covers and covered by
      6. 8.2.6 Contains properly
      7. 8.2.7 Overlapping geometries
      8. 8.2.8 Touching geometries
      9. 8.2.9 The faces of equality: geometry
      10. 8.2.10 Underpinnings of relationship functions
    3. Summary
  17. Part 2. Putting PostGIS to work
  18. 9 Proximity analysis
    1. 9.1 Nearest neighbor searches
      1. 9.1.1 Which places are within X distance?
      2. 9.1.2 Using ST_DWithin and ST_Distance for N closest results
      3. 9.1.3 Using ST_DWithin and DISTINCT ON to find closest locations
      4. 9.1.4 Intersects with tolerance
      5. 9.1.5 Items between distances
      6. 9.1.6 Finding the N closest places using KNN distance operators
    2. 9.2 Using KNN with geography types
      1. 9.2.1 Using window functions to number the closest N places
    3. 9.3 Geotagging
      1. 9.2.1 Tagging data to a specific region
      2. 9.2.2 Linear referencing: snapping points to the closest linestring
      3. 9.2.3 PostGIS cluster window functions
    4. Summary
  19. 10 PostGIS TIGER geocoder
    1. 10.1 Installing the PostGIS TIGER geocoder
    2. 10.2 Loading TIGER data
      1. 10.2.1 Configuration tables
      2. 10.2.2 Loading nation and state data
    3. 10.3 Normalizing addresses
      1. 10.3.1 Using normalize_address
      2. 10.3.2 Using the PAGC address normalizer
    4. 10.4 Geocoding
      1. 10.4.1 Geocoding using address text
      2. 10.4.2 Geocoding using normalized addresses
      3. 10.4.3 Geocoding intersections
      4. 10.4.4 Batch geocoding
    5. 10.5 Reverse geocoding
    6. Summary
  20. 11 Geometry and geography processing
    1. 11.1 Using spatial aggregate functions
      1. 11.1.1 Creating a multipolygon from many multipolygon records
      2. 11.1.2 Creating linestrings from points
    2. 11.2 Clipping, splitting, tessellating
      1. 11.2.1 Clipping
      2. 11.2.2 Splitting
      3. 11.2.3 Tessellating
    3. 11.3 Breaking linestrings into smaller segments
      1. 11.3.1 Segmentizing linestrings
      2. 11.3.2 Creating two-point linestrings from many-point linestrings
      3. 11.3.3 Breaking linestrings at point junctions
    4. 11.4 Translating, scaling, and rotating geometries
      1. 11.4.1 Translating
      2. 11.4.2 Scaling
      3. 11.4.3 Rotating
    5. 11.5 Using geometry functions to manipulate and create geographies
      1. 11.5.1 Cast-safe functions
    6. Summary
  21. 12 Raster processing
    1. 12.1 Loading and preparing raster data
    2. 12.2 Forming larger rasters using spatial aggregate functions
      1. 12.2.1 Reconstituting tiled files
      2. 12.2.2 Carving out areas of interest using clipping and unioning
      3. 12.2.3 Using specific expression types with ST_Union
    3. 12.3 Working with bands
      1. 12.3.1 Using ST_AddBand to form multiband rasters from single-band rasters
      2. 12.3.2 Using ST_Band to process a subset of bands
    4. 12.4 Tiling rasters
    5. 12.5 Raster and geometry intersections
      1. 12.5.1 Pixel stats
      2. 12.5.2 Adding a Z coordinate to a 2D linestring using ST_Value and ST_SetZ
      3. 12.5.3 Converting 2D polygons to 3D polygons
    6. 12.6 Raster statistics
      1. 12.6.1 Extruding pixel values
      2. 12.6.2 Raster statistics functions
    7. 12.7 Map algebra
      1. 12.7.1 Choosing between expression or callback function
      2. 12.7.2 Using a single-band map algebra expression
      3. 12.7.3 Using a single-band map algebra function
      4. 12.7.4 Map algebra with neighborhoods
    8. Summary
  22. 13 Building and using topologies
    1. 13.1 What topology is
    2. 13.2 Using topologies
      1. 13.2.1 Installing the topology extension
      2. 13.2.2 Creating a topology
      3. 13.2.3 The topogeometry type
      4. 13.2.4 Recap of using topologies
    3. 13.3 Topology of Victoria, BC
      1. 13.3.1 Creating the Victoria topology
      2. 13.3.2 Adding primitives to a topology
      3. 13.3.3 Creating topogeometries
    4. 13.4 Fixing topogeometry issues by editing topology primitives
      1. 13.4.1 Removing faces by removing edges
      2. 13.4.2 Checking for shared faces
      3. 13.4.3 Editing topogeometries
    5. 13.5 Inserting and editing large data sets
    6. 13.6 Simplifying with topology in mind
    7. 13.7 Topology validation and summary functions
    8. Summary
  23. 14 Organizing spatial data
    1. 14.1 Spatial storage approaches
      1. 14.1.1 Heterogeneous columns
      2. 14.1.2 Homogeneous columns
      3. 14.1.3 Typmod vs. constraints
      4. 14.1.4 Table inheritance
      5. 14.1.5 Table partitioning
    2. 14.2 Modeling a real city
      1. 14.2.1 Modeling using heterogeneous geometry columns
      2. 14.2.2 Modeling using homogeneous geometry columns
      3. 14.2.3 Modeling using partitioning
    3. 14.3 Making auto-updatable views
    4. 14.4 Using triggers and rules
      1. 14.4.1 Triggers
      2. 14.4.2 Using INSTEAD OF triggers
      3. 14.4.3 Using other triggers
    5. Summary
  24. 15 Query performance tuning
    1. 15.1 The query planner
      1. 15.1.1 Different kinds of spatial queries
      2. 15.1.2 Common table expressions and how they affect plans
    2. 15.2 Planner statistics
    3. 15.3 Using explain to diagnose problems
      1. 15.3.1 Text explain vs. pgAdmin graphical explain
      2. 15.3.2 The plan with no index
    4. 15.4 Planner and indexes
      1. 15.4.1 The plan with a spatial index
      2. 15.4.2 Indexes
    5. 15.5 Common SQL patterns and how they affect plans
      1. 15.5.1 Subqueries in SELECT
      2. 15.5.2 FROM subqueries and basic CTEscommon table expressions (CTEs)
      3. 15.5.3 Window functions and self joins
      4. 15.5.4 Lateral joins
    6. 15.6 System and function settings
      1. 15.6.1 Key system variables that affect plan strategies
      2. 15.6.2 Function-specific settings
      3. 15.6.3 Encouraging parallel plans
    7. 15.7 Optimizing spatial data
      1. 15.7.1 Fixing invalid geometries
      2. 15.7.2 Reducing the number of vertices by simplification
      3. 15.7.3 Reducing the number of vertices by breaking geometries apart
      4. 15.7.4 Clustering
    8. Summary
  25. Part 3. Using PostGIS with other tools
  26. 16 Extending PostGIS with pgRouting and procedural languages
    1. 16.1 Solving network routing problems with pgRouting
      1. 16.1.1 Installing pgRouting
    2. 16.2 Extending PostgreSQL with PLs
      1. 16.2.1 Basic installation of PLs
      2. 16.2.2 What you can do with PLs
    3. 16.3 PL/R
      1. 16.3.1 Getting started with PL/R
      2. 16.3.2 What you can do with PL/R
      3. 16.3.3 Using R packages in PL/R
      4. 16.3.4 Converting geometries into R spatial objects and plotting spatial objects
      5. 16.3.5 Outputting plots as binaries
    4. 16.4 PL/Python
      1. 16.4.1 Installing PL/Python
      2. 16.4.2 Writing a PL/Python function
      3. 16.4.3 Using Python packages
      4. 16.4.4 Geocoding example
    5. 16.5 PL/V8: JavaScript in the database
      1. 16.5.1 Installing PL/V8
      2. 16.5.2 Enabling PL/V8 in a database
      3. 16.5.3 Using other JavaScript libraries and functions in PL/V8
      4. 16.5.4 Using PL/V8 to write map algebra functions
    6. Summary
  27. 17 Using PostGIS in web applications
    1. 17.1 Limitations of conventional web technologies
    2. 17.2 Mapping servers
      1. 17.2.1 Lightweight mapping servers
      2. 17.2.2 Full mapping servers
    3. 17.3 Mapping clients
      1. 17.3.1 Proprietary services
    4. 17.4 Using MapServer
      1. 17.4.1 Installing MapServer
      2. 17.4.2 Security considerations
      3. 17.4.3 Creating WMS and WFS services
      4. 17.4.4 Calling a mapping service using a reverse proxy
    5. 17.5 Using GeoServer
      1. 17.5.1 Installing GeoServer
      2. 17.5.2 Setting up PostGIS workspaces
      3. 17.5.3 Accessing PostGIS layers via GeoServer WMS/WFS
    6. 17.6 Basics of OpenLayers and Leaflet
      1. 17.6.1 OpenLayers primer
      2. 17.6.2 Leaflet primer
      3. 17.6.3 Synopsis of the OpenLayers and Leaflet APIs
    7. 17.7 Displaying data with PostGIS queries and web scripting
      1. 17.7.1 Using PostGIS and PostgreSQL geometry output functions
      2. 17.7.2 Using PostGIS MVT output functionsMapbox Vector Tiles (MVT)
    8. Summary
  28. Appendix A. Additional resources
    1. A.1 Planet sites
    2. A.2 Open source tools and offerings
      1. A.2.1 Self-contained GIS suites
      2. A.2.2 Open source desktop tools
    3. A.3 Open source extract-transform-load (ETL)
    4. A.4 Places to get free data
  29. Appendix B. Installing, compiling, and upgrading
    1. B.1 Installing PostgreSQL and PostGIS
      1. B.1.1 Using PostgreSQL and PostGIS in Docker
      2. B.1.2 EnterpriseDB one-click installers
      3. B.1.3 MacOS-specific installers
      4. B.1.4 Installing on a Linux server (Red Hat EL, CentOS) using YUM
      5. B.1.5 PostgreSQL Apt repository
      6. B.1.6 Other available binaries and distros
      7. B.1.7 Database as a service offerings for PostGIS
      8. B.1.8 Compiling and installing from PostGIS source
    2. B.2 Creating a PostGIS database
    3. B.3 Upgrading PostGIS
      1. B.3.1 PostGIS soft upgrade using extensions
      2. B.3.2 Upgrading PostgreSQL and PostGIS using pg_upgrade
      3. B.3.3 Upgrading PostGIS from 1.X to 2.X or 3.X
  30. Appendix C. SQL primer
    1. C.1 The information_schema
    2. C.2 Querying data with SQL
      1. C.2.1 SELECT, FROM, WHERE, and ORDER BY clauses
      2. C.2.2 Indexes
      3. C.2.3 Aliasing
      4. C.2.4 Why use AS when you don’t need to
      5. C.2.5 Using subselects
      6. C.2.6 JOINs
    3. C.3 UPDATE, INSERT, and DELETE
      1. C.3.1 UPDATE
      2. C.3.2 INSERT
      3. C.3.3 DELETE
  31. Appendix D. PostgreSQL features
    1. D.1 What makes PostgreSQL special?
      1. D.1.1 PostgreSQL’s unique features
      2. D.1.2 Basic enterprise features
      3. D.1.3 Advanced enterprise features
      4. D.1.4 More features in PostgreSQL 12, 13, and 14
    2. D.2 Useful PostgreSQL resources
      1. D.2.1 General resources
      2. D.2.2 PostgreSQL-specific tools
    3. D.3 Connecting to a PostgreSQL server
      1. D.3.1 Core configuration files
      2. D.3.2 Launching psql
      3. D.3.3 Launching pgAdmin
      4. D.3.4 Connection difficulties
      5. D.3.5 Enabling advanced administration for pgAdmin
    4. D.4 Controlling access to data
      1. D.4.1 Connection rules
      2. D.4.2 Users and groups (rolesusers and groups (roles))
      3. D.4.3 Rights management
    5. D.5 Backup and restore
      1. D.5.1 Backup
      2. D.5.2 Restore
      3. D.5.3 Setting up automated jobs for backup
    6. D.6 Data structures and objects
      1. D.6.1 PostgreSQL objects
      2. D.6.2 Built-in data types
      3. D.6.3 Anatomy of a database function
      4. D.6.4 Defining custom data types
      5. D.6.5 Creating tables and views
    7. D.7 Writing functions in SQL
      1. D.7.1 When to use SQL functions
      2. D.7.2 Creating an SQL function
      3. D.7.3 Rules
      4. D.7.4 Creating aggregate functions
    8. D.8 Writing functions in PL/pgSQL
      1. D.8.1 When to use PL/pgSQL functions
      2. D.8.2 Creating a PL/pgSQL function
      3. D.8.3 Creating triggers
    9. D.9 Index performance
      1. D.9.1 B-tree index gotchas
      2. D.9.2 Functional index gotchas
    10. D.10 Computed columns
  32. index

Product information

  • Title: PostGIS in Action, Third Edition
  • Author(s): Regina O. Obe, Leo S. Hsu
  • Release date: September 2021
  • Publisher(s): Manning Publications
  • ISBN: 9781617296697