Title here
Summary here
sudo apt -y install postgresql postgresql-contrib postgis
sudo systemctl enable postgresql
sudo -u postgres createuser postgis -P
sudo -u postgres createdb postgis -O postgis
sudo -u postgres psql -d postgis -c "CREATE EXTENSION postgis;"
sudo apt-get -y install gdal-bin unzip
Based on this doc
Natural Earth Data downloads found here
mkdir ~/spatial_data && \
cd ~/spatial_data
wget https://naciscdn.org/naturalearth/packages/natural_earth_vector.zip && \
unzip natural_earth_vector.zip && \
cd 110m_cultural
sudo -u postgres ogr2ogr -f PostgreSQL PG:dbname=postgis -progress -nlt PROMOTE_TO_MULTI ./ne_110m_admin_0_countries.shp
sudo -u postgres ogrinfo -so PG:dbname=postgis ne_110m_admin_0_countries
sudo -u postgres psql -d postgis
postgis=# \d ne_110m_admin_0_countries
SELECT admin, ST_Y(ST_Centroid(wkb_geometry)) as latitude
FROM ne_110m_admin_0_countries
ORDER BY latitude DESC
LIMIT 10;
According to the document linked, this query will list the top ten most northerly countries:
admin | latitude
-----------+--------------------
Greenland | 74.77048769398986
Norway | 69.15685630975351
Iceland | 65.07427633529105
Finland | 64.50409403963651
Sweden | 62.811484968080336
Russia | 61.961663494923
Canada | 61.46907614534896
Estonia | 58.643695426630906
Latvia | 56.80717513427924
Denmark | 56.06393446179454
sudo -u postgres dropdb postgis && \
sudo -u postgres createuser postgis -P && \
sudo -u postgres createdb postgis -O geouser && \
sudo -u postgres psql -d postgis -c "CREATE EXTENSION postgis;"