PostGIS in Action

522 Pages·2011·15.85 MB·English

IN ACTION
Regina O. Obe
Leo S. Hsu
FOREWORD BY PAUL RAMSEY
M A N N I N G
www.it-ebooks.info
PostGIS in Action
www.it-ebooks.info
www.it-ebooks.info
PostGIS in Action
REGINA O. OBE
LEO S. HSU
MANNING
Greenwich
(74° w. long.)
www.it-ebooks.info
Download from Wow! eBook <www.wowebook.com>
For online information and ordering of this and other Manning books, please visit
www.manning.com. The publisher offers discounts on this book when ordered in quantity.
For more information, please contact
Special Sales Department
Manning Publications Co.
180 Broad Street
Suite 1323
Stamford, CT 06901
Email: [email protected]
©2011 by Manning Publications Co. All rights reserved.
No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in
any form or by means electronic, mechanical, photocopying, or otherwise, without prior written
permission of the publisher.
Many of the designations used by manufacturers and sellers to distinguish their products are
claimed as trademarks. Where those designations appear in the book, and Manning
Publications was aware of a trademark claim, the designations have been printed in initial caps
or all caps.
Recognizing the importance of preserving what has been written, it is Manning’s policy to have
the books we publish printed on acid-free paper, and we exert our best efforts to that end.
Recognizing also our responsibility to conserve the resources of our planet, Manning books are
printed on paper that is at least 15 percent recycled and processed without elemental chlorine.
Manning Publications Co. Development editor: Sebastian Sterling
180 Broad Street Copyeditor: Linda Recktenwald
Suite 1323 Typesetter: Marija Tudor
Stamford, CT 06901 Cover designer: Marija Tudor
ISBN: 9781935182269
Printed in the United States of America
1 2 3 4 5 6 7 8 9 10 – MAL – 17 16 15 14 13 12 11
www.it-ebooks.info
Download from Wow! eBook <www.wowebook.com>
brief contents
PART 1 LEARNING POSTGIS..................................................... 1
1 ■ What is a spatial database? 3
2 ■ Geometry types 33
3 ■ Organizing spatial data 53
4 ■ Geometry functions 80
5 ■ Relationships between geometries 117
6 ■ Spatial reference system considerations 153
7 ■ Working with real data 173
PART 2 PUTTING POSTGIS TO WORK ................................... 201
8 ■ Techniques to solve spatial problems 203
9 ■ Performance tuning 241
PART 3 USING POSTGIS WITH OTHER TOOLS ....................... 277
10 ■ Enhancing SQL with add-ons 279
11 ■ Using PostGIS in web applications 312
12 ■ Using PostGIS in a desktop environment 345
13 ■ PostGIS raster 371
v
www.it-ebooks.info
Download from Wow! eBook <www.wowebook.com>
vi BRIEF CONTENTS
www.it-ebooks.info
Download from Wow! eBook <www.wowebook.com>
contents
foreword xv
preface xvii
acknowledgments xix
about this book xxi
about the cover illustration xxviii
PART 1 LEARNING POSTGIS . ....................................... 1
1 What is a spatial database? 3
1.1 Thinking spatially 3
Introducing the geometry data type 6
1.2 Modeling 7
Imagine the possibilities 8
1.3 Introducing PostgreSQL and PostGIS 9
PostgreSQL strengths 9 ■ PostGIS, adding GIS to
PostgreSQL 13 ■ Alternatives to PostgreSQL and
PostGIS 14 ■ What works with PostGIS 15
1.4 Getting started with PostGIS 16
Verifying version of PostGIS and PostgreSQL 17 ■ Creating
geometries with PostGIS 17
vii
www.it-ebooks.info
Download from Wow! eBook <www.wowebook.com>
viii CONTENTS
1.5 Working with real data 20
Loading comma-separated data 21 ■ Spatializing flat file
data 22 ■ Loading data from spatial data sources 25
1.6 Using spatial queries to analyze data 28
Proximity queries 29 ■ Viewing spatial data with
OpenJUMP 30
1.7 Summary 31
2 Geometry types 33
2.1 Geometry columns in PostGIS 34
The geometry_columns table 34 ■ Interacting with the
geometry_columns table 37
2.2 A panoply of geometries 38
What’s a geometry? 38 ■ Points 39 ■ Linestrings 40
Polygons 41 ■ Collection geometries 43 ■ Curved
geometries 47 ■ 3D geometries 51
2.3 Summary 52
3 Organizing spatial data 53
3.1 Spatial storage approaches 53
Heterogeneous geometry columns 54 ■ Homogeneous geometry
columns 56 ■ Table inheritance 57
3.2 Modeling a real city 60
Modeling using a heterogeneous geometry column 61
Modeling using homogeneous geometry columns 64
Modeling using inheritance 66
3.3 Using rules and triggers 69
Rules versus triggers 69 ■ Using rules 71
Using triggers 73
3.4 Summary 78
4 Geometry functions 80
4.1 Constructors 81
Creating geometries from well-known text and well-known binary
representations 81 ■ Autocasting in PostgreSQL/PostGIS 83
4.2 Outputs 84
Well-known text and well-known binary 85 ■ Keyhole Markup
Language 85 ■ Geography Markup Language 86
www.it-ebooks.info
Download from Wow! eBook <www.wowebook.com>
CONTENTS ix
Geometry JavaScript Object Notation 86 ■ Scalable Vector
Graphics 86 ■ Geohash 87 ■ Examples of output
functions 87
4.3 Accessor functions: getters and setters 88
Getting and setting spatial reference system 88 ■ Transform to
a different spatial reference 89 ■ Geometry type 90
Coordinate and geometry dimensions 91 ■ Geometry
validity 92 ■ Number of points that define a geometry 93
4.4 Measurement functions 94
Planar measures for geometry types 95 ■ Geodetic measurement
for geometry types 96 ■ Measurement with geography type 98
4.5 Decomposition 99
Boxes and envelopes 99 ■ Coordinates 101
Boundaries 102 ■ Point marker for a geometry: centroid,
point on surface, and nth point 103 ■ Breaking down multi
and collection geometries 105
4.6 Composition 108
Making points 108 ■ Making polygons 110 ■ Promoting
single to multi geometries 112
4.7 Simplification 112
Coordinate rounding using ST_SnapToGrid 113
Simplifying geometries 114
4.8 Summary 115
5 Relationships between geometries 117
5.1 Introducing spatial relationship functions 118
5.2 Intersections 119
Segmenting linestrings with polygons 120 ■ Clipping polygons
with polygons 121
5.3 Specific intersection relationships 123
Interior, exterior, and boundary of a geometry 123 ■ Contains
and Within 125 ■ Covers and CoveredBy 127
ContainsProperly 128 ■ Overlapping geometries 129
Touching geometries 129 ■ Crossing geometries 130
Disjoint geometries 131
5.4 The remainder: ST_Difference and ST_SymDifference 131
5.5 Nearest neighbor 134
Intersects with tolerance 135 ■ Finding N closest objects 135
Using SQL Window functions to number results 137
www.it-ebooks.info
Download from Wow! eBook <www.wowebook.com>