Project

General

Profile

Download (1.43 KB) Statistics
| Revision:

root/inputs/CVS/plot_/create.sql @ 11264

1 6742 aaronmk 
SELECT
2 
*
3 
, (SELECT "placeName"
4 
    FROM place
5 
    JOIN "namedPlace" USING ("NAMEDPLACE_ID")
6 
    WHERE
7 11264 aaronmk 
    place."PLOT_ID" = plot."PLOT_ID"
8 6742 aaronmk 
    AND "placeSystem" = 'continent'
9 
    LIMIT 1
10 
    ) AS continent
11 
, (SELECT "placeName"
12 
    FROM place
13 
    JOIN "namedPlace" USING ("NAMEDPLACE_ID")
14 
    WHERE
15 11264 aaronmk 
    place."PLOT_ID" = plot."PLOT_ID"
16 6742 aaronmk 
    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 11264 aaronmk 
    place."PLOT_ID" = plot."PLOT_ID"
24 6742 aaronmk 
    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 11264 aaronmk 
    place."PLOT_ID" = plot."PLOT_ID"
32 6742 aaronmk 
    AND "placeSystem" = 'county'
33 
    LIMIT 1
34 
    ) AS county_
35 
, (SELECT "placeName"
36 
    FROM place
37 
    JOIN "namedPlace" USING ("NAMEDPLACE_ID")
38 
    WHERE
39 11264 aaronmk 
    place."PLOT_ID" = plot."PLOT_ID"
40 6742 aaronmk 
    AND "placeSystem" = 'quadrangle'
41 
    LIMIT 1
42 
    ) AS quadrangle
43 
, (SELECT "placeName"
44 
    FROM place
45 
    JOIN "namedPlace" USING ("NAMEDPLACE_ID")
46 
    WHERE
47 11264 aaronmk 
    place."PLOT_ID" = plot."PLOT_ID"
48 6742 aaronmk 
    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 11264 aaronmk 
    place."PLOT_ID" = plot."PLOT_ID"
56 6742 aaronmk 
    AND "placeSystem" = 'River Basin'
57 
    LIMIT 1
58 
    ) AS "River Basin"
59 
FROM plot