CREATE DATABASE geodb;
------------------------------
CREATE EXTENSION postgis;
------------------------------
CREATE TABLE locations(
location_id INTEGER PRIMARY KEY,
location_name VARCHAR(30) );
------------------------------
SELECT AddGeometryColumn('locations', 'pos', 2100, 'POINT', 2);
------------------------------
CREATE TABLE locations(
location_id INTEGER PRIMARY KEY,
location_name VARCHAR(30),
pos geometry(POINT,2100));
------------------------------
ALTER TABLE locations
ADD CONSTRAINT location_valid_check CHECK (ST_isvalid(pos));
------------------------------------
CREATE INDEX locations_idx ON locations USING GIST (pos);
------------------------------------
CREATE TABLE links(
link_id INTEGER PRIMARY KEY,
roadname_gr VARCHAR(50),
roadname_lt VARCHAR(50),
weight FLOAT );
--------------------------------
SELECT AddGeometryColumn('links', 'segment', 2100, 'LINESTRING', 2);
----------------------------------------------
ALTER TABLE links
ADD CONSTRAINT segment_valid_check CHECK (ST_isvalid(segment));
-----------------------------------------
CREATE INDEX links_idx ON links USING GIST (segment);
-----------------------------------------
CREATE TABLE zones(
zone_id INTEGER PRIMARY KEY,
zone_name VARCHAR(30) );
-----------------------------------------
SELECT AddGeometryColumn('zones', 'sector', 2100, 'POLYGON', 2);
-----------------------------------------
ALTER TABLE zones
ADD CONSTRAINT sector_valid_check CHECK (ST_isvalid(sector));
-----------------------------------------
INSERT INTO locations VALUES (301,'ΣΥΝΤΑΓΜΑ',
ST_GeomFromText('POINT(476654 4202904)', 2100));
INSERT INTO locations VALUES (302,'ΟΜΟΝΟΙΑ',
ST_GeomFromText('POINT(476021 4203771)', 2100));
INSERT INTO locations VALUES (303,'ΑΜΠΕΛΟΚΗΠΟΙ',
ST_GeomFromText('POINT(478878 4204071)', 2100));
INSERT INTO locations VALUES (304,'ΦΙΞ',
ST_GeomFromText('POINT(475687 4201381)', 2100));
INSERT INTO locations VALUES (305,'ΣΤΑΔΙΟ ΚΑΡΑΪΣΚΑΚΗ',
ST_GeomFromText('POINT(470229 4199447)', 2100));
INSERT INTO locations VALUES (306,'ΟΛΥΜΠΙΑΚΟ ΣΤΑΔΙΟ',
ST_GeomFromText('POINT(481094 4208546)', 2100));
INSERT INTO locations VALUES (307,'ΕΥΑΓΓΕΛΙΣΜΟΣ',
ST_GeomFromText('POINT(477963 4203061)', 2100));
-----------------------------------------
INSERT INTO links VALUES (101,'ΑΛΕΞΑΝΔΡΑΣ ΛΕΩΦ.','ALEXANDRAS',18.25, ST_GeomFromText('LINESTRING( 476259 4204638 , 478895 4204061)', 2100));
INSERT INTO links VALUES (102,'ΒΑΣ. ΣΟΦΙΑΣ ΛΕΩΦ.','VAS. SOFIAS',24.69, ST_GeomFromText('LINESTRING( 476638 4202908 , 477354 4202809 , 477997 4203023 , 478895 4204061)', 2100));
INSERT INTO links VALUES (103,'ΠΑΝΕΠΙΣΤΗΜΙΟΥ','PANEPISTIMIOU',12.87, ST_GeomFromText('LINESTRING( 476638 4202908 , 476152 4203748 , 475995 4203798)', 2100));
INSERT INTO links VALUES (104,'ΠΑΤΗΣΙΩΝ','PATISSION',18.3, ST_GeomFromText('LINESTRING( 475995 4203798 , 476259 4204638)', 2100));
INSERT INTO links VALUES (105,'ΑΜΑΛΙΑΣ ΛΕΩΦ.','AMALIAS',26.86, ST_GeomFromText('LINESTRING( 476638 4202908 , 476598 4202603 , 476269 4202197 , 476250 4202037)', 2100));
INSERT INTO links VALUES (106,'ΣΥΓΓΡΟΥ ΛΕΩΦ.','SYNGROU',62.34, ST_GeomFromText('LINESTRING( 476250 4202037 , 472967 4198790)', 2100));
INSERT INTO links VALUES (107,'ΚΗΦΙΣΙΑΣ ΛΕΩΦ.','KIFISSIAS',29.51, ST_GeomFromText('LINESTRING( 478895 4204061 , 479267 4204500 , 479641 4205387 , 481368 4208164)', 2100));
INSERT INTO links VALUES (108,'ΠΑΤΗΣΙΩΝ','PATISSION',13.67, ST_GeomFromText('LINESTRING( 476259 4204638 , 476560 4206110 , 476677 4208157 , 476330 4208395 , 476072 4208960)', 2100));
INSERT INTO links VALUES (109,'ΠΟΣΕΙΔΩΝΟΣ ΛΕΩΦ.','POSEIDONOS',56.8, ST_GeomFromText('LINESTRING( 472967 4198790 , 472674 4198735 , 471857 4199295 , 470188 4199388 , 469418 4199867)', 2100));
INSERT INTO links VALUES (110,'ΒΟΥΛΙΑΓΜΕΝΗΣ ΛΕΩΦ.','VOULIAGMENIS', 51.72, ST_GeomFromText('LINESTRING( 476250 4202037 , 476437 4201916 , 476546 4200848 , 477056 4199017 , 477407 4198197)', 2100));
INSERT INTO links VALUES (111,'ΑΓ. ΚΩΝΣΤΑΝΤΙΝΟΥ','AG. KONSTANTINOU', 14.5, ST_GeomFromText('LINESTRING( 475995 4203798 , 475346 4203934 , 474764 4203806)', 2100));
INSERT INTO links VALUES (112,'ΙΕΡΑ ΟΔΟΣ','IERA ODOS',34.85,
ST_GeomFromText('LINESTRING( 474764 4203806, 474314 4204006 , 473014 4205166)', 2100));
INSERT INTO links VALUES (113,'ΠΕΙΡΑΙΩΣ','PIREOS',29.92, ST_GeomFromText('LINESTRING( 475995 4203798 , 474926 4203181 , 473432 4202084, 472569 4201162 , 471193 4200596)', 2100));
INSERT INTO links VALUES (114,'ΠΕΙΡΑΙΩΣ','PIREOS',25.61, ST_GeomFromText('LINESTRING( 471193 4200596 , 469418 4199867)', 2100));
INSERT INTO links VALUES (115,'ΚΗΦΙΣΟΥ ΛΕΩΦ.','KIFISSOU',45.82, ST_GeomFromText('LINESTRING( 471193 4200596 , 471592 4201833 , 471313 4202478 , 472323 4204393 , 473014 4205166)', 2100));
INSERT INTO links VALUES (116,'ΚΗΦΙΣΟΥ ΛΕΩΦ.','KIFISSOU',39.63, ST_GeomFromText('LINESTRING( 473014 4205166 , 473991 4205758 , 475051 4206700 , 475170 4207275 , 476072 4208960)', 2100));
INSERT INTO links VALUES (117, 'ΜΕΣΟΓΕΙΩΝ', 'MESOGEION', 41.76, ST_GeomFromText('LINESTRING( 478895 4204061 , 479161 4203999 , 479518 4204319 , 480144 4204811 , 481909 4206175 , 484065 4207065)', 2100));
-----------------------------------------
INSERT INTO zones VALUES (501, 'ΑΘΗΝΑ', ST_GeomFromText('POLYGON(( 473569 4202148 , 474778 4201575 , 474825 4201525 , 475240 4201082 , 474794 4200604 , 475644 4200030 , 478264 4201782 , 478582 4203294 , 481256 4203453 , 481511 4204933 , 479426 4205618 , 477229 4206652 , 478216 4208546 , 476688 4208753 , 475176 4207305 , 475065 4206668 , 473091 4205172 , 472423 4203103 , 473569 4202148 ))', 2100));
INSERT INTO zones VALUES (502, 'ΠΕΙΡΑΙΑΣ', ST_GeomFromText('POLYGON(( 471076 4199236 , 471214 4200607 , 469779 4201255 , 467282 4201563 , 467133 4200192 , 465815 4199650 , 466591 4198927 , 467611 4199129 , 467930 4199841 , 468377 4199863 , 468302 4199225 , 468005 4198683 , 467314 4198874 , 466602 4198534 , 467165 4197780 , 467867 4197376 , 468876 4197843 , 468759 4198534 , 469004 4198821 , 469131 4198279 , 469790 4198417 , 470279 4198927 , 470353 4199310 , 471076 4199236 ))', 2100));
INSERT INTO zones VALUES (503, 'ΜΑΡΟΥΣΙ', ST_GeomFromText('POLYGON(( 481370 4208169 , 481865 4209231 , 484259 4209302 , 485009 4210321 , 484882 4212389 , 482517 4213012 , 481696 4211100 , 481002 4211171 , 480166 4210180 , 480662 4208820 , 481370 4208169 ))', 2100));
INSERT INTO zones VALUES (504, 'ΠΕΡΙΣΤΕΡΙ', ST_GeomFromText('POLYGON(( 472852 4205012 , 473091 4205172 , 475065 4206668 , 475176 4207305 , 474036 4207759 , 472534 4208716 , 470864 4209475 , 470424 4208412 , 471228 4207198 , 471000 4206393 , 472852 4205012 ))', 2100));
INSERT INTO zones VALUES (505, 'ΝΕΑ ΣΜΥΡΝΗ', ST_GeomFromText('POLYGON(( 473437 4199247 , 474141 4198830 , 473993 4198470 , 474818 4197856 , 475453 4198957 , 476003 4199327 , 475644 4200030 , 474794 4200604 , 473437 4199247 ))', 2100));
INSERT INTO zones VALUES (506, 'ΚΑΛΛΙΘΕΑ', ST_GeomFromText('POLYGON(( 472678 4198742 , 472971 4198796 , 473437 4199247 , 474794 4200604 , 475240 4201082 , 474825 4201525 , 474491 4201444 , 474020 4201570 , 473001 4201386 , 473043 4200819 , 472363 4200196 , 471939 4199262 , 472462 4198894 , 472533 4198844 , 472678 4198742 ))', 2100));
-----------------------------------------
 shp2pgsql -s 2100 -t 2D C:\services\2f483c11-298e-4875-b538-ae8ebbd9c0a6.shp services > C:\services\services.sql
---------------------------------------------
CREATE TABLE parks (
gid serial PRIMARY KEY,
id int4,
name varchar(30));
---------------------------------------------
SELECT AddGeometryColumn('parks','the_geom','2100','MULTIPOLYGON',2);
---------------------------------------------
INSERT INTO "parks" ("id","name",the_geom) VALUES ('201','ΠΕΔΙΟΝ ΑΡΕΩΣ','SRID=2100;01060000000100000001030000000100000007000000315E0A778D111D41120FC0AB170A5041E01F606BF0121D4165724CA0840A5041528DC4E3BA171D41ECFFEECE750A50418C12F1D67E251D41983DBBF4A40A50413AA39398402A1D41D378DFF3830A50412C1E71FCE7271D41C5F1C253C9095041315E0A778D111D41120FC0AB170A5041');
INSERT INTO "parks" ("id","name",the_geom) VALUES ('202','ΕΘΝΙΚΟΣ ΚΗΠΟΣ','SRID=2100;01060000000100000001030000000100000007000000E384BCDE77171D4142784E02670850418BCA643EAA221D41689DD34C4E085041BE0121B0E41F1D41B9E4D959DC075041F7D3229D821A1D41B6859987A2075041F60E2BA05C131D41933AAABED50750411D24C1D4DA161D41695E4FC61A085041E384BCDE77171D4142784E0267085041');
INSERT INTO "parks" ("id","name",the_geom) VALUES
('203','ΙΠΠΟΔΡΟΜΟΣ','SRID=2100;01060000000100000001030000000100000007000000B32AFB39B4E51C41A2426B1ADA04504189E71C1118DE1C419546DDA061045041BC3BCE8683D91C4136762A0454045041E8E678001FD61C41146D080D79045041A81F120075DD1C41C307C31A1C055041B844CD209BE11C411FDD794A1F055041B32AFB39B4E51C41A2426B1ADA045041');
INSERT INTO "parks" ("id","name",the_geom) VALUES ('204','ΒΟΤΑΝΙΚΟΣ
ΚΗΠΟΣ','SRID=2100;01060000000100000001030000000100000006000000B5BB80E64CE31C41547E23775A0A504165FC9AD992F01C41581EB52A9E095041C1B8B80027EC1C41BDCEB5D140095041BAC181BFA2E61C4154091F3A6F095041A1C2848257DC1C41636602EFB8095041B5BB80E64CE31C41547E23775A0A5041');
---------------------------------------------
---------------------------------------------
--Ερώτημα σημείου (point query):
--Βρες τις ζώνες που περιλαμβάνουν μια συγκεκριμένη θέση ενδιαφέροντος
-- https://postgis.net/docs/ST_Within.html
SELECT zone_id, zone_name
FROM zones
WHERE ST_WITHIN( ST_GeomFromText('POINT(482000 4210000)',2100), sector);
---------------------------------------------
---------------------------------------------
--Ερώτημα απόστασης από σημείο (distance query):
--Βρες τα σημεία ενδιαφέροντος βρίσκονται εντός απόστασης 200 μέτρων από συγκεκριμένη θέση
-- https://postgis.net/docs/ST_Distance.html
SELECT location_id,
ST_DISTANCE(pos, ST_GeomFromText('POINT(476500 4202800)',2100)) AS dist
FROM locations
WHERE ST_DISTANCE(pos, ST_GeomFromText('POINT(476500 4202800)',2100))<=200;
---------------------------------------------
---------------------------------------------
-- Ερώτημα περιοχής (range query):
--Βρες ποια σημεία τέμνουν συγκεκριμένη πολυγωνική περιοχή ενδιαφέροντος:
-- https://postgis.net/docs/ST_Intersects.html
SELECT location_id, location_name
FROM locations
WHERE ST_INTERSECTS(pos, ST_GeomFromText('POLYGON( (475000 4202000, 480000 4202000, 480000 4206000 , 475000 4206000 , 475000 4202000 ))',2100) );
---------------------------------------------
---------------------------------------------
-- Ερώτημα εγκλεισμού (containment query):
--Βρες ποια σημεία ενδιαφέροντος βρίσκονται εντός του δήμου Αθηναίων:
SELECT location_id, location_name
FROM locations
WHERE ST_WITHIN(pos, (SELECT sector FROM zones
WHERE zone_name='ΑΘΗΝΑ') );
---------------------------------------------
---------------------------------------------
-- Ερώτημα περίφραξης (enclosure query):
--Βρες ποια ζώνη περικλείει το κτίριο ΦΙΞ
-- https://postgis.net/docs/ST_Contains.html
SELECT zone_name
FROM zones
WHERE ST_CONTAINS( sector, (SELECT pos FROM locations
WHERE location_name='ΦΙΞ'));
---------------------------------------------
---------------------------------------------
--Ερωτήματα χωρικής σύνδεσης
--με εγκλεισμό (spatial containment join)
--Βρες τα σημεία ενδιαφέροντος και τις ζώνες όπου ανήκουν
SELECT location_id, location_name, zone_id, zone_name
FROM locations, zones
WHERE ST_WITHIN(pos, sector);
---------------------------------------------
--με περίφραξη (spatial enclosure join)
--Βρες τα σημεία ενδιαφέροντος και τις ζώνες που τα περικλείουν
SELECT location_id, location_name, zone_id, zone_name
FROM locations, zones
WHERE ST_CONTAINS(sector, pos);
---------------------------------------------
--με τομή (spatial intersection join)
--Βρες από ποιες ζώνες περνά κάθε δρόμος
SELECT roadname_gr, zone_name
FROM links, zones
WHERE ST_INTERSECTS(segment, sector);
---------------------------------------------
-- με επικάλυψη (spatial overlap join)
--Βρες σε ποιες ζώνες υπάρχει επικάλυψη με πάρκα
-- https://postgis.net/docs/ST_Overlaps.html
SELECT name, zone_name
FROM parks, zones
WHERE ST_OVERLAPS(the_geom, sector);
---------------------------------------------
---------------------------------------------
-- Ερώτημα χωρικής (αυτο-)σύνδεσης:
-- με βάση την απόσταση (distance self-join):
-- Βρες όλα τα ζεύγη σημείων που απέχουν μεταξύ τους απόσταση μικρότερη από 2000 μέτρα
SELECT L1.location_name AS loc1, L2.location_name AS loc2,
ST_DISTANCE(L1.pos, L2.pos) AS dist
FROM locations L1, locations L2
WHERE ST_DISTANCE(L1.pos, L2.pos)<=2000
AND L1.location_id < L2.location_id;
---------------------------------------------
-- με βάση την γειτνίαση (adjacency self-join):
--Βρες όλα τα ζεύγη ζωνών που γειτνιάζουν
-- https://postgis.net/docs/ST_Touches.html
SELECT Z1.zone_name, Z2.zone_name
FROM zones Z1, zones Z2
WHERE ST_TOUCHES(Z1.sector, Z2.sector)
AND Z1.zone_id < Z2.zone_id;
---------------------------------------------
---------------------------------------------
-- Γεωμετρικές συναρτήσεις.
-- Εμβαδόν πολυγωνικής περιοχής
-- https://postgis.net/docs/ST_Area.html
SELECT zone_name, ST_area(sector) AS calc_area
FROM zones
ORDER BY calc_area DESC;
---------------------------------------------
-- Μήκος γραμμικών αξόνων
-- https://postgis.net/docs/ST_Length.html
SELECT roadname_gr, SUM(ST_length(segment)) AS calc_length
FROM links
GROUP by roadname_gr
ORDER BY calc_length DESC;
---------------------------------------------
-- Πλήθος κορυφών ανά πολύγωνο
-- https://postgis.net/docs/ST_NPoints.html
SELECT zone_id, zone_name, ST_npoints(sector) AS vertex_count
FROM zones;
---------------------------------------------
-- Ελάχιστα περιβάλλοντα παραλληλόγραμμα για τις περιοχές
--Μετατροπή σε μορφή κειμένου
-- https://postgis.net/docs/ST_Envelope.html
SELECT zone_name, ST_AsText(ST_envelope(sector)) AS MBBText
FROM zones;
--Εναλλακτικά, μετατροπή σε GML, χρησιμοποιώντας 10 ψηφία για τις συντεταγμένες
SELECT zone_name, ST_AsGml(ST_envelope(sector), 10) AS MBBGml
FROM network.zones;
--Εναλλακτικά, μετατροπή σε KML, χρησιμοποιώντας 10 ψηφία για τις συντεταγμένες
SELECT zone_name, ST_AsKml(ST_envelope(sector), 10) AS MBBGml
FROM network.zones;
---------------------------------------------
---------------------------------------------
-- Εύρεση γεωμετριών εντός αποστάσεως.
-- Όλες οι σημειακές θέσεις που βρίσκονται εντός Ευκλείδειας αποστάσεως 1000 μέτρων από συγκεκριμένες συντεταγμένες:
-- https://postgis.net/docs/ST_DWithin.html
SELECT location_name, ST_AsText(pos) AS loc
FROM locations
WHERE ST_DWITHIN(pos, (ST_GeometryFromText('SRID=2100;POINT(476000 4204000)')), 1000);
---------------------------------------------
---------------------------------------------
-- Μετασχηματισμοί σε άλλο σύστημα γεωαναφοράς.
-- Από το ΕΓΣΑ87 (όπου είναι ορισμένα τα στοιχεία του πίνακα) στο WGS84 (κωδικός: 4326)
-- https://postgis.net/docs/ST_Transform.html
SELECT ST_AsText(pos), ST_AsText(ST_Transform(pos,4326)) AS loc_wgs84
FROM locations;

---------------------------------
CREATE TABLE locwgs(
location_id INTEGER PRIMARY KEY,
location_name VARCHAR(30) );
------------------------------------
SELECT AddGeometryColumn('', 'locwgs','pos',4326,'POINT',2);
ALTER TABLE locwgs ADD CONSTRAINT locwgs_valid_check CHECK (ST_isvalid(pos));
CREATE INDEX locwgs_idx ON locwgs USING GIST (pos);
-----------------------------------
INSERT INTO locwgs VALUES (301,'SYNTAGMA',ST_GeomFromText('POINT(23.7358 37.976)',4326));
INSERT INTO locwgs VALUES (302,'OMONOIA',ST_GeomFromText('POINT(23.7286 37.9838)',4326));
INSERT INTO locwgs VALUES (303,'AMPELOKIPOI',ST_GeomFromText('POINT(23.7611 37.9866)',4326));
-----------------------------------
-- Τοπολογικός έλεγχος μεταξύ γεωμετριών , αλλά τα στοιχεία είναι σε διαφορετικά συστήματα γεωαναφοράς. Παρατηρήστε ότι ο πίνακας locwgs είναι σε WGS84, οπότε οι γεωμετρίες του πρέπει να μετασχηματιστούν ειδικά για τον έλεγχο:
SELECT location_id, location_name, zone_id, zone_name
FROM locwgs, zones
WHERE ST_CONTAINS(sector, ST_SetSRID(ST_Transform(pos,2100), 2100));
---------------------------------------------
---------------------------------------------
DROP TABLE locwgs CASCADE;
DROP TABLE locations CASCADE;
DROP TABLE links CASCADE;
DROP TABLE zones CASCADE;
DROP TABLE parks CASCADE;