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

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

-

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

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

-
Thanks! The function ST_AsEWKT() to return "as well known text" was just what I needed to make sense of the centroid data. What I meant by an aggregate centroid is a point that may fall outside of any of the polygons being queried, the first moment or center of balance of the aggregated polygons on the spheroid's surface. – Gregory May 2 '12 at 4:40

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!

-
Very helpful. I suspect I'll be using the ST_Y() and ST_Ymin/max functions a lot in the coming weeks. Thanks. – Gregory May 2 '12 at 4:44