Environment
Windows 10
Postgresql 11
To start you need a set of shape files that you know the location of.
For demonstration purposes I use the OS Open Road open data shape files.
Next within explorer I create a directory in the c:\ root called data.
And then I create two subdirectories.
1 – shp
2 – sql
Place all the shape files you wish to be loaded into a postgis database into the newly created c:\data\shp directory.
Next we will make sql text files that can be used to transfer the information from shape format into Postgis. At this point it is not necessary to even know what database you are going to place the shape files into.
Next open up the command prompt and navigate to the bin directory of your particular postgres installation. Standard as follows :-
Next I copy in the below text.
For %f in (C:\data\shp\*shp) do Shp2pgsql –s 27700 %f public.%~nf > C:\data\sql\%~nf.sql
This will take all the shape files in the C:\data\shp\ directory and create sql text files placing them in the C:\data\sql\. After completion you may wish to go to the directory and check that creation has occurred. You can even look at the text within a single file preferably with some kind of code editor to check that it looks approximately correct.
Next within psql I create the target database and apply the postgis extension.
create database osopenroaddb;
create extension postgis;
Next, unless you have a lot of time on your hands you will want to go to the
C:\Users\Mark\AppData\Roaming\postgresql
and open up the pgpass.conf file and place the following line in it.
localhost:5432:*:postgres:yoursecretpassword
Without that line you will be asked for a password everytime a new sql file is run.
Next back in command prompt bin directory do the following. Note no password it takes it from the pgpass.conf file.
For %f in (c:\data\sql\*sql) do psql –h localhost –d osopenroaddb –U postgres –f %f > nul
If you have a lot of big files this could take a while to run. But once finished all your shape files should now be in your postgis instance in the specified database.