Mapping
- SQL Admin: http://localhost/phpmyadmin
- OS: https://www.ordnancesurvey.co.uk/opendatadownload/products.html
- Getting shapes into MySQL: http://www.bostongis.com/PrinterFriendly.aspx?content_name=ogr_cheatsheet
Need Maria 10.1 Plus
http://www.2daygeek.com/install-upgrade-mariadb-10-on-centos-rhel-fedora/
Need to run "sudo dnf erase mariadb-common" as well as mariadb and mariadb-server
Start Stop
systemctl start|stop mariadb
Importing from ESRI Shape to MySQL
MySQL 4 and 5 support spatial objects to a limited extent. The functionality is not as rich as PostGIS, but for basic mapping work and limited spatial analysis, and if you already have MySQL installed, it may suit your needs just fine. Below is a snippet of code that will import a shape file called world_adm0.shp into a MySQL database and will rename it world. NOTE: that if you have a virgin MySQL database with no geometry_columns or spatial_ref_sys table, then those will automatically be created.
sudo dnf install gdal
create "test" database and "matthew" user if necessary
Polling - 31826 rows!
ogr2ogr -f "MySQL" MYSQL:"test,host=localhost,user=matthew,password=PASSWORD,port=3306" -nln "world" -a_srs "EPSG:4326" ~/Documents/mapping/uk/Data/POLLING\ DISTRICTS_\(ENG\)/Shape/polling_districts_England_region.shp -skipfailures -overwrite -lco ENGINE=MyISAM
Constituency - 629, that's a bit more like it
ogr2ogr -f "MySQL" MYSQL:"test,host=localhost,user=matthew,password=PASSWORD,port=3306" -nln "westminster_const" -a_srs "EPSG:4326" ~/Documents/mapping/uk/Data/GB/westminster_const_region.shp -skipfailures -overwrite -lco ENGINE=MyISAM
Shapes
SELECT ST_Boundary(SHAPE) FROM `world` WHERE OGR_FID = 1
31826 rows!
Plotting
Look at http://terraformer.io/wkt-parser/#using-in-the-browser
Importing
Add extra ID column: ALTER TABLE mytable ADD `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
Electoral Commission: http://www.electoralcommission.org.uk/our-work/our-research/electoral-data