Lat/Lon to UTM conversion across multiple zones

Question

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?

Answer 1

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:

 CASE...
    WHEN...
    THEN...
    WHEN.. 
    THEN...
    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.

Answer 2

Some simple utility functions:

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

create or replace function utm(geometry)
returns geometry
as
$$
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;