[GIS] How to query aggregate max, min, median latitude of polygons in PostGIS

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

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

Related Question