PostGIS in Action, 3rd Edition
- Length: 664 pages
- Edition: 3
- Language: English
- Publisher: Manning
- Publication Date: 2021-09-21
- ISBN-10: 1617296694
- ISBN-13: 9781617296697
- Sales Rank: #741310 (See Top 100 Books)
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.
Summary
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 author
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.
From the 2nd edition of PostGIS in Action by Regina O. Obe and Leo S. Hsu PostGIS in Action Copyright dedication brief contents contents front matter foreword preface acknowledgments about this book Who should read this book? How this book is organized: a roadmap About the code liveBook discussion forum About the title about the Author about the cover illustration Part 1. Introduction to PostGIS 1 What is a spatial database? 1.1 Thinking spatially 1.2 Introducing PostGIS 1.2.1 Why PostGIS 1.2.2 Standards conformance 1.2.3 PostGIS is powerful 1.2.4 Built on top of PostgreSQL 1.2.5 Free—as in money 1.2.6 Free—as in freedom 1.2.7 Alternatives to PostGIS 1.3 Installing PostGIS 1.3.1 Verifying versions of PostGIS and PostgreSQL 1.4 Spatial data types 1.4.1 Geometry type 1.4.2 Geography type 1.4.3 Raster type 1.4.4 Topology type 1.5 Hello real world 1.5.1 Digesting the problem 1.5.2 Modeling 1.5.3 Loading data 1.5.4 Writing the query 1.5.5 Viewing spatial data with OpenJump 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.2.2 Linestrings 2.2.3 Polygons 2.2.4 Collection geometries 2.2.5 The M coordinate 2.2.6 The Z coordinate 2.2.7 Polyhedral surfaces and TINs 2.2.8 Generating TINs 2.2.9 Curved geometries 2.2.10 Spatial catalog for geometry 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 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 Coordinate reference system 3.1.5 Spatial reference system essentials 3.1.6 Projections 3.2 Selecting a spatial reference system for storing data 3.2.1 Pros and cons of using EPSG:4326 3.2.2 Geography data type for EPSG:4326 3.2.3 Mapping just for presentation 3.2.4 Covering the globe when distance is a concern 3.3 Determining the spatial reference system of source data 3.3.1 Guessing at a spatial reference system 3.3.2 When the SRS is missing from the spatial_ref_sys table 3.4 History of PROJ support in PostGIS 3.4.1 PROJ 4 3.4.2 PROJ 5 3.4.3 PROJ 6 3.4.4 PROJ 7 3.4.5 PROJ 8 and beyond Summary 4 Working with real data 4.1 PostgreSQL built-in tools 4.1.1 Psql 4.1.2 pgAdmin4 4.1.3 Pg_dump and pg_restore 4.2 Downloading files 4.3 Extracting files 4.4 Importing and exporting shapefiles 4.4.1 Importing with shp2pgsql 4.4.2 Importing and exporting with shp2pgsql-gui 4.4.3 Exporting with pgsql2shp 4.5 Importing and exporting vector data with ogr2ogr 4.5.1 Environment variables in ogr2ogr 4.5.2 Ogrinfo 4.5.3 Importing with ogr2ogr 4.5.4 Exporting with ogr2ogr 4.6 Querying external data using PostgreSQL foreign data wrappers 4.6.1 File_fdw foreign data wrapper 4.6.2 Ogr_fdw foreign data wrapper 4.6.3 Converting hstore tags to jsonb 4.7 Importing raster data with raster2pgsql 4.7.1 Raster2pgsql command-line switches 4.7.2 Raster2pgsql supported formats 4.7.3 Loading a single file with raster2pgsql 4.7.4 Loading multiple files and tiling in shell script 4.7.5 Using PostgreSQL functions to output raster data 4.8 Exporting raster data with GDAL 4.8.1 Using gdalinfo to inspect rasters 4.8.2 Gdal_translate and gdalwarp Summary 5 Using PostGIS on the desktop 5.1 Desktop viewing tools at a glance 5.1.1 OpenJUMP 5.1.2 QGIS 5.1.3 gvSIG 5.1.4 Jupyter Notebook and JupyterLab 5.1.5 Spatial database support 5.1.6 Format support 5.1.7 Web services supported 5.2 OpenJUMP 5.2.1 OpenJUMP feature summary 5.2.2 Installing OpenJUMP 5.2.3 Ease of use 5.2.4 OpenJUMP plug-ins 5.2.5 OpenJUMP scripting 5.2.6 OpenJUMP format support 5.2.7 PostGIS support 5.2.8 Registering data sources 5.2.9 Rendering PostGIS geometries 5.2.10 Exporting data 5.3 QGIS 5.3.1 Installing QGIS 5.3.2 Using QGIS with PostGIS 5.4 GvSIG 5.4.1 Using gvSIG with PostGIS 5.4.2 Exporting data 5.5 JupyterLab and Jupyter Notebook 5.5.1 Installing Jupyter 5.5.2 Launching Jupyter Notebook 5.5.3 Launching JupyterLab 5.5.4 Creating a Python notebook 5.5.5 Magic commands 5.5.6 Performing raw queries with Jupyter Notebook 5.5.7 UTable 5.6 sing GeoPandas, Shapely, and Matplotlib to work with spatial data 5.5.8 Viewing data on a map with folium Summary 6 Geometry and geography functions 6.1 Output functions 6.1.1 Well-known text (WKT) and well-known binary (WKB) 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 Tiles (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 Creating geographies from text and binary formats 6.2.3 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 define 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 Promoting single geometries to multi-geometries 6.7 Simplification functions 6.7.1 Grid snapping and coordinate rounding 6.7.2 Simplification 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 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 Summary Part 2. 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 Items between distances 9.1.6 Finding the N closest places using KNN distance operators 9.2 Using KNN with geography types 9.2.1 Using window functions to number the closest N places 9.3 Geotagging 9.2.1 Tagging data to a specific region 9.2.2 Linear referencing: snapping points to the closest linestring 9.2.3 PostGIS cluster window functions 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 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 Summary 12 Raster processing 12.1 Loading and preparing raster data 12.2 Forming larger rasters using spatial aggregate functions 12.2.1 Reconstituting tiled files 12.2.2 Carving out areas of interest using clipping and unioning 12.2.3 Using specific expression types with ST_Union 12.3 Working with bands 12.3.1 Using ST_AddBand to form multiband rasters from single-band rasters 12.3.2 Using ST_Band to process a subset of bands 12.4 Tiling rasters 12.5 Raster and geometry intersections 12.5.1 Pixel stats 12.5.2 Adding a Z coordinate to a 2D linestring using ST_Value and ST_SetZ 12.5.3 Converting 2D polygons to 3D polygons 12.6 Raster statistics 12.6.1 Extruding pixel values 12.6.2 Raster statistics functions 12.7 Map algebra 12.7.1 Choosing between expression or callback function 12.7.2 Using a single-band map algebra expression 12.7.3 Using a single-band map algebra function 12.7.4 Map algebra with neighborhoods 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 Summary 14 Organizing spatial data 14.1 Spatial storage approaches 14.1.1 Heterogeneous columns 14.1.2 Homogeneous columns 14.1.3 Typmod vs. constraints 14.1.4 Table inheritance 14.1.5 Table partitioning 14.2 Modeling a real city 14.2.1 Modeling using heterogeneous geometry columns 14.2.2 Modeling using homogeneous geometry columns 14.2.3 Modeling using partitioning 14.3 Making auto-updatable views 14.4 Using triggers and rules 14.4.1 Triggers 14.4.2 Using INSTEAD OF triggers 14.4.3 Using other triggers 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 CTEscommon table expressions (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 strategies 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 Summary Part 3. 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.2 Extending PostgreSQL with PLs 16.2.1 Basic installation of PLs 16.2.2 What you can do with PLs 16.3 PL/R 16.3.1 Getting started with PL/R 16.3.2 What you can do with PL/R 16.3.3 Using R packages in PL/R 16.3.4 Converting geometries into R spatial objects and plotting spatial objects 16.3.5 Outputting plots as binaries 16.4 PL/Python 16.4.1 Installing PL/Python 16.4.2 Writing a PL/Python function 16.4.3 Using Python packages 16.4.4 Geocoding example 16.5 PL/V8: JavaScript in the database 16.5.1 Installing PL/V8 16.5.2 Enabling PL/V8 in a database 16.5.3 Using other JavaScript libraries and functions in PL/V8 16.5.4 Using PL/V8 to write map algebra functions Summary 17 Using PostGIS in web applications 17.1 Limitations of conventional web technologies 17.2 Mapping servers 17.2.1 Lightweight mapping servers 17.2.2 Full mapping servers 17.3 Mapping clients 17.3.1 Proprietary services 17.4 Using MapServer 17.4.1 Installing MapServer 17.4.2 Security considerations 17.4.3 Creating WMS and WFS services 17.4.4 Calling a mapping service using a reverse proxy 17.5 Using GeoServer 17.5.1 Installing GeoServer 17.5.2 Setting up PostGIS workspaces 17.5.3 Accessing PostGIS layers via GeoServer WMS/WFS 17.6 Basics of OpenLayers and Leaflet 17.6.1 OpenLayers primer 17.6.2 Leaflet primer 17.6.3 Synopsis of the OpenLayers and Leaflet APIs 17.7 Displaying data with PostGIS queries and web scripting 17.7.1 Using PostGIS and PostgreSQL geometry output functions 17.7.2 Using PostGIS MVT output functionsMapbox Vector Tiles (MVT) Summary 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 MacOS-specific installers B.1.4 Installing on a Linux server (Red Hat EL, CentOS) using YUM B.1.5 PostgreSQL Apt repository B.1.6 Other available binaries and distros B.1.7 Database as a service offerings for PostGIS B.1.8 Compiling and installing from PostGIS source B.2 Creating a PostGIS database B.3 Upgrading PostGIS B.3.1 PostGIS soft upgrade using extensions B.3.2 Upgrading PostgreSQL and PostGIS using pg_upgrade B.3.3 Upgrading PostGIS from 1.X to 2.X or 3.X Appendix C. SQL primer C.1 The 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.3 UPDATE, INSERT, and DELETE C.3.1 UPDATE C.3.2 INSERT C.3.3 DELETE index
Donate to keep this site alive
1. Disable the AdBlock plugin. Otherwise, you may not get any links.
2. Solve the CAPTCHA.
3. Click download link.
4. Lead to download server to download.