Make PG database

[edwardchalstrey1]

• [x] Load REPD into PG table (all columns)
• [x] Load OSM into PG table (csv version)
• [x] Load FiT into PG table
• [x] Load MV data into PG table and construct primary key
• [x] Make sure COPY in the sql gets the csv files in a way that isn't specific to my machine
• [x] Write a pre-processing script in Python that gets the csv data ready for postgres import
• [x] Script the creation of the DB with SQL queries (including importing the data)
• [x] Perform some queries test that make use of the linked tables (e.g. on repd_id)
• [x] Add repd_id table to main sql script
• [x] Remove space from postcode column REPD
• [x] Use Excel (or Python?) to convert dates to YYYY-MM-DD, prior to PG import This seems to already be the case in postgres
• [x] Convert tag_start_date to a proper date using a Python function

[edwardchalstrey1]

• [x] The repd:id in osm data sometimes has multiple ids split by semicolons. This appears to be because they genuinely are part of the same thing. Separate table needed: "Normal form" - see blackboard photo. (could answer my own Stack after)
• [x] A lot of columns in FiT also have “Unknown” string rather than being left blank (don't worry, keep as unknown)
• [x] The "Storage Co-location REPD Ref ID” column in REPD is like Ref ID but has commas… AND! some entries are “n/a” so forced to use varchar unless we modify the raw data. "FiT Tariff (p/kWh)” is also varchar rather than float because of n/a. Sort these out in same way as items above - use Python pre-processing.
• [x] Automate the manual data transformations. Done for REPD, but difficulty with FiT is that it's much quicker to do manually than converting excel to csv via pandas and with osm, the edits I made seemed very specific to the data
• [x] What to do about lack of FiT primary key? Find duplicates, why are they there? If they are the same, add a number (index/id) in pre-processing (select distinct into new table)
• [x] "Storage Co-location REPD Ref ID” is a float, rather than an int like repd_id because the csv has some with NaN (or the pandas version does)

[edwardchalstrey1]

Notes: I've added an index for fit table, but not been able to easily identify duplicate rows, because when I try to find them e.g. with https://stackoverflow.com/questions/28156795/how-to-find-duplicate-records-in-postgresql - nothing comes up, which is weird because doing select distinct suggests there are

[edwardchalstrey1]

Decision made that FiT duplicate rows are probably separate things and should be treated as such