Look Inside
FREE
You can see any available part of this book for free.Click the table of contents to start reading.
free previous edition eBook included
An eBook copy of the previous edition of this book is included at no additional cost. It will be automatically added to your Manning Bookshelf within 24 hours of purchase.
PostGIS in Action, Third Edition teaches readers of all levels to write spatial queries for PostgreSQL. It first gives you a background in vector-, raster-, and topology-based GIS and then quickly moves into analyzing, viewing, and mapping data. This fully updated third edition covers key changes in PostGIS 3.0 and PostgreSQL 12, including parallelization support, partitioned tables, and new JSON functions that help in creating web mapping applications.A must-read book for novice geographers.
About the Technology
Processing location and topology data requires specialized know-how. PostGIS is a free spatial database extender for PostgreSQL that delivers the features and firepower you need to take on nearly any geodata task. With it, you can easily create location-aware queries in just a few lines of SQL code and build the back end for a mapping, raster analysis, or routing application with minimal effort.About the book
PostGIS in Action, Third Edition teaches you to solve real-world geodata problems. It first gives you a background in vector-, raster-, and topology-based GIS and then quickly moves into analyzing, viewing, and mapping data. You'll learn how to optimize queries for maximum speed, simplify geometries for greater efficiency, and create custom functions for your own applications. You'll also learn how to apply your existing GIS knowledge to PostGIS and integrate with other GIS tools. Fully updated to the latest versions of PostGIS and PostgreSQL, this Third Edition covers new PostGIS features including Foreign Data Wrappers, Database as a Service, parallelization of queries, and new JSON and Vector Tiles functions that help in creating web mapping applications.
Table of Contents takes you straight to the bookdetailed table of contents
Part 1: Introduction to PostGIS
1 What is a spatial database?
1.1 Thinking spatially
1.2 Introducing PostGIS
1.3 Why PostGIS
1.3.1 Standards conformance
1.3.2 Built on top of PostgreSQL
1.3.3 Free—as in money
1.3.4 Alternatives to PostGIS
1.3.5 Oracle Spatial
1.3.6 Microsoft SQL Server
1.4 Installing PostGIS
1.4.1 Verifying versions of PostGIS and PostgreSQL
1.5 Spatial data types
1.5.1 Geometry type
1.5.2 Geography type
1.5.3 Raster type
1.5.4 Topology type
1.6 Hello real world
1.6.1 Digesting the problem
1.6.2 Modeling
1.6.3 Loading data
1.6.4 Writing the query
1.6.5 Viewing spatial data with OpenJump
1.7 Summary
2 Spatial data types
2.1 Type modifiers
2.1.1 Subtype type modifiers
2.1.2 Spatial reference identifier
2.2 Geometry
2.2.1 Points
2.3 Geography
2.3.1 Differences between geography and geometry
2.3.2 Spatial catalogs for geography
2.4 Raster
2.4.1 Properties of rasters
2.4.2 Creating rasters
2.4.3 Spatial catalog for rasters
2.5 Summary
3 Spatial reference systems
3.1 Spatial reference systems: What are they?
3.1.1 Geoids
3.1.2 Ellipsoids
3.1.3 Datum
3.1.4 Selecting a spatial reference system for storing data
3.1.5 Determining the spatial reference system of source data
3.2 Summary
4 Working with real data
4.1 General utilities
4.2 PostgreSQL built-in tools
4.2.1 psql
4.2.2 pgAdmin4
4.2.3 pg_dump and pg_restore
4.3 Downloading files
4.3.1 Extracting files
4.3.2 Importing and exporting shapefiles
4.3.3 Importing and exporting vector data with ogr2ogr
4.4 Querying external data using PostgreSQL foreign data wrappers
4.4.1 file_fdw Foreign Data wrapper
4.4.2 ogr_fdw Foreign Data wrapper
4.4.3 Converting hstore tags to jsonb
4.4.4 Importing raster data with raster2pgsql
4.4.5 Exporting raster data with GDAL
4.5 Summary
5 Using PostGIS on the desktop
5.1 Desktop viewing tools at a glance
5.2 Capsule reviews
5.2.1 OpenJUMP
5.2.2 QGIS
5.2.3 gvSIG
5.2.4 Jupyter notebook and lab
5.2.5 Spatial database support
5.2.6 Format support
5.2.7 Web services supported
5.3 OpenJUMP workbench
5.3.1 OpenJUMP feature summary
5.3.2 Installing OpenJUMP
5.3.3 PostGIS support
5.3.4 Register data source
5.3.5 Rendering PostGIS geometries
5.3.6 Exporting data
5.4 QGIS
5.4.1 Installing QGIS
5.4.2 Using QGIS with PostGIS
5.4.3 gvSIG
5.4.4 Jupyter lab and notebook
5.4.5 Installing Jupyter
5.4.6 Launching Jupyter Notebook App
5.4.7 Launching Jupyter Lab App
5.4.8 Creating a Python notebook
5.4.9 Magic Commands
5.4.10 Doing raw queries with Jupyter Notebook
5.5 Using GeoPandas, shapely, and matplotlib to work with spatial data
5.5.1 Loading data with GeoPandas
5.6 Viewing data on a map with folium
5.7 Summary
Part 2: PostGIS basic operations
6 Geometry and geography functions
6.1 Output functions
6.1.1 Well-known binary (WKB) and well-known text (WKT)
6.1.2 Keyhole Markup Language (KML)
6.1.3 Geography Markup Language (GML)
6.1.4 Geometry JavaScript Object Notation (GeoJSON)
6.1.5 Scalable Vector Graphics (SVG)
6.1.6 Mapbox Vector Tile (MVT) and Protocol Buffers
6.1.7 Tiny WKB (TWKB)
6.1.8 Extensible 3D Graphics (X3D)
6.1.9 Examples of output functions
6.1.10 Geohash
6.2 Constructor functions
6.2.1 Creating geometries from text and binary formats
6.2.2 ST_GeomFromText
6.2.3 ST_GeomFromWKB and ST_GeomFromEWKB
6.2.4 ST_GeomFromGML, ST_GeomFromGeoJSON, ST_GeomFromKML, ST_GeomFromTWKB, ST_GeomFromGeoHash
6.2.5 Creating geographies from text and binary formats
6.2.6 Using text or binary representations as function arguments
6.3 Accessor and setter functions
6.3.1 Spatial reference identifiers
6.3.2 Transforming geometry to different spatial references
6.3.3 Using transformation with the geography type
6.3.4 Geometry type functions
6.3.5 Geometry and coordinate dimensions
6.3.6 Retrieving coordinates
6.3.7 Checking geometry validity
6.3.8 Number of points that defines a geometry
6.4 Measurement functions
6.4.1 Geometry planar measurements
6.4.2 Geodetic measurements
6.5 Decomposition functions
6.5.1 Bounding box of geometries
6.5.2 Boundaries and converting polygons to linestrings
6.5.3 Centroid, Median, and Point on surface
6.5.4 Returning points defining a geometry
6.5.5 Decomposing multi-geometries and geometry collections
6.6 Composition functions
6.6.1 Making points
6.6.2 Making polygons
6.6.3 ST_MakeEnvelope
6.6.4 ST_MakePolygon
6.6.5 ST_BuildArea
6.6.6 ST_Polygonize
6.6.7 Promoting single geometries to multi-geometries
6.7 Simplification functions
6.7.1 Grid snapping and coordinate rounding
6.7.2 Simplification
6.8 Summary
7 Raster functions
7.1 Raster terminology
7.2 Raster constructors
7.2.1 Converting geometries to rasters with ST_AsRaster
7.2.2 Loading rasters with raster2pgsql
7.2.3 Constructing rasters from scratch: ST_MakeEmptyRaster and ST_AddBand
7.2.4 Setting pixels: ST_SetValue and ST_SetValues
7.2.5 Creating rasters from other rasters
7.2.6 Converting other raster formats with ST_FromGDALRaster
7.3 Raster output functions
7.3.1 ST_AsPNG, ST_AsJPEG, and ST_AsTiff
7.3.2 Output using ST_AsGDALRaster
7.3.3 Using psql to export rasters
7.4 Raster accessors and setters
7.4.1 Basic raster metadata properties
7.4.2 Pixel statistics
7.4.3 Pixel value accessors
7.4.4 Band metadata setters
7.5 Georeferencing functions
7.5.1 Metadata setters
7.5.2 Processing functions
7.6 Reclassing functions
7.7 Polygonizing functions
7.7.1 ST_ConvexHull
7.7.2 ST_Envelope
7.7.3 ST_Polygon
7.7.4 ST_MinConvexHull
7.8 Summary
8 Spatial relationships
8.1 Bounding box and geometry comparators
8.1.1 The bounding box
8.1.2 Bounding box comparators
8.2 Relating two geometries
8.2.1 Interior, exterior, and boundary of a geometry
8.2.2 Intersections
8.2.3 A house plan model
8.2.4 Contains and within
8.2.5 Covers and covered by
8.2.6 Contains properly
8.2.7 Overlapping geometries
8.2.8 Touching geometries
8.2.9 The faces of equality: geometry
8.2.10 Underpinnings of relationship functions
8.3 Summary
Part 3: Putting PostGIS to work
9 Proximity analysis
9.1 Nearest neighbor searches
9.1.1 Which places are within X distance?
9.1.2 Using ST_DWithin and ST_Distance for N closest results
9.1.3 Using ST_DWithin and DISTINCT ON to find closest locations
9.1.4 Intersects with tolerance
9.1.5 Finding N closest places using KNN distance operators
9.2 Using KNN with geography types
9.2.1 Using window functions to number closest N places
9.3 Geotagging
9.3.1 Tagging data to a specific region
9.3.2 Linear referencing: snapping points to the closest linestring
9.4 Summary
10 PostGIS TIGER geocoder
10.1 Installing the PostGIS TIGER geocoder
10.2 Loading TIGER data
10.2.1 Configuration tables
10.2.2 Loading nation and state data
10.3 Normalizing addresses
10.3.1 Using normalize_address
10.3.2 Using the PAGC address normalizer
10.4 Geocoding
10.4.1 Geocoding using address text
10.4.2 Geocoding using normalized addresses
10.4.3 Geocoding intersections
10.4.4 Batch geocoding
10.5 Reverse geocoding
10.6 Summary
11 Geometry and geography processing
11.1 Using spatial aggregate functions
11.1.1 Creating a multipolygon from many multipolygon records
11.1.2 Creating linestrings from points
11.2 Clipping, splitting, tessellating
11.2.1 Clipping
11.2.2 Splitting
11.2.3 Tessellating
11.3 Breaking linestrings into smaller segments
11.3.1 Segmentizing linestrings
11.3.2 Creating two-point linestrings from many-point linestrings
11.3.3 Breaking linestrings at point junctions
11.4 Translating, scaling, and rotating geometries
11.4.1 Translating
11.4.2 Scaling
11.4.3 Rotating
11.5 Using geometry functions to manipulate and create geographies
11.5.1 Cast-safe functions
11.6 Summary
12 Raster processing
12.1 Loading and preparing data
12.1.1 Forming larger rasters using spatial aggregate functions
12.1.2 Working with bands
12.1.3 Tiling rasters
12.1.4 Raster and geometry intersections
12.1.5 Raster statistics
12.1.6 Map algebra
12.2 Summary
13 Building and using topologies
13.1 What topology is
13.2 Using topologies
13.2.1 Installing the topology extension
13.2.2 Creating a topology
13.2.3 The topogeometry type
13.2.4 Recap of using topologies
13.3 Topology of Victoria, BC
13.3.1 Creating the Victoria topology
13.3.2 Adding primitives to a topology
13.3.3 Creating topogeometries
13.4 Fixing topogeometry issues by editing topology primitives
13.4.1 Removing faces by removing edges
13.4.2 Checking for shared faces
13.4.3 Editing topogeometries
13.5 Inserting and editing large data sets
13.6 Simplifying with topology in mind
13.7 Topology validation and summary functions
13.8 Summary
14 Organizing spatial data
14.1 Spatial storage approaches
14.2 Heterogeneous columns
14.2.1 Pros of heterogeneous columns
14.2.2 Homogeneous columns
14.2.3 Typmod vs. constraints
14.2.4 Table inheritance
14.2.5 Table partitioning
14.3 Modeling a real city
14.3.1 Modeling using heterogeneous geometry columns
14.3.2 Modeling using homogeneous geometry columns
14.3.3 Modeling using partitioning
14.4 Making auto-updatable views
14.5 Using Triggers and rules
14.5.1 Triggers
14.5.2 Using INSTEAD OF triggers
14.5.3 Using other triggers
14.6 Summary
15 Query performance tuning
15.1 The query planner
15.1.1 Different kinds of spatial queries
15.1.2 Common table expressions and how they affect plans
15.2 Planner statistics
15.3 Using explain to diagnose problems
15.3.1 Text explain vs. pgAdmin graphical explain
15.3.2 The plan with no index
15.4 Planner and indexes
15.4.1 The plan with a spatial index
15.4.2 Indexes
15.5 Common SQL patterns and how they affect plans
15.5.1 Subqueries in SELECT
15.5.2 FROM subqueries and basic CTEs
15.5.3 Window functions and self joins
15.5.4 Lateral joins
15.6 System and function settings
15.6.1 Key system variables that affect plan strategy
15.6.2 Function-specific settings
15.6.3 Encouraging Parallel Plans
15.7 Optimizing spatial data
15.7.1 Fixing invalid geometries
15.7.2 Reducing the number of vertices by simplification
15.7.3 Reducing the number of vertices by breaking geometries apart
15.7.4 Clustering
15.8 Summary
Part 4: Using PostGIS with other tools
16 Extending PostGIS with pgRouting and procedural languages
16.1 Solving network routing problems with pgRouting
16.1.1 Installing pgRouting
16.1.2 Extending PostgreSQL with PLs
16.1.3 PL/R
16.1.4 PL/Python
16.1.5 PL/V8 - JavaScript in the Database
16.1.6 Summary
17 Using PostGIS in web applications
Appendixes
Appendix A: Additional resources
A.1 Planet sites
A.2 Open source tools and offerings
A.2.1 Self-contained GIS suites
A.2.2 Open source desktop tools
A.3 Open source extract-transform-load (ETL)
A.4 Places to get free data
Appendix B: Installing, compiling, and upgrading
B.1 Installing PostgreSQL and PostGIS
B.1.1 Using PostgreSQL and PostGIS in Docker
B.1.2 EnterpriseDB one-click installers
B.1.3 Creating a PostGIS database
B.1.4 Upgrading PostGIS
Appendix C: SQL primer
C.1 information_schema
C.2 Querying data with SQL
C.2.1 SELECT, FROM, WHERE, and ORDER BY clauses
C.2.2 Indexes
C.2.3 Aliasing
C.2.4 Why use AS when you don’t need to
C.2.5 Using subselects
C.2.6 JOINs
C.2.7 UPDATE, INSERT, and DELETE
Appendix D: PostgreSQL features
D.1 What makes PostgreSQL special?
D.1.1 PostgreSQL’s unique features
D.1.2 Basic enterprise features
D.1.3 Advanced enterprise features
D.1.4 More features in PostgreSQL 12, and coming in 13
D.1.5 Useful PostgreSQL resources
D.1.6 Connecting to a PostgreSQL server
D.1.7 Controlling access to data
D.1.8 Backup and restore
D.1.9 Data structures and objects
D.1.10 Writing functions in SQL
D.1.11 Writing functions in PL/pgSQL
D.1.12 Index performance
D.1.13 Computed Columns
What's inside
- 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
- Updated for PostGIS 3 and PostgreSQL 12
placing your order...
Don't refresh or navigate away from the page.
Manning Early Access Program (MEAP)
Read chapters as they are written, get the finished eBook as soon as it’s ready, and receive the pBook long before it's in bookstores.
- customers also bought these items
- Kubernetes in Action, Second Edition
- The Art of Network Penetration Testing
- Grokking Simplicity
- Designing APIs with Swagger and OpenAPI
- Python Workout
- customers also bought these items
- Designing APIs with Swagger and OpenAPI
- Real-World Cryptography
- Python Workout
- How Machine Learning Works
- Geoprocessing with Python
Prices displayed in rupees will be charged in USD when you check out.
customers also reading
This book
1-hop
2-hops
3-hops
FREE domestic shipping on three or more pBooks
