Now that we have downloaded some OS OpenData, we can look into working with it.
Loading .csv data into PostGIS was surprisingly difficult, but can be done in a number of ways. This post will cover doing it using a .vrt (virtual raster) file.
In this example we are using Ordnance Survey and ultimately Royal Mail: Code-Point Open data. This can be found from OpenData and is a CSV point file for UK postcodes.
First we need to modify the header file provided with Code-Point:
I have deleted the short codes and replaced them with sensible headers. You should only have one line in your header file.
Firstly the files are provided split up into a number of different files. We can combine all of the .csv files using the cat command in Linux.
cat *.csv > codepoint.csv
So this will combine all of the individual .csv files into the codepoint.csv file. Your header file should be the top line.
So now we can look at the data using ogrinfo, part of the gdal suite.
We create a virtual raster definition file with our X and Y columns, and projection defined:
codepoint.csv wkbPoint EPSG:27700
So now we can run “ogrinfo” on codepoint.vrt:
ogrinfo -ro -al -so codepoint.vrt
Result:
So we can see that there was an error opening the codepoint.csv file itself but the .vrt worked fine. This is probably down to a memory issue on my part. So your mileage may vary. I tried again with just one postcode area and it worked fine (ab postcode area .csv was named codepoint.csv):
So I need to find a more memory friendly way to do this. So I will load the postcode files one at a time, appending them to any data that is already loaded.
So first I need to add the header row to the top of each file. The cat command worked really well last time so lets try that.
Rename the header file to .txt. So it will be called Code-Point_Open_Column_Headers.txt and consists of only one row with our desired column headers.
Write a bash script that adds the header file to the beginning of each .csv file:
#!/bin/bash # vesanto ################################## echo " ******************************* " echo " Add a header to codepoint" echo " ******************************* " for codepointFiles in *.csv do Echo $codepointFiles cat Code-Point_Open_Column_Headers.txt >> $codepointFiles.csv cat $codepointFiles >> $codepointFiles.csv echo "Done" done
Run the file:
Bash cat_command.sh
Delete all of the .csv file (not the .csv.csv files!)
And we rename .csv.csv to .csv:
for i in *.csv.csv; do mv "$i" "${i/.csv.csv}".csv; done
Then we can create the .vrt files for each csv file:
So we want to create the equivalent of for each csv file:
codepoint.csv wkbPoint EPSG:27700
So we can do this using another bash script:
#!/bin/bash
# vesanto
##################################
echo " ******************************* "
echo " Create OGR .vrt file"
echo " ******************************* "
for codepointFiles in *.csv
do
echo $codepointFiles
fileN=$codepointFiles
layerName=${fileN%????}
echo '' >> $codepointFiles.vrt
echo ' ' >> $codepointFiles.vrt
echo ' '$codepointFiles' ' >> $codepointFiles.vrt
echo ' wkbPoint ' >> $codepointFiles.vrt
echo ' EPSG:27700 ' >> $codepointFiles.vrt
echo ' ' >> $codepointFiles.vrt
echo ' ' >> $codepointFiles.vrt
echo "Done"
done
And finally we can load the files using a final bash script (this could be done by looping through the files as well):
ogr2ogr -nlt PROMOTE_TO_MULTI -progress -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ab.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" al.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ba.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" bb.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" bd.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" bh.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" bl.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" bn.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" br.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" bs.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" b.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ca.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" cb.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" cf.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ch.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" cm.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" co.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" cr.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ct.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" cv.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" cw.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" da.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" dd.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" de.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" dg.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" dh.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" dl.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" dn.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" dt.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" dy.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ec.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" eh.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" en.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" e.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ex.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" fk.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" fy.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" gl.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" gu.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" g.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ha.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" hd.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" hg.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" hp.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" hr.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" hs.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" hu.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" hx.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ig.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ip.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" iv.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ka.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" kt.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" kw.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ky.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" la.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ld.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" le.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ll.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ln.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ls.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" lu.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" l.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" me.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" mk.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ml.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" m.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ne.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ng.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" nn.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" np.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" nr.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" n.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" nw.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ol.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ox.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" pa.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" pe.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ph.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" pl.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" po.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" pr.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" rg.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" rh.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" rm.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" sa.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" se.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" sg.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" sk.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" sl.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" sm.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" sn.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" so.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" sp.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" sr.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ss.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" st.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" s.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" sw.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" sy.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ta.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" td.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" tf.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" tn.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" tq.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" tr.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ts.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" tw.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ub.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" wa.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" wc.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" wd.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" wf.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" wn.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" wr.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ws.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" w.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" wv.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" yo.vrt ogr2ogr -nlt PROMOTE_TO_MULTI -progress -update -append -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:"dbname='os_open' host='localhost' port='5432' user='postgres' password='postgres'" ze.vrt
So the ogr2ogr command is:
ogr2ogr -nlt PROMOTE_TO_MULTI -progress -nln codepoint -skipfailures -lco PRECISION=no -f PostgreSQL PG:”dbname=’os_open’ host=’localhost’ port=’5432′ user=’os_open’ password=’os_open'” source_file
Explained:
-nlt | PROMOTE_TO_MULTI, creates it as a multipart geometry, not important for points
-progress | Shows a progress bar
-nln codepoint | The name of the layer in the database, so because we are appending data to the first csv file loaded this is important.
-skipfailures -lco PRECISION=no | Personal preference
-f PostgreSQL PG:”dbname=’os_open’ host=’localhost’ port=’5432′ user=’os_open’ password=’os_open'” | Destination, so the details of your PostGIS database (see setting up PostGIS for help).
The latter commands also have the -append tag, which means they will be appended to the first one loaded.
Excellent. Though this was a bit intensive. Doing this in QGIS would have been a lot easier. However this can now be scripted and automatically run just by replacing the original .csv input files allowing for an easy update of the data when a new Cope-Point dataset is released.
Sources: