PaGE Spatial database

From gnewarchaeology wiki
Jump to: navigation, search
PaGE > PaGE Spatial database



Refer to PostgreSQL for database creation and management.

Survey

-- Domenico Giusti
-- <domenico.giusti@uni-tuebingen.de>
--
-- Eberhard Karls Universität Tübingen
-- Mathematisch-Naturwissenschaftlichen Fakultät
-- Instituts für Ur- und Frühgeschichte und Archäologie des Mittelalters
-- AG: Paläoanthropologie
-- Rümelinstr. 23 - 72070 Tübingen
--
-- PostgreSQL script -- English
--
-- This file can be redistributed and/or modified under
-- the terms of the GNU Public License, version 3.
--------------------------------------------------------------------------------

-- Page project - Survey Database

--------------------------------------------------------------------------------

-- drop tables
--DROP TABLE  survey CASCADE;
DROP TABLE region CASCADE;
DROP TABLE locality CASCADE;
DROP TABLE calendar CASCADE;
DROP TABLE su CASCADE;
DROP TABLE su_calendar CASCADE;
DROP TABLE site CASCADE;
DROP TABLE faunal CASCADE;
DROP TABLE lithic CASCADE;
DROP TABLE other CASCADE;

-- create tables and constraints

-- -- create table: survey
-- CREATE TABLE survey (
-- 	id serial PRIMARY KEY,
-- 	project varchar(64) NOT NULL, --PaGE
-- 	date varchar(4) NOT NULL, --year(yyyy)
-- 	comment text NULL
-- );

-- COMMENT ON TABLE survey IS 'survey project year';
-- Not necessary! Time table added and linked to survey unit table.

-- create table: region
CREATE TABLE region (
       id serial PRIMARY KEY,
       --id_survey integer REFERENCES survey (id),
       name varchar(64) UNIQUE NOT NULL, --Mygdonia basin/...
       region varchar(64) NOT NULL, --Macedonia/...
       country varchar(64) NOT NULL, --Greece
       comment text NULL
       --bibtex
);

-- add a geometry column (regional bounding box)
SELECT AddGeometryColumn ('public','region','geom','4326','POLYGON',2);

COMMENT ON TABLE region IS 'main surveyed geographical region';

-- create table: locality
CREATE TABLE locality (
       id serial PRIMARY KEY,
       id_region integer REFERENCES region (id),
       name varchar(64) UNIQUE NOT NULL, --Megalo Rema/Gerakarou/...
       comment text NULL
);

-- add a geometry column (locality point)
--SELECT AddGeometryColumn ('public','region','geom','4326','POLYGON',2);

COMMENT ON TABLE locality IS 'surveyed locality within the main region';

-- create table: calendar
CREATE TABLE calendar (
       id serial PRIMARY KEY,
       date date NOT NULL, --survey day (dd/mm/yyyy)
       comment text NULL
);

SET datestyle = "ISO, DMY";

COMMENT ON TABLE calendar IS 'calendar of survey';
-- Could be linked to a diary table/journal log

-- create table: su
CREATE TABLE su (
       id serial PRIMARY KEY,
       id_locality integer REFERENCES locality (id),
       num varchar(16) NOT NULL, --#
       type varchar(64) NOT NULL, --section/profile/field
       lat_1 varchar(32) NOT NULL, --gps waypoint 1 N (wgs84)
       lon_1 varchar(32) NOT NULL, --gps waypoint 1 E (wgs84)
       elev_1 varchar(32) NOT NULL, --gps waypoint 1 elev (meters)
       lat_2 varchar(32) NOT NULL, --gps waypoint 2 N (wgs84)
       lon_2 varchar(32) NOT NULL, --gps waypoint 2 E (wgs84)
       elev_2 varchar(32) NOT NULL, --gps waypoint 2 elev (meters)
       lat_3 varchar(32) NOT NULL, --gps waypoint 3 N (wgs84)
       lon_3 varchar(32) NOT NULL, --gps waypoint 3 E (wgs84)
       elev_3 varchar(32) NOT NULL, --gps waypoint 3 elev (meters)
       lat_4 varchar(32) NOT NULL, --gps waypoint 4 N (wgs84)
       lon_4 varchar(32) NOT NULL, --gps waypoint 4 E (wgs84)
       elev_4 varchar(32) NOT NULL, --gps waypoint 4 elev (meters)
       length varchar(16) NULL, --absolute or max/min length in meters
       width varchar(16) NULL, --absolute or max/min width in meters
       depth varchar(16) NULL, --absolute or max/min thickness/depth in meters
       dim_comment text NULL, --comment about su dimension
       deposit varchar(256) NULL, --lacustrine/fluvial/alluvial/soil/colluvium
       formation varchar(256) NULL, --geological formation: platanochori,gerakarou
       geo_comment text NULL, --comment about su geological context
       sediment_size varchar(256) NULL, --clay/silt/sand/gravel/cobble/boulder
       sediment_rounding varchar(256) NULL, --angular/sub-rounded/well-rounded
       sediment_sorting varchar(256) NULL, --very well/well/moderately well/moderately/poorly/very poorly
       sediment_support varchar(256) NULL, --matrix/clast
       sediment_color varchar(256) NULL, --munsel/color
       sediment_structures varchar(256) NULL, --ripple/lamination/grading(normal/inverse)/imbrication/nodules/...
       sed_comment text NULL, --comment about sedimentary characteristics
       stratigraphy text NULL, --stratigraphic correlation
       fault varchar(512) NULL, --fault
       geomorphology text NULL, --geomorphological setting
       erosion integer NULL, --1/2/3/4/5/6/7
       erosion_comment varchar(512) NULL, --observation about erosion value
       disturbance integer NULL, --1/2/3/4/5/6/7
       disturbance_comment varchar(512) NULL, --observation about disturbance value
       visibility integer NULL, --1/2/3/4/5/6/7
       visibility_comment varchar(512) NULL, --observation about visibility value
       photo varchar(64) NULL --photos number/id (not link!)
       --photo_link
       --photo_log
       --sketch/scan of the original form
);

-- add a geometry column
SELECT AddGeometryColumn ('public','su','geom','4326','POLYGON',3);

-- create an index
CREATE INDEX su_gist ON su USING GIST ( geom );

-- after building an index, it is important to force PostgreSQL to collect table statistics, which are used to optimize query plans
---VACUUM ANALYZE su (su_gist);

COMMENT ON TABLE su IS 'survey unit';

-- create table: su_calendar
CREATE TABLE su_calendar (
       id_calendar integer REFERENCES calendar (id),
       id_su integer REFERENCES su (id)
);

COMMENT ON TABLE su_calendar IS 'relational table between su and calendar';

-- create table: site
CREATE TABLE site (
       id serial PRIMARY KEY,
       id_su integer REFERENCES su (id),
       num varchar(16) NOT NULL, --#
       lat varchar(32) NOT NULL, --gps waypoint N (wgs84)
       lon varchar(32) NOT NULL, --gps waypoint E (wgs84)
       elev varchar(32) NOT NULL, --gps waypoint elev (meters)
       type varchar(16) NOT NULL, --site/findspot
       lithic boolean NULL, --yes/no/NULL
       faunal boolean NULL, --yes/no/NULL
       other boolean NULL, --yes/no/NULL
       importance integer NULL, --1(<10finds)/2(>10finds)/3(i like)
       comment text NULL,
       photo varchar(64) NULL --photos number/id (not link!)
       --photo_link
       --photo_log
);

SELECT AddGeometryColumn ('public','site','geom','4326','POINT',3);

-- create an index
CREATE INDEX poi_gist ON site USING GIST ( geom );

-- after building an index, it is important to force PostgreSQL to collect table statistics, which are used to optimize query plans
---VACUUM ANALYZE poi (poi_gist);

COMMENT ON TABLE site IS 'point of interest (site/findspot) within the survey unit';

-- create table: faunal
CREATE TABLE faunal (
       id integer PRIMARY KEY,
       id_site integer REFERENCES site (id),
       description varchar(512) NULL, --complete/fragment bone/tooth
       lat varchar(32) NULL, --N wgs84 gps coord (= photo label)
       lon varchar(32) NULL, --E (dd.dddd)
       elev varchar(32) NULL, --elevation (meters)
       photo varchar(64) NULL, --photos number/id (not link!)
       comment text NULL
       --photo_link
       --photo_log
);

SELECT AddGeometryColumn ('public','faunal','geom','4326','POINT',3);

-- create an index
CREATE INDEX faunal_gist ON faunal USING GIST ( geom );

-- after building an index, it is important to force PostgreSQL to collect table statistics, which are used to optimize query plans
---VACUUM ANALYZE faunal (faunal_gist);

COMMENT ON TABLE faunal IS 'faunal remain from site within the su';

-- tranform the lat lon entries in geom
----UPDATE faunal SET geom = ST_SetSRID(ST_MakePoint(lat,lon,elev),4326);

-- create table: lithic
CREATE TABLE lithic (
       id integer PRIMARY KEY,
       id_site integer REFERENCES site (id),
       description varchar(256) NULL, --complete/fragment flake
       lat varchar(32) NULL, --N wgs84 gps coord (= photo label)
       lon varchar(32) NULL, --E (dd.dddd)
       elev varchar(32) NULL, --elevation (meters)
       photo varchar(64) NULL, --photos number/id (not link!)
       comment text NULL
       --photo_link
       --photo_log
);

SELECT AddGeometryColumn ('public','lithic','geom','4326','POINT',3);

-- create an index
CREATE INDEX lithic_gist ON lithic USING GIST ( geom );

-- after building an index, it is important to force PostgreSQL to collect table statistics, which are used to optimize query plans
---VACUUM ANALYZE lithic (lithic_gist);

COMMENT ON TABLE lithic IS 'lithic remain from site within the su';

-- create table: other
CREATE TABLE other (
       id integer PRIMARY KEY,
       id_site integer REFERENCES site (id),
       description varchar(256) NULL, --shell/
       lat varchar(32) NULL, --N wgs84 gps coord (= photo label)
       lon varchar(32) NULL, --E (dd.dddd)
       elev varchar(32) NULL, --elevation (meters)
       photo varchar(64) NULL, --photos number/id (not link!)
       comment text NULL
       --photo_link
       --photo_log
);

SELECT AddGeometryColumn ('public','other','geom','4326','POINT',3);

-- create an index
CREATE INDEX other_gist ON other USING GIST ( geom );

-- after building an index, it is important to force PostgreSQL to collect table statistics, which are used to optimize query plans
---VACUUM ANALYZE lithic (lithic_gist);

COMMENT ON TABLE other IS 'other remain from site within the su';

-- create table: paleontology
-- create table: taphonomy
-- create table: technology