Bringing PostGIS to PostgreSQL on Alibaba Cloud (Part 2)

Prerequisites

  1. A valid Alibaba Cloud account. If you don’t have one already, sign up to a Free Trial to enjoy up to $300 worth in Alibaba Cloud products free of charge.
  2. You have an ECS instance that is installed with Ubuntu 16.04. You can select your preferred region and configurations. Your choices will not affect the outcome of the server setup.
  3. A root password is set for your server.
  4. You have an ApsaraDB RDS for PostgreSQL that is connected to your Ubuntu 16.04 instance.

Procedure

Non-Spatial Queries

SELECT COUNT(*),* FROM places WHERE fclass='town' GROUP BY places.gid
SELECT name, fclass, COUNT(fclass) FROM places
WHERE name = 'bamba location' AND fclass = 'town'
GROUP BY name, fclass
ORDER BY name, fclass

Spatial Functions

  • ST_Distance
  • ST_DWithin
  • ST_Length
  • ST_Intersects
  • ST_Within

1. The ST_Distance functions (geometry, geometry)

  • One degree comprises of 60 nautical miles
  • 1 nautical mile is equivalent to 1852meters
SELECT ST_DISTANCE(p1.geom, p2.geom) as distance FROM places p1, places p2 WHERE p1.name = 'Kisian' and p2.name = 'Kajiado'
SELECT ST_DISTANCE(p1.geom, p2.geom) as distance * 60 * 1852  FROM places p1, places p2 WHERE p1.name = 'Kisian' and p2.name = 'Kajiado'

2. The ST_DWithin function (geometry, geometry, float)

SELECT *,h.name AS town, ST_DISTANCE(s.geom, h.geom) *69 AS distance FROM places AS h INNER JOIN places AS s ON (ST_DWITHIN(s.geom, h.geom, (1.00))) WHERE h.fclass = 'town' and s.name = 'Kajiado'

3. The ST_Intersects function (geometry, geometry)

select *,ri.name FROM places AS rk INNER JOIN places AS ri ON (st_intersects(rk.geom,ri.geom)) WHERE rk.name = 'Nairobi'

4. The ST_Within function (geometry A, geometry B)

SELECT * FROM places
WHERE ST_Within(geom, ST_GeomFromText('Polygon((9
-4.850154078505659 33.6181640625,
-4.850154078505659 42.1875,
4.806364708499998 42.1875,
4.806364708499998 33.6181640625,
-4.850154078505659 33.6181640625))',
4326))
ORDER BY name;

5. The ST_Length function

$ ogr2ogr -f geoJSON waterways.geojson gis_osm_waterways_free_1_1.shp

Load GeoJSON data to Database using ogr2ogr

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

Rename the geom column

sh
ALTER TABLE waterways
RENAME COLUMN wkb_geometry to geom;
SELECT name, (ST_Length(geom))*60*1852 AS length FROM waterways
WHERE name = 'Likoni River' AND fclass ='river'
$ raster2pgsql raster_options_go_here raster_file someschema.sometable > out.sql
CREATE TABLE myrasters(rid serial primary key, rast raster);
CREATE INDEX myrasters_rast_st_convexhull_idx ON myrasters USING gist( ST_ConvexHull(rast) );

Summary

  • We were able to install PostgreSQL and connect it with ApsaraDB for PostgreSQL.
  • Create a PostGIS extension and understand how it works.
  • Create Spatial tables.
  • Understand and use gdal for file conversion.
  • Use of shp2pgsql as well as raster2pgsql.
  • Load GeoJSON data to Database using ogr2ogr
  • Perform both Spatial and non- spatial functions, including ST_Distance, ST_DWithin, ST_Length, ST_Intersects, and ST_Within.

Original Source

--

--

--

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

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

How to access your digital data

Marksman: An Application of Flow to the Agile Methodology in Software Development

VIVID Playground 1.5 Release!

Java 8 | Method 2

Linear scalable read-write lock

Top Data Structures and Algorithms in Java That You Need To Know

Docker Swarm Mode and Distributed Traefik proxy with HTTPS

Connecting WordPress with AWS RDS Database

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

More from Medium

Transfer data from Jira to SharePoint using Matillion ETL Tool

Cloud System Architecture — Diagrams

ksqlDB —real-time SQL magic in the cybersecurity scenario— part 1

Setting up Hadoop with Docker and using MapReduce framework