Computation methods

Polygons have a few more useful methods. They can be applied to a collection of polygons (multi-polygon) as well.

Besides area you can calculate as well perimeter of surfaces. The perimeter of a polygon includes the length of all rings (exterior and interior).

SELECT "SHAPE".ST_asWKT(), "SHAPE".ST_Area(), "SHAPE".ST_Perimeter()
FROM "TESTSGEO"."SPATIALSHAPES"
WHERE "SHAPE".ST_GeometryType() in ('ST_Polygon');

As mentioned, you can calculate area and perimeter of multi-polygons as well. Below is the example of computations of the multi-polygon, which is a result of union aggregation of individual polygons in the table.

SELECT "AGGRSHAPE".ST_asWKT(), "AGGRSHAPE".ST_Area(), "AGGRSHAPE".ST_Perimeter()
FROM (
SELECT ST_UnionAggr("SHAPE") as "AGGRSHAPE"
FROM "TESTSGEO"."SPATIALSHAPES"
WHERE "SHAPE".ST_GeometryType() in ('ST_Polygon')
)

The method ST_Centroid() returns the point that is the mathematical centroid of a polygon or multi-polygon.

SELECT "SHAPE".ST_asWKT(), "SHAPE".ST_Centroid().ST_asWKT()
FROM "TESTSGEO"."SPATIALSHAPES"
WHERE "SHAPE".ST_GeometryType() in ('ST_Polygon');

And another example where all individual polygons are combined into one multi-polygon.

SELECT "AGGRSHAPE".ST_asWKT(), "AGGRSHAPE".ST_Centroid().ST_asWKT()
FROM (
SELECT ST_UnionAggr("SHAPE") as "AGGRSHAPE"
FROM "TESTSGEO"."SPATIALSHAPES"
WHERE "SHAPE".ST_GeometryType() in ('ST_Polygon')
);

Centroid of a polygon is not always at the surface of that polygon. Consider the following example.

SELECT "PolygonWithInnerRing".st_asSVG(),
"PolygonWithInnerRing".st_centroid().st_asSVG()
FROM (
SELECT NEW ST_Polygon('Polygon ((-5 -5, 5 -5, 0 5, -5 -5), (-2 -2, -2 0, 2 0, 2 -2, -2 -2))') as "PolygonWithInnerRing"
FROM dummy
);

Slightly modified SVG for better visualization, like the one below:

<svg xmlns="http://www.w3.org/2000/svg" version="1.1" viewBox="-5.01 -5.01 10.02 10.02">
<path fill="yellow" stroke="black" stroke-width="0.1%" d="
M -5,5 l 10,0 -5,-10 Z
M -2,2 l 0,-2 4,0 0,2 Z
"/>
<rect width="1%" height="1%" stroke="red" stroke-width="1%" x="0" y="1.79365"/>
</svg>

shows that the centroid (the red dot) is located inside of the inner ring, and therefore not f the surface of the polygon.

In some cases you will need to have a point (like some sort of marker on the map) that lays somewhere on the surface of a given polygon. That’s the purpose of a method ST_PointOnSurface().

Update the example above to include the calculation of the point-on-surface.

SELECT "PolygonWithInnerRing".ST_asSVG(),
"PolygonWithInnerRing".ST_Centroid().st_asSVG(),
"PolygonWithInnerRing".ST_PointOnSurface().st_asSVG()
FROM (
SELECT NEW ST_Polygon('Polygon ((-5 -5, 5 -5, 0 5, -5 -5), (-2 -2, -2 0, 2 0, 2 -2, -2 -2))') as "PolygonWithInnerRing"
FROM dummy
);

Again slightly modify styling of SVG objects, to have the centroid in red and the point-on-surface in blue.

<svg xmlns="http://www.w3.org/2000/svg" version="1.1" viewBox="-5.01 -5.01 10.02 10.02">
<path fill="yellow" stroke="black" stroke-width="0.1%" d="
M -5,5 l 10,0 -5,-10 Z
M -2,2 l 0,-2 4,0 0,2 Z
"/>
<rect width="1%" height="1%" stroke="red" stroke-width="1%" x="0" y="1.79365"/>
<rect width="1%" height="1%" stroke="blue" stroke-width="1%"  x="-2.25" y="0"/>
</svg>

You can see on the visualization that the blue dot is indeed located on the yellow surface.

The ST_Distance method computes the shortest distance between two geometries.

This method can only be used with geometries in a round-Earth spatial reference system for point to point calculations.

The statement below will return five geometries from the table SPATIALSHAPES that are closest to the point (0,0).

select top 5
"SHAPEID", "SHAPE".ST_asWKT(),
"SHAPE".ST_Distance(new st_point('POINT (0 0)'))
from "TESTSGEO"."SPATIALSHAPES"
where "SHAPE".st_isEmpty() = 0
order by 3 asc;