articles
Gis Analysis

# 25 PostGIS functions for spatial analysis – PostGIS functions

## PostGIS functions for spatial analysis, ostGIS is a powerful open-source spatial database extension for PostgreSQL that enables the storage, manipulation, and analysis of spatial data

### 25 PostGIS functions for spatial analysis

In this article cartogeek.com want to provide a useful small compilation on the main PostGIS functions for spatial analysis.

The functions are ordered or grouped by type of use and we are going to propose some examples to see how to build a space SQL query basic for each case.

## Use PostGIS for spatial analysis

Space databases are an essential tool for the development of most projects based on spatial information. Among all of them, the most widespread in the geotechnology and geotechnology sector Geographic Information Systems is without a doubt PostgreSQL – PostGIS.

PostGIS implements an overwhelming number of functions to work with geographic objects that will allow us to analyze, relate and make complex queries about our stored data.

## Main spatial functions in PostGIS

### Features for calculating geometric attributes

oneST_Area. Returns the area of a polygon or multipoligone. The column containing the geometry must be passed as a parameter. For non-polygonal geometries it returns values of 0. The units of measurement correspond to those contained in the geometry coordinate reference system.

 12 `SELECT` `ST_Area(geom) ``AS` `area``FROM` `datos.poligonos;`

2ST_Perimeter. Returns the length of the perimeter of a polygon or multipoligone. The column containing the geometry must be passed as a parameter. For non-polygonal geometries it returns values of 0. The units of measurement correspond to those contained in the geometry coordinate reference system.

 12 `SELECT` `ST_Perimeter(geom) ``AS` `perímetro``FROM` `datos.poligonos;`

3ST_Length. Returns the two-dimensional length of a line or multiline type geometry. The column containing the geometry must be passed as a parameter. For non-polygonal geometries it returns values of 0. The units of measurement correspond to those contained in the geometry coordinate reference system.

 12 `SELECT` `ST_Length(geom) ``AS` `longitud``FROM` `datos.lineas;`

### PostGIS functions for construction or creation of geometries

4.ST_MakePointAllows you to create 2-dimensional point-type objects ( values x, y ), 3 dimensions ( x, y, z ) i 4 dimensions ( x, y, z, m ).

 123 `SELECT` `ST_MakePoint(10 15);``SELECT` `ST_MakePoint(-3.7018, 40.3185);`

5ST_MakeLine. Allows you to create a dot-based line-type geometry. There are various methodologies.

The simplest:

 12 `SELECT`  `ST_MakeLine(geom) ``AS` `linea ``FROM` `datos.puntos;`

Orderedly based on a numeric field that identifies the order of the line layout:

 123456 `SELECT` `ST_MakeLine(id_puntos.geom) ``AS` `linea``FROM` `(``    ``SELECT` `puntos.id, geom``    ``FROM` `datos.puntos``    ``ORDER` `BY` `puntos.id``) ``AS` `id_puntos ;`

Or through an array of points:

 1234567 `SELECT` `ST_MakeLine(``    ``ARRAY[``        ``ST_MakePoint(5,2), ``        ``ST_MakePoint(4,5), ``        ``ST_MakePoint(8,10)``    ``]``);`

6ST_MakePolygonIt allows to build a polygon type geometry based on a line type geometry. Linear geometry must be closed, that is, its initial and final node must coincide.

 one2345 `SELECT` `ST_MakePolygon(``    ``ST_GeomFromText(``        ``'LINESTRING(10 10, 15 10, 20 15, 10 15, 10 10)'``    ``)``);`

7ST_GeomFromText. Returns a geometric object based on an expression in WKT format ( Well Known Text ) that defines it.

 12345 `SELECT` `ST_GeomFromText(``'POINT(20 20)'``)``SELECT` `ST_GeomFromText(``'LINESTRING(-5 10, 10 10, 10 15, 20 40, -10 20)'``)``SELECT` `ST_GeomFromText(``'POLYGON((10 10, 15 10, 10 15, 5 5, 10 10))'``)`

If you want to know more about the geometry encoding format Well Known Text you can take a look at this article where we talk about it.

8AddGeometryColumn and DropGeometryColumn. They allow you to create or remove the column that contains the geographic object of a table. The table schema, field name, SRID or coordinate system, geometry type, and dimensions must be specified.

 123 `SELECT` `AddGeometryColumn(``'datos'``, ``'nuevospuntos'``, ``'geom'``, 25830, ``'POINT'``, 2)``SELECT` `DropGeometryColumn(``'datos'``, ``'nuevospuntos'``, ``'geom'``)`

### PostGIS functions for spatial analysis

9ST_Buffer. Returns a geometry that represents all those points within a given radius from a given geometric object. Calculations will be based on the SRID of geometry.

 12 `SELECT` `ST_Buffer(geom, 750) ``FROM` `datos.lineas;`

10.ST_SymDifference. Returns a geometry that represents the parts of the objects in Table A and Table B that are not interested. It is called symmetric difference because it does not matter to pass one or the other table as the first argument, that is, ST_SymDifference ( A, B ) = ST_SymDifference ( B, A ).

 123456 `SELECT` `ST_SymDifference(``    ``parques.geom, ``    ``ST_Buffer(papeleras.geom, 500))``FROM``datos.poligonos ``as` `parques, ``datos.puntos ``as` `papeleras;`

elevenST_Intersection. Returns the geometry that represents the common points between two input geometries. It is important, in this case, to remember that since these are common points between both layers, A and B are evaluated in the same way, that is, ST_Intersection ( A, B ) = ST_Intersection ( B, A )

 123456 `SELECT` `ST_Intersection(``    ``parques.geom, ``    ``ST_Buffer(papeleras.geom, 500))``FROM``datos.poligonos ``as` `parques, ``datos.puntos ``as` `papeleras;`

12ST_UnionReturns a geometry that represents the union of two tables with spatial data.

 12 `SELECT` `ST_Union(lineas.geom, poligonos.geom) ``FROM` `datos.lineas, datos.poligonos;`

13.ST_CentroidReturns the centroid calculated from the input geometries.

 12 `SELECT` `ST_Centroid(poligonos.geom)``FROM` `datos.poligonos;`

14. ST_EnvelopeReturns a geometry that represents the bounding box of the geometries contained in the table.

 12 `SELECT` `ST_Envelope(lineas.geom)``FROM` `datos.lineas;`

### Boolean functions for relationships between geometries

fifteenST_Contains. Returns a Boolean value True for all those geometries of B contained in A that if, and only if, none of their points are outside the limits of A. Otherwise the Boolean value False returns.

 12 `SELECT` `ST_Contains(poligonos.geom, lineas.geom) ``FROM` `datos.lineas, datos.poligonos;`

16. ST_IntersectsReturns the Boolean value True if the geometries of A and B share any common point. That is, if they overlap at some point in space.

 12 `SELECT` `ST_Intersects(poligonos.geom, lineas.geom) ``FROM` `datos.lineas, datos.poligonos;`

17.ST_Overlaps. Returns the Boolean value True if geometry B overcomes geometry A, but B exceeds the limits of A. That is, if they occupy part of the same space in the same dimensions but without containing each other.

 12 `SELECT` `ST_Overlaps(poligonos_a.geom, poligonos_b.geom) ``FROM` `datos.poligonos_a, datos.poligonos_b;`

18ST_Touches. Returns True if the geometries of A and B have any point on their edges / limits in common. For points, apply if your coordinates match any point located on the edge of a polygon.

 12 `SELECT` `ST_Touches(poligonos.geom, lineas.geom) ``FROM` `datos.lineas, datos.poligonos;`

19. ST_CoversReturns True if no point of B is outside A, that is, if the area occupying A is capable of covering all the geometric elements entirely of B.

 12 `SELECT` `ST_Covers(poligonos.geom, lineas.geom) ``FROM` `datos.lineas, datos.poligonos;`

At this point we cannot forget one of the main functions of spatial relationship analysis between tables: the space join. In the following article we give all the details to know in depth how to carry out operations of space join at PostGIS.

### Functions related to coordinate and position systems

23ST_Translate. It allows to move or move the geometry, both in 2 dimensions and in 3 dimensions. We must pass as arguments the geometry and the displacement values on each axis.

 1 `SELECT` `ST_SetSRID(ST_Point(-3.7018, 40.3185), 4326);`

22ST_Transform. It allows to reproject geometries to a defined coordinate reference system. In our case we reproject from the original SRID ( EPSG: 25830 ) from the polygons in the table to EPSG: 4326:

 12 `SELECT` `ST_Translate(geom, -0.05, 0.25) ``FROM` `datos.poligonos;`

twenty-one. ST_SRIDLets you see what the SRID of a geometry or set of geometries is. Returns a column showing the value of the SRID EPSG code ( in our case returns: 25830 which corresponds to the ETRS89 UTM 30N ):

 12 `SELECT` `ST_Transform(geom, 4326) ``FROM` `datos.poligonos;`

twenty. ST_SetSRIDAllows you to set / assign the SRID or Coordinate Reference System of a geometry or set of geometries. In this case we are assigning the EPSG: 4326 ( WGS 84 ) to the point with its defined coordinates:

 12 `SELECT` `ST_SRID(geom) ``FROM` `datos.poligonos;`

### Geometry verification functions

24. GeometryTypeLets you know the type of geometry of an object. Returns a value that defines the typology within PostGIS, such as: ST_Point, ST_Linestring, ST_Polygon, ST_Multipoint…

 12 `SELECT` `ST_GeometryType(geom) ``as` `tipo``FROM` `datos.poligonos;`

25ST_IsValidLets you know if a geometry is valid. Returns a Boolean value for each geometry.

 12 `SELECT` `ST_IsValid(geom) ``as` `validez``FROM` `datos.poligonos;`

If, for example, we wanted to know how many geometries in a table are valid and invalid, we could make a query of the style:

 1234 `SELECT` `ST_IsValid(geom) ``as` `validez, ``(``select` `count``(ST_IsValid(geom)) ``as` `numero)``FROM` `datos.poligonos``GROUP` `BY` `validez`