CREATE TABLE idioktites( afm VARCHAR(12) NOT NULL, adt VARCHAR (8) NOT NULL,eponymo VARCHAR(20) not null, onoma VARCHAR(15),
 tilefono VARCHAR(15), odos VARCHAR(20), arithmos INTEGER, poli VARCHAR(20), tk VARCHAR(10), CONSTRAINT pr_key_idiokt PRIMARY KEY(AFM), CONSTRAINT uniq_idiokt UNIQUE (ADT)); 

CREATE TABLE agroktimata( code VARCHAR(10) NOT NULL, toponymio VARCHAR(30), oikismos VARCHAR(20), poli  VARCHAR(20), 
elaiodentra INTEGER, CONSTRAINT pr_key_agro PRIMARY KEY (code));

CREATE TABLE idioktisies( afm VARCHAR(12) NOT NULL, code VARCHAR(10) NOT NULL, pososto smallint, 
CONSTRAINT pr_key_idioktis PRIMARY KEY(AFM,code), CONSTRAINT check_psosto CHECK(pososto<=100 AND pososto>0), 
CONSTRAINT forkey_afm FOREIGN KEY (afm) REFERENCES idioktites(afm)  MATCH SIMPLE  ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT forkey_code FOREIGN KEY(code) REFERENCES agroktimata(code) MATCH SIMPLE   ON UPDATE RESTRICT ON DELETE RESTRICT); 

INSERT INTO idioktites VALUES('123456789', 'ΖΛ123456', 'Παπαδάκης', 'Γιάννης', '2345678', 'Καλοκαιρινού', 100,'Ηράκλειο', '71410'), 
('1000','ΖH123456','Λαμπράκης','Νίκος',NULL,NULL,'0','Αρκαλοχώρι',NULL),('2000','ΑΔ234567','Παπαδάκης','Μανολης','345678','Καλοκαιρινου','50','Ηράκλειο','714 10'),
('4000','ΗΙ345678','Μπελής','Γιάννης','345',NULL,'0','Ιεράπετρα',NULL),
('3000','ΖΛ123478','Δεληγιάννης','Ηλίας','2345678','Κατεχακη','5','Ηράκλειο','71410');

INSERT INTO agroktimata VALUES('Σ1','Αποσαμι Μετοχι','Σκαλανι','Ηράκλειο','33'),
('Κ1','Κορακοβούνι','Αμφιθέα','Ηράκλειο','2'),
('ΚΜ1','Κάμπος','Αρκαλοχώρι','Μινώα Πεδιάδος','23'),
('ΚΜ2','Κάμπος','Αρκαλοχώρι','Μινώα Πεδιάδος','4'),
('ΒΝ1','Βουνό','Αρκαλοχώρι','Μινώα Πεδιάδος','27'),
('ΦΛ1','Φλέγα','Μακρυλιά','Ιεράπετρα','10'),
('ΦΛ2','Φλέγα','Μακρυλιά','Ιεράπετρα','8'),
('ΒΝ2','Βουνό','Αρκαλοχώρι','Μινώα Πεδιάδος','15'),
('ΠΝ1','Ζουρβες','Μακρυλιά','Ιεράπετρα','26'),
('ΨΡΚ1','Ψαροκεφάλα','Αρκαλοχώρι','Μινώα Πεδιάδος','16'),
('ΨΡΚ2','Ψαροκεφάλα','Αρκαλοχώρι','Μινώα Πεδιάδος','16');

INSERT INTO idioktisies VALUES('1000','ΒΝ2','50'),('1000','Σ1','100'),('3000','ΒΝ2','60'),('3000','ΒΝ1','100'),
('3000','Κ1','50'),('4000','ΚΜ1','100'),('4000','Κ1','50'),('3000','ΦΛ1','100'),('2000','ΚΜ2','10'),
('3000','ΦΛ2','100'),('4000','ΨΡΚ1','100'),('4000','ΨΡΚ2','100'),('3000','ΠΝ1','100'),('2000','ΠΝ1','20');

CREATE EXTENSION postgis; 

SELECT AddGeometryColumn('public', 'agroktimata', 'agr_geom', 4121,'POLYGON',2);
ALTER TABLE  public.agroktimata ADD CONSTRAINT agrokt_geom CHECK (st_isvalid(agr_geom));
CREATE INDEX agr_idx ON public.agroktimata USING GIST (agr_geom); 

update agroktimata set agr_geom=st_GeomFromText('POLYGON((602346.817 3907797.981,
602350.266 3907807.337, 602353.715 3907816.692, 602348.274 3907822.557,
602337.098 3907830.187, 602327.870 3907816.850, 602322.795 3907805.542,
602346.817  3907797.981))',4121) where code='Κ1';
update agroktimata set agr_geom=st_GeomFromText('POLYGON((608028.61 3904373.784, 608036.387 3904371.133, 608040.377 3904369.599, 608048.416 3904365.07, 608055.593 3904360.454, 608061.628 3904354.715, 608065.885 3904350.076, 608069.565 3904344.082, 608071.959 3904338.692, 608075.288 3904331.158, 608078.525 3904324.296, 608080.867 3904319.801, 608082.93 3904316.316, 608083.286 3904314.878, 608080.799 3904311.245, 608077.79 3904307.139, 608076.569 3904308.236, 608074.827 3904309.63, 608068.177 3904314.947, 608061.826 3904320.151, 608058.338 3904322.041, 608053.055 3904324.775, 608046.761 3904328.847, 608037.675 3904335.54, 608027.357 3904341.019, 608023.43 3904343.204, 608024.404 3904351.765, 608025.409 3904360.242, 608025.757 3904364.673, 608027.263 3904369.313, 608028.61 3904373.784))',4121)
 where code='Σ1'; 

CREATE TABLE public.roads (id character varying(10) NOT NULL,
  name character varying(60), road_geom geometry(LineString,4121),
CONSTRAINT road_pkey PRIMARY KEY (id), CONSTRAINT road_geom CHECK (st_isvalid(road_geom)));

CREATE INDEX road_idx ON public.roads USING GIST (road_geom);

INSERT INTO public.roads VALUES ('Κ1Π1','Κορακοβούνι-Παράδρομος1',
St_GeomFromText('LINESTRING(602355.643 3907814.22, 602332.36 3907750.455)',4121));

INSERT INTO public.roads VALUES ('Κ1Π2','Κορακοβούνι-Παράδρομος2',
St_GeomFromText('LINESTRING(602335.535 3907831.947, 602320.718 3907806.547,
 602314.897 3907789.349, 602314.897 3907789.349, 602311.722 3907776.384, 602311.722 3907776.384,
 602308.018 3907763.949, 602308.018 3907763.949, 602307.489 3907755.218, 602307.489 3907755.218,
 602306.96 3907754.424)',4121));

CREATE TABLE public.aktes (id character varying(10) NOT NULL,
  name character varying(60), aktes_geom geometry(LineString,4121),
CONSTRAINT aktes_pkey PRIMARY KEY (id), CONSTRAINT aktes_geom CHECK (st_isvalid(aktes_geom)));

CREATE INDEX aktes_idx ON public.aktes USING GIST (aktes_geom);

INSERT INTO public.roads VALUES ('Κ1ΚΡΜ','Κουρμούλη',
St_GeomFromText('LINESTRING(602433.034 3907705.74, 602413.587 3907717.118, 602397.315 3907724.923,
602365.433 3907739.343, 602331.037 3907750.72, 602307.225 3907752.836, 602254.308 3907736.697,
602243.989 3907739.607, 602225.997 3907763.949, 602222.029 3907776.517, 602222.029 3907776.517,
602216.737 3907809.722, 602215.149 3907830.889, 602212.768 3907851.262, 602207.609 3907875.206,
602207.344 3907897.432, 602211.577 3907938.442, 602215.811 3908017.023, 602214.752 3908026.019,
602216.605 3908033.692, 602228.246 3908067.824, 602239.888 3908104.072, 602251.794 3908132.382)',
4121));

INSERT INTO public.roads VALUES ('Κ1ΞΥΛ','Ξυλούρη Μενέλαου',
St_GeomFromText('LINESTRING(602252.191 3908131.853,
602231.024 3908149.051,
602210.651 3908162.28,
602192.924 3908174.98,
602179.166 3908188.209,
602163.555 3908205.936,
602138.949 3908226.838,
602123.603 3908253.032,
602114.872 3908280.02,
602105.214 3908317.194,
602106.934 3908346.166,
602101.113 3908380.297,
602100.32 3908400.67,
602096.086 3908444.326,
602087.355 3908478.458,
602083.915 3908502.535,
602084.445 3908535.343)',
4121));

INSERT INTO public.roads VALUES ('Κ1ΠΡΛΕΘΝ','Παράλληλος Εθνικής',
St_GeomFromText('LINESTRING(602084.445 3908535.079,
602065.395 3908547.249,
601990.782 3908570.797,
601934.69 3908594.081,
601890.505 3908615.247,
601838.382 3908643.558,
601800.546 3908666.048,
601767.209 3908689.86,
601708.736 3908726.637,
601655.819 3908761.298,
601632.535 3908780.877,
601609.252 3908793.842,
601588.35 3908793.312,
601539.402 3908804.689,
601515.06 3908810.775,
601493.893 3908813.421,
601472.198 3908808.394,
601457.116 3908815.802,
601440.977 3908828.502,
601427.747 3908840.673,
601409.756 3908851.785,
601355.516 3908872.423,
601280.903 3908893.06,
601217.139 3908900.469,
601166.868 3908909.2,
601162.899 3908972.7,
601162.105 3909001.804,
601167.397 3909025.088,
601174.012 3909039.375,
601187.77 3909046.255,
601200.205 3909045.99,
601288.047 3908996.513,
601327.47 3908981.432)',
4121));

INSERT INTO public.roads VALUES ('ΕΘΝIKH','Εθνική οδός',
St_GeomFromText('LINESTRING(605174.916 3910038.179,
605170.948 3910052.996,
605168.302 3910083.159,
605162.746 3910095.859,
605153.485 3910103.532,
605137.875 3910107.765,
605117.237 3910114.115,
605104.008 3910121.788,
605097.393 3910133.694,
605085.487 3910162.798,
605077.814 3910171.001,
605066.966 3910176.028,
605036.274 3910181.319,
605009.022 3910168.619,
604893.928 3910097.182,
604778.57 3910000.079,
604679.086 3909888.69,
604635.694 3909817.517,
604589.921 3909734.305,
604453.661 3909426.726,
604399.421 3909303.695,
604342.536 3909209.767,
604292.265 3909146.267,
604215.006 3909074.83,
604158.65 3909035.803,
604052.155 3908985.533,
603591.117 3908847.287,
602854.913 3908612.469,
602666.397 3908556.907,
602489.126 3908518.542,
602307.886 3908513.25,
602104.818 3908553.599,
601996.338 3908588.657,
601917.624 3908625.699,
601791.286 3908702.428,
601367.951 3908967.673,
601326.941 3908982.225)',
4121));

INSERT INTO public.roads VALUES ('ΠΕΡΗΡΚΒΙΑΝ','Περιφερειακή Ηράκλειο Βιάνος',
St_GeomFromText('LINESTRING(605174.916 3910038.312,
605173.064 3910013.97,
605174.387 3909981.162,
605186.293 3909936.976,
605204.153 3909908.401,
605221.748 3909888.822,
605250.72 3909865.538,
605355.495 3909814.474,
605398.093 3909780.475,
605431.827 3909732.85,
605446.379 3909677.287,
605454.978 3909562.855,
605481.437 3909490.094,
605599.177 3909281.073,
605632.25 3909101.156,
605687.812 3908999.291,
605796.292 3908900.072,
605858.469 3908811.436,
605903.448 3908676.499,
605988.115 3908544.207,
606146.865 3908394.717,
606210.101 3908339.022,
606261.033 3908313.886,
606305.616 3908303.435,
606584.487 3908306.61,
606664.127 3908302.112,
606762.42 3908271.553,
606864.946 3908214.006,
606921.17 3908164.397,
606974.748 3908089.652,
607001.471 3908037.529,
607025.019 3907975.219,
607037.19 3907914.497,
607041.159 3907829.83,
607022.373 3907727.172,
606994.592 3907657.718,
606966.149 3907610.755,
606804.092 3907368.661,
606758.451 3907274.072,
606727.362 3907174.853,
606708.18 3907074.311,
606704.873 3906987.66,
606704.873 3906916.884,
606716.117 3906848.092,
606733.315 3906754.165,
606776.972 3906582.846,
606902.649 3906069.554,
606970.118 3905857.887,
607074.629 3905535.756,
607120.269 3905354.516,
607151.622 3905210.45,
607159.957 3905089.271,
607164.19 3904740.02,
607167.894 3904730.495,
607179.536 3904725.203,
607207.317 3904729.701)',
4121));

INSERT INTO public.roads VALUES ('ΣΚΑΛΑΝΙ','ΔΗΜΟΤΙΚΟ ΔΙΑΜΕΡΙΣΜΑ ΣΚΑΛΑΝΙ',
St_GeomFromText('LINESTRING(607207.582 3904729.569,
607254.942 3904734.464,
607328.497 3904735.522,
607442.4 3904712.9,
607543.603 3904671.757,
607592.551 3904653.501,
607603.399 3904641.066,
607613.983 3904628.101,
607654.199 3904572.274,
607685.42 3904501.365,
607788.343 3904357.167,
607830.412 3904275.675,
607880.154 3904189.685,
607902.379 3904169.842,
607908.994 3904167.725)',
4121));

INSERT INTO public.roads VALUES ('ΑΠΟΣΑΜΗ','ΑΠΟΣΑΜΙ ΣΚΑΛΑΝΙ',
St_GeomFromText('LINESTRING(607908.729 3904167.725,
607938.363 3904189.95,
607943.522 3904192.199,
607972.362 3904187.833,
607989.295 3904187.304,
608009.271 3904194.183,
608025.808 3904207.677,
608052.001 3904249.217,
608060.865 3904275.278,
608071.713 3904293.138,
608087.191 3904315.76,
608078.063 3904330.047,
608071.051 3904347.51,
608061.129 3904359.813,
608039.83 3904372.513,
608015.886 3904381.773,
608001.069 3904391.96,
607995.38 3904404.924,
607992.735 3904415.243,
607993.264 3904440.908,
607972.494 3904494.354,
607978.315 3904514.727,
607958.736 3904558.118,
607942.993 3904607.728,
607926.456 3904642.124,
607914.021 3904657.073,
607899.733 3904666.333,
607850.785 3904716.604,
607832.794 3904730.363,
607782.523 3904775.342,
607770.087 3904768.198,
607733.575 3904795.715,
607699.972 3904767.933,
607604.722 3904696.231,
607575.618 3904686.442,
607538.312 3904688.558,
607500.212 3904693.056)',
4121));

INSERT INTO public.aktes VALUES ('ΑΚΤΒΡΗΡΑΚ','Βόρειες Ακτές Νομού Ηρακλείου',
St_GeomFromText('LINESTRING(592634.303 3919508.297,
592607.844 3919217.255,
592422.636 3918873.296,
592634.303 3918714.546,
592845.97 3918582.254,
592581.386 3918291.212,
592786.438 3918132.461,
593196.543 3918132.461,
593474.357 3917920.794,
593772.013 3918158.92,
593957.222 3917814.961,
594334.254 3917814.961,
594413.629 3918291.212,
594750.974 3918344.128,
594962.641 3918000.169,
594698.057 3917444.543,
594750.974 3917179.959,
595147.849 3917179.959,
595313.214 3916862.459,
595247.068 3916690.479,
594962.641 3916571.416,
594486.39 3916306.833,
594493.004 3915923.186,
594215.191 3915341.102,
594387.171 3915036.83,
594307.796 3914732.559,
594433.473 3914031.411,
594539.307 3913634.536,
594433.473 3912999.534,
594777.432 3912761.409,
594777.432 3912338.075,
595068.474 3912020.574,
595200.766 3911359.114,
596444.31 3911041.614,
597343.896 3911015.155,
598005.355 3910856.405,
599513.483 3910882.863,
600757.027 3911041.614,
601074.528 3911385.573,
602476.822 3911438.49,
603244.116 3911835.365,
603852.659 3912311.616,
604831.619 3912602.659,
605076.359 3912377.762,
604825.004 3912298.387,
604831.619 3911941.199,
605565.839 3911795.678,
606015.632 3911808.907,
606610.945 3911927.97,
606954.904 3911835.365,
607682.51 3911875.053,
607927.25 3911359.114,
608377.043 3911438.49,
608396.886 3911266.51,
608317.511 3910737.342,
608304.282 3910552.134,
608701.158 3910419.842,
609243.555 3910472.758,
609150.95 3910724.113,
609322.93 3910869.634,
609547.826 3910750.572,
609561.055 3910512.446,
609759.493 3910472.758,
610037.306 3910512.446,
610169.598 3910631.509,
610487.099 3910737.342,
610619.391 3910618.28,
611095.642 3910763.801,
611413.142 3910671.196,
611399.913 3910419.842,
611942.31 3910485.988,
612074.602 3910234.633,
612656.687 3910459.529,
612921.27 3910763.801,
613146.167 3910565.363,
613344.605 3910552.134,
613622.418 3910459.529,
614045.752 3910512.446,
614257.419 3910419.842,
614495.544 3910538.904,
614879.191 3910591.821,
615620.026 3910737.342,
615871.38 3910909.322,
616056.589 3910843.176,
616228.569 3910922.551,
616466.694 3910935.78,
616651.903 3910618.28,
617670.551 3910737.342,
617842.53 3911001.926,
618160.031 3910975.468,
618537.063 3911173.906)',
4121));