Postgres‐Scripts

Create Sirte-Table

-- Table: environment.volcanos

DROP TABLE IF EXISTS environment.hydrothermal_vents;

CREATE TABLE IF NOT EXISTS environment.hydrothermal_vents
(
    id SERIAL PRIMARY KEY,
    name text COLLATE pg_catalog."default",
    description text COLLATE pg_catalog."default",
    data_owner text COLLATE pg_catalog."default",
    x_coord double precision,
    y_coord double precision,
    z_coord double precision,
    geom geometry(PointZ,4326),
    country text COLLATE pg_catalog."default"
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS environment.hydrothermal_vents
    OWNER to postgres;

REVOKE ALL ON TABLE environment.hydrothermal_vents FROM PUBLIC;

GRANT SELECT ON TABLE environment.hydrothermal_vents TO PUBLIC;

GRANT ALL ON TABLE environment.hydrothermal_vents TO postgres;
-- Index: volcanos_gix

-- DROP INDEX IF EXISTS environment.volcanos_gix;

CREATE INDEX IF NOT EXISTS hydrothermal_vents_gix
    ON environment.hydrothermal_vents USING gist
    (geom)
    WITH (fillfactor=90, buffering=auto)
    TABLESPACE pg_default;

-- Trigger: 10_trigger_point_geom_update

-- DROP TRIGGER IF EXISTS "10_trigger_point_geom_update" ON environment.volcanos;

CREATE OR REPLACE TRIGGER "10_trigger_point_geom_update"
    BEFORE INSERT OR UPDATE 
    ON environment.hydrothermal_vents
    FOR EACH ROW
    EXECUTE FUNCTION public.point_geom_update();

-- Trigger: 20_trigger_point_geom_get_country

-- DROP TRIGGER IF EXISTS "20_trigger_point_geom_get_country" ON environment.volcanos;

CREATE OR REPLACE TRIGGER "20_trigger_point_geom_get_country"
    BEFORE INSERT OR UPDATE 
    ON environment.hydrothermal_vents
    FOR EACH ROW
    EXECUTE FUNCTION public.point_geom_get_country();

Create Lines-Table

CREATE TABLE leylines.lines (
    id integer NOT NULL,
    "group" text,
    name text,
    description text,
    image text,
    video text,
    links text,
    data_owner text,
    geom public.geometry(LineStringZ,4326)
);

ALTER TABLE leylines.lines OWNER TO postgres;

--
-- Name: lines_gid_seq; Type: SEQUENCE; Schema: leylines; Owner: postgres
--

CREATE SEQUENCE leylines.lines_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE leylines.lines_id_seq OWNER TO postgres;

--
-- Name: lines_gid_seq; Type: SEQUENCE OWNED BY; Schema: leylines; Owner: postgres
--

ALTER SEQUENCE leylines.lines_id_seq OWNED BY leylines.lines.id;

--
-- Name: lines gid; Type: DEFAULT; Schema: leylines; Owner: postgres
--

ALTER TABLE ONLY leylines.lines ALTER COLUMN id SET DEFAULT nextval('leylines.lines_id_seq'::regclass);

--
-- Name: lines_gid_seq; Type: SEQUENCE SET; Schema: leylines; Owner: postgres
--

SELECT pg_catalog.setval('leylines.lines_id_seq', 6, true);

--
-- Name: lines lines_pkey; Type: CONSTRAINT; Schema: leylines; Owner: postgres
--

ALTER TABLE ONLY leylines.lines
    ADD CONSTRAINT lines_pkey PRIMARY KEY (id);

--
-- Name: sidx_lines_geom; Type: INDEX; Schema: leylines; Owner: postgres
--

CREATE INDEX sidx_lines_geom ON leylines.lines USING gist (geom);

--
-- Name: TABLE lines; Type: ACL; Schema: leylines; Owner: postgres
--

GRANT SELECT ON TABLE leylines.lines TO PUBLIC;

Upate all rows of column

UPDATE environment.hydrothermal_vents set data_owner = 'https://doi.pangaea.de/10.1594/PANGAEA.917894' where true;

Create new countries table

Source: https://www.naturalearthdata.com/downloads/10m-cultural-vectors/10m-admin-0-countries/

ogr2ogr -f "PostgreSQL" -t_srs "EPSG:4326" PG:"host=127.0.0.1 user=****** dbname=leylines password=****** schemas=public" ne_10m_admin_0_countries.shp -nln countries -nlt PROMOTE_TO_MULTI -sql 'select name_en as name from ne_10m_admin_0_countries' -lco GEOMETRY_NAME=geom -lco FID=id