1

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
)
  • Creating a buffer without using ST_union results in overlapping polygons which should be dissolved, as described in the second question I linked to. – srha Oct 31 '17 at 23:13
3

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,
  1. 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.

  • 1
    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/…) – srha Oct 31 '17 at 23:02
  • 1
    @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). – tinlyx Nov 1 '17 at 2:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.