How do I create a dissolved buffer using PostGIS with EPSG 4326?

Question

I'm trying to buffer a polyline (WGS84/EPSG 4326) by 50km, and then dissolve all the overlaps in the buffer. So far I've tried a combination of:

1. casting the EPSG 4326 geometry as geography (to get accurate buffer) then
2. creating a dissolved buffer using ST_union(ST_buffer(geo.my_polyline)).

However, I'm getting a syntax error around my CAST close-parenthesis: ERROR: syntax error at or near ")"

Here is my code:

create table geo.fifty_km_buffer as (
select id, ST_union(ST_buffer(
 CAST(geo.polyline.geom) as geography, 
 50000
 )) as geom
from geo.polyline
)

• srha: Creating a buffer without using ST_union results in overlapping polygons which should be dissolved, as described in the second question I linked to.

Answer 1

There are a number issues in your SQL

create table geo.fifty_km_buffer as (
select id, ST_union(ST_buffer(
 CAST(geo.polyline.geom) as geography, 
 50000
 )) as geom
from geo.polyline
)

1. What gave you the syntax error is the usage of CAST. From the documentation, it should be

   CAST(geo.polyline.geom as geography),
   

instead of

 CAST(geo.polyline.geom) as geography,

2. ST_Union on geography type isn't supported yet. So you need to convert the geography back to the geometry type. So the query should be something like:

   create table ex.fifty_km_buffer as (
   select ST_union(CAST(ST_buffer(
    CAST(geo.polyline.geom as geography), 
    50000
    )
    as geometry)
    ) as geom
   from geo.polyline
   );
   

Also note that id field cannot appear here because of the aggregate function ST_Union.

• srha: This is great, thank you. Given that I'm going from geography back to geometry, would this slow down my SQL quite a bit? Would it just be faster to reproject the original data into a more buffer-friendly CRS? (I'm thinking of this: gis.stackexchange.com/questions/207171/…)
• tinlyx: @srha Sure. I think it may be faster, but if you convert to ANY projected CRS, the buffer distance (5km) is going to be inexact due to map distortions (which may be negligible sometimes).