Project

General

Profile

1
SELECT
2
*
3
, (SELECT "placeName"
4
    FROM place
5
    JOIN "namedPlace" USING ("NAMEDPLACE_ID")
6
    WHERE
7
    place."PLOT_ID" = plot."PLOT_ID"
8
    AND "placeSystem" = 'continent'
9
    LIMIT 1
10
    ) AS continent
11
, (SELECT "placeName"
12
    FROM place
13
    JOIN "namedPlace" USING ("NAMEDPLACE_ID")
14
    WHERE
15
    place."PLOT_ID" = plot."PLOT_ID"
16
    AND "placeSystem" = 'area|country|territory'
17
    LIMIT 1
18
    ) AS "area|country|territory"
19
, (SELECT "placeName"
20
    FROM place
21
    JOIN "namedPlace" USING ("NAMEDPLACE_ID")
22
    WHERE
23
    place."PLOT_ID" = plot."PLOT_ID"
24
    AND "placeSystem" = 'region|state|province'
25
    LIMIT 1
26
    ) AS "region|state|province"
27
, (SELECT "placeName"
28
    FROM place
29
    JOIN "namedPlace" USING ("NAMEDPLACE_ID")
30
    WHERE
31
    place."PLOT_ID" = plot."PLOT_ID"
32
    AND "placeSystem" = 'county'
33
    LIMIT 1
34
    ) AS county_
35
, (SELECT "placeName"
36
    FROM place
37
    JOIN "namedPlace" USING ("NAMEDPLACE_ID")
38
    WHERE
39
    place."PLOT_ID" = plot."PLOT_ID"
40
    AND "placeSystem" = 'quadrangle'
41
    LIMIT 1
42
    ) AS quadrangle
43
, (SELECT "placeName"
44
    FROM place
45
    JOIN "namedPlace" USING ("NAMEDPLACE_ID")
46
    WHERE
47
    place."PLOT_ID" = plot."PLOT_ID"
48
    AND "placeSystem" = 'Geographic Name'
49
    LIMIT 1
50
    ) AS "Geographic Name"
51
, (SELECT "placeName"
52
    FROM place
53
    JOIN "namedPlace" USING ("NAMEDPLACE_ID")
54
    WHERE
55
    place."PLOT_ID" = plot."PLOT_ID"
56
    AND "placeSystem" = 'River Basin'
57
    LIMIT 1
58
    ) AS "River Basin"
59
FROM plot
(4-4/11)