Stephen Mather: ST_Buffer for those who want really want all buffer features in geogr

[المشرف العام]

My previous post on improving buffering geography in PostGIS*has three disadvantages:

1. It doesn’t do this at a low enough level to automatically use the best available local coordinate system. This leaves it to the user to choose the best available local coordinate system. I should fix this (but I probably won’t just now…).
2. It uses a different function name than you otherwise use for buffering. Can we use the same name as ST_Buffer?
3. Finally, it won’t let you use all the other parameters that ST_Buffer exposes through the use of buffer style parameters.

First, let’s address point 2. With PostgreSQL functions, it is possible to “overload” a function. This means that the same function name, but with a different set of inputs is effectively a different function but with the same name. This is because function + inputs = signature. Since our inputs for our new function are different in number and type from the existing ST_Buffer functions, we can add our function using the same name. The disadvantage is that we will likely lose and have to re-create these functions when we dump and reload the database. (For the advanced user, put it in an alternate schema, and add that to your schema search path so it’s readily available when you want to use it, but you don’t have to go out of your way to make sure that it dumps and reloads.)

CREATE OR REPLACE FUNCTION ST_Buffer(g1 geography, radius_of_buffer float, num_seg_quarter_circle integer, local_coord integer)RETURNS geometry AS$BODY$ WITH transformed AS ( SELECT ST_Transform(g1::geometry, local_coord) AS geom),buffered AS ( SELECT ST_Buffer(geom, radius_of_buffer, num_seg_quarter_circle) AS geom FROM transformed),transformed_4326 AS ( SELECT ST_Transform(geom, 4326) AS geom FROM buffered)SELECT * FROM transformed_4326 $BODY$LANGUAGE sql VOLATILE;We can also address point 3. Let’s perform a similar overload for buffer style parameters:

CREATE OR REPLACE FUNCTION ST_Buffer(g1 geography, radius_of_buffer float, buffer_style_parameters text, local_coord integer)RETURNS geometry AS$BODY$ WITH transformed AS ( SELECT ST_Transform(g1::geometry, local_coord) AS geom),buffered AS ( SELECT ST_Buffer(geom, radius_of_buffer, buffer_style_parameters) AS geom FROM transformed),transformed_4326 AS ( SELECT ST_Transform(geom, 4326) AS geom FROM buffered)SELECT * FROM transformed_4326 $BODY$LANGUAGE sql VOLATILE;Finally, we can use our special buffer style parameters with geography:

SELECT 0 AS id, ST_Buffer( ST_GeomFromText( 'LINESTRING(41 -81,41 -82,42 -82)' ), 500000, 'join=mitre mitre_limit=5.0', 3734);*

Example buffer of line with join=mitre mitre_limit=5.0.


This is a hack. Daniel’s note in the previous post still stands — it would be better to do this at a lower level within geography, rather than asking the user to know and employ the best possible local coordinate system.

أكثر...