Next, a PostgreSQL database was set up that was spatially enabled with PostGIS. This is done by opening cPanel and clicking on the PostgreSQL icon. A new database was created with the name “travelle_kindersley”.
The next tool used was phpPgAdmin from the cPanel. This opens up a new interface for working with the database. The database in the menu was clicked and “create schema” was selected. This created two schemas: public and topology.
After returning to the cPanel, the PostGIS icon was selected, and the option to install PostGIS into the database was chosen. The shp2pgsql tool was chosen and the Kindersley shapefile was loaded into the database as Kindersley_main_spatial.
The next process was to load all of the quotation CSV files into the database. This was done by selecting the “public” schema, and choosing “create table.” The table was named by title of the CSV file and three text columns were created: key, subcategory, and quote. With the new table selected, a tool called “import” was chosen which brought the CSV file into the database. This process was done for each of the nine quotation files.
Once the text files were in the database, they were linked to the spatial table by creating views. With “Views” under the public schema selected, “Create View with Wizard” was chosen. On the next screen, the Kindersley_main_spatial table and one of the text tables was chosen. This screen lets the user choose which columns to import and how to link them. Since the goal was to have a record for each quotation with the spatial information attached, a “Left Join” was chosen with the quotations table on the left. The “Key” field from both tables was chosen to link them together.