· 7 years ago · Oct 24, 2018, 05:36 AM
1
2
3-- DROP SCHEMA IF EXISTS cw3 CASCADE;
4-- CREATE SCHEMA cw3;
5-- SET search_path TO cw3;
6--
7-- CREATE EXTENSION IF NOT EXISTS postgis;
8
9DROP TABLE IF EXISTS budynki259913;
10DROP TABLE IF EXISTS drogi259913;
11DROP TABLE IF EXISTS pktinfo259913;
12
13CREATE TABLE budynki259913 (
14 id INT,
15 geometria GEOMETRY(POLYGON, 0),
16 nazwa TEXT,
17 wysokosc NUMERIC
18);
19
20CREATE TABLE drogi259913 (
21 id INT,
22 geometria GEOMETRY(LINESTRING, 0),
23 nazwa TEXT
24);
25
26CREATE TABLE pktinfo259913 (
27 id INT,
28 geometria GEOMETRY(POINT, 0),
29 nazwa TEXT,
30 liczprac NUMERIC
31);
32
33INSERT INTO drogi259913 VALUES(1, ST_GeomFromText('LINESTRING(7.5 10.5, 7.5 0)', 0), 'RoadY');
34INSERT INTO drogi259913 VALUES(2, ST_GeomFromText('LINESTRING(0 4.5, 12 4.5)', 0), 'RoadX');
35
36INSERT INTO budynki259913 VALUES(1, ST_GeomFromText('POLYGON((8 4, 10.5 4, 10.5 1.5, 8 1.5, 8 4))', 0), 'BuildingA', 50);
37INSERT INTO budynki259913 VALUES(2, ST_GeomFromText('POLYGON((4 7, 6 7, 6 5, 4 5, 4 7))', 0), 'BuildingB', 150);
38INSERT INTO budynki259913 VALUES(3, ST_GeomFromText('POLYGON((3 8, 5 8, 5 6, 3 6, 3 8))', 0), 'BuildingC', 30);
39INSERT INTO budynki259913 VALUES(4, ST_GeomFromText('POLYGON((9 9, 10 9, 10 8, 9 8, 9 9))', 0), 'BuildingD', 20);
40INSERT INTO budynki259913 VALUES(5, ST_GeomFromText('POLYGON((1 2, 2 2, 2 1, 1 1, 1 2))', 0), 'BuildingF', 500);
41
42INSERT INTO pktinfo259913 VALUES(1, ST_GeomFromText('POINT(1 3.5)', 0), 'G', 3);
43INSERT INTO pktinfo259913 VALUES(2, ST_GeomFromText('POINT(5.5 1.5)', 0), 'H', 2);
44INSERT INTO pktinfo259913 VALUES(3, ST_GeomFromText('POINT(9.5 6)', 0), 'I', 1);
45INSERT INTO pktinfo259913 VALUES(4, ST_GeomFromText('POINT(6.5 6)', 0), 'J', 5);
46INSERT INTO pktinfo259913 VALUES(5, ST_GeomFromText('POINT(6 9.5)', 0), 'K', 3);
47
48--2
49SELECT SUM(ST_LENGTH(geometria)) AS dlugosc_drog FROM drogi259913;
50
51--3
52SELECT ST_AsText(geometria), ST_Area(geometria), ST_Perimeter(geometria)
53FROM budynki259913
54WHERE nazwa = 'BuildingA';
55
56--4
57SELECT nazwa, ST_Area(geometria)
58FROM budynki259913
59ORDER BY nazwa ASC;
60
61--5
62SELECT nazwa, ST_Area(geometria) AS ppow
63FROM budynki259913
64ORDER BY ppow DESC
65LIMIT 2;
66
67--6
68SELECT ST_Distance(budynki259913.geometria, pktinfo259913.geometria)
69FROM budynki259913, pktinfo259913
70WHERE budynki259913.nazwa = 'BuildingC' AND pktinfo259913.nazwa = 'G';
71
72--7
73SELECT ST_Area(ST_Difference(geometria, (SELECT ST_Buffer(geometria, 0.5) FROM budynki259913 WHERE nazwa = 'BuildingB')))
74FROM budynki259913
75WHERE nazwa = 'BuildingC';
76
77--8
78UPDATE budynki259913 SET nazwa = nazwa || 'X'
79WHERE ST_Distance(geometria, ST_GeomFromText('POINT(0 0)', 0)) < 5;
80
81SELECT nazwa FROM budynki259913;
82
83--9
84DROP TABLE IF EXISTS odl259913;
85CREATE TABLE odl259913 (
86 id SERIAL PRIMARY KEY,
87 nazwa TEXT,
88 liczprac INT,
89 odleglosc NUMERIC
90);
91
92
93INSERT INTO odl259913(nazwa, liczprac, odleglosc) SELECT pktinfo259913.nazwa, liczprac, ST_Distance(pktinfo259913.geometria, budynki259913.geometria) AS odl
94FROM pktinfo259913, budynki259913
95WHERE budynki259913.nazwa = 'BuildingA'
96ORDER BY odl DESC;
97
98SELECT * FROM odl259913;
99
100
101--10
102INSERT INTO drogi259913 VALUES(3, ST_GeomFromText('LINESTRING(1.5 3, 1 1)', 0), 'LineC');
103
104SELECT ST_Length(ST_Intersection(drogi259913.geometria, budynki259913.geometria))
105FROM budynki259913, drogi259913
106WHERE drogi259913.nazwa = 'LineC' AND budynki259913.nazwa = 'BuildingFX';
107
108--11
109SELECT ST_AREA(ST_SYMDIFFERENCE(geometria, ST_GeomFromText('POLYGON((4 7, 6 7, 6 8, 4 8, 4 7))', 0)))
110FROM budynki259913
111WHERE nazwa = 'BuildingC';
112
113--12
114UPDATE budynki259913
115SET geometria = ST_GeomFromText('POLYGON((8 4, 10.5 4, 10.5 1.5, 8 1.5, 8 4), (8.5 2,9 2,9 3,8.5 3,8.5 2))', 0)
116WHERE nazwa = 'BuildingA';
117
118DROP TABLE IF EXISTS poligon1259913;
119CREATE TABLE poligon1259913 (
120 geometria GEOMETRY(POLYGON, 0)
121);
122
123INSERT INTO poligon1259913 (SELECT geometria AS dd
124FROM budynki259913
125WHERE ST_NumInteriorRings(geometria) > 0);
126
127--13
128DROP TABLE IF EXISTS dzialka259913;
129CREATE TABLE dzialka259913 (
130 id INT,
131 nazwa TEXT,
132 geometria GEOMETRY(POLYGON, 0),
133 wlasciciel TEXT
134);
135
136INSERT INTO dzialka259913 VALUES (1, 'dzialka1', ST_GeomFromText('POLYGON((3 5, 7.5 5, 7.5 7.5, 3 7.5, 3 5))', 0), 'Kowalski');
137
138SELECT pktinfo259913.*
139FROM pktinfo259913, dzialka259913
140WHERE ST_Contains(dzialka259913.geometria, pktinfo259913.geometria);
141
142--14
143SELECT budynki259913.nazwa
144FROM budynki259913, drogi259913
145WHERE drogi259913.nazwa = 'RoadX' AND ST_Y(ST_Centroid(budynki259913.geometria)) > ST_Y(ST_Centroid(drogi259913.geometria));
146
147--15
148DROP TABLE IF EXISTS pktinfo_new259913;
149CREATE TABLE pktinfo_new259913 (
150 geometria GEOMETRY(POINT, 0),
151 nazwa TEXT,
152 odl NUMERIC
153);
154
155INSERT INTO pktinfo_new259913
156SELECT pktinfo259913.geometria, pktinfo259913.nazwa, ST_Distance(budynki259913.geometria, pktinfo259913.geometria)
157FROM pktinfo259913, budynki259913
158WHERE budynki259913.nazwa = 'BuildingD';
159
160SELECT * FROM pktinfo_new259913;
161
162DROP TABLE IF EXISTS gc259913;
163CREATE TABLE gc259913(
164 geometriaBud GEOMETRY(POLYGON, 0),
165 geometriaPkt GEOMETRY(POINT, 0)
166);
167
168INSERT INTO gc259913 VALUES (
169 (SELECT geometria FROM budynki259913 WHERE nazwa = 'BuildingD'),
170 (SELECT geometria FROM pktinfo_new259913 ORDER BY odl DESC LIMIT 1)
171);
172
173--16
174
175DELETE FROM pktinfo259913 WHERE nazwa = (SELECT pktinfo259913.nazwa
176FROM pktinfo259913, dzialka259913
177WHERE ST_Contains(dzialka259913.geometria, pktinfo259913.geometria));
178
179SELECT * FROM pktinfo259913;
180
181--17
182
183WITH obszar AS ( SELECT geometria FROM budynki259913 ORDER BY ST_Perimeter(geometria) DESC LIMIT 1 )
184SELECT nazwa
185FROM pktinfo259913
186WHERE ST_Contains( ST_Buffer((SELECT * FROM obszar), 4), geometria );
187
188--18
189
190DROP TABLE IF EXISTS geom259913;
191CREATE TABLE geom259913 (
192 geometria GEOMETRY,
193 is_simple BOOLEAN
194);
195
196INSERT INTO geom259913 VALUES ( ST_GeomFromText('POLYGON((8 4, 10.5 4, 10.5 1.5, 8 1.5, 8 4), (8.5 2,9 2,9 3,8.5 3,8.5 2))', 0), ST_IsSimple( ST_GeomFromText('POLYGON((8 4, 10.5 4, 10.5 1.5, 8 1.5, 8 4), (8.5 2,9 2,9 3,8.5 3,8.5 2))' )));
197SELECT * FROM geom259913;