How to calculate extreme point (most north or east etc) of geography(polygon) type in PostGIS

Question

I have a table which include geography(polygon) type.

CREATE TABLE place (
  name varchar(100),
  geom GEOMETRY(POLYGON, 4326)
);
insert into place
SELECT 'place1', ST_GeomFromText('POLYGON((-71.1776585052917
 42.3902909739571,-71.1776820268866 42.3903701743239,
-71.1776063012595 42.3903825660754,-71.1775826583081 
42.3903033653531,-71.1776585052917 42.3902909739571))',4326);

I want to know following points of geometry(polygon)

• most north point
• most south point
• most east point
• most west point

( This seems to be commonly called "extreme point")

For example, in the above image, the coordinates (x, y) of the four corners of the rectangle are the information I want to know. (But my applications data is not true rectangle.that has more corner.)

I checked document, I couldn't find suitable function.

https://postgis.net/docs/reference.html

How can I get or calculate extreme points using SQL?

Answer 1

You want ST_ENVELOPE as in

SELECT 'place1', st_asText(ST_Envelope(ST_GeomFromText('POLYGON((-71.1776585052917
42.3902909739571,-71.1776820268866 42.3903701743239,
-71.1776063012595 42.3903825660754,-71.1775826583081 
42.3903033653531,-71.1776585052917 42.3902909739571))',4326)));

Gives

POLYGON((-71.1776820268866 42.3902909739571,-71.1776820268866 42.3903825660754,-71.1775826583081 42.3903825660754,-71.1775826583081 42.3902909739571,-71.1776820268866 42.3902909739571))

UPDATE For those points you could use ST_OrientedEnvelope.

If you are not using Postgis 2.5 and don't have access to ST_ORIENTATEDENVELOPE you could get the points by looking for the intersection of the outer ring of the polygon and the envelope:

SELECT 'place1', st_asText(st_intersection(ST_ExteriorRing(ST_Envelope(ST_GeomFromText('POLYGON((-71.1776585052917
 42.3902909739571,-71.1776820268866 42.3903701743239,
-71.1776063012595 42.3903825660754,-71.1775826583081 
42.3903033653531,-71.1776585052917 42.3902909739571))',4326))),ST_ExteriorRing(ST_GeomFromText('POLYGON((-71.1776585052917
 42.3902909739571,-71.1776820268866 42.3903701743239,
-71.1776063012595 42.3903825660754,-71.1775826583081 
42.3903033653531,-71.1776585052917 42.3902909739571))',4326))));

gives

MULTIPOINT(-71.1776820268866 42.3903701743239,-71.1776585052917 
42.3902909739571,-71.1776063012595 42.3903825660754,-71.1775826583081 
42.3903033653531)

Answer 2

Isn't st_ymax, st_ymin, st_xmax, st_xmin for this need? https://postgis.net/docs/ST_YMax.html "Returns the Y maxima of a 2D or 3D bounding box or a geometry."

Answer 3

Use ST_DumpPoints to get the individual points, and then sort by the desired ordinate and ordering to get the extreme X or Y coordinate:

WITH data(geom) AS (VALUES
  ('POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239, -71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))'::geometry)
)
SELECT ST_AsText(t.geom) AS extreme_N
  FROM (SELECT (ST_DumpPoints(ST_RemoveRepeatedPoints(ST_Points(geom)))).geom 
          FROM data) AS t
  ORDER BY ST_Y(geom) DESC
  LIMIT 1;

To get all extreme points for multiple geometries in a single query use the RANK window function:

WITH data(id, geom) AS (VALUES
   (1, 'POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 42.3903701743239, -71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917 42.3902909739571))'::geometry)
  ,(2, 'POLYGON ((-71.1775 42.3902, -71.1773 42.3908, -71.1769 42.3905, -71.177 42.39, -71.1775 42.3902))'::geometry)
),
pts AS (SELECT id, (ST_DumpPoints(ST_RemoveRepeatedPoints(ST_Points(geom)))).geom FROM data),
ranked AS (SELECT id, ST_AsText(geom), 
  RANK() OVER (PARTITION BY id ORDER BY ST_X(geom) DESC) AS rank_max_x,
  RANK() OVER (PARTITION BY id ORDER BY ST_X(geom) ASC)  AS rank_min_x,
  RANK() OVER (PARTITION BY id ORDER BY ST_Y(geom) DESC) AS rank_max_y,
  RANK() OVER (PARTITION BY id ORDER BY ST_Y(geom) ASC)  AS rank_min_y
  FROM pts
)
SELECT * FROM ranked
  WHERE rank_max_x = 1 OR rank_min_x = 1 OR rank_max_y = 1 OR rank_min_y = 1;

Answer 4

Maybe something simple:

SELECT ST_X(geometry) FROM table ORDER BY by ST_X(geometry) LIMIT 1 - West

SELECT ST_Y(geometry) FROM table ORDER BY by ST_Y(geometry) LIMIT 1 - South etc