Bring PostGIS to PostgreSQL on Alibaba Cloud (Part 1)

A Bit about PostGIS

Before we get our hands dirty let’s make sure that we understand the terms that appear in this tutorial:

Why Use PostGIS? What Are Its Advantages?

PostGIS is one of the most reliable open-source DBMS available currently, having been on the market for some time already. It hosts several advantages over other alternatives:

  • It has many useful spatial functions to search, analyze, convert and manage spatial data.
  • It has both vector and raster data support.

Prerequisites

For you to complete this tutorial, you’ll need to have the following:

  1. An ECS instance that is installed with Ubuntu 16.04. You can select your preferred region and configurations; this will not affect the outcome of the server setup.
  2. A root password for your server.
  3. An ApsaraDB RDS for PostgreSQL instance connected to your Ubuntu 16.04 instance.

Procedure

Installing PostgreSQL

For now, you will need to first begin with the PostgreSQL command line interface, which you will install PosegreSQL on our Alibaba Cloud ECS instance. You will later learn how to connect to your Apsara RDS for PostgreSQL.

$ sudo apt-get update$ sudo apt-get install -y postgresql postgresql-contrib

Connecting to ApsaraDB RDS for PostgreSQL

When you connect to an RDS instance through a client, choose to use an intranet or Internet address as follows:

  • Use an Internet IP address for the other situations.
  • Port: Refers to the port number of the RDS instance. If your application accesses the RDS instance through the intranet, enter the intranet port number of the RDS instance. If your application accesses the RDS instance through the Internet, enter the Internet port number of the RDS instance.
  • User: Refers to the initial account name of the RDS instance.
  • Password: Refers to the password of the initial account name of the RDS instance.

Installing PostGIS

Now install PostGIS in the database server. Run the following command:

$ sudo apt-get install -y postgis postgresql-9.5-postgis-2.2

Install Geospatial Libraries

We will be installing a special tool that is part of PostGIS’S infrastructure, a library known as Geospatial Data Abstraction Library (GDAL). It will allow us to covert spatial data from one form to another and to transform and re-project raster data files. You can use this command to install the tool:

$ sudo apt-get install binutils libproj-dev gdal-bin`
GDAL 1.11.3, released 2015/09/16

Create a Spatial Database

Now that we have installed PostgreSQL with the PostGIS extension, we are ready to develop a spatial database. A spatial database allows you to be able to create geometric fields to store the geographic data (such as points, lines or polygons).

  • A line is represented by a set of x- and y-coordinate pairs that define a connected path through space.
  • A polygon is represented by a boundary consisting of several lines that form a closed loop, or more precisely a shape.
psql -h <your_host> -d mydatabase -U <your_user>
mydatabase=# CREATE EXTENSION postgis;
mydatabase=# CREATE EXTENSION postgis;
CREATE EXTENSION
mydatabase=#

Install PostGIS Preview

With everything out of the way, we are left with installing PostGIS Preview which is a lightweight node API and front end for quickly previewing PostGIS queries created by NYC Planning. You can check out the repository on Github .

git clone https://github.com/NYCPlanning/labs-postgis-preview.git
(DATABASE_URL=postgres://user:password@host:port/database)

Loading GIS Data

We will begin with downloading the data we will be using downloading the data from Open Street Map.

$ wget https://download.geofabrik.de/africa/kenya-latest-free.shp.zip
  • .shx: Spatial index file for geometries stored in the .shp.
  • .dbf: Database file for holding non-geometric attribute data (such as integer and character fields).
  • .prj: Contains the spatial reference information for the geographic data stored in the shapefile.
$ ogrinfo -so gis_osm_places_a_free_1.shp gis_osm_places_a_free_1
INFO: Open of `gis_osm_places_a_free_1.shp'
using driver `ESRI Shapefile' successful.
Layer name: gis_osm_places_a_free_1
Geometry: Polygon
Feature Count: 839104
Extent: (33.933116, -4.665723) - (41.877231, 4.544926)
Layer SRS WKT:
GEOGCS["GCS_WGS_1984",
DATUM["WGS_1984",
SPHEROID["WGS_84",6378137,298.257223563]],
PRIMEM["Greenwich",0],
UNIT["Degree",0.017453292519943295]]
osm_id: String (10.0)
code: Integer (4.0)
fclass: String (28.0)
name: String (100.0)
type: String (20.0)

Using shp2pgsql Data Loader

shp2pgsql Data Loader simply converts ESRI Shape files into SQL suitable for insertion into a PostGIS/PostgreSQL database. Let’s see how it works.

shp2pgsql -g geom gis_osm_places_a_free_1.shp places > places.sql
psql -d mydatabase -f places.sql

Converting from Shapefile to GeoJSON

We had earlier mentioned that we were going to use PostGIS Preview to view the final output of the sql queries we perform. It only supports data that is in GeoJSON format (format for encoding a variety of geographic data structures).

$ ogr2ogr -f geoJSON places.geojson gis_osm_places_a_free_1.shp

Load GeoJSON Data to Database Using ogr2ogr

We can also use ogr2ogr to load the geojson file to the database directly. All we need to do is input the correct datase name, the geojson file and also the table we will be creating.

ogr2ogr -append -f "PostgreSQL" PG:"dbname=mydatabase" places.geojson -nln places

Conclusion

If you have followed every step correctly in this file, then you probably have a PostgreSQL database populated with spatial data. You have learned how to populated your database with either a shapefile, sql file or a GeoJSON file. You have also known how to use GDAL to convert a shapefile into a geoJSON as well as check the metadata for a shapefile.

Original Source

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store