1 |
4515
|
aaronmk
|
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
|