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

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?

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)
``````

• Thank you for your response. But what I wanted was extreme points(x,y) in polygon. Added an explanatory image. I reviewed the document referring to your answer, the function `ST_OrientedEnvelope` may be effective for me. but some problem happend. `function st_orientedenvelope(geometry) does not exist`
– y y
May 20, 2019 at 10:04
• from the st_orientated page "Availability: 2.5.0" so you need a recent version May 20, 2019 at 10:06
• you could look for the points in the original polygon that touch the bbox. May 20, 2019 at 10:07
• I checked my version, that is 2.4. I'll try to update it. thanks too.
– y y
May 20, 2019 at 10:09
• "points in the original polygon that touch the bbox" This is my purpose! so I Re-investigate bbox.
– y y
May 20, 2019 at 10:15

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."

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;
``````

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

• `ST_X` and `ST_Y` won't return the actual vertices of a polygon, just the centroid (which will be less than max and more than min). And using `Max` and `Min` functions would be far more efficient than sorting the table. They could be run on both the X and Y dimensions as well. Aug 24 at 17:44