kopia lustrzana https://github.com/openmaptiles/openmaptiles
106 wiersze
3.0 KiB
SQL
106 wiersze
3.0 KiB
SQL
DROP TABLE IF EXISTS osm_border_linestring_adm CASCADE;
|
|
|
|
-- etldoc: osm_border_linestring -> osm_border_linestring_adm
|
|
-- etldoc: osm_border_disp_linestring -> osm_border_linestring_adm
|
|
-- etldoc: ne_10m_admin_0_countries -> osm_border_linestring_adm
|
|
CREATE TABLE IF NOT EXISTS osm_border_linestring_adm AS (
|
|
WITH
|
|
-- Prepare lines from osm to be merged
|
|
multiline AS (
|
|
SELECT osm_id,
|
|
ST_Node(ST_Collect(geometry)) AS geometry,
|
|
BOOL_OR(maritime) AS maritime,
|
|
FALSE AS disputed
|
|
FROM osm_border_linestring
|
|
WHERE admin_level = 2 AND ST_Dimension(geometry) = 1
|
|
AND osm_id NOT IN (SELECT DISTINCT osm_id FROM osm_border_disp_linestring)
|
|
GROUP BY osm_id
|
|
),
|
|
|
|
mergedline AS (
|
|
SELECT osm_id,
|
|
(ST_Dump(ST_LineMerge(geometry))).geom AS geometry,
|
|
maritime,
|
|
disputed
|
|
FROM multiline
|
|
),
|
|
-- Create polygons from all boundaries to preserve real shape of country
|
|
polyg AS (
|
|
SELECT (ST_Dump(
|
|
ST_Polygonize(geometry))).geom AS geometry
|
|
FROM (
|
|
SELECT (ST_Dump(
|
|
ST_LineMerge(geometry))).geom AS geometry
|
|
FROM (SELECT ST_Node(
|
|
ST_Collect(geometry)) AS geometry
|
|
FROM osm_border_linestring
|
|
WHERE admin_level = 2 AND ST_Dimension(geometry) = 1
|
|
) nodes
|
|
) linemerge
|
|
),
|
|
|
|
centroids AS (
|
|
SELECT polyg.geometry,
|
|
ne.adm0_a3
|
|
FROM polyg,
|
|
ne_10m_admin_0_countries AS ne
|
|
WHERE ST_Within(
|
|
ST_PointOnSurface(polyg.geometry), ne.geometry)
|
|
),
|
|
|
|
country_osm_polyg AS (
|
|
SELECT country.adm0_a3,
|
|
border.geometry
|
|
FROM polyg border,
|
|
centroids country
|
|
WHERE ST_Within(country.geometry, border.geometry)
|
|
),
|
|
|
|
rights AS (
|
|
SELECT osm_id,
|
|
adm0_r,
|
|
geometry,
|
|
maritime,
|
|
disputed
|
|
FROM (
|
|
SELECT a.osm_id AS osm_id,
|
|
b.adm0_a3 AS adm0_r,
|
|
a.geometry,
|
|
a.maritime,
|
|
a.disputed
|
|
FROM mergedline AS a
|
|
LEFT JOIN country_osm_polyg AS b
|
|
-- Create short line on the right of the boundary (mergedline) and find state where line lies.
|
|
ON ST_Within(
|
|
ST_OffsetCurve(
|
|
(ST_LineSubString(a.geometry, 0.3,0.3004)), 70, 'quad_segs=4 join=mitre'), b.geometry)
|
|
) line_rights
|
|
)
|
|
|
|
SELECT osm_id,
|
|
adm0_l,
|
|
adm0_r,
|
|
geometry,
|
|
maritime,
|
|
2::integer AS admin_level,
|
|
disputed
|
|
FROM (
|
|
SELECT r.osm_id AS osm_id,
|
|
b.adm0_a3 AS adm0_l,
|
|
r.adm0_r AS adm0_r,
|
|
r.geometry,
|
|
r.maritime,
|
|
r.disputed
|
|
FROM rights AS r
|
|
LEFT JOIN country_osm_polyg AS b
|
|
-- Create short line on the left of the boundary (mergedline) and find state where line lies.
|
|
ON ST_Within(
|
|
ST_OffsetCurve(
|
|
(ST_LineSubString(r.geometry, 0.4,0.4004)), -70, 'quad_segs=4 join=mitre'), b.geometry)
|
|
) both_lines
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS osm_border_linestring_adm_geom_idx
|
|
ON osm_border_linestring_adm
|
|
USING GIST (geometry);
|