Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
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. –  pyrogerg 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!

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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