How to query aggregate max, min, median latitude of polygons in PostGIS?

Question

I am just starting to learn how to use PostGIS (1.5.4) and my SQL skills are essentially those of a competent beginner. Having perused the PostGIS documentation and searched these forums, I'm not finding the answers to what are surely some very simple questions.

Given a single table loaded with polygons from shapefiles, how can I query the latitudinal maximum and minimum and the centroids of single and aggregate rows? I'll note that I have found the ST_Centroid() function, but haven't figured out how to interpret its output (CRS=WGS84).

diciu · Accepted Answer

Maximum latitude for one row:

osm=# select st_ymax(way) from planet_osm_polygon where name = 'Sector 1';
  st_ymax   
------------
 5549655.89
(1 row)

maximum latitude over aggregate:

osm=# select max(st_ymax(way)) from planet_osm_polygon where name in ('Sector 1', 'Sector 2');
    max     
------------
 5549655.89
(1 row)

Centroid in human readable form:

osm=# select st_asewkt(st_centroid(way)) from planet_osm_polygon where name = 'Sector 1';
                      st_asewkt                      
-----------------------------------------------------
 SRID=900913;POINT(2901008.17035358 5541774.5761637)
(1 row)

Centroid reprojected to EPSG 4326 (WGS84) in human readable form:

osm=# select st_asewkt(st_centroid(st_transform(way, 4326))) from planet_osm_polygon where name = 'Sector 1';
                     st_asewkt                      
----------------------------------------------------
 SRID=4326;POINT(26.0602007930189 44.4911898194805)
(1 row)

"Centroid of aggregate rows" I'm not sure how to read.
You can aggregate geometries by using st_union and then compute the centroid of the aggregated geometry:

osm=# select st_astext(st_centroid(st_union(way))) from planet_osm_polygon where name in ('Sector 1', 'Sector 2');
                st_astext                 
------------------------------------------
 POINT(2903674.04223639 5540178.39415804)
(1 row)

Later edit: If median latitude is the average of minimum and maximum latitude, I don't think st_centroid is guaranteed to be equal to it. If you require average latitude, you can just compute (st_ymax(way) + st_ymin(way))/2.

Encomium · Answer

I know this answer is 8 years too late, but the above answers may pre-date PostGIS' implementation of St_GeometricMedian.
Here's a working example that unions every centroid of features in a given table and finds the median:
with b as (select st_centroid(geom) as geom from geo_table)

select 
ST_GeometricMedian(st_union(geom)) as median_centroid_wkb,
ST_AsText(ST_GeometricMedian(st_union(geom))) as median_centroid_wkt,
from b

Mike T · Answer

Take a look at the standard aggregate functions for PostgreSQL. The typical ones are min, avg, max, but there are many others. Median is not supplied by default, but you can add it, then use median(something::numeric) to use it (similar to where you see avg(something) below).

ST_Centroid is good for getting an estimate for the middle, but for the top/bottom coordinates from each polygon, you can use ST_Ymin and ST_Ymax. Here is a typical aggregate:

SELECT min(ST_Ymin(geom)), avg(ST_Y(ST_Centroid(geom))), max(ST_Ymax(geom))
FROM my_table;

You should get one row. If you want to get fancier, and group them by some other classification:

SELECT category, min(ST_Ymin(geom)), avg(ST_Y(ST_Centroid(geom))), max(ST_Ymax(geom))
FROM my_table
GROUP BY category
ORDER BY category;

Should show the min/avg/max latitudes for each category. Aggregate fun!

How to query aggregate max, min, median latitude of polygons in PostGIS?

3 Answers

Add your own answers!

Ask a Question