--Check version of postgis:
Select postgis_full_version();
--Check version of Postgres:
Select version();




--Example of creating a table and adding geometry columns

CREATE TABLE try (srid INTEGER NOT NULL PRIMARY KEY, name VARCHAR (20));
SELECT AddgeometryColumn ('', 'try', 'geom', -1, 'LINESTRING', 2);

--Checking if gemetry is valid
SELECT ST_IsValid(the_geom1) FROM public_observation;

DELETE FROM public_observation WHERE ST_IsValid(the_geom1) IS NOT TRUE;

-- Example of inserting
INSERT INTO historical_information(historical_information_id, hazard_type, losses, occurances, type, location)
VALUES (3,'landslide', 'roads', 6, 'high', ST_SetSRID(ST_MakePoint(-71.2, 42.3),4326));


--Deleting records
DELETE FROM landslidenew WHERE "OBJECTID" IS NULL  AND "SHAPE_Leng" IS NULL;




--Example of selecting - usual SQL
Select * from avalanchenew where date_of_event IS NULL;

SELECT count (*) as "number of settlements"
FROM public.settlements;

--Involving geometries:


--Give total area of all landslides:
SELECT sum(ST_Area(the_geom)) as area FROM landslidenew;

--transform Geometries
--Give total area in km in Gauss/Kruger -Pulkovo SRID (2467):
SELECT sum(ST_Area(ST_Transform(the_geom, 2467))) as "area in km^2" FROM landslidenew;











--Give number of lanslide that affect each settlement;
SELECT s.name_eng, count(*) as area FROM settlements as s , landslidenew as l
WHERE ST_Contains(l.the_geom, ST_Transform(s.the_geom, 4326))
GROUP BY s.name_eng;












--the same in other way:
SELECT s.name_eng, l.date_of_oc
FROM settlements s, landslidenew l
WHERE ST_Transform(s.the_geom, 4326) && l.the_geom

--Give area in km^2 of landslides that affected each settlement
SELECT s.name_eng, SUM(ST_Area(ST_Transform(l.the_geom, 2467)))/1000 as "area in km" FROM settlements as s , landslidenew as l
WHERE ST_Contains(ST_Transform(l.the_geom, 2467),s.the_geom)
GROUP BY s.name_eng;

SELECT * FROM public_observation p, landslidenew l
WHERE ST_Intersects (p.the_geom1, l.the_geom);

DELETE FROM public_observation WHERE nea_checked =1;


--Update  example
UPDATE public_observation
SET nea_checked =1
from landslidenew as ln
WHERE ST_Intersects (public_observation.the_geom1, ln.the_geom);


--example of sequences:
--Create sequence
CREATE SEQUENCE tablename_colname_seq;
--Apply sequence rules in create table:
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq'),name1 text);
--Change sequence ownership to table, so that if delected then sequence is also deleted
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
INSERT INTO tablename(name1) values ('Centuki'), ('derbi'), ('pastoral');
DROP TABLE tablename;
-- //get the next value in sequence (nextval...):
Select currval('tablename_colname_seq');

--Creating a sequence in already existing tables:
ALTER TABLE landslidenew DROP COLUMN gid;
CREATE SEQUENCE landslidenew_seq;
ALTER TABLE landslidenew ADD COLUMN gid integer NOT NULL DEFAULT nextval ('landslidenew_seq');
ALTER SEQUENCE landslidenew_seq OWNED BY landslidenew.gid;
Select nestval('landslidenew_seq'); --check




--//////////////////////////////
--Rules and triggers:

--Rule in genral form looks like:

-- CREATE [ OR REPLACE ] RULE name AS ON event
--     TO table [ WHERE condition ]
--     DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }
-- 

ALTER TABLE public_observation ADD COLUMN nea_checked integer;


CREATE RULE my_rule AS
ON INSERT TO landslidenew 
DO ALSO UPDATE public_observation
	SET nea_checked =1
	from landslidenew 
	WHERE ST_Intersects (public_observation.the_geom1, NEW.the_geom);
	

GRANT SELECT UPDATE DELETE ON landslidenew TO Gigi


	

CREATE TRIGGER insert_lanslide_trigger
BEFORE INSERT ON landslidenew
FOR EACH ROW EXECUTE PROCEDURE my_trigger();

CREATE OR REPLACE FUNCTION my_trigger()
RETURNS TRIGGER AS $$
BEGIN
	IF ST_Intersects(public_obersation.the_geom1, NEW.the_geom) THEN
		UPDATE public_observation
		SET nea_checked =1
		FROM landslidenew 
		WHERE ST_Intersects (public_observation.the_geom1, NEW.the_geom);;
	END IF;
	RETURN NULL;
END;
$$
LANGUAGE plpgsql;


INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type") 
SELECT '', 'public', 'public_observation', 'the_geom1', 2, 4326, 'POINT';

Select ST_ASText(the_geom1) FROM public_observation



--////////////
--Views


CREATE OR REPLACE VIEW hazrd_view AS 
	SELECT l.gid, name,type, date_of_event, date_of_oc
	FROM flashfloodnew f, landslidenew l;


SELECT * FROM hazrd_view