This advanced PostgreSQL/PostGIS tutorial will provide users with a set of two plpgsql functions: utmzone(geometry) and st_buffer_meters(geometry, double precision). These functions will be helpful for anyone trying to buffer latitude/longitude data using meters or kilometers. These functions have been designed to work with any projection, allowing users to buffer geometry objects using distances specified in meters for all areas of the globe.
ST_Buffer and Projections
The PostGIS ST_Buffer function allows users to buffer any geometry objects by passing in a simple distance parameter. This function can cause some interesting results when using dataset that use lat/long projections. Often buffers on points that occur near the equator tend to look somewhat decent. However, when performing the same query with data located near the poles, buffers around points will appear elongated. The issue arises because the units for global projections are measured in radians and not meters. In order to solve this problem we must transform the projection into one with meters as the measured units. The UTM (WGS 84) projections cut the globe into a set of many different UTM grid zones, each being defined by a unique SRID value. This set of projections can be used to measure distances in meters for all areas of the globe.
UTM Zone Function
The UTM Zone plpgsql function has been designed to find the correct UTM (WGS 84) SRID for a point (in any SRID). Copy and paste the following SQL into a query window and execute the query to create the utmzone(geometry) function in your database.
/* Function: utmzone(geometry)
DROP FUNCTION utmzone(geometry);
Usage: SELECT ST_Transform(the_geom, utmzone(ST_Centroid(the_geom))) FROM sometable; */
CREATE OR REPLACE FUNCTION utmzone(geometry)
RETURNS integer AS
$BODY$
DECLARE
geomgeog geometry;
zone int;
pref int;
BEGIN
geomgeog:= ST_Transform($1,4326);
IF (ST_Y(geomgeog))>0 THEN
pref:=32600;
ELSE
pref:=32700;
END IF;
zone:=floor((ST_X(geomgeog)+180)/6)+1;
RETURN zone+pref;
END;
$BODY$ LANGUAGE 'plpgsql' IMMUTABLE
COST 100;
ST_Buffer_Meters Function
The st_buffer_meters(geometry, double precision) plpgsql function is a custom function which builds off of the utmzone(geometry) function. The goal of this function is to transform your data into a UTM meter unit project, perform our buffer using meter units, and then transform the data back into the original projection. The function logic is explained through the following steps:
- Determine the original SRID value of the geometry object
- Determine the correct UTM SRID value using the centroid of the geometry
- Transform the geometry into the UTM meter unit projection
- Perform the buffer using the number of meters passed through the 2ndparameter
- Transform the geometry back into the original SRID projection
To install the customized st_buffer_meters(geometry, double precision) plpgsql function into your database, copy and paste the following SQL into a query window and execute the query.
/* Function: ST_Buffer_Meters(geometry, double precision)
DROP FUNCTION ST_Buffer_Meters(geometry, double precision);
Usage: SELECT ST_Buffer_Meters(the_geom, num_meters) FROMsometable; */
CREATE OR REPLACE FUNCTION ST_Buffer_Meters(geometry, doubleprecision)
RETURNS geometry AS
$BODY$
DECLARE
orig_srid int;
utm_srid int;
BEGIN
orig_srid:= ST_SRID($1);
utm_srid:= utmzone(ST_Centroid($1));
RETURN ST_transform(ST_Buffer(ST_transform($1, utm_srid), $2), orig_srid);
END;
$BODY$ LANGUAGE 'plpgsql' IMMUTABLE
COST 100;
You now have the two required plpgsql functions installed on your database. These functions are now available to use in any SQL queries you develop. Make sure to use the following usage when executing a st_buffer_meters(geometry, double precision) query.
Simple usage
SELECT ST_Buffer_Meters(the_geom, num_meters) FROM sometable;
Geometry with Unknown SRID -1
SELECT ST_Buffer_Meters(ST_SetSRID(the_geom, srid_value), num_meters) FROM sometable;
Buffer objects by 500 meters
SELECT ST_Buffer_Meters(the_geom, 500) FROM sometable;
Buffer objects using a column of meter values
SELECT ST_Buffer_Meters(the_geom, meter_colname) FROM sometable;
Buffer objects by 10 kilometers
SELECT ST_Buffer_Meters(the_geom, 10000) FROM sometable;
Buffer objects using a column of kilometer values
SELECT ST_Buffer_Meters(the_geom, kilometer_colname * 1000) FROM sometable;
