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
)

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.