Project

General

Profile

Download (1.24 KB) Statistics
| Revision:

root/inputs/VegBank/plot_/create.sql @ 11811

1 4515 aaronmk 
SELECT
2 
*
3 
, (SELECT placename
4 
    FROM place
5 
    JOIN namedplace USING (namedplace_id)
6 
    WHERE
7 11601 aaronmk 
    place.plot_id = plot."locationID"
8 4515 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 11601 aaronmk 
    place.plot_id = plot."locationID"
16 4515 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 11601 aaronmk 
    place.plot_id = plot."locationID"
24 4515 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 11601 aaronmk 
    place.plot_id = plot."locationID"
32 4515 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 11601 aaronmk 
    place.plot_id = plot."locationID"
40 4515 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 11601 aaronmk 
    place.plot_id = plot."locationID"
48 4515 aaronmk 
    AND placesystem = 'Geographic Name'
49 
    LIMIT 1
50 
    ) AS "Geographic Name"
51 
FROM plot
52 11600 aaronmk 
-- runtime: 5 s ("5127.354 ms") @starscream