Processing steps

Split domain file

Split domain file in 6 smaller files (1 million records each)

split -l 1000000 -d 2019-05-21_all_domains_NL.txt domains-nl

Run geolocation script

Process files with this script, running on virtual machine in SURFsara HPC Cloud

Total run time:

• Started Tue 1 Sep, 12:45 (approx.)
• Completed Thu 3 Sep, 18:39
• So total run time approximately 54 hours.

QGIS

Import resulting files in QGIS.

For each location, establish corresponding province using Dutch Province polygons layer (shapefile). According to description, provinces file is suitable for map scales range 1:750,000 - 1:1,000,000, which implies spatial accuracy of about 1 km.

(To classify provinces: right-click layer, Properties, Symbology, then at top change Single symbol to Categorized; in Value dropdown menu select PROV_CODE; click Classify).

Spatial join with province layer

In QGIS: Data Management Tools, Join Attributes by Location ... and then export resulting layer as CSV. Repeat for each of the 6 files.

Combine CSVs

Combine the 6 output CSV files using (head command writes header line of 1st file to output file; subsequent tail commands append all lines except header line from all files):

head -1 ~/kb/geolocation-nldomain/qgis/domains-nl00-join-prov.csv > domains-nl-join-prov.csv; tail -n +2 -q ~/kb/geolocation-nldomain/qgis/domains-nl0?-join-prov.csv >> domains-nl-join-prov.csv

Install Python Pandas

sudo apt-get install python3-pandas
sudo python3 -m pip install markdown
sudo python3 -m pip install tabulate

Analysis

Script here.

Compute:

• Number of active/inactive domains
• Domain count by country (absolute + relative as % of all active domains)
• Domain count by Dutch province (absolute + relative as % of all active domains hosted in NL)

Analysis results

• Number of active domains: 4606514
• Number of inactive domains: 1256783
• Number of active domains with no country ID:26634

Countries

Provinces

Caveats

• Accuracy of geolocation. Value of accuracyRadius reported by GeoLite2 database often 50 -100 km (TODO: compute some stats on this).