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

3 Answers 3


Maximum latitude for one row:

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

maximum latitude over aggregate:

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

Centroid in human readable form:

osm=# select st_asewkt(st_centroid(way)) from planet_osm_polygon where name = 'Sector 1';
 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';
 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');
 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, 2012 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, 2012 at 4:44

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)

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

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.