Spatial Data Science with PostgreSQL: Geometries

A beginners walkthrough in PostGIS Geometry types and functions.

Geometries are the glues that hold together geospatial data. They form an integral part of any spatial data processing. In this tutorial, I will go through some of the different types of geometries available in Postgis. We also touch on some of the most used functions with real-world data examples.

In my last article, I explained how to install PostgreSQL and activate Postgis extensions. We also covered how to get started quickly in PgAdmin, create spatial databases and load spatial data. So if you have not set up PostgreSQL and PostGIS, you can use this as a starting point.

http://A practical guide to getting started with PostgreSQL and Python for Spatial Data.towardsdatascience.com

Geometries

In PostGIS, all objects and functions specified in the Open Geospatial Consortium (OGC) and the “Simple Features for SQL” specifications are available. Geometry data types supported in Postgis include:

POINT()
LINESTRING()
POLYGON((),())
MULTIPOINT((),())
MULTILINESTRING((),())
MULTIPOLYGON(((),()), (()))
GEOMETRYCOLLECTION(POINT(),LINESTRING())

PostGIS also extends raster data types as well as 3-D (Height denoted as Z) and 4-D (commonly time indicated as M) geometry types.

Casting your spatial data to PostGIS geometry will let you take advantage of the spatial functionalities available in PostGIS. A new favourite feature of this is the in-built geometry visualization in PgAdmin, which we will see later in the next section. In this tutorial, we cover only the basic geometries, Points, Polygon and Lines. Other geometries are an extended version of these fundamental geometry types.

Points

We start with Points Geometry, which usually referred to a dot in space which has no size and no width. Let us see Point Geometry in real-world data and explore some of its methods in PostGIS.

The classical SQL query contains SELECT and From. In the next piece of SQL, we return the first ten rows of tfl_stations dataset, which we have uploaded earlier.

SELECT id, name, zone, geom 
FROM public.tfl_stations
LIMIT 10;
Photo by Ronald Yang on Unsplash

This result, however, has geometry column and if you click on the eye, you’ll see your geometries rendered as in below map.

Photo by Ronald Yang on Unsplash

To get the Geometry type of data, you can use ST_GeometryType(geom) to find out. For example, the following SQL code returns the Geometry Data type of the station’s dataset.

SELECT ST_GeometryType(geom)
FROM tfl_stations
FETCH FIRST ROW ONLY

As we expect, this query returns ST_Point.

“ST_Point”

If you like to get the geometry as text with coordinates, you can also use ST_AsText(geom). The following query returns coordinates of each point.

SELECT name, zone, ST_AsText(geom)
FROM tfl_stations
LIMIT 10;
Photo by Ronald Yang on Unsplash

To access the coordinates separately, we can use ST_X and ST_Y, as shown in the example below.

SELECT name, zone, ST_X(GEOM) AS Longitude, ST_Y(GEOM) AS Latitude
FROM tfl_stations
FETCH FIRST 10 ROW ONLY
Photo by Ronald Yang on Unsplash

Here are some highlights of Point functions in PostGIS:

Linestrings

LineStrings are connected straight lines between two or more points. In PostGIS, you can construct Lines from connected points.

We have already uploaded streets dataset, So let us query the data.

SELECT streetname, streetclas, streettype, ST_Length(geom) AS STREET_LENGTH
FROM public.streets
LIMIT 10;

Using ST_length in PostGIS, we simply create an additional column in the dataset calculate the length of each street, using ST_Length in PostGIS.

Photo by Ronald Yang on Unsplash

Now, what unit of measurement is this? You need to look into the Spatial Reference system of the dataset. To find out which SRID, the dataset uses you can find it with either Find_SRID or ST_SRID. We use the later here

SELECT ST_SRID(geom)
FROM public.streets
LIMIT 1;

And we find out that we have EPSG:26917, which uses metre as a unit of measurement. Let us change the street length to KM since we now know that we metre as a unit of measure.

SELECT streetname, streetclas, streettype, ST_Length(geom)/1000 AS STREET_LENGTH_KM
FROM public.streets
LIMIT 10;

Here are some highlights of LineString functions in PostGIS:

Polygons

Polygons are constructed from closed LineStrings. The polygon contains all enclosed area and its boundary, while the closed Linestring in the boundary is called the exterior ring

In the next example, we are calculating the area of each building using ST_Area(geom) function in PostGIS.

SELECT theme, ST_Area(geom)
FROM public.buildings
LIMIT 10;

Here are some highlights of Polygon functions in PostGIS:

Conclusion

Geometries form the basic blocks of spatial data analysis. In this tutorial, we have seen different types of Geometries in PostGIS. With some examples, we have also covered the basic functionalities of each geometry type, including Points, Polygon and LineString.

Leave a Reply

Your email address will not be published. Required fields are marked *