Table of Contents for
PostGIS Cookbook - Second Edition

Version ebook / Retour

Cover image for bash Cookbook, 2nd Edition PostGIS Cookbook - Second Edition by Thomas J Kraft Published by Packt Publishing, 2018
  1. PostGIS Cookbook, Second Edition
  2. Title Page
  3. Copyright and Credits
  4. PostGIS Cookbook Second Edition
  5. Packt Upsell
  6. Why subscribe?
  7. PacktPub.com
  8. Contributors
  9. About the authors
  10. Packt is searching for authors like you
  11. Table of Contents
  12. Preface
  13. Who this book is for
  14. What this book covers
  15. To get the most out of this book
  16. Download the example code files
  17. Download the color images
  18. Conventions used
  19. Sections
  20. Getting ready
  21. How to do it…
  22. How it works…
  23. There's more…
  24. See also
  25. Get in touch
  26. Reviews
  27. Moving Data In and Out of PostGIS
  28. Introduction
  29. Importing nonspatial tabular data (CSV) using PostGIS functions
  30. Getting ready
  31. How to do it...
  32. How it works...
  33. Importing nonspatial tabular data (CSV) using GDAL
  34. Getting ready
  35. How to do it...
  36. How it works...
  37. Importing shapefiles with shp2pgsql
  38. How to do it...
  39. How it works...
  40. There's more...
  41. Importing and exporting data with the ogr2ogr GDAL command
  42. How to do it...
  43. How it works...
  44. See also
  45. Handling batch importing and exporting of datasets
  46. Getting ready
  47. How to do it...
  48. How it works...
  49. Exporting data to a shapefile with the pgsql2shp PostGIS command
  50. How to do it...
  51. How it works...
  52. Importing OpenStreetMap data with the osm2pgsql command
  53. Getting ready
  54. How to do it...
  55. How it works...
  56. Importing raster data with the raster2pgsql PostGIS command
  57. Getting ready
  58. How to do it...
  59. How it works...
  60. Importing multiple rasters at a time
  61. Getting ready
  62. How to do it...
  63. How it works...
  64. Exporting rasters with the gdal_translate and gdalwarp GDAL commands
  65. Getting ready
  66. How to do it...
  67. How it works...
  68. See also
  69. Structures That Work
  70. Introduction
  71. Using geospatial views
  72. Getting ready
  73. How to do it...
  74. How it works...
  75. There's more...
  76. See also
  77. Using triggers to populate the geometry column
  78. Getting ready
  79. How to do it...
  80. There's more...
  81. Extending further...
  82. See also
  83. Structuring spatial data with table inheritance
  84. Getting ready
  85. How to do it...
  86. How it works...
  87. See also
  88. Extending inheritance – table partitioning
  89. Getting ready
  90. How to do it...
  91. How it works...
  92. See also
  93. Normalizing imports
  94. Getting ready
  95. How to do it...
  96. How it works...
  97. There's more...
  98. Normalizing internal overlays
  99. Getting ready
  100. How to do it...
  101. How it works...
  102. There's more...
  103. Using polygon overlays for proportional census estimates
  104. Getting ready
  105. How to do it...
  106. How it works...
  107. Working with Vector Data – The Basics
  108. Introduction
  109. Working with GPS data
  110. Getting ready
  111. How to do it...
  112. How it works...
  113. Fixing invalid geometries
  114. Getting ready
  115. How to do it...
  116. How it works...
  117. GIS analysis with spatial joins
  118. Getting ready
  119. How to do it...
  120. How it works...
  121. Simplifying geometries
  122. How to do it...
  123. How it works...
  124. Measuring distances
  125. Getting ready
  126. How to do it...
  127. How it works...
  128. Merging polygons using a common attribute
  129. Getting ready
  130. How to do it...
  131. How it works...
  132. Computing intersections
  133. Getting ready
  134. How to do it...
  135. How it works...
  136. Clipping geometries to deploy data
  137. Getting ready
  138. How to do it...
  139. How it works...
  140. Simplifying geometries with PostGIS topology
  141. Getting ready
  142. How to do it...
  143. How it works...
  144. Working with Vector Data – Advanced Recipes
  145. Introduction
  146. Improving proximity filtering with KNN
  147. Getting ready
  148. How to do it...
  149. How it works...
  150. See also
  151. Improving proximity filtering with KNN – advanced
  152. Getting ready
  153. How to do it...
  154. How it works...
  155. See also
  156. Rotating geometries
  157. Getting ready
  158. How to do it...
  159. How it works...
  160. See also
  161. Improving ST_Polygonize
  162. Getting ready
  163. How to do it...
  164. See also
  165. Translating, scaling, and rotating geometries – advanced
  166. Getting ready
  167. How to do it...
  168. How it works...
  169. See also
  170. Detailed building footprints from LiDAR
  171. Getting ready
  172. How to do it...
  173. How it works...
  174. Creating a fixed number of clusters from a set of points
  175. Getting ready
  176. How to do it...
  177. Calculating Voronoi diagrams
  178. Getting ready
  179. How to do it...
  180. Working with Raster Data
  181. Introduction
  182. Getting and loading rasters
  183. Getting ready
  184. How to do it...
  185. How it works...
  186. Working with basic raster information and analysis
  187. Getting ready
  188. How to do it...
  189. How it works...
  190. Performing simple map-algebra operations
  191. Getting ready
  192. How to do it...
  193. How it works...
  194. Combining geometries with rasters for analysis
  195. Getting ready
  196. How to do it...
  197. How it works...
  198. Converting between rasters and geometries
  199. Getting ready
  200. How to do it...
  201. How it works...
  202. Processing and loading rasters with GDAL VRT
  203. Getting ready
  204. How to do it...
  205. How it works...
  206. Warping and resampling rasters
  207. Getting ready
  208. How to do it...
  209. How it works...
  210. Performing advanced map-algebra operations
  211. Getting ready
  212. How to do it...
  213. How it works...
  214. Executing DEM operations
  215. Getting ready
  216. How to do it...
  217. How it works...
  218. Sharing and visualizing rasters through SQL
  219. Getting ready
  220. How to do it...
  221. How it works...
  222. Working with pgRouting
  223. Introduction
  224. Startup – Dijkstra routing
  225. Getting ready
  226. How to do it...
  227. Loading data from OpenStreetMap and finding the shortest path using A*
  228. Getting ready
  229. How to do it...
  230. How it works...
  231. Calculating the driving distance/service area
  232. Getting ready
  233. How to do it...
  234. See also
  235. Calculating the driving distance with demographics
  236. Getting ready
  237. How to do it...
  238. Extracting the centerlines of polygons
  239. Getting ready
  240. How to do it...
  241. There's more...
  242. Into the Nth Dimension
  243. Introduction
  244. Importing LiDAR data
  245. Getting ready
  246. How to do it...
  247. See also
  248. Performing 3D queries on a LiDAR point cloud
  249. How to do it...
  250. Constructing and serving buildings 2.5D
  251. Getting ready
  252. How to do it...
  253. Using ST_Extrude to extrude building footprints
  254. How to do it...
  255. Creating arbitrary 3D objects for PostGIS
  256. Getting ready
  257. How to do it...
  258. Exporting models as X3D for the web
  259. Getting ready
  260. How to do it...
  261. There's more...
  262. Reconstructing Unmanned Aerial Vehicle (UAV) image footprints with PostGIS 3D
  263. Getting started
  264. How to do it...
  265. UAV photogrammetry in PostGIS – point cloud
  266. Getting ready
  267. How to do it...
  268. UAV photogrammetry in PostGIS – DSM creation
  269. Getting ready
  270. How to do it...
  271. PostGIS Programming
  272. Introduction
  273. Writing PostGIS vector data with Psycopg
  274. Getting ready
  275. How to do it...
  276. How it works...
  277. Writing PostGIS vector data with OGR Python bindings
  278. Getting ready
  279. How to do it...
  280. How it works...
  281. Writing PostGIS functions with PL/Python
  282. Getting ready
  283. How to do it...
  284. How it works...
  285. Geocoding and reverse geocoding using the GeoNames datasets
  286. Getting ready
  287. How to do it...
  288. How it works...
  289. Geocoding using the OSM datasets with trigrams
  290. Getting ready
  291. How to do it...
  292. How it works...
  293. Geocoding with geopy and PL/Python
  294. Getting ready
  295. How to do it...
  296. How it works...
  297. Importing NetCDF datasets with Python and GDAL
  298. Getting ready
  299. How to do it...
  300. How it works...
  301. PostGIS and the Web
  302. Introduction
  303. Creating WMS and WFS services with MapServer
  304. Getting ready
  305. How to do it...
  306. How it works...
  307. See also
  308. Creating WMS and WFS services with GeoServer
  309. Getting ready
  310. How to do it...
  311. How it works...
  312. See also
  313. Creating a WMS Time service with MapServer
  314. Getting ready
  315. How to do it...
  316. How it works...
  317. Consuming WMS services with OpenLayers
  318. Getting ready
  319. How to do it...
  320. How it works..
  321. Consuming WMS services with Leaflet
  322. How to do it...
  323. How it works...
  324. Consuming WFS-T services with OpenLayers
  325. Getting ready
  326. How to do it...
  327. How it works...
  328. Developing web applications with GeoDjango – part 1
  329. Getting ready
  330. How to do it...
  331. How it works...
  332. Developing web applications with GeoDjango – part 2
  333. Getting ready
  334. How to do it...
  335. How it works...
  336. Developing a web GPX viewer with Mapbox
  337. How to do it...
  338. How it works...
  339. Maintenance, Optimization, and Performance Tuning
  340. Introduction
  341. Organizing the database
  342. Getting ready
  343. How to do it...
  344. How it works...
  345. Setting up the correct data privilege mechanism
  346. Getting ready
  347. How to do it...
  348. How it works...
  349. Backing up the database
  350. Getting ready
  351. How to do it...
  352. How it works...
  353. Using indexes
  354. Getting ready
  355. How to do it...
  356. How it works...
  357. Clustering for efficiency
  358. Getting ready
  359. How to do it...
  360. How it works...
  361. Optimizing SQL queries
  362. Getting ready
  363. How to do it...
  364. How it works...
  365. Migrating a PostGIS database to a different server
  366. Getting ready
  367. How to do it...
  368. How it works...
  369. Replicating a PostGIS database with streaming replication
  370. Getting ready
  371. How to do it...
  372. How it works...
  373. Geospatial sharding
  374. Getting ready
  375. How to do it...
  376. How it works...
  377. Paralellizing in PosgtreSQL
  378. Getting ready
  379. How to do it...
  380. How it works...
  381. Using Desktop Clients
  382. Introduction
  383. Adding PostGIS layers – QGIS
  384. Getting ready
  385. How to do it...
  386. How it works...
  387. Using the Database Manager plugin – QGIS
  388. Getting ready
  389. How to do it...
  390. How it works...
  391. Adding PostGIS layers – OpenJUMP GIS
  392. Getting ready
  393. How to do it...
  394. How it works...
  395. Running database queries – OpenJUMP GIS
  396. Getting ready
  397. How to do it...
  398. How it works...
  399. Adding PostGIS layers – gvSIG
  400. Getting ready
  401. How to do it...
  402. How it works...
  403. Adding PostGIS layers – uDig
  404. How to do it...
  405. How it works...
  406. Introduction to Location Privacy Protection Mechanisms
  407. Introduction
  408. Definition of Location Privacy Protection Mechanisms – LPPMs
  409. Classifying LPPMs
  410. Adding noise to protect location data
  411. Getting ready
  412. How to do it...
  413. How it works...
  414. Creating redundancy in geographical query results
  415. Getting ready
  416. How to do it...
  417. How it works...
  418. References
  419. Other Books You May Enjoy
  420. Leave a review - let other readers know what you think

How to do it...

Carry out the following steps:

  1. Open the GeoServer administrative interface, which is typically located at http://localhost:8080/geoserver, in your favorite browser and log in using your credentials (admin as the username and geoserver as the password) if you are just using the GeoServer default installation and have not customized things. After starting GeoServer, you should see the following:
GeoServer welcome screen viewed in a browser
  1. After successfully logging in, create a workspace by clicking on the Workspace link under Work (in the left-hand-side panel of the GeoServer application's main menu) and then click on the Add new workspace link. In the text boxes of the form that appears, specify the following values and then click on the Submit button:
  2. Now, to create a PostGIS store, click on the Stores link under Data (in the left-hand-side panel of the GeoServer application's main menu). Now, click on the Add new store link, and then on the PostGIS link under Vector Data Sources, as shown in the following screenshot:
GeoServer screen to configure new data sources
  1. In the New Vector Data Source page, complete the form's fields, as follows:
    1. Select postgis_cookbook from the Workspace drop-down list.
    2. Enter postgis_cookbook in the Data Source Name field.
    3. Enter localhost in the host field.
    4. Enter 5432 in the port field.
    5. Enter postgis_cookbook in the database field.
    6. Enter chp09 in the schema field.
    7. Enter me in the user field.
    8. Enter mypassword in the passwd field.

The New Vector Data Source page is shown in the following screenshot:

  1. Now, click on the Save button to successfully create your PostGIS store.
  2. Now, you are ready to publish the PostGIS counties layer as WMS and WFS. On the Layers page, click on the Add a new resource link. Now, select postgis_cookbook from the Add layer from drop-down list. Click on the Publish link to the right of the counties layer.
  3. On the Edit Layer page, shown in the following screenshot, click on the links Compute from data and Compute from native bounds, and then click on the Save button:
GeoServer screen to edit the countries layer for publishing
  1. Now, you need to define the style used to display the layer to the user. Unlike MapServer, GeoServer uses the OGC-standard Styled Layer Descriptor (SLD) notation. Click on the Styles link under Data and then on the Add new style link. Fill the text fields in the form, as follows:
    • Enter Counties classified per size in the Name field.
    • Enter postgis_cookbook in the Workspace field
  1. In the text area for the SLD, add the following XML code defining the style for the counties layer. Then, click on the Validate button to check whether your SLD definition is correct and then click on the Submit button to save the new style:
        <?xml version="1.0" encoding="UTF-8"?> 
        <sld:StyledLayerDescriptor xmlns="http://www.opengis.net/sld" 
xmlns:sld="http://www.opengis.net/sld"
xmlns:ogc="http://www.opengis.net/ogc"
xmlns:gml="http://www.opengis.net/gml" version="1.0.0"> <sld:NamedLayer> <sld:Name>county_classification</sld:Name> <sld:UserStyle> <sld:Name>county_classification</sld:Name> <sld:Title>County area classification</sld:Title> <sld:FeatureTypeStyle> <sld:Name>name</sld:Name> <sld:Rule> <sld:Title>Large counties</sld:Title> <ogc:Filter> <ogc:PropertyIsGreaterThanOrEqualTo> <ogc:PropertyName>square_mil</ogc:PropertyName> <ogc:Literal>5000</ogc:Literal> </ogc:PropertyIsGreaterThanOrEqualTo> </ogc:Filter> <sld:PolygonSymbolizer> <sld:Fill> <sld:CssParameter
name="fill">#FF0000</sld:CssParameter> </sld:Fill> <sld:Stroke/> </sld:PolygonSymbolizer> </sld:Rule> <sld:Rule> <sld:Title>Small counties</sld:Title>
<ogc:Filter> <ogc:PropertyIsLessThan> <ogc:PropertyName>square_mil</ogc:PropertyName> <ogc:Literal>5000</ogc:Literal> </ogc:PropertyIsLessThan>
</ogc:Filter> <sld:PolygonSymbolizer> <sld:Fill> <sld:CssParameter
name="fill">#0000FF</sld:CssParameter> </sld:Fill> <sld:Stroke/> </sld:PolygonSymbolizer> </sld:Rule> </sld:FeatureTypeStyle> </sld:UserStyle> </sld:NamedLayer> </sld:StyledLayerDescriptor>

The following screenshot shows how the new style looks on the New style GeoServer page:

GeoServer screen for creating a new style as an SLD document
  1. Now you need to associate the created style with the counties layer. Go back to the layer page (Data | Layers), click on the counties layer link, and then, on the Edit Layer page, click on the Publishing section. Select Counties classified per size in the Default style drop-down list and then click on the Save button.

 

  1. Now that your WMS and WFS services for the PostGIS counties layer are ready, it is time to start using them! First, test the GetCapabilities WMS request. To do this, you can click on one of the links on the right-hand-side panel on the GeoServer web application home page. You can click on the link for either WMS version 1.1.1 or WMS version 1.3.0. Click on one of the links or type the GetCapabilities request directly in the browser as http://localhost:8080/geoserver/ows?service=wms&version=1.3.0&request=GetCapabilities.
  2. Now, we will investigate the GetCapabilities response, shown as follows. You will find a lot of information about WMS is available on your GeoServer instance, such as the WMS-supported requests, projections, and a lot of other information about each published layer. In the case of the counties layer, the following code is an extract from the GetCapabilities document. Note the main layer information, such as the name, title, abstract (you could redefine all of these using the GeoServer web application), the supported Coordinate Reference Systems (CRS), the geographic extent, and the associated style:
        <Layer queryable="1"> 
          <Name>postgis_cookbook:counties</Name> 
          <Title>counties</Title> 
          <Abstract/> 
          <KeywordList> 
            <Keyword>counties</Keyword> 
            <Keyword>features</Keyword> 
          </KeywordList> 
          <CRS>EPSG:4326</CRS> 
          <CRS>CRS:84</CRS> 
          <EX_GeographicBoundingBox> 
            <westBoundLongitude>-179.133392333984
</westBoundLongitude> <eastBoundLongitude>-64.566162109375
</eastBoundLongitude> <southBoundLatitude>17.6746921539307
</southBoundLatitude> <northBoundLatitude>71.3980484008789
</northBoundLatitude> </EX_GeographicBoundingBox> <BoundingBox CRS="CRS:84" minx="-179.133392333984"
miny="17.6746921539307" maxx="-64.566162109375"
maxy="71.3980484008789"/> <BoundingBox CRS="EPSG:4326" minx="17.6746921539307"
miny="-179.133392333984" maxx="71.3980484008789" maxy="-
64.566162109375"/> <Style> <Name>Counties classified per size</Name> <Title>County area classification</Title> <Abstract/> <LegendURL width="20" height="20"> <Format>image/png</Format> <OnlineResource
xmlns:xlink="http://www.w3.org/1999/xlink"
xlink:type="simple" xlink:href=
"http://localhost:8080/geoserver/
ows?service=WMS&amp;request=GetLegendGraphic&amp;
format=image%2Fpng&amp;width=20&amp;height=20&amp;
layer=counties"/> </LegendURL> </Style> </Layer>
  1. To test the GetMap and GetFeatureInfo WMS requests, the GeoServer web application offers you a very handy way with the Layer Preview page. Navigate to Data | Layer Preview and then click on the OpenLayers link next to the counties layer. The Layer Preview page is based on the OpenLayers JavaScript library and lets you experiment with the GetMap and GetFeatureInfo requests.
  2. Try to navigate the map; at each zoom and pan action, GeoServer will stream out a new image provided by the response output to a GetMap request. By clicking on the map, you can perform a GetFeatureInfo request and the user interface will display the feature's attributes corresponding to the point on the map on which you clicked. A very effective way to check how the requests are sent to GeoServer as you navigate the map is by using the Firefox Firebug plugin or the Chrome (or Chromium if you are using Linux) Developer Tools. With these tools, you will be able to identify the GetMap and GetFeatureInfo requests that are being sent behind the scenes from the OpenLayers viewer to GeoServer. One such map is shown in the following screenshot:

Here is what you get when inspecting the requests with any in-browser developer tool, check the request URL, and verify the parameters sent to geoserver; this is how it looks with Firefox:

  1. Now, try a WMS GetMap request by typing the URL http://localhost:8080/geoserver/postgis_cookbook/wms?LAYERS=postgis_cookbook%3Acounties&STYLES=&FORMAT=image%2Fpng&SERVICE=WMS&VERSION=1.1.1&REQUEST=GetMap&SRS=EPSG%3A4326&BBOX=-200.50286594033,7.6152902245522,-43.196688503029,81.457450330258&WIDTH=703&HEIGHT=330 in your browser.
  2. Try a WMS GetFeatureInfo request, as well, by typing the URL http://localhost:8080/geoserver/postgis_cookbook/wms?REQUEST=GetFeatureInfo&EXCEPTIONS=application%2Fvnd.ogc.se_xml&BBOX=-126.094303%2C37.16812%2C-116.262667%2C41.783255&SERVICE=WMS&INFO_FORMAT=text%2Fhtml&QUERY_LAYERS=postgis_cookbook%3Acounties&FEATURE_COUNT=50&Layers=postgis_cookbook%3Acounties&WIDTH=703&HEIGHT=330&format=image%2Fpng&styles=&srs=EPSG%3A4326&version=1.1.1&x=330&y=158.

This will be displayed by prompting the previous URL:

  1. Now, as you did for the MapService WMS, test the GeoServer WMS in QGIS. Create a WMS connection named GeoServer on localhost, pointing to the GeoServer GetCapabilities document (http://localhost:8080/geoserver/ows?service=wms&version=1.3.0&request=GetCapabilities). Then, connect to the WMS server (for example, from the QGIS browser), select counties from the Layers list, and add it to the map, as shown in the following screenshot; then navigate the layer and try to identify some of the features:
  1. Having used WMS, try to test a couple of WFS requests. A typical WFS GetCapability request can be executed by typing the URL http://localhost:8080/geoserver/wfs?service=wfs&version=1.1.0&request=GetCapabilities. You could also click on one of the WFS links on the home page of the GeoServer web interface.
  2. Investigate the XML GetCapabilities response and try to identify the information about your layer. You should have a <FeatureType> element, such as the following, corresponding to the counties layer:
        <FeatureType> 
          <Name>postgis_cookbook:counties</Name> 
          <Title>counties</Title> 
          <Abstract/> 
          <Keywords>counties, features</Keywords> 
          <SRS>EPSG:4326</SRS> 
          <LatLongBoundingBox minx="-179.133392333984" 
miny="17.6746921539307" maxx="-64.566162109375"
maxy="71.3980484008789"/> </FeatureType>
  1. As shown in the previous recipe, a typical WFS request is GetFeature, which will result in a GML response. Try it by typing the URL http://localhost:8080/geoserver/wfs?service=wfs&version=1.0.0&request=GetFeature&typeName=postgis_cookbook:counties&maxFeatures=5 in your browser. You will receive a GML output composed of a <wfs:FeatureCollection> element and a collection of <gml:featureMember> elements (possibly five elements, as specified in the maxFeatures request's parameter). You will get an output that is similar to the following code:
        <gml:featureMember> 
          <postgis_cookbook:counties fid="counties.3962"> 
            <postgis_cookbook:the_geom> 
              <gml:Polygon srsName="http://www.opengis.net/
gml/srs/epsg.xml#4326"> <gml:outerBoundaryIs> <gml:LinearRing> <gml:coordinates xmlns:gml=
"http://www.opengis.net/gml"
decimal="." cs="," ts=""> -101.62554932,36.50246048 -
101.0908432,36.50032043 ... ... ... </gml:coordinates> </gml:LinearRing> </gml:outerBoundaryIs> </gml:Polygon> </postgis_cookbook:the_geom> <postgis_cookbook:area>0.240</postgis_cookbook:area> <postgis_cookbook:perimeter>1.967
</postgis_cookbook:perimeter> <postgis_cookbook:co2000p020>3963.0
</postgis_cookbook:co2000p020> <postgis_cookbook:state>TX</postgis_cookbook:state> <postgis_cookbook:county>Hansford
County</postgis_cookbook:county> <postgis_cookbook:fips>48195</postgis_cookbook:fips> <postgis_cookbook:state_fips>48
</postgis_cookbook:state_fips> <postgis_cookbook:square_mil>919.801
</postgis_cookbook:square_mil> </postgis_cookbook:counties> </gml:featureMember>
  1. Now, as you did with WMS, try the counties WFS in QGIS (or in your favorite desktop GIS client). Create a new WFS connection by using either the QGIS browser or the Add WFS Layer button and then clicking on the New Connection button. In the Create a new WFS connection dialog box, type GeoServer on localhost in the Name field and add the WFS GetCapabilities URL (http://localhost:8080/geoserver/wfs?service=wfs&version=1.1.0&request=GetCapabilities) in the URL field.
  2. Add the WFS counties layer from the previous dialog box and, as a test, select some of the counties and export them to a new shapefile using the Save As command from the layer's context menu, as shown in the following screenshot: