Progetto

Generale

Profilo

Scarica (1,07 KB)

Nuova funzione #397 » struct.sql

struttura db - Diego Sorrentino, 25-11-2020 13:42

 
drop view commune_intensity;
drop table commune_scores;
drop table event;
drop table commune;

create table event (
id serial primary key,
ads_id_event int8,
event_date timestamp,
lat float8,
lon float8,
depth float8,
zone_name text,
n_quests int8,
mag_pref_type text,
mag_pref_value float8
);
SELECT AddGeometryColumn ('','event','epicenter',4326,'POINT',2);

CREATE TABLE commune(
id serial primary key,
name text,
istat_code int8,
lat float8,
lon float8
);
SELECT AddGeometryColumn ('','commune','municipality',4326,'POINT',2);

CREATE TABLE commune_scores(
id serial primary key,
id_event int8 references event,
id_commune int8 references commune,
intensity_type int8,
intensity_value float8,
n_valid_quests int8
);

create or replace view commune_intensity as
select cs.*,
e.ads_id_event, e.event_date,
c.municipality
from commune_scores as cs
inner join event as e on (cs.id_event = e.id)
inner join commune as c on (cs.id_commune = c.id)
order by intensity_value;

create or replace view list_event as
select * from event order by mag_pref_type;
(2-2/8)