3

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

enter image description here

I want to know following points of geometry(polygon)

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

enter image description here

( 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?

4 Answers 4

3

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

enter image description here

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)

enter image description here

6
  • 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
    – Ian Turton
    May 20, 2019 at 10:06
  • you could look for the points in the original polygon that touch the bbox.
    – Ian Turton
    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
1

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

0

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

1
  • 1
    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.
    – Vince
    Aug 24 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.