Bringing PostGIS to PostgreSQL on Alibaba Cloud (Part 2)

Prerequisites

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

  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

To perform both spatial and non-spatial function queries on ApsaraDB for PostgreSQL, complete the steps outlined in the following sections:

Non-Spatial Queries

With our database populated, we will now develop our very first query. Before we can start applying spatial functions, it is important that we develop knowledge of using basic standard functions on PostgreSQL.

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

We will cover a few spatial functions and see how we can use them to extract data from our database. They are:

  • ST_Distance
  • ST_DWithin
  • ST_Length
  • ST_Intersects
  • ST_Within

1. The ST_Distance functions (geometry, geometry)

For geometry type returns the minimum 2D Cartesian distance between two geometries in projected units (spatial ref units). For geography type defaults to return the minimum geodesic distance between two geographies in meters. For example, POINT(-1.344165 51.381320) position is given in degrees. Another geographic reference system could use a different unit of measurement, such as meters or feet.

  • 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)

For geometry: The distance is specified in units defined by the spatial reference system of the geometries. For this function to make sense, the source geometries must both be of the same coordinate projection, having the same SRID.

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)

If a geometry or geography shares any portion of space, then they intersect. If this returns true, then the geometries also spatially intersect. Disjoint implies false for spatial intersection.

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)

The ST_Within spatial function simply returns true when Geometry A is completely inside B. Enter the query below:

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

The ST_Length function returns the length of a geometrical object if it’s a line. The measurement is in the units of its spatial reference. In order to understand how it works we will try out an example.

$ ogr2ogr -f geoJSON waterways.geojson gis_osm_waterways_free_1_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 database name, the geojson file and also the table we will be creating.

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

Rename the geom column

At the moment the geom column is named as wkb_geometry. We need to change it to geom in relation to PostGIS PrevieLoad GeoJSON data to Database using ogr2ogrw, which only accepts the geom column as geom.

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

Now let take a second look at what we have all done in this tutorial:

  • 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

--

--

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