27,59 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Fachliteratur
- Sprache: Englisch
PostGIS is open source extension onf PostgreSQL object-relational database system that allows GIS objects to be stored and allows querying for information and location services. The aim of this book is to help you master the functionalities offered by PostGIS- from data creation, analysis and output, to ETL and live edits.
The book begins with an overview of the key concepts related to spatial database systems and how it applies to Spatial RMDS. You will learn to load different formats into your Postgres instance, investigate the spatial nature of your raster data, and finally export it using built-in functionalities or 3th party tools for backup or representational purposes.
Through the course of this book, you will be presented with many examples on how to interact with the database using JavaScript and Node.js. Sample web-based applications interacting with backend PostGIS will also be presented throughout the book, so you can get comfortable with the modern ways of consuming and modifying your spatial data.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 272
Veröffentlichungsjahr: 2017
Ähnliche
Title Page
BIRMINGHAM - MUMBAI
Copyright
Mastering PostGIS
Copyright © 2017 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author(s), nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
First published: May 2017
Production reference: 1260517
ISBN 978-1-78439-164-5
www.packtpub.com
Credits
Authors
Dominik Mikiewicz
Michal Mackiewicz
Tomasz Nycz
Copy Editor
Safis Editing
Reviewers
Prashant Verma
Eric Pimpler
Project Coordinator
Nidhi Joshi
Commissioning Editor
Amey Varangaonkar
Proofreader
Safis Editing
Acquisition Editor
Vinay Argekar
Indexer
Mariammal Chettiyar
ContentDevelopmentEditor
Mayur Pawanikar
Graphics
Tania Dutta
Technical Editor
Dinesh Chaudhary
Production Coordinator
Aparna Bhagat
About the Authors
Dominik Mikiewicz is a senior GIS consultant and the owner of one-person software shop Cartomatic. When not coding, he spends time with wife and kids, trying to make the little ones enjoy mountain trekking. He is also a long-distance cycling and running enthusiast.
Michal Mackiewicz has been working as a software engineer at GIS Support for five years. His main job is to orchestrate various open source geospatial components and creating application-specific GIS systems. PostgreSQL and PostGIS are among his favorite tools, and are used in almost every project. Apart from developing, he also runs PostGIS training courses. When not at work, he volunteers for OpenStreetMap and a local railway preservation society.
Tomasz Nycz is a geographer and cartographer. He initiated the implementation of GIS in the State Fire Service units in Poland. He works with recognized GIS companies in the emergency management industry. In practice, he uses QGIS and PostGIS. He has been an OpenStreetMap contributor for years. He also develops his scientific interests in the use of new technologies in geomorphology and remote sensing. He is also an avid drone pilot and mountain explorer.
About the Reviewers
Eric Pimpler is the founder and owner of GeoSpatial Training Services (geospatialtraining.com) and has over 20 years of experience in implementing and teaching GIS solutions using Esri, Google Earth/Maps, and open source technology. Currently, he focuses on ArcGIS scripting with Python and the development of custom ArcGIS Server web and mobile applications using JavaScript. He is the author of Programming ArcGIS 10.1 with Python Cookbook.
Eric has a bachelor's degree in Geography from Texas A&M University and a master's degree in Applied Geography with a concentration in GIS from Texas State University.
Prashant Verma started his IT carrier in 2011 as a Java developer at Ericsson working in the telecoms domain. After a couple of years of Java EE experience, he moved into the big data domain, and has worked on almost all the popular big data technologies, such as Hadoop, Spark, Flume, Mongo, and Cassandra. He has also played with Scala. Currently, he works with QA Infotech as lead data engineer, working on solving e-Learning problems using analytics and machine learning. Prashant has also worked on Apache Spark for Java Developers as a technical reviewer.
www.PacktPub.com
For support files and downloads related to your book, please visit www.PacktPub.com.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at [email protected] for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.
https://www.packtpub.com/mapt
Get the most in-demand software skills with Mapt. Mapt gives you full access to all Packt books and video courses, as well as industry-leading tools to help you plan your personal development and advance your career.
Why subscribe?
Fully searchable across every book published by Packt
Copy and paste, print, and bookmark content
On demand and accessible via a web browser
Customer Feedback
Thanks for purchasing this Packt book. At Packt, quality is at the heart of our editorial process. To help us improve, please leave us an honest review on this book's Amazon page at https://www.amazon.com/dp/1784391646.
If you'd like to join our team of regular reviewers, you can e-mail us at [email protected]. We award our regular reviewers with free eBooks and videos in exchange for their valuable feedback. Help us be relentless in improving our products!
Table of Contents
Preface
What this book covers
What you need for this book
Who this book is for
Conventions
Reader feedback
Customer support
Downloading the example code
Downloading the color images of this book
Errata
Piracy
Questions
Importing Spatial Data
Obtaining test data
Setting up the database
Importing flat data
Importing data using psql
Importing data interactively
Importing data non-interactively
Importing data using pgAdmin
Extracting spatial information from flat data
Importing shape files using shp2pgsql
shp2pgsql in cmd
The shp2pgsql GUI version
Importing vector data using ogr2ogr
Importing GML
Importing MIF and TAB
Importing KML
ogr2ogr GUI (Windows only)
Importing data using GIS clients
Exporting a shapefile to PostGIS using QGIS and SPIT
Exporting shapefile to PostGIS using QGIS and DbManager
Exporting spatial data to PostGIS from Manifold GIS
Importing OpenStreetMap data
Connecting to external data sources with foreign data wrappers
Connecting to SQL Server Spatial
Connecting to WFS service
Loading rasters using raster2pgsql
Importing a single raster
Importing multiple rasters
Importing data with pgrestore
Summary
Spatial Data Analysis
Composing and decomposing geometries
Creating points
Extracting coordinates from points
Composing and decomposing Multi-geometries
Multi-geometry decomposition
Composing and decomposing LineStrings
LineString composition
LineString decomposition
Composing and decomposing polygons
Polygon composition
Polygon decomposition
Spatial measurement
General warning - mind the SRID!
Measuring distances between two geometries
Measuring the length, area, and perimeter of geometries
Line length
Polygon perimeter
Polygon area
Geometry bounding boxes
Accessing bounding boxes
Creating bounding boxes
Using bounding boxes in spatial queries
Geometry simplification
Geometry validation
Simplicity and validity
Testing for simplicity and validity
Checking for validity
Repairing geometry errors
Validity constraint
Intersecting geometries
Nearest feature queries
Summary
Data Processing - Vector Ops
Primer - obtaining and importing OpenStreetMap data
Merging geometries
Merging polygons
Merging MultiLineStrings
Slicing geometries
Splitting a polygon by LineString
Splitting a LineString with another LineString
Extracting a section of LineString
Buffering and offsetting geometries
Offsetting features
Creating convex and concave hulls
Computing centroids, points-on-surface, and points-on-line
Reprojecting geometries
Spatial relationships
Touching
Crossing
Overlapping
Containing
Radius queries
Summary
Data Processing - Raster Ops
Preparing data
Processing and analysis
Analytic and statistical functions
Vector to raster conversion
Raster to vector conversion
Spatial relationship
Metadata
Summary
Exporting Spatial Data
Exporting data using \COPY in psql
Exporting data in psql interactively
Exporting data in psql non-interactively
Exporting data in PgAdmin
Exporting vector data using pgsql2shp
pgsql2sph command line
pgsql2shp gui
Exporting vector data using ogr2ogr
Exporting KML revisited
Exporting SHP
Exporting MapInfo TAB and MIF
Exporting to SQL Server
ogr2ogr GUI
Exporting data using GIS clients
Exporting data using QGIS
Exporting data using Manifold.
Outputting rasters using GDAL
Outputting raster using psql
Exporting data using the PostgreSQL backup functionality
Summary
ETL Using Node.js
Setting up Node.js
Making a simple Node.js hello world in the command line
Making a simple HTTP server
Handshaking with a database using Node.js PgSQL client
Retrieving and processing JSON data
Importing shapefiles revisited
Consuming JSON data
Geocoding address data
Consuming WFS data
Summary
PostGIS – Creating Simple WebGIS Applications
ExtJS says Hello World
Configuring GeoServer web services
Importing test data
Outputting vector data as WMS services in GeoServer
Outputting raster data as WMS services in GeoServer
Outputting vector data as WFS services
Making use of PgRaster in a simple WMS GetMap handler
Consuming WMS
Consuming WMS in ol3
Consuming WMS in Leaflet
Enabling CORS in Jetty
Consuming WFS in ol3
Outputting and consuming GeoJSON
Consuming GeoJSON in ol3
Consuming GeoJSON in Leaflet
Outputting and consuming TopoJSON
Consuming TopoJSON in ol3
Consuming TopoJSON in Leaflet
Implementing a simple CRUD application that demonstrates vector editing via web interfaces
WebGIS CRUD server in Node.js
WebGIS CRUD client
Layer manager
Drawing tools
Analysis tools - buffering
Summary
PostGIS Topology
The conceptual model
The data
Installation
Creating an empty topology
Importing Simple Feature data into topology
Checking the validity of input geometries
Creating a TopoGeometry column and a topology layer
Populating a TopoGeometry column from an existing geometry
Inspecting and validating a topology
Topology validation
Accessing the topology data
Querying topological elements by a point
Locating nodes
Locating edges
Locating faces
Topology editing
Adding new elements
Creating TopoGeometries
Splitting and merging features
Splitting features
Merging features
Updating edge geometry
Topology-aware simplification
Importing sample data
Topology output
GML output
TopoJSON output
Summary
pgRouting
Installing the pgRouting extension
Importing routing data
Importing shapefiles
Importing OSM data using osm2pgrouting
pgRouting algorithms
All pairs shortest path
Shortest path
Shortest path Dijkstra
A-Star (A*)
K-Dijkstra
K-Shortest path
Turn restrictions shortest path (TRSP)
Driving distance
Traveling sales person
Handling one-way edges
Consuming pgRouting functionality in a web app
Summary
Preface
PostGIS is an open source extension of the PostgreSQL object-relational database system that allows GIS objects to be stored and allows querying for information and location services. The aim of this book is to help you master the functionalities offered by PostGIS, from data creation, analysis, and output to ETL and live edits.
The book begins with an overview of the key concepts related to spatial database systems and how it applies to spatial RMDS. You will learn to load different formats into your Postgres instance, investigate the spatial nature of your raster data, and finally export it using built-in functionalities or third-party tools for backup or representational purposes.
Through the course of this book, you will be presented with many examples on how to interact with the database using JavaScript and Node.js. Sample web-based applications interacting with backend PostGIS will also be presented throughout the book, so you can get comfortable with the modern ways of consuming and modifying your spatial data.
What this book covers
Chapter 1, Importing Spatial Data, will cover simple import procedures to import data to PgSQL/PostGIS.
Chapter 2, Spatial Data Analysis, looks at vector data analysis, and we'll find our way through a rich function set of PostGIS.
Chapter 3, Data Processing - Vector Ops, discusses the functions available for vector data processing.
Chapter 4, Data Processing - Raster Ops, discusses the functions available for raster data processing.
Chapter 5, Exporting Spatial Data, looks into exporting a dataset from PostGIS to other GIS formats.
Chapter 6, ETL Using Node.js, explains how to perform ETL ops using JavaScript in Node.js.
Chapter 7, PostGIS – Creating Simple WebGIS Applications, focuses on publishing PostGIS data with the usage of web platforms.
Chapter 8, PostGIS Topology, we will discusses different PostGIS Topology types and functions that are used to manage topological objects such as faces, edges, and nodes.
Chapter 9, pgRouting, explains the pgRouting extension and its implementations.
What you need for this book
This book will guide you through the installation of all the tools that you need to follow the examples.
Following is the list of software and the download link to work through this book:
PostgreSQL 9.x (
https://www.postgresql.org/download/
)
PostGIS 2.x (
http://postgis.net/install/
)
QGIS 2.x (
http://www.qgis.org/en/site/forusers/download.html
)
ogr2ogr / gdal (
http://ogr2gui.ca/
)
Manifold 8 (
http://manifold.net/updates/downloads.shtml
)
SQL SERVER 2016 (
https://www.microsoft.com/en-us/sql-server/sql-server-downloads
)
pgAdmin 3 (Should be bundled with PostgreSQL, if not
https://www.pgadmin.org/download/
)
OL3 (
https://openlayers.org/download/
)
Leaflet (
http://leafletjs.com/download.html
)
GeoServer 2.9 + with bundled jetty (
http://geoserver.org/
)
ExtJs (
https://www.sencha.com/products/evaluate/
)
Node.js (
https://nodejs.org/en/download/
)
pgRouting (
http://pgrouting.org/download.html
)
Who this book is for
If you are a GIS developer or analyst who wants to master PostGIS to build efficient, scalable GIS applications, this book is for you. If you want to conduct advanced analysis of spatial data, this book will also help you. The book assumes that you have a working installation of PostGIS in place, and have working experience with PostgreSQL.
Conventions
In this book, you will find a number of text styles that distinguish between different kinds of information. Here are some examples of these styles and an explanation of their meaning.
Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "The next lines of code read the link and assign it to theBeautifulSoupfunction."
A block of code is set as follows:
drop table if exists data_import.osgb_addresses; create table data_import.osgb_addresses( uprn bigint, os_address_toid varchar,
Any command-line input or output is written as follows:
mastering_postgis=# \copy data_import.earthquakes_csv from data\2.5_day.csv with DELIMITER ',' CSV HEADER
New terms and important words are shown in bold. Words that you see on the screen, for example, in menus or dialog boxes, appear in the text like this: "In order to download new modules, we will go toFiles| Settings|Project Name|Project Interpreter."
Reader feedback
Feedback from our readers is always welcome. Let us know what you think about this book-what you liked or disliked. Reader feedback is important for us as it helps us develop titles that you will really get the most out of.
To send us general feedback, simply e-mail [email protected], and mention the book's title in the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide at www.packtpub.com/authors.
Customer support
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
Downloading the example code
You can download the example code files for this book from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
You can download the code files by following these steps:
Log in or register to our website using your e-mail address and password.
Hover the mouse pointer on the
SUPPORT
tab at the top.
Click on
Code Downloads & Errata
.
Enter the name of the book in the
Search
box.
Select the book for which you're looking to download the code files.
Choose from the drop-down menu where you purchased this book from.
Click on
Code Download
.
Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of:
WinRAR / 7-Zip for Windows
Zipeg / iZip / UnRarX for Mac
7-Zip / PeaZip for Linux
The code bundle for the book is also hosted on GitHub at https://github.com/PacktPublishing/Mastering-Postgis. We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!
Downloading the color images of this book
We also provide you with a PDF file that has color images of the screenshots/diagrams used in this book. The color images will help you better understand the changes in the output. You can download this file from https://www.packtpub.com/sites/default/files/downloads/MasteringPostgis_ColorImages.pdf.
Errata
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books-maybe a mistake in the text or the code-we would be grateful if you could report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded to our website or added to any list of existing errata under the Errata section of that title.
To view the previously submitted errata, go to https://www.packtpub.com/books/content/support and enter the name of the book in the search field. The required information will appear under the Errata section.
Piracy
Piracy of copyrighted material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works in any form on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at [email protected] with a link to the suspected pirated material.
We appreciate your help in protecting our authors and our ability to bring you valuable content.
Questions
If you have a problem with any aspect of this book, you can contact us at [email protected], and we will do our best to address the problem.
Importing Spatial Data
Learning database tools means working with data, so we need to cover that aspect first. There are many ways of importing data to PgSQL/PostGIS; some are more database-specific, some are PostGIS-specific, and some use external tools. To complicate things a bit more, quite often real-world data import processes are wrapped into programs that perform different tasks and ops in order to maintain the data quality and integrity when importing it. The key though is that even very complex import tools usually use simpler procedures or commands in order to achieve their goals.
Such simple import procedures are described in this chapter. We specifically focus on:
Importing flat data through both psql and pgAdmin and extracting spatial information from flat data
Importing shape files using
shp2pgsql
Importing vector data using
ogr2ogr
Importing vector data using GIS clients
Importing OpenStreetMap data
Connecting to external data sources with data wrappers
Loading rasters using
raster2pgsql
Importing data with pgrestore
Obtaining test data
Before we start importing, let's get some data examples in different formats, specifically these:
Earthquake data in CSV and KML format (
https://earthquake.usgs.gov/earthquakes/map/
)
UK Ordnance Survey sample data (
https://www.ordnancesurvey.co.uk/business-and-government/licensing/sample-data/discover-data.html
)
AddressBase in CSV and GML
Code-Point Polygons in SHP, TAB and MIF
Points of Interest in TXT format
NaturalEarth (
http://www.naturalearthdata.com/downloads/110m-physical-vectors/
)
110M coastlines
110M land
50M Gray Earth
Setting up the database
All the examples in this chapter use a database named mastering_postgis. This database has been created off the postgis template.
The PgSQL on my dev machine listens on port 5434, which is not the default port for the Postgres database (default is 5432); so when using a default DB setup, you may have to adjust some of the examples a bit.
Schemas are a great way of managing the data and splitting it into meaningful collections. In most scenarios, one will have some production data, archive data, incoming data, and so on sensibly kept in separate schemas. Using additional schemas will depend on your requirements, but we do encourage you to introduce using schemas into your daily practice if you do not yet do so. The following examples import the data into tables defined in the data_import schema.
Importing flat data
Loading flat data may seem to be a bit dull initially but it is important to understand that many popular and interesting datasets often contain the spatial information in very different formats, such as:
Coordinates expressed in Lon/Lat or projected coordinates
Encoded geometry, for example WKT, TopoJSON, GeoJSON
Location in the form of an address
Location in non-cartesian coordinates, for example start point, angle and direction
While the earlier examples indicate the data would require further processing in order to extract the spatial content into a usable form, clearly ability to import flat datasets should not be underestimated
Importing data using psql
Psql is the pgsql's command-line tool. While one can achieve quite a lot with GUI based database management utilities, psql is very useful when one needs to handle database backups, management and alike via scripting. When there is no GUI installed on the server, psql becomes pretty much the only option so it is worth being familiar with it even if you're not a fan.
In order to import the data in psql we will use a \COPY command. This requires us to define the data model for the incoming data first.
Defining the table data model from a text file may be prone to errors that will prevent data from being imported. If for of some reason you are not sure what data types are stored in the particular columns of your source file you can import all the data as text and then re-cast it as required at a later time.
Importing data interactively
In this example we will import the earthquakes data from USGS. So let's fire up psql and connect to the database server:
F:\mastering_postgis\chapter02>psql -h localhost -p 5434 -U postgres
You should see a similar output:
psql (9.5.0)
Type "help" for help.
postgres=#
Then we need to connect to the mastering_postgis database:
postgres=# \c mastering_postgis
The following output should be displayed:
You are now connected to database "mastering_postgis" as user
"postgres".
mastering_postgis=#
If you have not had a chance to create our data_import schema, let's take care of it now by typing the following command:
mastering_postgis=# create schema if not exists data_import;
You should see a similar output:
NOTICE: schema "data_import" already exists, skipping
CREATE SCHEMA
Once the schema is there, we create the table that will store the data. In order to do so just type or paste the following into psql:
create table data_import.earthquakes_csv ( "time" timestamp with time zone, latitude numeric, longitude numeric, depth numeric, mag numeric, magType varchar, nst numeric, gap numeric, dmin numeric, rms numeric, net varchar, id varchar, updated timestamp with time zone, place varchar, type varchar, horizontalError numeric, depthError numeric, magError numeric, magNst numeric, status varchar, locationSource varchar, magSource varchar );
You should see the following output:
mastering_postgis=# create table data_import.earthquakes_csv ( mastering_postgis(# "time" timestamp with time zone, mastering_postgis(# latitude numeric, mastering_postgis(# longitude numeric, mastering_postgis(# depth numeric, mastering_postgis(# mag numeric, mastering_postgis(# magType varchar, mastering_postgis(# nst numeric, mastering_postgis(# gap numeric, mastering_postgis(# dmin numeric, mastering_postgis(# rms numeric, mastering_postgis(# net varchar, mastering_postgis(# id varchar, mastering_postgis(# updated timestamp with time zone, mastering_postgis(# place varchar, mastering_postgis(# type varchar, mastering_postgis(# horizontalError numeric, mastering_postgis(# depthError numeric, mastering_postgis(# magError numeric, mastering_postgis(# magNst numeric, mastering_postgis(# status varchar, mastering_postgis(# locationSource varchar, mastering_postgis(# magSource varchar mastering_postgis(# ); CREATE TABLE
Now, as we have our data table ready, we can finally get to the import part. The following command should handle importing the data into our newly created table:
\copy data_import.earthquakes_csv from data\2.5_day.csv with DELIMITER ',' CSV HEADER
You should see a similar output:
mastering_postgis=# \copy data_import.earthquakes_csv from data\2.5_day.csv with DELIMITER ',' CSV HEADER
COPY 25
While you can customize your data after importing, you may wish to only import a subset of columns in the first place. Unfortunately \COPY command imports all the columns (although you may specify where to put them) so the solution to this would be:
Create a table that models the input CSV
Import all the data
Create a table with a subset of columns
Copy data over
Delete the input table
Even though everything said earlier is possible in psql, it requires quite a lot of typing. Because of that we will take care of this scenario in the next stage. Demonstrating the non-interactive psql mode.
Importing data non-interactively
For the non-interactive psql data import example we'll do a bit more than in the interactive mode. We'll:
Import the full earthquakes dataset
Select a subset of earthquakes data mentioned in the previous example and insert it into its own table
Import another dataset - in this case the Ordnance Survey's POIs
Basically the non-interactive usage of psql means we simply provide it with an SQL to execute. This way we can put together many statements without having to execute them one by one.
Once again we will need the data model prior to loading the data, and then a \COPY command will be used.
If you're still in psql, you can execute a script by simply typing:
\i path\to\the\script.sql
For example:
\i F:/mastering_postgis/chapter02/code/data_import_earthquakes.sql
You should see a similar output:
mastering_postgis-# \i F:/mastering_postgis/chapter02/code/data_import_earthquakes.sql
CREATE SCHEMA
psql:F:/mastering_postgis/chapter02/code/data_import_earthquakes.sql:5: NOTICE: table "earthquakes_csv" does not exist, skipping
DROP TABLE
CREATE TABLE
COPY 25
psql:F:/mastering_postgis/chapter02/code/data_import_earthquakes.sql:58: NOTICE: table "earthquakes_csv_subset" does not exist, skipping
DROP TABLE
SELECT 25
mastering_postgis-#
If you quit psql already, type the following command into cmd:
psql -h host -p port -U user -d database -f path\to\the\script.sql
For example:
psql -h localhost -p 5434 -U postgres -d mastering_postgis -f F:\mastering_postgis\chapter02\code\data_import_earthquakes.sql
You should see a similar output:
F:\mastering_postgis\chapter02>psql -h localhost -p 5434 -U postgres -d mastering_postgis -f F:\mastering_postgis\chapter02\code\data_import_earthquakes.sql
psql:F:/mastering_postgis/chapter02/code/data_import_earthquakes.sql:2: NOTICE: schema "data_import" already exists, skipping
CREATE SCHEMA
DROP TABLE
CREATE TABLE
COPY 25
DROP TABLE
SELECT 25
The script executed earlier is in the book's code repository under Chapter02/code/ data_import_earthquakes.sql.
Loading OS POI data is now a piece of cake. This dataset is in a bit of a different format though, so it requires slight adjustments. You can review the code in Chapter02/code/ data_import_gb_poi.sql.
Importing data using pgAdmin
In this section we'll import some new data we have not interacted with before - this time we'll have a look at the Ordnance Survey's address data we obtained in the CSV format.
PgAdmin's import functionality is basically a wrapper around the \COPY so it does require a data model in order to work. Because of that, let's quickly create a table that will be populated with the imported data. You can do it with the GUI by simply right-clicking a schema node you want to create the table in and choosing New Object | New Table and then providing all the necessary model definitions in the displayed window:
You can also type some SQL which in many cases is a bit quicker:
drop table if exists data_import.osgb_addresses; create table data_import.osgb_addresses( uprn bigint, os_address_toid varchar, udprn integer, organisation_name varchar, department_name varchar, po_box varchar, sub_building_name varchar, building_name varchar, building_number varchar, dependent_thoroughfare varchar, thoroughfare varchar, post_town varchar, dbl_dependent_locality varchar, dependent_locality varchar, postcode varchar, postcode_type varchar, x numeric, y numeric, lat numeric, lon numeric, rpc numeric, country varchar, change_type varchar, la_start_date date, rm_start_date date, last_update_date date, class varchar );
Once our table is ready, importing data is just a matter of right clicking the table node in PgAdmin and choosing Import. An import wizard that assists with the import process will be displayed:
Extracting spatial information from flat data
As we have some flat data already in our database, it's time to extract the spatial information. So far all the datasets, used Cartesian coordinate systems so our job is really straightforward:
drop table if exists data_import.earthquakes_subset_with_geom; select id, "time", depth, mag, magtype, place,Points of Interest in TXT format ST_SetSRID(ST_Point(longitude, latitude), 4326) as geom into data_import.earthquakes_subset_with_geom from data_import.earthquakes_csv;
This example extracts a subset of data and puts data into a new table with coordinates being expressed as a geometry type, rather than two columns with numeric data appropriate for Lon and Lat.
In order to quickly preview the data, we dump the table's content to KML using ogr2ogr (this is a little spoiler on the next chapter on exporting the data from PostGIS indeed):
ogr2ogr -f "KML" earthquakes_from_postgis.kml PG:"host=localhost port=5434 user=postgres dbname=mastering_postgis" data_import.earthquakes_subset_with_geom -t_srs EPSG:4326
Such KML can be viewed for example in Google Earth (you can use the original KML downloaded from USGS just as a cross check for the output data):
Importing shape files using shp2pgsql
ESRI shapefile (SHP) is still the most common exchange format for sharing GIS data. The format itself is made of a few files such as SHP, SHX, DBF, andPRJ, where the first three are the required files and the file with projection information is not obligatory.
The standard PostGIS tool for loading shapefiles is shp2pgsql - you will find it in the bin folder of your postgres installation. shp2pgsql is a command-line utility that can either extract the shapefile data into SQL or pipe the output directly into psql (we'll see both approaches). shp2pgsql also has a GUI version that can be accessed directly in PgAdmin.
In this example, we'll use some NaturalEarth
