Additional User-contributed Functions

This is an area to put utility functions or wrappers around PostGIS

• Find UTM (WGS84) SRID for a point (in any SRID)

 -- Function: utmzone(geometry)
 -- DROP FUNCTION utmzone(geometry);

 CREATE OR REPLACE FUNCTION utmzone(geometry)
   RETURNS integer AS
 $BODY$
 DECLARE
     geomgeog geometry;
     zone int;
     pref int;

 BEGIN
     geomgeog:=transform($1,4326);

     IF (y(geomgeog))>0 THEN
 	pref:=32600;
     ELSE
	pref:=32700;
     END IF;

     zone:=floor((x(geomgeog)+180)/6)+1;

     RETURN zone+pref;
 END;
 $BODY$ LANGUAGE 'plpgsql' IMMUTABLE
   COST 100;

• Find Length of Day for a given location and date

   CREATE OR REPLACE FUNCTION daylength(geometry, date)
   RETURNS double precision AS 'select 24*(ACOS(1-(1-TAN(radians(Y(transform($1,4326))))*TAN(.409088 * COS(.0172024 * extract(doy from $2)))))/PI());'
   LANGUAGE 'sql';
  

• Rotate Geometry around a point(geom,x,y)

  CREATE OR REPLACE FUNCTION RotateAtPoint(geometry, double precision, double precision, double precision)
   RETURNS geometry AS 'SELECT translate( rotate( translate( $1, -1*$2, -1*$3), $4), $2, $3)'
     LANGUAGE 'sql';
  

• Create Ellipse (x,y,rx,ry,rotation,#of segments in ¼ of ellipse)

  CREATE OR REPLACE FUNCTION Ellipse(double precision, double precision, double precision, double precision, double precision, integer)
   RETURNS geometry AS
   'SELECT translate( rotate( scale( buffer(makepoint(0,0), 0.5, $6), $3, $4), $5), $1, $2)'
   LANGUAGE 'sql';
  

• Return a multilinestring consisting of the interior and exterior rings of a polygon/multipolygon

This already exists in PostGIS: see ST_Boundary(geometry)

Example use: - where the_geom is a multipolygon
 SELECT fnpoly_to_rings(the_geom)
 FROM sometable

 CREATE OR REPLACE FUNCTION fnpoly_to_rings(geometry)
 RETURNS geometry AS
 $$
 SELECT collect(the_line)  as multiline
 FROM (SELECT ST_ExteriorRing(the_poly) as the_line
 	FROM (SELECT ST_GeometryN($1, g.n) As the_poly
         FROM
            generate_series(1, ST_NumGeometries($1)) As g(n) ) As polys
 	UNION ALL
 	SELECT ST_InteriorRingN(the_poly,
 	generate_series(1, ST_NumInteriorRings(the_poly))) as the_line
   FROM (SELECT ST_GeometryN($1, g.n) As the_poly
 		FROM generate_series(1, ST_NumGeometries($1)) As g(n) ) As polys
	) As all_lines
 $$
  LANGUAGE 'sql' IMMUTABLE;
COMMENT ON FUNCTION fnpoly_to_rings(geometry) IS 'Takes as argument a multipolygon or polygon and returns a multilinestring consisting of the interior and exterior rings of the polygon/multipolygon';

  CREATE OR REPLACE FUNCTION upgis_!lineshift(centerline geometry, dist double precision)
  RETURNS geometry AS
  $$
  DECLARE
  	delx float;
  	dely float;
  	x0 float;
  	y0 float;
  	x1 float;
  	y1 float;
  	az float;
  	dir integer;
  	line geometry;
  BEGIN
  	az := ST_Azimuth (ST_!StartPoint(centerline), ST_!EndPoint(centerline));
  	dir := CASE WHEN az < pi() THEN -1 ELSE 1 END;
  	delx := ABS(COS(az)) * dist * dir;
  	dely := ABS(SIN(az)) * dist * dir;

  	IF az > pi()/2 AND az < pi() OR az > 3 * pi()/2 THEN
  		line := ST_Translate(centerline, delx, dely) ;
  	ELSE
  		line := ST_Translate(centerline, -delx, dely);
  	END IF;

  	RETURN line;
  END;
  $$
  LANGUAGE 'plpgsql' IMMUTABLE;
  COMMENT ON FUNCTION upgis_lineshift(geometry, double precision) IS 'Takes a 2D line string and shifts it dist units along
 the perpendicular defined by the straight line between the start and end point
 Convention: (right is positive and left is negative.  right being defined as to right of observer
 standing at start point and looking down the end point)';

}}}