Code from https://github.com/osm-fr/osmose-backend/issues/326#issuecomment-415979703

basic

DROP TABLE IF EXISTS z30;
-- Get all zone:maxspeed ways, with geometry in Lambert93
CREATE TEMP TABLE z30 AS
SELECT
  id,
  tags,
  ST_Transform(linestring, 2154) AS linestring
FROM
  ways
WHERE
  tags != ''::hstore AND
  tags?'zone:maxspeed' AND
  ways.linestring && st_setsrid(st_makeline(st_makepoint(2.2057,48.9055), st_makepoint(2.409, 48.827)), 4326)
;

DROP TABLE IF EXISTS a30;
CREATE TEMP TABLE a30 AS
SELECT
  1 AS a,
  ST_Transform(linestring, 4326) AS linestring
FROM (
  SELECT
    ST_Buffer(
      (ST_Dump(
        ST_Union(ST_Buffer(linestring, 60))
       )).geom,
      -61
    ) AS linestring
  FROM
    z30
) AS t
WHERE
  ST_IsValid(linestring) AND
  NOT ST_IsEmpty(linestring)
;

COPY (SELECT row_to_json(fc)
FROM (
    SELECT 'FeatureCollection' AS type, array_to_json(array_agg(f)) AS features
    FROM (
        SELECT
            'Feature' AS type,
            ST_AsGeoJSON(a30.linestring)::json AS geometry
        FROM a30
    ) AS f
)  AS fc) TO STDOUT;

improved

-- Cluster Zone30 with buffer
DROP TABLE IF EXISTS a0_30;
CREATE TABLE a0_30 AS
SELECT
  (ST_Dump(
    ST_Union(ST_Buffer(linestring_proj, 60))
  )).path[1] AS cid,
  (ST_Dump(
    ST_Union(ST_Buffer(linestring_proj, 60))
  )).geom AS geom
FROM
  highways
WHERE
  tags?'zone:maxspeed' AND
  highways.tags->'zone:maxspeed' LIKE '%:30'
;

DROP TABLE IF EXISTS a1_30;
CREATE TABLE a1_30 AS
SELECT
  cid,
  (ST_DumpRings(geom)).path[1] AS path,
  (ST_DumpRings(geom)).geom
FROM
  a0_30
;

-- Remove small inner hole
DROP TABLE IF EXISTS a2_30;
CREATE TABLE a2_30 AS
SELECT
  cid,
  path,
  ST_ExteriorRing(geom) AS geom
FROM
  a1_30
WHERE
  path = 0 OR
  ST_Area(geom) > 100 * 100
;

-- Rebuild cluster and shrink the buffer back
DROP TABLE IF EXISTS a3_30;
CREATE TABLE a3_30 AS
SELECT
  ST_Buffer(CASE
    WHEN t.inners IS NULL THEN ST_MakePolygon(a.geom)
    ELSE ST_MakePolygon(a.geom, t.inners)
  END, -61) AS geom
FROM
  (SELECT cid, geom FROM a2_30 WHERE path = 0) AS a
  LEFT JOIN
    (SELECT cid, ST_Accum(geom) AS inners FROM a2_30 WHERE path > 0 GROUP BY cid) AS t ON
    a.cid = t.cid
;

COPY (SELECT row_to_json(fc)
FROM (
    SELECT 'FeatureCollection' AS type, array_to_json(array_agg(f)) AS features
    FROM (
        SELECT
            'Feature' AS type,
            ST_AsGeoJSON(ST_Transform(a3_30.geom, 4326))::json AS geometry
        FROM a3_30
        WHERE NOT ST_IsEmpty(a3_30.geom)
    ) AS f
)  AS fc) TO STDOUT;