Add default Geography tree #7488
Description
This was created using this SQL script on a new Wizard database, and it does not include Earth as a node. This was the preferred approach to match the structure we recommend for tree creation in the WorkBench (and it is more similar to the one used for taxonomy). This is an adapted version of https://github.com/specify/specify6/blob/master/config/geonames.sql.zip
If desired, we could include all abbreviations and other metadata, but this covers the most important parts. Feedback is welcome.
In the future, it would be great to use more up-to-date political boundaries, possibly from https://gadm.org/license.html with permission.
"Continent","Country","State","County","GeographyCode","CentroidLat","CentroidLon"
Africa,"","","",AF,"7.19","21.09"
Africa,Algeria,"","",DZ,"28.00","3.00"
Africa,Algeria,Adrar,"","","25.75",-1.00
Africa,Algeria,Annaba,"","","36.83","7.58"
Africa,Algeria,El Bayadh,"","","32.50","1.17"
Africa,Algeria,El Oued,"","","33.17","7.25"
Africa,Algeria,El Tarf,"","","36.75","8.17"
Africa,Algeria,Illizi,"","","26.83","8.17"
Africa,Algeria,Oran,"","","35.67",-0.50
Africa,Algeria,Oum el Bouaghi,"","","35.83","7.08"
Africa,Algeria,Wilaya d' Alger,"","","36.74","3.01"
Africa,Algeria,Wilaya de Ain Defla,"","","36.17","2.17"
Africa,Algeria,Wilaya de Ain Temouchent,"","","35.33",-1.08
Africa,Algeria,Wilaya de Batna,"","","35.50","5.92"
Africa,Algeria,Wilaya de Bechar,"","","30.25",-3.08
Africa,Algeria,Wilaya de Bejaia,"","","36.67","4.92"
Africa,Algeria,Wilaya de Biskra,"","","34.67","5.42"
Africa,Algeria,Wilaya de Blida,"","","36.58","3.00"
Africa,Algeria,Wilaya de Bordj Bou Arreridj,"","","36.08","4.75"
Africa,Algeria,Wilaya de Bouira,"","","36.25","3.92"
Africa,Algeria,Wilaya de Boumerdes,"","","36.75","3.67"
WITH RECURSIVE AncestryPaths AS (
SELECT
g.GeographyID,
g.ParentID,
gtdi.Name AS CurrentRankName,
g.Name AS CurrentGeoName,
CASE WHEN gtdi.Name = 'Continent' THEN g.Name ELSE NULL END AS Continent,
CASE WHEN gtdi.Name = 'Country' THEN g.Name ELSE NULL END AS Country,
CASE WHEN gtdi.Name = 'State' THEN g.Name ELSE NULL END AS State,
CASE WHEN gtdi.Name = 'County' THEN g.Name ELSE NULL END AS County,
g.Abbrev,
g.GeographyCode,
g.CentroidLat,
g.CentroidLon
FROM
geography AS g
INNER JOIN
geographytreedefitem AS gtdi ON g.GeographyTreeDefItemID = gtdi.GeographyTreeDefItemID
WHERE
gtdi.Name IN ('Continent', 'Country', 'State', 'County')
UNION ALL
SELECT
ap.GeographyID,
p.ParentID,
p_gtdi.Name AS CurrentRankName,
p.Name AS CurrentGeoName,
COALESCE(CASE WHEN p_gtdi.Name = 'Continent' THEN p.Name ELSE NULL END, ap.Continent) AS Continent,
COALESCE(CASE WHEN p_gtdi.Name = 'Country' THEN p.Name ELSE NULL END, ap.Country) AS Country,
COALESCE(CASE WHEN p_gtdi.Name = 'State' THEN p.Name ELSE NULL END, ap.State) AS State,
COALESCE(CASE WHEN p_gtdi.Name = 'County' THEN p.Name ELSE NULL END, ap.County) AS County,
ap.Abbrev,
ap.GeographyCode,
ap.CentroidLat,
ap.CentroidLon
FROM
AncestryPaths AS ap
INNER JOIN
geography AS p ON ap.ParentID = p.GeographyID
INNER JOIN
geographytreedefitem AS p_gtdi ON p.GeographyTreeDefItemID = p_gtdi.GeographyTreeDefItemID
WHERE
ap.ParentID IS NOT NULL
)
SELECT
COALESCE(ap.Continent, '') AS Continent,
COALESCE(ap.Country, '') AS Country,
COALESCE(ap.State, '') AS State,
COALESCE(ap.County, '') AS County,
COALESCE(ap.GeographyCode, '') AS GeographyCode,
COALESCE(CAST(ap.CentroidLat AS CHAR), '') AS CentroidLat,
COALESCE(CAST(ap.CentroidLon AS CHAR), '') AS CentroidLon
FROM (
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY GeographyID ORDER BY
(CASE WHEN Continent IS NOT NULL THEN 1 ELSE 0 END) +
(CASE WHEN Country IS NOT NULL THEN 2 ELSE 0 END) +
(CASE WHEN State IS NOT NULL THEN 4 ELSE 0 END) +
(CASE WHEN County IS NOT NULL THEN 8 ELSE 0 END) DESC
) as rn
FROM
AncestryPaths
) AS ap
WHERE
ap.rn = 1
AND (ap.Continent IS NOT NULL OR ap.Country IS NOT NULL OR ap.State IS NOT NULL OR ap.County IS NOT NULL) -- Exclude any rows that ended up entirely empty
ORDER BY
Continent, Country, State, County;