-- step 1 - database creation
create database postgis-example;
-- step 2 activate the postgis extension
create extension postgis;
-- step 3 - import a shapefile in a table in the database (rail network) - from command line from the relevant folder that hosts the spapefile
shp2pgsql -I -s 2100 cd6cca01-996f-4120-8ff2-96e094e8d405.shp railnetwork | psql -U postgres -d postgis-example
-- step 4 - remove unused columns from table railnetwork (keep columns gid and geom)
ALTER TABLE railnetwork
DROP COLUMN shape_len;
-- step 4 - import a shapefile in a table in the database (environmental education centers)
shp2pgsql -I -W "greek" -s 2100 kpe.shp kpe | psql -U postgres -d postgis-example
-- step 5
ALTER TABLE kpe RENAME COLUMN Όνομα TO name;
ALTER TABLE kpe RENAME COLUMN Σελίδα TO webpage;
ALTER TABLE kpe DROP COLUMN id, DROP COLUMN mail;
-- step 6 - import a shapefile in a table in the database (katafygia)
shp2pgsql -I -W "greek" -s 2100 katafygia.shp katafygia | psql -U postgres -d postgis-example
-- step 7
ALTER TABLE katafygia DROP COLUMN objectid, DROP COLUMN kode, DROP COLUMN fek, 
DROP COLUMN area_, DROP COLUMN perimeter, DROP COLUMN hectares,
DROP COLUMN updated, DROP COLUMN prefecture, DROP COLUMN descriptio,
DROP COLUMN created_by, DROP COLUMN created_da, DROP COLUMN updated_by,
DROP COLUMN id, DROP COLUMN shape_area, DROP COLUMN shape_len;
-- step 8 -import a shapefile in a table in the database (natura 2000 areas)
shp2pgsql -I -s 2100 natura2000gr.shp natura | psql -U postgres -d postgis-example
-- step 9
ALTER TABLE natura DROP COLUMN fid, DROP COLUMN objectid, DROP COLUMN code, 
DROP COLUMN area, DROP COLUMN perimeter, DROP COLUMN hectares,
DROP COLUMN sitetype;
-- step 10
ALTER TABLE natura RENAME COLUMN name_latin TO name;
-- step 11
create table perifereies (
gid character varying(254) NOT NULL,
per character varying(254),
geom public.geometry(MultiPolygon,2100)
)
-- step 12
COPY perifereies(gid, per, geom)
FROM 'C:\postgis-example\perifereies.csv'
DELIMITER ','
CSV HEADER;
-- step 13
ALTER TABLE perifereies RENAME COLUMN per TO periphery;