Changes between Initial Version and Version 1 of UsersWikiplpgsqlfunctions

Timestamp:

Apr 13, 2009, 1:57:51 PM (15 years ago)

Author:

pierre

Comment:

—

UsersWikiplpgsqlfunctions

@@ +1 @@
+==  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 1/4 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)';
+}}}
+