Bring PostGIS to PostgreSQL on Alibaba Cloud (Part 1)

A Bit about PostGIS

Why Use PostGIS? What Are Its Advantages?

  • Its ability to build indexes in any kind of data, having a generic index structure.
  • It has many useful spatial functions to search, analyze, convert and manage spatial data.
  • It has both vector and raster data support.

Prerequisites

  1. A valid Alibaba Cloud account. If you don’t have one already, sign up to the Free Trial to enjoy up to $300 worth in Alibaba Cloud products.
  2. 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.
  3. A root password for your server.
  4. An ApsaraDB RDS for PostgreSQL instance connected to your Ubuntu 16.04 instance.

Procedure

Installing PostgreSQL

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

Connecting to ApsaraDB RDS for PostgreSQL

  • Use the intranet IP address associated with the ECS instance on which your client is installed and that is located in the same region and the same network type as the RDS instance to be connected to it.
  • Use an Internet IP address for the other situations.
  • Host name/address: Refers to the connection address of the RDS instance. If your application accesses the RDS instance through the intranet, enter the intranet IP address of the RDS instance. If your application accesses the RDS instance through the Internet, enter the Internet IP address of the RDS instance.
  • 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

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

Install Geospatial Libraries

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

Create a Spatial Database

  • A point is represented by a single x- and y-coordinate pair.
  • 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

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

Loading GIS Data

$ wget https://download.geofabrik.de/africa/kenya-latest-free.shp.zip
  • .shp: Holds the vector data for the world borders geometries.
  • .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 -g geom gis_osm_places_a_free_1.shp places > places.sql
psql -d mydatabase -f places.sql

Converting from Shapefile to GeoJSON

$ ogr2ogr -f geoJSON places.geojson gis_osm_places_a_free_1.shp

Load GeoJSON Data to Database Using ogr2ogr

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

Conclusion

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
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com