4 dec 2022 re-create plunder database in a2hosting

database: voyccom_plunder

used by both plunder and geosketch

• geosketch is used to update the data
• plunder reads the data

Historical note: three databases were used at webfaction.

1. jhagstrand_plunder - the original plunder database of geographical data.
2. voyc - used temporarily for building some of the plunder data js files.
3. jhagstrand_voyc - the historical political data used in voyc.

In the move from webfaction to a2hosting, those second two files were lost. That is not critical, because they were used only temporarily.

Plunder loads its data from static js files, generated from the database.

Voyc uses google maps api, and therefore does not need geographical data.

Going forward, we will rebuild the voyccom_plunder database. It will contain all the geographic data for drawing the map. It will also contain political data, copied from the Voyc database. How will we resolve this dual maintenance of the political data?

Create voyccom_plunder database on a2hosting/cPanel tool

Create the schema

psql -d voyccom_plunder -U voyccom_jhagstrand -c "create schema plunder;"

This is evidently an older version of data found on natural earth data website.

cd ~/webprod/geosketch/geosketch/datasource/osmtilemill
wget https://github.com/polpols/OSM-Tilemill/archive/master.zip
unzip -j master */10m_geography_regions_polys.*
shp2pgsql -c -W LATIN1 10m_geography_regions_polys plunder.georegions >load.georegions.sql
psql -d voyccom_plunder -U voyccom_jhagstrand <load.georegions.sql

This creates a table named georegions containing 1048 records classified by terrain type.

select gid, scalerank, featurecla, name, namealt, region, subregion, geom from plunder.georegions;

cd ~/webprod/geosketch/geosketch/datasource/naturalearthdata/rivers
wget http://www.naturalearthdata.com/http//www.naturalearthdata.com/download/50m/physical/ne_50m_rivers_lake_centerlines.zip
unzip -j ne_50m_rivers_lake_centerlines.zip
shp2pgsql -c -W LATIN1 ne_50m_rivers_lake_centerlines plunder.rivers50 >load.rivers50.sql
psql -d voyccom_plunder -U voyccom_jhagstrand <load.rivers50.sql

select guid, scalerank, featurecla, name, note, min_zoom, name_alt, min_label, name_en, label from plunder.rivers50

cd ~/webprod/geosketch/geosketch/datasource/naturalearthdata/lakes
wget https://www.naturalearthdata.com/http//www.naturalearthdata.com/download/50m/physical/ne_50m_lakes.zip
unzip ne_50m_lakes.zip
shp2pgsql -c -W LATIN1 ne_50m_lakes plunder.lakes50 >load.lakes50.sql

A table on this page includes names of the major seas. https://en.wikipedia.org/wiki/List_of_political_and_geographic_borders

empire and treasure are copied from database voyccom_voyc table fpd.

psql -t -d jhagstrand_voyc -U jhagstrand_voyc <empire.sql >../../html/data/empire.js

select 'voyc.data.empire = {"type": "FeatureCollection","features":['
union all (select '{"type":"Feature","geometry":' || st_asgeojson(st_forcerhr(the_geom),6) || ',"properties":{id:' || id || ',"name":"' || replace(headline, E'\n', ' ') || '","b":' || timebegin || ',"e":' || timeend || ',"fb":' || forebear || ',"c":' || color || '}},'
from fpd.fpd
where editstatus < 10
and maptype in (3,4)  
and datatype in (2,5,6,7,8,10)
order by forebear, timebegin, timeend)
union all select ']}';

psql -t -d jhagstrand_voyc -U jhagstrand_voyc <treasure.sql >../../html/data/treasure.js */

select 'voyc.data.treasure = {"type": "GeometryCollection","geometries":['
union all (select '{tp:"pt",co:[' || round(st_x(the_geom)::numeric,3) || ',' || round(st_y(the_geom)::numeric,3) || '],b:' || timebegin || ',e:' || timeend || ',score:1000,cap:0,id:' || id || ',name:"' || headline || '"},'
from fpd.fpd
where editstatus < 10
and maptype = 2
and datatype in (2,5,6,7,8,10)
and timebegin < 1900
order by timebegin, timeend)
union all select ']};window["voyc"]["onScriptLoaded"]("treasure.js");';