[postgis-users] area queries on lat/long data

[Alex Mayrhofer]

Nelson Guda wrote:
> Even a function that was able to look up the
> appropriate UTM srid would be enormously helpful.

quick reply: i've done such a function - see below. It does not, however,
consider the "special" cases of UTM eg. norway.

alex

--- returns UTM zone SRID of a given point
--- point is a POINT of  2 or more dimensions in WGS84
--- (means that input SRID must be 4326)
CREATE OR REPLACE FUNCTION find_utm_srid (
	p geometry) RETURNS integer AS $$
	
DECLARE
	out_srid integer;
	base_srid integer;
	
BEGIN
	IF srid(p) != 4326 THEN
		RAISE NOTICE 'find_utm_srid: input geometry has wrong SRID (%)', srid(p);
		RETURN NULL;
	END IF;
	
	IF y(p) < 0 THEN
		--- south hemisphere
		base_srid := 32700;
	ELSE
		--- north hemisphere or on equator
		base_srid := 32600;
	END IF;
	
	out_srid := base_srid + floor((x(p)+186)/6);
	IF (x(p) = 180) THEN
		out_srid := base_srid + 60;
	END IF;
	
	--- TODO: consider special cases around norway etc.
	RETURN out_srid;
END;
$$ LANGUAGE plpgsql;