Download and store climate data

**The project involves - Extracting station data from a text file into a mysql database table - Using the station data to determine download urls of further files with html or csv data - Extracting climate data from the html or csv files into a mysql database table - Providing php functions for re-updating this data whenever required** ** ** ## Deliverables # **Obtaining Climate Data from Australian Bureau of Meteorology Website** ## **Summary** **The project involves - Extracting station data from a text file into a mysql database table - Using the station data to determine download urls of further files with html or csv data - Extracting climate data from the html or csv files into a mysql database table - Providing php functions for re-updating this data whenever required** ## **Details** **1. Extract weather station data from file into mysql database Source file: "[login to view URL]" can be obtained from url: <ftp://[login to view URL]> This is text file of about 20,000 lines with 12 fixed width fields on each line (following the header lines). The task is to create a mySQL table with these fields, and populate it from this file. The columns are as follows (As labelled / description / suggested database field name)** * **Site / Station number / station_id** * **Dist / District number / district_id** * **Site name / Station name / station_name** * **Start / Start year / year_start** * **End / End year / year_end** * **Lat / Latitude / latitude** * **Lon / Longitude / longitude** * **Source / Location source / source** * **STA / State abbreviation / state** * **Height (m) / Station height metres / height_m** * **Bar_ht / Barometer height metres / barometer_m** * **WMO / WMO station id / WMO_id** **Notes** * **Use numeric database fields for all numeric data (eg. Station number, District number, etc.)** * **Some data is shown as ".." or "...." - these should be entered as NULL in the db** * **The data will need to be accessed by 1) Station number (column 1) or 2) WMO number (column 12), so should be indexed on each of these.** **2. Get climate data for each station Get data for each station for which the "End" year field is NULL (meaning those sites which still collect climate data), and for which the following url exists. The page url is: [[login to view URL]][1] where XXXXXX is the zero filled 6-digit site number Example: <[login to view URL]> Note that each page includes a link to a *.csv file, and it may be easier for you to obtain the csv file link and then get data from that file, rather than scraping the shtml contents to get the climate data. (And if you get the file link instead of parsing the html, then you can do so from the page url [[login to view URL]][1] for efficiency, as this is smaller). 3. Store climate data for each such station Store into database table/s - the exact structure and layout is up to you, but must be indexed by station number, for example a typical query would be to get all the monthly average maximum temperatures for a given station for each month, or to get the average monthly rainfall for each month for a given station. 4. PHP Code I want to be able to update the tables in an automated way, for two separate processes. - PHP code I can run to re-import the [login to view URL] file and update the stations table. This might be a routine with the filename/path to use as a parameter eg. function updateStationsData($stationsFileWithPath). - PHP code I can run to re-query all the climate data and update the climate table/s. This could be a routine with no parameters eg. function updateClimateData. Notes** * **It would be very helpful to use parallel processing for downloading of climate data pages/files, for example using curl_multi_exec.** * **Any techniques you have for efficient scraping would also be helpful!** **5. Deliverables** * **mySQL database as *.sql file which I may use to import the database structure and data** * **PHP code to update the stations table and climate table/s**