Project

General

Profile

« Previous | Next » 

Revision 4515

Added by Aaron Marcuse-Kubitza about 12 years ago

inputs/VegBank/: Added plot_/

View differences:

inputs/VegBank/plot_/create.sql
1 
SELECT
2 
*
3 
, (SELECT placename
4 
    FROM place
5 
    JOIN namedplace USING (namedplace_id)
6 
    WHERE
7 
    place.plotplace_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.plotplace_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.plotplace_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.plotplace_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.plotplace_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.plotplace_id = plot.plot_id
48 
    AND placesystem = 'Geographic Name'
49 
    LIMIT 1
50 
    ) AS "Geographic Name"
51 
FROM plot

Also available in: Unified diff