-- We calculate the score of the distance between the capital city and the centroids of their countries
DROP TABLE SCORES_CENTROID;
CREATE TABLE SCORES_CENTROID AS
select rank() over(order by score desc), country_name, city_name, distance, area, score from (
SELECT 
ccc.country_name, cc.city_name, ST_Distance_Spheroid(ccc.geom, cc.geom, 'SPHEROID["WGS 84",6378137,298.257223563]')/1000 distance, wc.area, 100 - (ST_Distance_Spheroid(ccc.geom, cc.geom, 'SPHEROID["WGS 84",6378137,298.257223563]')/1000/((ST_Perimeter(ST_MinimumBoundingCircle(wc.geom), true)/1000)/(2*pi())))*100 score
FROM capital_cities cc 
JOIN centroid_capital_cities ccc ON ccc.country_name = cc.country_name
JOIN world_countries_work wc ON wc.name = ccc.country_name) scores
ORDER BY score desc;

-- We calculate the score of the distance between the capital city and the centre of the minimum bounding circle of the countries
DROP TABLE SCORES_CENTRE;
CREATE TABLE SCORES_CENTRE AS
select rank() over(order by score desc), country_name, city_name, distance, area, score from (
SELECT 
ccc.country_name, cc.city_name, ST_Distance_Spheroid(ccc.geom, cc.geom, 'SPHEROID["WGS 84",6378137,298.257223563]')/1000 distance, wc.area, 100 - (ST_Distance_Spheroid(ccc.geom, cc.geom, 'SPHEROID["WGS 84",6378137,298.257223563]')/1000/((ST_Perimeter(ST_MinimumBoundingCircle(wc.geom), true)/1000)/(2*pi())))*100 score
FROM capital_cities cc 
JOIN center_circle_capital_cities ccc ON ccc.country_name = cc.country_name
JOIN world_countries_work wc ON wc.name = ccc.country_name) scores
ORDER BY score desc;

-- Here requests to see the ranking
SELECT rank, country_name, city_name, round(cast(distance as numeric), 3) distance, round(cast(area as numeric), 3) area, round(cast(score as numeric), 2) score from SCORES_CENTROID;
SELECT rank, country_name, city_name, round(cast(distance as numeric), 3) distance, round(cast(area as numeric), 3) area, round(cast(score as numeric), 2) score from SCORES_CENTRE;
