| This page contains different code snippets or configurations that does not fit in the source repository.
PLSQL functions---------------- getGeomFromCellId
CREATE OR REPLACE FUNCTION getGeomFromCellId(INT4)
RETURNS TEXT AS $BODY$
DECLARE cellId alias for $1;
DECLARE geomText TEXT;
DECLARE latitude INT4;
DECLARE longitude INT4;
DECLARE minLongitude VARCHAR;
DECLARE minLatitude VARCHAR;
DECLARE maxLongitude VARCHAR;
DECLARE maxLatitude VARCHAR;
BEGIN
longitude=(cellId%360)-180;
latitude= -90;
if cellId>0 then
latitude = floor(cellId/360) -90;
end if;
minLongitude = CAST(longitude as VARCHAR);
minLatitude = CAST(latitude as VARCHAR);
maxLongitude = CAST(longitude+1 as VARCHAR);
maxLatitude = CAST(latitude+1 as VARCHAR);
geomText ='POLYGON((' || minLongitude || ' ' || maxLatitude || ',' || maxLongitude || ' ' || maxLatitude || ',' || maxLongitude || ' ' || minLatitude || ',' || minLongitude || ' ' || minLatitude || ',' || minLongitude || ' ' || maxLatitude || '))'; RETURN geomText; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ------------------------- Example use: update gbif_density set the_geom=GeomFromText(getGeomFromCellID(cell_id),4326) where the_geom is null;
toCellId
CREATE OR REPLACE FUNCTION toCellId(float8, float8) RETURNS INT4 AS $BODY$ DECLARE latitude alias for $1; DECLARE longitude alias for $2; DECLARE cellId INT4; DECLARE la INT4; DECLARE lo INT4; BEGIN if latitude <-90 or latitude > 90 or longitude <-180 or longitude > 180 then RETURN null; end if; la = floor(latitude+90); lo = floor(longitude+180); cellId = (la*360) + lo;
RETURN cellId; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ----------------- Example use: select toCellId(89.5,179.5);
creategraticule Creates a graticule table of 1 degree with cell_id and corresponding Geometry.
CREATE OR REPLACE FUNCTION creategraticule() RETURNS INT4 AS $BODY$ DECLARE latitude FLOAT8; DECLARE longitude FLOAT8; DECLARE counter INT4; BEGIN counter = 0; CREATE TABLE "public"."graticule" (cell_id INT4 PRIMARY KEY); SELECT AddGeometryColumn('public','graticule','the_geom','4326','POLYGON',2); CREATE INDEX "graticule_the_geom_gist" ON "public"."graticule" using gist ("the_geom" gist_geometry_ops); LOOP INSERT INTO "public"."graticule"("cell_id","the_geom") VALUES (counter, GeomFromText(getGeomFromCellID(counter),4326));
counter = counter +1; if counter = 64800 then return; end if; END LOOP;
RETURN; END; $BODY$ LANGUAGE 'plpgsql';
|