🏺 PEPAdb: Archaeological Database

This project contains a set of SQL scripts to create, populate, and transform an archaeological database structured in PostgreSQL/PostGIS. Below is a step-by-step description of the workflow and the implemented functionalities.

---

🧱 1. Archaeological Structures (structures)

The structures table stores geographic and contextual information about archaeological structures.

🔧 Table Structure

CREATE TABLE pepadb.structures (
    id VARCHAR PRIMARY KEY,
    id_site NUMERIC,
    structure VARCHAR NULL,
    country VARCHAR,
    adm1 VARCHAR,
    adm2 VARCHAR, 
    millennium VARCHAR, 
    struc_type VARCHAR,
    x_coord NUMERIC,
    y_coord NUMERIC
);

📥 Data Import

Data is loaded from a CSV file:

COPY pepadb.structures(...)
FROM '.../csvStructures/structures.csv'
DELIMITER ','
CSV HEADER;

🗺️ Georeferencing

A geom column is added to represent structures as geographical points:

ALTER TABLE pepadb.structures ADD COLUMN geom GEOMETRY(Point, 4326);

UPDATE pepadb.structures
SET geom = ST_SetSRID(ST_MakePoint(x_coord, y_coord), 4326);

---

🏕️ 2. Archaeological Sites (sites)

This table represents the archaeological sites to which the structures belong.

📦 Definition and Import

CREATE TABLE pepadb.sites (
    id NUMERIC PRIMARY KEY,
    site VARCHAR NULL
);

COPY pepadb.sites(...)
FROM '.../csvSites/sites.csv'
DELIMITER ','
CSV HEADER;

Establishing the foreign key:

ALTER TABLE pepadb.structures
ADD CONSTRAINT fk_sites FOREIGN KEY (id_site) REFERENCES pepadb.sites(id);

---

🔬 3. Archaeological Records (records)

Contains details about items found within each structure.

📦 Definition and Import

CREATE TABLE pepadb.records (
    id_inv VARCHAR PRIMARY KEY,
    id_structure VARCHAR,
    type VARCHAR(150) NULL,
    raw_material VARCHAR(150) NULL
);

COPY pepadb.records(...)
FROM '.../csvRecords/records.csv'
DELIMITER ','
CSV HEADER;

🔗 Relationships

ALTER TABLE pepadb.records
ADD CONSTRAINT fk_structures FOREIGN KEY (id_structure) REFERENCES pepadb.structures(id);

---

🔗 4. Structure ↔ Record Relationship (struc_record)

One-to-many relationship between structures and records.

CREATE TABLE pepadb.struc_record AS 
SELECT ...
FROM pepadb.structures
CROSS JOIN pepadb.records
WHERE structures.id = records.id_structure;

ALTER TABLE pepadb.struc_record ADD PRIMARY KEY (id_inv);

ALTER TABLE pepadb.struc_record
ADD CONSTRAINT fk_struc_rec FOREIGN KEY (id_inv) REFERENCES pepadb.records(id_inv);

---

📊 5. GeoViewer Layer (contingency_table)

A contingency table that groups raw materials by structure for mapping and visualisation.

CREATE TABLE pepadb.contingency_table AS 
SELECT
    id, structure, ..., 
    COUNT(CASE WHEN raw_material = 'Amber' THEN 1 END) AS Amber,
    ...
    COUNT(*) AS n_items,
    geom
FROM pepadb.struc_record
GROUP BY id, structure, ..., geom;

Establishing the foreign key:

ALTER TABLE pepadb.contingency_table 
ADD CONSTRAINT fk_contingency_table FOREIGN KEY (id) REFERENCES pepadb.structures(id);

---

💾 6. Database Layer (database_table)

Groups records and counts items by type and raw material.

CREATE TABLE pepadb.database_table AS (
SELECT ...
FROM pepadb.struc_record
GROUP BY ...
ORDER BY ...
);

---

📤 7. Export to JSON

Transforms the data into a JSON object for frontend or API integration:

COPY (
  SELECT json_agg(row_to_json(database_table))::text
  FROM pepadb.database_table
  WHERE id IS NOT NULL
) TO '.../json/database_table.json';

---

✅ Validations

• 🔎 Verifies that all structures are represented in records.
• 📌 Ensures referential integrity between tables.
• 🧪 Item counts by structure and type/material.

---

📁 Expected CSV File Structure

/csvStructures/structures.csv
/csvSites/sites.csv
/csvRecords/records.csv
/json/database_table.json

---

🧭 Requirements

• PostgreSQL ≥ 13
• PostGIS enabled
• Scripts executable via pgAdmin or CLI

---

📌 Notes

• All coordinates must be georeferenced (EPSG:4326).
• This project can be integrated with visualisation tools such as GeoServer or Leaflet.

---

🏁 End of Process

This script prepares a fully functional relational spatial database suitable for archaeological analysis, GIS, and geographic visualisation.

---