Skip to content

Instantly share code, notes, and snippets.

@Phyks
Created August 27, 2018 17:30
Show Gist options
  • Save Phyks/9938cbc561c07a33be21ee74e27b687a to your computer and use it in GitHub Desktop.
Save Phyks/9938cbc561c07a33be21ee74e27b687a to your computer and use it in GitHub Desktop.
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;
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment