Install PostGIS

Official project website

  • https://postgis.net

Install PostGIS

Install PostgreSQL & PostGIS

sudo apt -y install postgresql postgresql-contrib postgis
sudo systemctl enable postgresql

Create the postgis db & user and enable the postgis extension

sudo -u postgres createuser postgis -P
sudo -u postgres createdb postgis -O postgis
sudo -u postgres psql -d postgis -c "CREATE EXTENSION postgis;"

Loading spatial data

Install pre-requisites

sudo apt-get -y install gdal-bin unzip

Natural Earth Data

Based on this doc

Natural Earth Data downloads found here

Download the spatial data

mkdir ~/spatial_data && \
cd ~/spatial_data
wget https://naciscdn.org/naturalearth/packages/natural_earth_vector.zip && \
unzip natural_earth_vector.zip && \
cd 110m_cultural

Load the spatial data into the postgis database

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

Query the spatial data

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

Troubleshooting

Drop and re-create the postgis database

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;"