2

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 '17 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. – Chris Schulz Nov 28 '17 at 9:51
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.

1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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