KIS-ORCA subsea cables#

https://kis-orca.org/

IMPORTANT: There may be some incorrect name assignments as a simple backfill and multiline conversion was used and no further checks were done

import os
from zipfile import BadZipFile, ZipFile

import contextily as cx
import geopandas as gpd
import matplotlib.pyplot as plt
import pandas as pd

from h2ss import data as rd
import subprocess

# base data download directory
DATA_DIR = os.path.join("data", "subsea-cables")

FILE_NAME = "Olex_KIS-ORCA-v2023.zip"

URL = f"https://kis-orca.org/wp-content/uploads/2020/12/{FILE_NAME}"

DATA_FILE = os.path.join(DATA_DIR, FILE_NAME)

# basemap cache directory
cx.set_cache_dir(os.path.join("data", "basemaps"))

plt.rcParams["xtick.major.size"] = 0
plt.rcParams["ytick.major.size"] = 0

rd.download_data(url=URL, data_dir=DATA_DIR, file_name=FILE_NAME)

Data 'Olex_KIS-ORCA-v2023.zip' already exists in 'data/subsea-cables'.
Data downloaded on: 2023-11-12 22:35:48.040165+00:00
Download URL: https://kis-orca.org/wp-content/uploads/2020/12/Olex_KIS-ORCA-v2023.zip
SHA256 hash: fd646e524a74fc36ee9401a0f1af6e977464ae76c6d8cc0126f4c738c6e4480a

Read data#

# extract the archive
try:
    z = ZipFile(DATA_FILE)
    z.extractall(DATA_DIR)
except BadZipFile:
    print("There were issues with the file", DATA_FILE)

DATA_FILE = os.path.join(DATA_DIR, ZipFile(DATA_FILE).namelist()[0])

# extract gz file using gzip
# https://www.gnu.org/software/gzip/
subprocess.run(["gzip", "-d", "<", DATA_FILE, ">", DATA_FILE[:-3]])

0

DATA_FILE = DATA_FILE[:-3]

for n, line in enumerate(open(DATA_FILE, "r", encoding="ISO-8859-15")):
    if n < 11:
        print(line[:-1])

'"20230130_KIS-ORCAv2023_31-Jan-2023"
Rute uten navn
Fikspos
3052.58800 -291.95800 1675123200 Gulramme
Navn Repeater
MTekst 0:Issue  : "20230130_KIS-ORCAv2023_31-Jan-2023"
MTekst 1:Object : Repeater
MTekst 2:Detail : APOLLO NORTH
MTekst 3:Contact: VODAFONE
MTekst 4:Status : ACTIVE
MTekst 5:Tel    : +44(0)207 138 7117

# coordinates
with open(f"{DATA_FILE}_data.txt", "w", encoding="utf-8") as outfile:
    for n, line in enumerate(open(DATA_FILE, "r", encoding="ISO-8859-15")):
        if line[0].isdigit():
            outfile.write(f"{n} {line}")

# names
with open(f"{DATA_FILE}_names.txt", "w", encoding="utf-8") as outfile:
    for n, line in enumerate(open(DATA_FILE, "r", encoding="ISO-8859-15")):
        if "MTekst 2" in str(line):
            outfile.write(f"{n}, {line[18:]}")

data = pd.read_csv(
    f"{DATA_FILE}_data.txt",
    header=None,
    sep=" ",
    names=["y", "x", "z", "text"],
)

names = pd.read_csv(
    f"{DATA_FILE}_names.txt", header=None, names=["index", "name"]
)

data.head()
y x z text
3 3052.588 -291.958 1675123200 Gulramme
14 3059.834 -325.624 1675123200 Gulramme
25 3060.112 -361.904 1675123200 Gulramme
36 3060.160 -404.239 1675123200 Gulramme
47 3059.491 -424.326 1675123200 Gulramme 
data.shape

(442011, 4)

names.head()
index name
0 7 APOLLO NORTH
1 18 APOLLO NORTH
2 29 APOLLO NORTH
3 40 APOLLO NORTH
4 51 APOLLO NORTH 
names.shape

(21647, 2)

names.set_index("index", inplace=True)

Merge names with coordinates#

names = names.reindex(range(max(data.index) + 1))

# handle missing data with backfill / forward fill
names = names.bfill()
names = names.ffill()

names.head()
name
index
0 APOLLO NORTH
1 APOLLO NORTH
2 APOLLO NORTH
3 APOLLO NORTH
4 APOLLO NORTH 
names.shape

(657317, 1)

# merge names and data
data = pd.merge(data, names, left_index=True, right_index=True)

data.head()
y x z text name
3 3052.588 -291.958 1675123200 Gulramme APOLLO NORTH
14 3059.834 -325.624 1675123200 Gulramme APOLLO NORTH
25 3060.112 -361.904 1675123200 Gulramme APOLLO NORTH
36 3060.160 -404.239 1675123200 Gulramme APOLLO NORTH
47 3059.491 -424.326 1675123200 Gulramme APOLLO NORTH 
data.shape

(442011, 5)

Convert to geodataframe#

# drop duplicate entries using coordinates
data = data.drop_duplicates(["y", "x"])

data.shape

(419079, 5)

# convert coords from minutes to degrees
# https://gis.stackexchange.com/a/241922
data["x"] = data["x"] / 60
data["y"] = data["y"] / 60

data.head()
y x z text name
3 50.876467 -4.865967 1675123200 Gulramme APOLLO NORTH
14 50.997233 -5.427067 1675123200 Gulramme APOLLO NORTH
25 51.001867 -6.031733 1675123200 Gulramme APOLLO NORTH
36 51.002667 -6.737317 1675123200 Gulramme APOLLO NORTH
47 50.991517 -7.072100 1675123200 Gulramme APOLLO NORTH 
# convert to geodataframe
data = gpd.GeoDataFrame(
    data, geometry=gpd.points_from_xy(data.x, data.y, crs=4326)
)

data.head()
y x z text name geometry
3 50.876467 -4.865967 1675123200 Gulramme APOLLO NORTH POINT (-4.86597 50.87647)
14 50.997233 -5.427067 1675123200 Gulramme APOLLO NORTH POINT (-5.42707 50.99723)
25 51.001867 -6.031733 1675123200 Gulramme APOLLO NORTH POINT (-6.03173 51.00187)
36 51.002667 -6.737317 1675123200 Gulramme APOLLO NORTH POINT (-6.73732 51.00267)
47 50.991517 -7.072100 1675123200 Gulramme APOLLO NORTH POINT (-7.07210 50.99152) 
data.crs

<Geographic 2D CRS: EPSG:4326>
Name: WGS 84
Axis Info [ellipsoidal]:
- Lat[north]: Geodetic latitude (degree)
- Lon[east]: Geodetic longitude (degree)
Area of Use:
- name: World.
- bounds: (-180.0, -90.0, 180.0, 90.0)
Datum: World Geodetic System 1984 ensemble
- Ellipsoid: WGS 84
- Prime Meridian: Greenwich

ax = data.to_crs(3857).plot(marker=".", markersize=1, figsize=(9, 9))
cx.add_basemap(ax, source=cx.providers.CartoDB.Positron)
plt.tick_params(labelbottom=False, labelleft=False)
plt.tight_layout()
plt.show()
../_images/99b5230d2cc998f877bbab0eb5503ce048bdcff544d1c38c71561d9b3066bb42.png

Dissolve geometries#

# dissolve by name
data = data.dissolve("name").reset_index()

data.head()
name geometry y x z text
0 50m Fishing Advisory Safety Zone ABERDEEN WIN... MULTIPOINT (-2.06615 57.21560, -2.06615 57.215... 57.215867 -2.063850 1675123200 Brunsirkel
1 50m Fishing Advisory Safety Zone ANHOLT WIND ... MULTIPOINT (-1.98668 57.21603, -1.98667 57.216... 57.216033 -1.986683 1675123200 Brunsirkel
2 50m Fishing Advisory Safety Zone BARROW WIND ... MULTIPOINT (-3.32998 53.99517, -3.32998 53.995... 56.676350 11.160500 1675123200 Brunsirkel
3 50m Fishing Advisory Safety Zone BEATRICE WIN... MULTIPOINT (-3.29142 53.98413, -3.29140 53.984... 53.984133 -3.291417 1675123200 Brunsirkel
4 50m Fishing Advisory Safety Zone BLYTH DEMO P... MULTIPOINT (-2.89668 58.25688, -2.89668 58.256... 58.256883 -2.896683 1675123200 Brunsirkel 
data.shape

(6126, 6)

Data for the Irish Sea#

# extent
mask = (-7, 53, -4.5, 54)

data_ie = data.clip(mask)

data_ie
name geometry y x z text
2139 HIBERNIA ATLANTIC SEG.D TELECOM CABLE MULTIPOINT (-6.12517 53.39633, -6.12417 53.397... 53.402833 -6.104000 1675123200 Brunsirkel
902 CELTIC TELECOM CABLE MULTIPOINT (-5.03608 53.37180, -5.03058 53.36447) 53.371800 -5.036083 1675123200 Brunsirkel
1295 ESAT 2 TELECOM CABLE MULTIPOINT (-6.20643 53.32382, -6.20222 53.324... 50.088667 -5.710950 1675123200 Brunsirkel
2135 HIBERNIA ATLANTIC MULTIPOINT (-6.01117 53.43167, -5.97792 53.347... 53.893500 -4.318000 1675123200 Rødramme
4794 ROCKABILL TELECOM CABLE MULTIPOINT (-6.10937 53.49357, -6.10557 53.493... 51.423183 1.616950 1675123200 Brunsirkel
6113 WESTERN LINK ARDNEILL TO WIRRAL 1 POWER CABLE MULTIPOINT (-5.10547 53.94238, -5.10395 53.939... 51.085450 1.220567 1675123200 Brunsirkel
1288 EMERALD BRIDGE POINT (-4.95258 53.28408) 53.284083 -4.952583 1675123200 Rødramme
1294 ESAT 2 MULTIPOINT (-6.04087 53.34388, -6.02700 53.34995) 53.343883 -6.040867 1675123200 Rødramme
5019 SIRIUS SOUTH MULTIPOINT (-5.96933 53.47467, -5.64117 53.556... 53.731500 -3.591333 1675123200 Rødramme
2137 HIBERNIA ATLANTIC SEG.A TELECOM CABLE MULTIPOINT (-5.07918 53.95348, -5.07802 53.948... 51.740550 1.289317 1675123200 Brunsirkel
2120 HAVHINGSTEN SEG 1.5 TELECOM CABLE MULTIPOINT (-4.57660 53.84340, -4.57633 53.841... 53.932983 -4.566550 1675123200 Brunsirkel
3119 LANIS 1 TELECOM CABLE MULTIPOINT (-6.12443 53.43257, -6.12413 53.432... 53.447933 -6.072367 1675123200 Brunsirkel
2118 HAVHINGSTEN SEG 1.3 TELECOM CABLE MULTIPOINT (-5.00462 53.84620, -4.98688 53.851... 53.920800 -4.971600 1675123200 Brunsirkel
85 AE CONNECT SEG 05 TELECOM CABLE MULTIPOINT (-4.70408 53.24558, -4.69628 53.247... 53.245583 -4.704083 1675123200 Brunsirkel
2119 HAVHINGSTEN SEG 1.4 TELECOM CABLE MULTIPOINT (-4.99742 53.84620, -4.99542 53.847... 53.847283 -4.950200 1675123200 Brunsirkel
2117 HAVHINGSTEN SEG 1.2 TELECOM CABLE MULTIPOINT (-4.96995 53.92820, -4.95973 53.961... 54.084900 -4.760417 1675123200 Brunsirkel
1289 EMERALD BRIDGE TELECOM CABLE MULTIPOINT (-6.11635 53.43230, -6.05838 53.442... 52.377817 4.528117 1675123200 Brunsirkel
1017 DOGGER BANK A WF EXTENT POINT (-6.12470 53.43220) 53.432200 -6.124700 1675123200 Brunsirkel
2121 HAVHINGSTEN SEG 2.1 TELECOM CABLE POINT (-4.57918 53.84250) 53.842500 -4.579183 1675123200 Brunsirkel
2138 HIBERNIA ATLANTIC SEG.C TELECOM CABLE MULTIPOINT (-6.07333 53.41667, -6.06650 53.419... 55.375967 -6.554117 1675123200 Brunsirkel
5020 SIRIUS SOUTH TELECOM CABLE MULTIPOINT (-6.05768 53.45273, -6.00000 53.466... 55.582000 -4.838000 1675123200 Brunsirkel
751 BT-TE1 TELECOM CABLE MULTIPOINT (-6.12460 53.43145, -6.03127 53.454... 56.737583 -5.241533 1675123200 Brunsirkel
1281 EAST WEST INTERCONNECTOR POWER CABLE MULTIPOINT (-6.08452 53.52402, -6.08442 53.523... 58.186050 -2.745967 1675123200 Brunsirkel
903 CELTIXCONNECT TELECOM CABLE MULTIPOINT (-6.17010 53.35503, -6.16405 53.352... 55.836833 8.125800 1675123200 Brunsirkel
2107 HAVFRUE SEG 07 TELECOM CABLE MULTIPOINT (-6.08127 53.54735, -6.08102 53.545... 53.549400 -6.036033 1675123200 Brunsirkel
2116 HAVHINGSTEN SEG 1.1 TELECOM CABLE MULTIPOINT (-6.01972 53.55267, -5.97415 53.558... 55.126600 -1.111167 1675123200 Brunsirkel 
data_ie.shape

(26, 6)

Convert multipoint geometries to multilines#

# convert all multi points to multi lines
data_ie_1 = data_ie[
    data_ie["geometry"].astype(str).str.contains("MULTI")
].reset_index(drop=True)

data_ie_1["geometry"] = gpd.GeoSeries.from_wkt(
    "LINESTRING ("
    + data_ie_1["geometry"].astype(str).str.split("(", expand=True)[1],
    crs=4326,
)

# merge multilines with remaining geometry data
data_ie = pd.concat(
    [
        data_ie[~data_ie["geometry"].astype(str).str.contains("MULTI")],
        data_ie_1,
    ]
).reset_index(drop=True)

data_ie
name geometry y x z text
0 EMERALD BRIDGE POINT (-4.95258 53.28408) 53.284083 -4.952583 1675123200 Rødramme
1 DOGGER BANK A WF EXTENT POINT (-6.12470 53.43220) 53.432200 -6.124700 1675123200 Brunsirkel
2 HAVHINGSTEN SEG 2.1 TELECOM CABLE POINT (-4.57918 53.84250) 53.842500 -4.579183 1675123200 Brunsirkel
3 HIBERNIA ATLANTIC SEG.D TELECOM CABLE LINESTRING (-6.12517 53.39633, -6.12417 53.397... 53.402833 -6.104000 1675123200 Brunsirkel
4 CELTIC TELECOM CABLE LINESTRING (-5.03608 53.37180, -5.03058 53.36447) 53.371800 -5.036083 1675123200 Brunsirkel
5 ESAT 2 TELECOM CABLE LINESTRING (-6.20643 53.32382, -6.20222 53.324... 50.088667 -5.710950 1675123200 Brunsirkel
6 HIBERNIA ATLANTIC LINESTRING (-6.01117 53.43167, -5.97792 53.347... 53.893500 -4.318000 1675123200 Rødramme
7 ROCKABILL TELECOM CABLE LINESTRING (-6.10937 53.49357, -6.10557 53.493... 51.423183 1.616950 1675123200 Brunsirkel
8 WESTERN LINK ARDNEILL TO WIRRAL 1 POWER CABLE LINESTRING (-5.10547 53.94238, -5.10395 53.939... 51.085450 1.220567 1675123200 Brunsirkel
9 ESAT 2 LINESTRING (-6.04087 53.34388, -6.02700 53.34995) 53.343883 -6.040867 1675123200 Rødramme
10 SIRIUS SOUTH LINESTRING (-5.96933 53.47467, -5.64117 53.556... 53.731500 -3.591333 1675123200 Rødramme
11 HIBERNIA ATLANTIC SEG.A TELECOM CABLE LINESTRING (-5.07918 53.95348, -5.07802 53.948... 51.740550 1.289317 1675123200 Brunsirkel
12 HAVHINGSTEN SEG 1.5 TELECOM CABLE LINESTRING (-4.57660 53.84340, -4.57633 53.841... 53.932983 -4.566550 1675123200 Brunsirkel
13 LANIS 1 TELECOM CABLE LINESTRING (-6.12443 53.43257, -6.12413 53.432... 53.447933 -6.072367 1675123200 Brunsirkel
14 HAVHINGSTEN SEG 1.3 TELECOM CABLE LINESTRING (-5.00462 53.84620, -4.98688 53.851... 53.920800 -4.971600 1675123200 Brunsirkel
15 AE CONNECT SEG 05 TELECOM CABLE LINESTRING (-4.70408 53.24558, -4.69628 53.247... 53.245583 -4.704083 1675123200 Brunsirkel
16 HAVHINGSTEN SEG 1.4 TELECOM CABLE LINESTRING (-4.99742 53.84620, -4.99542 53.847... 53.847283 -4.950200 1675123200 Brunsirkel
17 HAVHINGSTEN SEG 1.2 TELECOM CABLE LINESTRING (-4.96995 53.92820, -4.95973 53.961... 54.084900 -4.760417 1675123200 Brunsirkel
18 EMERALD BRIDGE TELECOM CABLE LINESTRING (-6.11635 53.43230, -6.05838 53.442... 52.377817 4.528117 1675123200 Brunsirkel
19 HIBERNIA ATLANTIC SEG.C TELECOM CABLE LINESTRING (-6.07333 53.41667, -6.06650 53.419... 55.375967 -6.554117 1675123200 Brunsirkel
20 SIRIUS SOUTH TELECOM CABLE LINESTRING (-6.05768 53.45273, -6.00000 53.466... 55.582000 -4.838000 1675123200 Brunsirkel
21 BT-TE1 TELECOM CABLE LINESTRING (-6.12460 53.43145, -6.03127 53.454... 56.737583 -5.241533 1675123200 Brunsirkel
22 EAST WEST INTERCONNECTOR POWER CABLE LINESTRING (-6.08452 53.52402, -6.08442 53.523... 58.186050 -2.745967 1675123200 Brunsirkel
23 CELTIXCONNECT TELECOM CABLE LINESTRING (-6.17010 53.35503, -6.16405 53.352... 55.836833 8.125800 1675123200 Brunsirkel
24 HAVFRUE SEG 07 TELECOM CABLE LINESTRING (-6.08127 53.54735, -6.08102 53.545... 53.549400 -6.036033 1675123200 Brunsirkel
25 HAVHINGSTEN SEG 1.1 TELECOM CABLE LINESTRING (-6.01972 53.55267, -5.97415 53.558... 55.126600 -1.111167 1675123200 Brunsirkel 
# get bounds of Irish Sea data
xmin, ymin, xmax, ymax = data_ie.to_crs(rd.CRS).total_bounds

# view plotter points in the Irish Sea
ax = data.to_crs(rd.CRS).plot(alpha=0.5, figsize=(9, 9))
plt.xlim(xmin, xmax)
plt.ylim(ymin, ymax)
cx.add_basemap(ax, source=cx.providers.CartoDB.Positron, crs=rd.CRS)
plt.tick_params(labelbottom=False, labelleft=False)
plt.tight_layout()
plt.show()
../_images/6607300c538d56f67a6488e6969b9108b31d08b062c711cfa35f02b792ef78d9.png 
# remove incorrect data lines that are obvious - Hibernia Atlantic
data_ie = data_ie.drop([6]).reset_index(drop=True)

ax = (
    gpd.GeoDataFrame(geometry=data_ie.to_crs(rd.CRS).buffer(750))
    .dissolve()
    .plot(figsize=(12, 12), alpha=0.25, color="slategrey")
)
data_ie.to_crs(rd.CRS).plot(
    column="name",
    legend=True,
    ax=ax,
    cmap="jet",
    legend_kwds={"loc": "upper right"},
)
data.to_crs(rd.CRS).plot(
    ax=ax, marker="x", color="black", markersize=20, alpha=0.5
)
plt.xlim(xmin - 10000, xmax + 50000)
plt.ylim(ymin, ymax)
cx.add_basemap(ax, source=cx.providers.CartoDB.Positron, crs=rd.CRS)
plt.tick_params(labelbottom=False, labelleft=False)
plt.tight_layout()
plt.show()
../_images/e113fd9ef6472ca9fa8135f3fceeee5b5e5e88b58071c81c4bd41fff39389be0.png 
data_ie.to_file(os.path.join(DATA_DIR, "KIS-ORCA.gpkg"))
On this page
Show Source