How to check which geometry is rectangle?

Question

I am doing a assignment in which I created spatial tables for 3 warehouse location. These 3 warehouses has polygon geometry. I want add query to show which gemetry is not rectangle. Below is table I created:

Answer 1

If rectangles are required to have exactly 4 corners, then only polygons where ST_NPoints(geom) > 5 need to be considered.

To test quadrilaterals for rectangularity, compare the lengths of their diagonals. In a rectangle the diagonal lengths are (almost) equal. Since finite numerical precision means the lengths will rarely be exactly equal, a tolerance factor is needed To use a dimension-free tolerance the lengths can be normalized by the total length.

Here's a query computing the "rectangularity ratio" from your data and a perfect rectangle:

WITH data(id, geom) AS (VALUES
  (1, 'POLYGON((144.78116 -37.824855, 144.780843 -37.826916, 144.782018 -37.827019, 144.78232 -37.82496, 144.78116 -37.824855))')
 ,(3, 'POLYGON((153.193238 -27.682795, 153.19302 -27.68375, 153.193568 -27.683843, 153.193795 -27.682894,153.193238 -27.682795))')
 ,(4, 'POLYGON ((153.1931 -27.6828, 153.1937 -27.6828, 153.19370000000004 -27.6838, 153.1931 -27.6838, 153.1931 -27.6828))')
)
SELECT id,
     (ST_Distance( ST_PointN(ST_ExteriorRing(geom), 1), ST_PointN(ST_ExteriorRing(geom), 3))
   -  ST_Distance( ST_PointN(ST_ExteriorRing(geom), 2), ST_PointN(ST_ExteriorRing(geom), 4)))
   / (ST_Distance( ST_PointN(ST_ExteriorRing(geom), 1), ST_PointN(ST_ExteriorRing(geom), 3))
   +  ST_Distance( ST_PointN(ST_ExteriorRing(geom), 2), ST_PointN(ST_ExteriorRing(geom), 4))) AS rect_ratio
FROM data;

which gives the result:

 id |      rect_ratio      
----+-----------------------
  1 | -0.025764420345746754
  3 |  -0.02484385683541937
  4 | 6.269495874867416e-12

Answer 2

If you are using projected data, you can check if the minimum oriented bounding box of the geometry is the same as the original geometry. Since a bounding box is a rectangle, you can deduce that the input is also a rectangle.

with src (id, geom) as ( values 
   (1,'polygon((0 0, 0 1, 1 1,1 0, 0 0))'::geometry),
   (2,'polygon((0 0, 0 1, 1 2,1 0, 0 0))'::geometry))
select id, ST_Equals(ST_OrientedEnvelope(geom),geom) is_rectangle
from src;

 id | is_rectangle
----+--------------
  1 | t
  2 | f
(2 rows)

Answer 3

The Cook County Assessor's Office has a set of SQL statements that, together, can be used to determine the regularity of a shape (in their case, it's applied to 1.8 million property parcels). https://github.com/ccao-data/data-architecture/pull/522

The six attributes they ascribe to each parcel were taken from a paper called "See how the land lies: Land valuation using spatial models" by Jacqueline Seufert, Geert Goeyvaerts & Sven Damen, published January 2023.

Read that GitHub pull request to see the SQL statements to ascribe these six attributes:

1. The standard deviation of the interior angles of the parcel polygon
2. The standard deviation of the parcel polygon edge length
3. The standard deviation of the parcel vertices' distance to the parcel centroid
4. The total number of vertices in the parcel polygon
5. The ratio of the length of the sides of the minimum spanning rotated rectangle
6. The ratio of the areas of the minimum spanning rotated rectangle and the parcel polygon itself