I have a data set that represents the extents of 1:25000 map sheets over a whole state. Its stored in PostGIS in WGS84 lat/lon. Id like to retrieve the MinX, MaxX, MinY and MaxY for the bounding box for each record. Easy... but... Id like to display it in UTM. Problem is that this state crosses multiple UTM zones. The DB should be smart enough to calculate the central meridian and give me a full UTM value for each extent. I can do this via code or in excel (shudder) but Id rather do this in the database so I can create a view. Any ideas/examples I can follow?

  • Do you plan to display the entire dataset at once? If so, you'll have to pick one UTM zone or another projected CRS. A display can only be in one CRS at a time, although data could be reprojected to that CRS from multiple other CRS.
    – mkennedy
    Nov 28, 2017 at 4:41
  • Thanks for the reply. Its just data im wanting to return, not visualisation. I just need MAP_NAME, UTM_ZONE, XMAX, XMIN, YMAX, YMIN in utm not lat/lon. I think the answer below works if I calculate the central meridian and do sub select transforms for each zone. Nov 28, 2017 at 9:51

2 Answers 2


Forget Excel...and associated shudders. Write your CREATE VIEW view AS SELECT... with a utm_zone column populated by conditional logic. Assuming a state covering two zones:

    ELSE null
END AS utm_zone

Throw a transform in the first bit and you're good to go. Don't forget to check resulting null values for exceptions.


Some simple utility functions:

create or replace function utmzone(geometry) 
returns integer
select int4(ceil((st_x($1) + 180.0)/6));
language 'sql'
immutable strict;

create or replace function utm(geometry)
returns geometry
select st_transform($1, 26900 + utmzone($1));
language 'sql'
stable strict;

They only work with lat/lon points, but hey, you have a constrained problem. I'm also assuming you're working north of the equator, hence the use of "26900" as the base UTM SRID to add to.

select utmzone(st_setsrid(st_makepoint(-125,3),4326));
select st_astext(utm(st_setsrid(st_makepoint(-174,34),4326)));

And putting it all together for your input table:

with corners as (
  select st_setsrid(st_makepoint(st_xmin(geom), st_ymin(geom)), 4326) as ll,
         st_setsrid(st_makepoint(st_xmax(geom), st_ymax(geom)), 4326) as ur
  from mapsheets
utmcorners as (
  select utmzone(ll) as ll_zone,
         utmzone(ur) as ur_zone,
         utm(ll) as ll,
         utm(ur) as ur
  from corners
select utmzone(ll) as zone,
       st_xmin(ll) as xmin,
       st_ymin(ll) as ymin,
       st_xmax(ur) as xmax,
       st_ymax(ur) as ymax
from utmcorners;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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