Project

General

Profile

Snippets » History » Version 42

Bernhard Koschiček-Krombholz, 2019-03-25 14:39

1 1 Alexander Watzinger
{{toc}}
2
3
Below are some unsorted database related code snippets used in development.
4
 
5
h1. Demo
6
7
Update SQL
8
<pre>
9
pg_dump openatlas_demo > /var/lib/postgresql/openatlas_demo.sql
10
pg_dump openatlas_demo_dev > /var/lib/postgresql/openatlas_demo_dev.sql
11
</pre>
12
Reset Demo
13
<pre>
14
/var/lib/postgresql/reset_demo.sh
15
</pre>
16
17
18
h1. Exports
19
20
h2. Database Structure
21
22
<pre>
23 31 Alexander Watzinger
pg_dump -sc --if-exists -n model -n gis -n log -n web -n import openatlas > install/1_structure.sql
24 1 Alexander Watzinger
</pre>
25
26
h2. Model Data
27
28
<pre>
29
pg_dump openatlas --inserts -a -t model.class -t model.class_i18n -t model.class_inheritance -t model.property -t model.property_i18n -t model.property_inheritance > data_model.sql
30
</pre> 
31
32
h2. Single Schema
33
34
<pre>
35
pg_dump -n web openatlas > /tmp/openatlas_web.sql
36
pg_dump -n model openatlas > /tmp/openatlas_web.sql
37
</pre>
38
39
h1. Recursive Events
40
41
To get all child events of a given event the SQL below (replace ROOT_EVENT_ID at bottom). It works but is slow and could be improved. 
42
43
<pre>
44
WITH RECURSIVE tree AS (
45
  SELECT e.id, ARRAY[]::INTEGER[] AS ancestors
46
  FROM model.entity e
47
  WHERE (SELECT s.id FROM model.entity s JOIN model.link l ON s.id = l.range_id AND l.domain_id = e.id AND l.property_id = (SELECT id FROM model.property WHERE code = 'P117')) IS NULL 
48
  UNION ALL 
49
  SELECT e.id, tree.ancestors || 
50
    (SELECT s.id FROM model.entity s JOIN model.link l ON s.id = l.range_id AND l.domain_id = e.id AND l.property_id = (SELECT id FROM model.property WHERE code = 'P117'))
51
  FROM model.entity e, tree
52
  WHERE (SELECT s.id FROM model.entity s JOIN model.link l ON s.id = l.range_id AND l.domain_id = e.id AND l.property_id = (SELECT id FROM model.property WHERE code = 'P117')) = tree.id
53
)
54
SELECT * FROM tree WHERE ROOT_EVENT_ID = ANY(tree.ancestors);
55
</pre>
56 2 Alexander Watzinger
57
h1. Person with residence
58
59 6 Alexander Watzinger
Select all persons who have a residence, appears first/last with point coordinates.
60 2 Alexander Watzinger
61 11 Bernhard Koschiček-Krombholz
h2. Point
62
63 19 Bernhard Koschiček-Krombholz
<pre>
64 16 Bernhard Koschiček-Krombholz
SELECT e.id, e.name, p.name, e.description, ST_X(g.geom) || ' ' || ST_Y(g.geom) AS coordinates
65 1 Alexander Watzinger
FROM model.entity e
66 11 Bernhard Koschiček-Krombholz
JOIN model.link l ON e.id = l.domain_id AND l.property_code IN ('P74', 'OA8', 'OA9')
67 2 Alexander Watzinger
JOIN model.entity p ON l.range_id = p.id
68 1 Alexander Watzinger
JOIN gis.point g ON p.id = g.entity_id
69 19 Bernhard Koschiček-Krombholz
WHERE e.class_code = 'E21';
70 1 Alexander Watzinger
</pre>
71 18 Bernhard Koschiček-Krombholz
72 19 Bernhard Koschiček-Krombholz
Added picture description and named the columns
73 17 Bernhard Koschiček-Krombholz
<pre>
74 29 Bernhard Koschiček-Krombholz
SELECT e.id, e.name, e.description AS entity_description, eb.id AS picture_id, eb.description AS picture_description,  p.name AS location_name, ST_X(g.geom) || ' ' || ST_Y(g.geom) AS coordinates
75 17 Bernhard Koschiček-Krombholz
FROM model.entity e
76
JOIN model.link l ON e.id = l.domain_id AND l.property_code IN ('P74', 'OA8', 'OA9')
77
JOIN model.entity p ON l.range_id = p.id
78
JOIN gis.point g ON p.id = g.entity_id
79
JOIN model.link lb ON e.id = lb.range_id AND lb.property_code IN ('P67') 
80
JOIN model.entity eb ON lb.domain_id = eb.id AND eb.system_type = 'file'
81 2 Alexander Watzinger
WHERE e.class_code = 'E21';
82 1 Alexander Watzinger
</pre>
83 3 Alexander Watzinger
84 39 Bernhard Koschiček-Krombholz
Added location description and complete url to file
85
<pre>
86 40 Bernhard Koschiček-Krombholz
SELECT e.id, e.name, e.description AS entity_description, eb.id AS picture_id, eb.description AS picture_description, 'https://orthodoxes-wien.oeaw.ac.at/repository/pic/' || eb.id AS pic_url, pn.name AS location_name, pn.description AS location_description, ST_X(g.geom) || ' ' || ST_Y(g.geom) AS coordinates
87 39 Bernhard Koschiček-Krombholz
FROM model.entity e
88
JOIN model.link l ON e.id = l.domain_id AND l.property_code IN ('P74', 'OA8', 'OA9')
89
JOIN model.entity p ON l.range_id = p.id
90
JOIN model.link pnl ON p.id = pnl.range_id AND pnl.property_code = 'P53'
91
JOIN model.entity pn ON pnl.domain_id = pn.id
92
JOIN gis.point g ON p.id = g.entity_id
93
JOIN model.link lb ON e.id = lb.range_id AND lb.property_code IN ('P67') 
94
JOIN model.entity eb ON lb.domain_id = eb.id AND eb.system_type = 'file'
95
WHERE e.class_code = 'E21';
96
</pre>
97
98 11 Bernhard Koschiček-Krombholz
h2. Polygon
99 4 Alexander Watzinger
100 1 Alexander Watzinger
<pre>
101 30 Bernhard Koschiček-Krombholz
SELECT e.id, e.name, p.name, e.description, ST_X(public.ST_PointOnSurface(geom)) || ' ' || ST_Y(public.ST_PointOnSurface(geom)) AS polygon_center_point
102 4 Alexander Watzinger
FROM model.entity e
103 11 Bernhard Koschiček-Krombholz
JOIN model.link l ON e.id = l.domain_id AND l.property_code IN ('P74', 'OA8', 'OA9')
104 4 Alexander Watzinger
JOIN model.entity p ON l.range_id = p.id
105
JOIN gis.polygon g ON p.id = g.entity_id
106
WHERE e.class_code = 'E21';
107
</pre>
108
109 20 Bernhard Koschiček-Krombholz
Added picture description and named the columns
110
<pre>
111 30 Bernhard Koschiček-Krombholz
SELECT e.id, e.name, e.description AS entity_description, eb.id AS picture_id, eb.description AS picture_description, p.name AS location_name, ST_X(public.ST_PointOnSurface(geom)) || ' ' || ST_Y(public.ST_PointOnSurface(geom)) AS polygon_center_point
112 20 Bernhard Koschiček-Krombholz
FROM model.entity e
113
JOIN model.link l ON e.id = l.domain_id AND l.property_code IN ('P74', 'OA8', 'OA9')
114
JOIN model.entity p ON l.range_id = p.id
115
JOIN model.link lb ON e.id = lb.range_id AND lb.property_code IN ('P67') 
116
JOIN model.entity eb ON lb.domain_id = eb.id AND eb.system_type = 'file'
117
JOIN gis.polygon g ON p.id = g.entity_id
118
WHERE e.class_code = 'E21';
119
</pre>
120
121 38 Bernhard Koschiček-Krombholz
Added location description and complete url to file
122
<pre>
123 41 Bernhard Koschiček-Krombholz
SELECT e.id, e.name, e.description AS entity_description, eb.id AS picture_id, eb.description AS picture_description, 'https://orthodoxes-wien.oeaw.ac.at/repository/pic/' || eb.id AS pic_url, pn.name AS location_name, pn.description AS location_description, ST_X(public.ST_PointOnSurface(geom)) || ' ' || ST_Y(public.ST_PointOnSurface(geom)) AS polygon_center_point
124 38 Bernhard Koschiček-Krombholz
FROM model.entity e
125
JOIN model.link l ON e.id = l.domain_id AND l.property_code IN ('P74', 'OA8', 'OA9')
126
JOIN model.entity p ON l.range_id = p.id
127
JOIN model.link pnl ON p.id = pnl.range_id AND pnl.property_code = 'P53'
128
JOIN model.entity pn ON pnl.domain_id = pn.id
129
JOIN model.link lb ON e.id = lb.range_id AND lb.property_code IN ('P67') 
130
JOIN model.entity eb ON lb.domain_id = eb.id AND eb.system_type = 'file'
131
JOIN gis.polygon g ON p.id = g.entity_id
132
WHERE e.class_code = 'E21';
133
</pre>
134
135 3 Alexander Watzinger
h1. Person with event location
136
137
Select all persons who took part at an event which was at a location with point coordinates
138
139 9 Bernhard Koschiček-Krombholz
h2. Point
140 10 Bernhard Koschiček-Krombholz
141 3 Alexander Watzinger
<pre>
142 13 Bernhard Koschiček-Krombholz
SELECT e.id, e.name, p.name, e.description, ST_X(g.geom) || ' ' || ST_Y(g.geom) AS coordinates
143 3 Alexander Watzinger
FROM model.entity e
144
JOIN model.link l ON e.id = l.range_id AND l.property_code IN ('P11', 'P14', 'P22', 'P23')
145
JOIN model.entity event ON l.domain_id = event.id
146
JOIN model.link lp ON event.id = lp.domain_id AND lp.property_code = 'P7'
147
JOIN model.entity p ON lp.range_id = p.id
148
JOIN gis.point g ON p.id = g.entity_id
149
WHERE e.class_code = 'E21';
150
</pre>
151 1 Alexander Watzinger
152 21 Bernhard Koschiček-Krombholz
Added picture description and named the columns
153
<pre>
154 27 Bernhard Koschiček-Krombholz
SELECT e.id, e.name, e.description AS entity_description, eb.id AS picture_id, eb.description AS picture_description, p.name AS location_name,  ST_X(g.geom) || ' ' || ST_Y(g.geom) AS coordinates
155 21 Bernhard Koschiček-Krombholz
FROM model.entity e
156
JOIN model.link l ON e.id = l.range_id AND l.property_code IN ('P11', 'P14', 'P22', 'P23')
157
JOIN model.entity event ON l.domain_id = event.id
158
JOIN model.link lp ON event.id = lp.domain_id AND lp.property_code = 'P7'
159
JOIN model.entity p ON lp.range_id = p.id
160
JOIN model.link lb ON e.id = lb.range_id AND lb.property_code IN ('P67') 
161
JOIN model.entity eb ON lb.domain_id = eb.id AND eb.system_type = 'file'
162
JOIN gis.point g ON p.id = g.entity_id
163
WHERE e.class_code = 'E21';
164
</pre>
165
166 36 Bernhard Koschiček-Krombholz
Added location description and complete url to file
167
<pre>
168 42 Bernhard Koschiček-Krombholz
SELECT e.id, e.name, e.description AS entity_description, eb.id AS picture_id, eb.description AS picture_description, 'https://orthodoxes-wien.oeaw.ac.at/repository/pic/' || eb.id AS pic_url, pn.name AS location_name, pn.description AS location_description, ST_X(g.geom) || ' ' || ST_Y(g.geom) AS coordinates 
169 36 Bernhard Koschiček-Krombholz
FROM model.entity e
170
JOIN model.link l ON e.id = l.range_id AND l.property_code IN ('P11', 'P14', 'P22', 'P23')
171
JOIN model.entity event ON l.domain_id = event.id
172
JOIN model.link lp ON event.id = lp.domain_id AND lp.property_code = 'P7'
173
JOIN model.entity p ON lp.range_id = p.id
174
JOIN model.link pnl ON p.id = pnl.range_id AND pnl.property_code = 'P53'
175
JOIN model.entity pn ON pnl.domain_id = pn.id
176
JOIN model.link lb ON e.id = lb.range_id AND lb.property_code IN ('P67') 
177
JOIN model.entity eb ON lb.domain_id = eb.id AND eb.system_type = 'file'
178
JOIN gis.point g ON p.id = g.entity_id
179
WHERE e.class_code = 'E21';
180
</pre>
181
182 9 Bernhard Koschiček-Krombholz
h2. Polygon
183 10 Bernhard Koschiček-Krombholz
184 8 Bernhard Koschiček-Krombholz
<pre>
185 14 Bernhard Koschiček-Krombholz
SELECT e.id, e.name, p.name, e.description, ST_X(public.ST_PointOnSurface(geom)) || ' ' || ST_Y(public.ST_PointOnSurface(geom)) AS polygon_center_point
186 8 Bernhard Koschiček-Krombholz
FROM model.entity e
187
JOIN model.link l ON e.id = l.range_id AND l.property_code IN ('P11', 'P14', 'P22', 'P23')
188
JOIN model.entity event ON l.domain_id = event.id
189
JOIN model.link lp ON event.id = lp.domain_id AND lp.property_code = 'P7'
190
JOIN model.entity p ON lp.range_id = p.id
191
JOIN gis.polygon g ON p.id = g.entity_id
192
WHERE e.class_code = 'E21';
193
</pre>
194 12 Alexander Watzinger
195 22 Bernhard Koschiček-Krombholz
Added picture description and named the columns
196
<pre>
197 26 Bernhard Koschiček-Krombholz
SELECT e.id, e.name, e.description AS entity_description, eb.id AS picture_id, eb.description AS picture_description, p.name AS location_name,  ST_X(public.ST_PointOnSurface(geom)) || ' ' || ST_Y(public.ST_PointOnSurface(geom)) AS polygon_center_point
198 22 Bernhard Koschiček-Krombholz
FROM model.entity e
199
JOIN model.link l ON e.id = l.range_id AND l.property_code IN ('P11', 'P14', 'P22', 'P23')
200
JOIN model.entity event ON l.domain_id = event.id
201
JOIN model.link lp ON event.id = lp.domain_id AND lp.property_code = 'P7'
202
JOIN model.entity p ON lp.range_id = p.id
203
JOIN model.link lb ON e.id = lb.range_id AND lb.property_code IN ('P67') 
204
JOIN model.entity eb ON lb.domain_id = eb.id AND eb.system_type = 'file'
205
JOIN gis.polygon g ON p.id = g.entity_id
206
WHERE e.class_code = 'E21';
207 1 Alexander Watzinger
</pre>
208 37 Bernhard Koschiček-Krombholz
209
Added location description and complete url to file
210
<pre>
211
SELECT e.id, e.name, e.description AS entity_description, eb.id AS picture_id, eb.description AS picture_description, 'https://openatlas.orthodoxes-wien.oeaw.ac.at/file/view/' || eb.id AS pic_url, pn.name AS location_name, pn.description AS location_description,  ST_X(public.ST_PointOnSurface(geom)) || ' ' || ST_Y(public.ST_PointOnSurface(geom)) AS polygon_center_point
212
FROM model.entity e
213
JOIN model.link l ON e.id = l.range_id AND l.property_code IN ('P11', 'P14', 'P22', 'P23')
214
JOIN model.entity event ON l.domain_id = event.id
215
JOIN model.link lp ON event.id = lp.domain_id AND lp.property_code = 'P7'
216
JOIN model.entity p ON lp.range_id = p.id
217
JOIN model.link pnl ON p.id = pnl.range_id AND pnl.property_code = 'P53'
218
JOIN model.entity pn ON pnl.domain_id = pn.id
219
JOIN model.link lb ON e.id = lb.range_id AND lb.property_code IN ('P67') 
220
JOIN model.entity eb ON lb.domain_id = eb.id AND eb.system_type = 'file'
221
JOIN gis.polygon g ON p.id = g.entity_id
222
WHERE e.class_code = 'E21';
223
</pre>
224
225 22 Bernhard Koschiček-Krombholz
226 12 Alexander Watzinger
h1. Place with location and types
227
228
This is experimental for Berni, there is a line for every type/place
229
230
<pre>
231
SELECT e.id, e.name, ST_X(g.geom) || ' ' || ST_Y(g.geom) AS coordinates, t.name
232
FROM model.entity e
233
JOIN model.link l ON e.id = l.domain_id AND l.property_code IN ('P53')
234
JOIN model.entity location ON l.range_id = location.id
235
JOIN gis.point g ON location.id = g.entity_id
236
LEFT JOIN model.link lt ON e.id = lt.domain_id AND lt.property_code = 'P2'
237
LEFT JOIN model.entity t ON lt.range_id = t.id
238
WHERE e.class_code = 'E18' ORDER BY e.name;
239
</pre>
240 32 Bernhard Koschiček-Krombholz
241
h1. Show all Entities without Case Studies
242
243
This is a DPP specific function
244
245
<pre>
246 33 Bernhard Koschiček-Krombholz
SELECT e.id, e.class_code, e.name, e.description
247
FROM model.entity e
248
LEFT JOIN model.link l ON e.id = l.domain_id AND l.property_code = 'P2'
249
   AND l.range_id IN (8241, 9962, 8250, 8245, 11821, 8247, 8243, 8244, 111277)
250
WHERE e.class_code IN ('E33', 'E6', 'E7', 'E8', 'E12', 'E21', 'E40', 'E74', 'E18', 'E22','E31', 'E84') 
251
   AND l.id IS NULL
252 32 Bernhard Koschiček-Krombholz
		
253
</pre>
254 34 Bernhard Koschiček-Krombholz
255
h1. Delete All Case Studies except One
256
257
This is a DPP specific function
258
259
<pre>
260
DELETE FROM model.entity WHERE id NOT IN
261
    (SELECT e.id FROM model.entity e
262
    JOIN model.link l ON
263
        e.id = l.domain_id
264
        AND l.property_code = 'P2'
265
        AND l.range_id = (SELECT id FROM model.entity WHERE name = 'Ethnonym of the Vlachs'))
266
AND class_code IN ('E33', 'E6', 'E7', 'E8', 'E12', 'E21', 'E74', 'E40', 'E31', 'E18', 'E84')
267
AND (system_type IS NULL OR system_type NOT IN ('source translation'));
268
</pre>
269 35 Bernhard Koschiček-Krombholz
270
h1. All Persons of a Case Study
271
272
This is a DPP specific function
273
274
<pre>
275
SELECT e.id, e.class_code, e.name, e.description
276
FROM model.entity e
277
LEFT JOIN model.link l ON e.id = l.domain_id AND l.property_code = 'P2'
278
   AND l.range_id IN (8241, 9962, 8250, 8245, 11821, 8247, 8243, 8244, 111277)
279
WHERE e.class_code IN ('E21', 'E40', 'E74') 
280
   AND l.range_id = (SELECT id FROM model.entity WHERE name = 'Historical Region of Macedonia')
281
</pre