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
Contents
- 1 Use PostGIS for spatial analysis
- 2 Main spatial functions in PostGIS
- 2.1 Features for calculating geometric attributes
- 2.2 PostGIS functions for construction or creation of geometries
- 2.3 PostGIS functions for spatial analysis
- 2.4 Boolean functions for relationships between geometries
- 2.5 Functions related to coordinate and position systems
- 2.6 Geometry verification functions
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
one. ST_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.
1 2 | SELECT ST_Area(geom) AS areaFROM datos.poligonos; |
2. ST_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.
1 2 | SELECT ST_Perimeter(geom) AS perímetroFROM datos.poligonos; |
3. ST_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.
1 2 | SELECT ST_Length(geom) AS longitudFROM datos.lineas; |
PostGIS functions for construction or creation of geometries
4.ST_MakePoint. Allows you to create 2-dimensional point-type objects ( values x, y ), 3 dimensions ( x, y, z ) i 4 dimensions ( x, y, z, m ).
1 2 3 | SELECT ST_MakePoint(10 15);SELECT ST_MakePoint(-3.7018, 40.3185); |
5. ST_MakeLine. Allows you to create a dot-based line-type geometry. There are various methodologies.
The simplest:
1 2 | SELECT ST_MakeLine(geom) AS linea FROM datos.puntos; |
Orderedly based on a numeric field that identifies the order of the line layout:
1 2 3 4 5 6 | SELECT ST_MakeLine(id_puntos.geom) AS lineaFROM ( SELECT puntos.id, geom FROM datos.puntos ORDER BY puntos.id) AS id_puntos ; |
Or through an array of points:
1 2 3 4 5 6 7 | SELECT ST_MakeLine( ARRAY[ ST_MakePoint(5,2), ST_MakePoint(4,5), ST_MakePoint(8,10) ]); |
6. ST_MakePolygon. It 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.
one 2 3 4 5 | SELECT ST_MakePolygon( ST_GeomFromText( 'LINESTRING(10 10, 15 10, 20 15, 10 15, 10 10)' )); |
7. ST_GeomFromText. Returns a geometric object based on an expression in WKT format ( Well Known Text ) that defines it.
1 2 3 4 5 | 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.
8. AddGeometryColumn 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.
1 2 3 | SELECT AddGeometryColumn('datos', 'nuevospuntos', 'geom', 25830, 'POINT', 2)SELECT DropGeometryColumn('datos', 'nuevospuntos', 'geom') |
PostGIS functions for spatial analysis
9. ST_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.
1 2 | 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 ).
1 2 3 4 5 6 | SELECT ST_SymDifference( parques.geom, ST_Buffer(papeleras.geom, 500))FROMdatos.poligonos as parques, datos.puntos as papeleras; |
eleven. ST_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 )
1 2 3 4 5 6 | SELECT ST_Intersection( parques.geom, ST_Buffer(papeleras.geom, 500))FROMdatos.poligonos as parques, datos.puntos as papeleras; |
12. ST_Union. Returns a geometry that represents the union of two tables with spatial data.
1 2 | SELECT ST_Union(lineas.geom, poligonos.geom) FROM datos.lineas, datos.poligonos; |
13.ST_Centroid. Returns the centroid calculated from the input geometries.
1 2 | SELECT ST_Centroid(poligonos.geom)FROM datos.poligonos; |
14. ST_Envelope. Returns a geometry that represents the bounding box of the geometries contained in the table.
1 2 | SELECT ST_Envelope(lineas.geom)FROM datos.lineas; |
Boolean functions for relationships between geometries
fifteen. ST_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.
1 2 | SELECT ST_Contains(poligonos.geom, lineas.geom) FROM datos.lineas, datos.poligonos; |
16. ST_Intersects. Returns 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.
1 2 | 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.
1 2 | SELECT ST_Overlaps(poligonos_a.geom, poligonos_b.geom) FROM datos.poligonos_a, datos.poligonos_b; |
18. ST_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.
1 2 | SELECT ST_Touches(poligonos.geom, lineas.geom) FROM datos.lineas, datos.poligonos; |
19. ST_Covers. Returns 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.
1 2 | 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.
23. ST_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); |
22. ST_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:
1 2 | SELECT ST_Translate(geom, -0.05, 0.25) FROM datos.poligonos; |
twenty-one. ST_SRID. Lets 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 ):
1 2 | SELECT ST_Transform(geom, 4326) FROM datos.poligonos; |
twenty. ST_SetSRID. Allows 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:
1 2 | SELECT ST_SRID(geom) FROM datos.poligonos; |
Geometry verification functions
24. GeometryType. Lets 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…
1 2 | SELECT ST_GeometryType(geom) as tipoFROM datos.poligonos; |
25. ST_IsValid. Lets you know if a geometry is valid. Returns a Boolean value for each geometry.
1 2 | SELECT ST_IsValid(geom) as validezFROM 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:
1 2 3 4 | SELECT ST_IsValid(geom) as validez, (select count(ST_IsValid(geom)) as numero)FROM datos.poligonosGROUP BY validez |
Read More
