################################################################################### #coding:utf-8 # ticket 276 # import data set inputsDS/Europe/Zalf ################################################################################### import WOSISAPI import subprocess import psycopg2 import getpass import sys sys.path.append('/home/eloi/Geo/workspace/WOSIS') ########################### CONSTRAINTS (start) ############################### #"Contact"."Organization" #CONSTRAINT "Organization_CountryId_fkey" FOREIGN KEY ("CountryId") REFERENCES "Location"."Country" ("CountryId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE SET NULL #"DIF"."DIF" #CONSTRAINT "DIF_OrganizationId_fkey" FOREIGN KEY ("Originating_Center") REFERENCES "Contact"."Organization" ("OrganizationId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE SET NULL, #CONSTRAINT "Data_Set_Progress_check" CHECK ("Data_Set_Progress"::text = 'Planned'::text OR "Data_Set_Progress"::text = 'In Work'::text OR "Data_Set_Progress"::text = 'Complete'::text) #"Reference"."Reference" #CONSTRAINT "FK_Reference_Data" FOREIGN KEY ("BinaryDataId") REFERENCES "Binary"."Data" ("BinaryDataId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, #CONSTRAINT "Reference_ReferenceTypeId_fkey" FOREIGN KEY ("ReferenceTypeId") REFERENCES "Reference"."ReferenceType" ("ReferenceTypeId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, #CONSTRAINT "ConstrUniek" UNIQUE ("ReferenceTypeId", "TypeReferenceId") #"Profile"."Profile" #CONSTRAINT "Profile_CountryId_fkey" FOREIGN KEY ("CountryId") REFERENCES "Location"."Country" ("CountryId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE SET NULL, #CONSTRAINT "Profile_ReferenceId_fkey" FOREIGN KEY ("ReferenceId") REFERENCES "Reference"."Reference" ("ReferenceId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE SET NULL, #"Profile"."Profile_x_DIF" #CONSTRAINT "Profile_mm_DIF_ProfileId_fkey" FOREIGN KEY ("ProfileId") REFERENCES "Profile"."Profile" ("ProfileId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE #CONSTRAINT "Profile_mm_DIF_DIF_Id_fkey" FOREIGN KEY ("DIF_Id") REFERENCES "DIF"."DIF" ("DIF_Id") MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, #"Attribute"."ValueDescriptor" #CONSTRAINT "ValueDescriptor_AttributeId_fkey" FOREIGN KEY ("AttributeId") REFERENCES "Attribute"."Attribute" ("AttributeId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, #CONSTRAINT "FK_ValueDescriptor_Domain" FOREIGN KEY ("DomainId") REFERENCES "Attribute"."Domain" ("DomainId") MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, #CONSTRAINT "ValueDescriptor_DataTypeId_fkey" FOREIGN KEY ("DataTypeId") REFERENCES "Attribute"."DataType" ("DataTypeId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE SET NULL, #CONSTRAINT "ValueDescriptor_UnitId_fkey" FOREIGN KEY ("UnitId") REFERENCES "Attribute"."Unit" ("UnitId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE SET NULL #"Analysis";"AnalyticalMethod" #CONSTRAINT "AnalyticalMethod_ReferenceId_fkey" FOREIGN KEY ("ReferenceId") REFERENCES "Reference"."Reference" ("ReferenceId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE SET NULL #"Analysis"."Organization_x_AnalyticalMethod" #CONSTRAINT "LaboratoryAnalyticalMethod_AnalyticalMethodId_fkey" FOREIGN KEY ("AnalyticalMethodId") REFERENCES "Analysis"."AnalyticalMethod" ("AnalyticalMethodId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE SET DEFAULT, #CONSTRAINT "OrganizationAnalyticalMethod_OrganizationId_fkey" FOREIGN KEY ("OrganizationId") REFERENCES "Contact"."Organization" ("OrganizationId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, #CONSTRAINT "OrganizationAnalyticalMethod_OrganizationId_AnalyticalMetho_key" UNIQUE ("OrganizationId", "AnalyticalMethodId") #"Profile"."ProfileAttribute" #CONSTRAINT "FK_ProfileAttribute_Profile" FOREIGN KEY ("ProfileId") REFERENCES "Profile"."Profile" ("ProfileId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, #CONSTRAINT "FK_ProfileAttribute_ValueDescriptor" FOREIGN KEY ("ValueDescriptorId") REFERENCES "Attribute"."ValueDescriptor" ("ValueDescriptorId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, #CONSTRAINT "FK_ProfileAttribute_LaboratoryAnalyticalMethod" FOREIGN KEY ("OrganizationAnalyticalMethodId") REFERENCES "Analysis"."Organization_x_AnalyticalMethod" ("OrganizationAnalyticalMethodId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE SET NULL, #CONSTRAINT "FK_ProfileAttribue_Data" FOREIGN KEY ("BinaryDataId") REFERENCES "Binary"."Data" ("BinaryDataId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, #CONSTRAINT "CK_Trust" CHECK ("Trust" = ANY (ARRAY['a'::bpchar, 'b'::bpchar, 'c'::bpchar, 'd'::bpchar])), #CONSTRAINT lodchk CHECK (length("LoD"::text) > 0 OR "LoD" IS NULL) #"Profile"."LayerAttribute" #CONSTRAINT "FK_LayerAttribute_Profile" FOREIGN KEY ("ProfileId" REFERENCES "Profile"."Profile" ("ProfileId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, #CONSTRAINT "FK_LayerAttribute_ValueDescriptor" FOREIGN KEY ("ValueDescriptorId") REFERENCES "Attribute"."ValueDescriptor" ("ValueDescriptorId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, #CONSTRAINT "FK_LayerAttribute_LaboratoryAnalyticalMethod" FOREIGN KEY ("OrganizationAnalyticalMethodId") REFERENCES "Analysis"."Organization_x_AnalyticalMethod" ("OrganizationAnalyticalMethodId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE SET NULL, #CONSTRAINT "FK_LayerAttribute_Data" FOREIGN KEY ("BinaryDataId") REFERENCES "Binary"."Data" ("BinaryDataId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, #CONSTRAINT "CK_Trust" CHECK ("Trust" = ANY (ARRAY['a'::bpchar, 'b'::bpchar, 'c'::bpchar, 'd'::bpchar])), #CONSTRAINT lodchk CHECK (length("LoD"::text) > 0 OR "LoD" IS NULL) #"Profile"."SoilMoisture" #CONSTRAINT "FK_SoilMoisture_Profile" FOREIGN KEY ("ProfileId") REFERENCES "Profile"."Profile" ("ProfileId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, #CONSTRAINT "FK_SoilMoisture_OrganizationAnalyticalMethod" FOREIGN KEY ("OrganizationAnalyticalMethodId") REFERENCES "Analysis"."Organization_x_AnalyticalMethod" ("OrganizationAnalyticalMethodId") MATCH SIMPLE ON UPDATE CASCADE ON DELETE SET NULL, #CONSTRAINT "CK_Trust" CHECK ("Trust" = ANY (ARRAY['a'::bpchar, 'b'::bpchar, 'c'::bpchar, 'd'::bpchar])) ########################### CONSTRAINTS (end) ############################### ########################### VARIABLES (start) ############################### # Contact.Organization organization_dic = {'Name1':'Leibniz Centre for Agricultural Landscape Research', 'Name2':'Institute of Land Use Systems', 'Acronym':'ZALF', 'Telephone':'+49 (0)33432 82-310', 'Telefax':'+49 (0)33432 82-387', 'Email':'vkannemann@zalf.de', 'Internet':'http://www.zalf.de', 'Address1':'Eberswalder Strabe 84','Address2':'', 'City':'Muncheberg', 'State_Prov':'', 'PostalCode':'15374', 'CountryId':'81', 'Notes':'', 'AccessCode':''} #Analysis.AnalyticalMethod publication_dic_01 = {'Title':'Soil quality - Pretreatment of samples for physico-chemical analysis (ISO 11464:2006)', 'ISBN':'', 'Publisher':'International Organization for Standardization (ISO)', 'PublicationYear':'2006', 'Issue':'', 'URL':'http://www.iso.org/iso/home/store/catalogue_tc/catalogue_detail.htm?csnumber=37718', 'Author':['']} publication_dic_02 = {'Title':'Soil quality - Determination of particle size distribution in mineral soil material - Method by sieving and sedimentation (ISO 11277:1998 + ISO 11277:1998 Corrigendum 1:2002)', 'ISBN':'', 'Publisher':'German Institute for Standardization (DIN)', 'PublicationYear':'2002', 'Issue':'', 'URL':'http://www.naw.din.de/cmd?level=tpl-art-detailansicht&committeeid=54739067&artid=53934894&languageid=en&bcrumblevel=3&subcommitteeid=54760493', 'Author':['']} publication_dic_03 = {} publication_dic_05 = {'Title':'Methodenbuch Band 1. Die Untersuchung von Böden. 4th edition', 'ISBN':'', 'Publisher':'VDLUFA-Verlag, Darmstadt', 'PublicationYear':'1991', 'Issue':'', 'URL':'http://www.vdlufa.de/Methodenbuch/index.php?option=com_content&view=article&id=25&Itemid=7', 'Author':['Thun, R.', 'Herrmann, R.', 'Knickmann, E.', 'Hoffmann, G.']} publication_dic_06 = {'Title':'Bodenkundliches Praktikum : eine Einfuehrung in pedologisches Arbeiten fuer Oekologen, insbesondere Land- und Forstwirte, und fuer Geowissenschaftler', 'ISBN':'', 'Publisher':'Hamburg [etc.] : Parey', 'PublicationYear':'1966', 'Issue':'', 'URL':'http://library.wur.nl/WebQuery/clc/193512', 'Author':['von Ernst Schlichting', 'Hans-Peter Blume']} publication_dic_07 = {'Title':'Soil quality - Determination of organic and total carbon after dry combustion (elementary analysis) (ISO 10694:1995)', 'ISBN':'', 'Publisher':'German Institute for Standardization (DIN)', 'PublicationYear':'1996', 'Issue':'', 'URL':'http://www.naw.din.de/cmd?level=tpl-art-detailansicht&committeeid=54739067&artid=2799936&languageid=en&bcrumblevel=3&subcommitteeid=62169019', 'Author':['']} publication_dic_09 = {'Title':'Soil quality - Determination of total nitrogen content by dry combustion ("elemental analysis") (ISO 13878:1998)', 'ISBN':'', 'Publisher':'International Organization for Standardization (ISO)', 'PublicationYear':'1998', 'Issue':'', 'URL':'http://www.iso.org/iso/home/store/catalogue_tc/catalogue_detail.htm?csnumber=23117', 'Author':['']} publication_dic_14 = {'Title':'Soil quality - Determination of dry bulk density (ISO 11272:1998)', 'ISBN':'', 'Publisher':'German Institute for Standardization (DIN)', 'PublicationYear':'1998', 'Issue':'', 'URL':'http://www.naw.din.de/cmd?artid=36566446&contextid=naw&bcrumblevel=1&subcommitteeid=54760493&level=tpl-art-detailansicht&committeeid=54739067&languageid=en', 'Author':['']} publication_dic_15 = {'Title':'Soil quality - Determination of soil water content as a volume fraction using coring sleeves - Gravimetric method (ISO 11461:2001)', 'ISBN':'', 'Publisher':'German Institute for Standardization (DIN)', 'PublicationYear':'2001', 'Issue':'', 'URL':'http://www.naw.din.de/cmd?level=tpl-art-detailansicht&committeeid=54739067&artid=46308562&languageid=en&bcrumblevel=3&subcommitteeid=54760493', 'Author':['']} publication_dic_17 = {'Title':'Bodenkundliche Kartieranleitung. 5. verbesserte und erweiterte Auflage', 'ISBN':'978-3-510-95804-7', 'Publisher':'Ad-Hoc-Arbeitsgruppe Boden', 'PublicationYear':'2005', 'Issue':'', 'URL':'', 'Author':['']} analytical_method_dic_01 = {'attribute':'Coarse sand', 'Name':'ISO 11464', 'Description':'Wet sieving', 'category1':'', 'category2':'', 'category3':''} analytical_method_dic_02 = {'attribute':'Coarse silt', 'Name':'DIN ISO 11277', 'Description':'Pipette method of Koehn', 'category1':'', 'category2':'', 'category3':''} analytical_method_dic_03 = {'attribute':'Total sand', 'Name':'Calculated sum of the individual fractions', 'Description':'Calculated sum of the individual fractions', 'category1':'', 'category2':'', 'category3':''} analytical_method_dic_04 = {'attribute':'Clay', 'Name':'DIN ISO 11277', 'Description':'', 'category1':'', 'category2':'', 'category3':''} analytical_method_dic_05 = {'attribute':'pH', 'Name':'A 5.1.1 VDLUFA Bodenuntersuchung, Methodenbuch I, 4th edition (1997)', 'Description':'Dry soil, 2mm sieved. Add 50ml of 0.1 mol/l KCl solution or 0.01 mol/l CaCl2 or water to 13ml of soil, and allow to stand overnight (shake vigorously at least twice). Before measurement shake vigorously again. Gauge: TitraMaster 85 (Radiometer Analytical)', 'category1':'', 'category2':'', 'category3':''} analytical_method_dic_06 = {'attribute':'Carbonate - carbon', 'Name':'Modified from method of Schlichting / Blume, Bodenkundliches Praktikum, page 108, 1966', 'Description':'Weigh in 250mg to 1000mg soil and mix with 20ml of H3PO4 (1:1 diluted with H2O). CO2 is separated as in the apparatus according to Scheibler-Finkener. Measuring principle: CO2 concentration is measured by means of electrical conductivity. Gauge: Carmhomat 12 D Gas analythical device (Firma Wösthoff o.H.)', 'category1':'', 'category2':'', 'category3':''} analytical_method_dic_07 = {'attribute':'Total carbon', 'Name':'DIN ISO 10694 (August 1996) version 7a', 'Description':'Elemental analysis: approximately 500mg of soil are weighed into ceramic boats and ashed at 1250°C in an oxygen flow. The carbon compounds are oxidized to CO2. Principle of measurement: CO2 is measured via an infrared cell. Gauge: CNS 2000 elemental analyzer', 'category1':'', 'category2':'', 'category3':''} analytical_method_dic_08 = {'attribute':'Organic carbon', 'Name':'Corg = Ct - CO3-C', 'Description':'Corg = Ct - CO3-C', 'category1':'', 'category2':'', 'category3':''} analytical_method_dic_09 = {'attribute':'Total nitrogen', 'Name':'ISO 13878 (1998)', 'Description':'"Elemental analysis: approximately 500mg of soil are weighed into ceramic boats and ashed at 1250°C in an oxygen flow. The nitrogen compounds are oxidized to N2 and NOx. Principle of measurement: Determination of nitrogen content is via thermal conductivity in a TC-Cell. Gauge: CNS 2000 elemental analyzer"', 'category1':'', 'category2':'', 'category3':''} analytical_method_dic_10 = {'attribute':'Humus', 'Name':'Calculated from Corg * 1.72', 'Description':'Calculated from Corg * 1.72', 'category1':'', 'category2':'', 'category3':''} analytical_method_dic_11 = {'attribute':'Phosphorus (P) plant-available', 'Name':'Methodenbuch VDLUFA* A 6.2.1.2', 'Description':'Phosphorus (PDL) and potassium (KDL). Dry soil, sieved to 2 mm, 4 g of air-dried soil are extracted with 200 ml of calcium lactate (pH 3.6). From the filtered solutions, the P and K contents are determined. Measuring principle: P: orthophosphate forms the heteropolic acid phosphomolydic acid (PMS). isopolymolybdic in sulfuric acid solution, the . It is reduced by ascorbic acid to phosphorus molybdenum blue. Measuring wavelength: 819 nm, measuring Photometer AT200 Olympus, K: Determination by flame photometry, gas composition acetylene / air mixture, measuring wavelength: 766.5 nm, meter: atomic absorption photometer (AAS) of solar, Unicam', 'category1':'', 'category2':'', 'category3':''} analytical_method_dic_12 = {'attribute':'Potassium (K) plant-available', 'Name':'Methodenbuch VDLUFA* A 6.2.1.2', 'Description':'Phosphorus (PDL) and potassium (KDL). Dry soil, sieved to 2 mm, 4 g of air-dried soil are extracted with 200 ml of calcium lactate (pH 3.6). From the filtered solutions, the P and K contents are determined. Measuring principle: P: orthophosphate forms the heteropolic acid phosphomolydic acid (PMS). isopolymolybdic in sulfuric acid solution, the . It is reduced by ascorbic acid to phosphorus molybdenum blue. Measuring wavelength: 819 nm, measuring Photometer AT200 Olympus, K: Determination by flame photometry, gas composition acetylene / air mixture, measuring wavelength: 766.5 nm, meter: atomic absorption photometer (AAS) of solar, Unicam', 'category1':'', 'category2':'', 'category3':''} analytical_method_dic_13 = {'attribute':'Magnesium (Mg) plant-available', 'Name':'Methodenbuch VDLUFA* A 6.2.4.1 *Methodenbuch Band1; Die Untersuchung von Böden, VDLUFA-Verlag, Darmstadt 4th edition (1991)', 'Description':'Dry soil, sieved to 2 mm 4 g of air-dried soil were extracted with 40 ml of 0.0125 m solution of calcium chloride. From the filtered solutions Mg - contents determined. Principle: The determination of Mg ions by means of atomic absorption. Gas composition acetylene / air mixture, measuring wavelength: 285.2 nm, Mg, Meter: atomic absorption photometer (AAS) of solar Unicam', 'category1':'', 'category2':'', 'category3':''} analytical_method_dic_14 = {'attribute':'Bulk density', 'Name':'DIN ISO 11272 (dry weight)', 'Description':'Sampling with 100 cm3 of extraction cylinders (undisturbed samples), drying at 105°C', 'category1':'', 'category2':'', 'category3':''} analytical_method_dic_15 = {'attribute':'Water content', 'Name':'DIN ISO 11461', 'Description':'', 'category1':'', 'category2':'', 'category3':''} analytical_method_dic_16 = {'attribute':'Total pore volume', 'Name':'PV = 100 - (TRD * 100/DS)', 'Description':'PV = 100 - (TRD * 100/DS)', 'category1':'', 'category2':'', 'category3':''} analytical_method_dic_17 = {'attribute':'Effective rooting depth', 'Name':'Ad-hoc-AG Boden (2005): Bodenkundl. Kartieranleitung (KA5), p. 340 ff', 'Description':'Derivation for multi-layer soil profiles by: Ad hoc AG Boden (2000): Methods of Soil Science Documentation - evaluation methods ...', 'category1':'', 'category2':'', 'category3':''} analytical_method_dic_18 = {'attribute':'Field capacity in the effective root zone', 'Name':'Ad-hoc-AG Boden (2005): Bodenkundl. Kartieranleitung (KA5), p. 340 ff', 'Description':'Derivation for multi-layer soil profiles by: Ad hoc AG Boden (2000): Methods of Soil Science Documentation - evaluation methods ...', 'category1':'', 'category2':'', 'category3':''} # DIF.DIF dif_dic = {'Entry_ID':'', 'Entry_Title':'', 'Summary':'', 'Metadata_Name':'', 'Metadata_Version':'', 'Quality':'', 'Access_Constraints':'', 'Use_Constraints':'', 'Data_Set_Progress':'', 'DIF_Revision_History':'', 'Multimedia_Sample_File':'', 'Multimedia_Sample_URL':'', 'Multimedia_Sample_Format':'', 'Multimedia_Sample_Caption':'', 'Multimedia_Sample_Description':'', 'Future_DIF_Revision_Date':'', 'Private':'', 'Multimedia_Sample_Uploaded':'', 'DIF.URL': ''} # Reference.Reference ReferenceTypeId = '1' reference_dic = {'Title':'Entwicklung und Vergleich von optimierten Anbausystemen für die landwirtschaft-liche Produktion von Energiepflanzen unter den verschiedenen Standort-bedingungen Deutschlands (EVA)', 'ISBN':'', 'Publisher':'Leibniz-Zentrum für Agrarlandschafts-forschung (ZALF)', 'PublicationYear':'2009', 'Issue':'', 'URL':'www.eva-verbund.de/uploads/media/schlussber_eva1__oekol.pdf', 'Author':['Matthias Willms', 'Michael Glemnitz', 'Johannes Hufnagel']} # Profile.Profile xy_table = 'sch_wosis_raw.zalf_bo_geografie' CountryId = '81' epsg_code_input = '31467'# ??? x_column = '"Rechtswert"' y_column = '"Hochwert"' LocationLoD = 15 Shared = 't'# ??? # 0/f/false, 1/t/true profile_date = 'Datum' # Attribute.Attribute tables_dic = {'sch_wosis_raw.zalf_bo_geografie': '"Profil_Nr"', 'sch_wosis_raw.zalf_bo_profil': '"Profil_Nr"', 'sch_wosis_raw.zalf_bo_chemie': '"Profil_Nr"'} # Profile.LayerAttribute layer_attribute_tables_dic = {'sch_wosis_raw.zalf_bo_profil': ['"HO"','"HU"'], 'sch_wosis_raw.zalf_bo_chemie': ['"HO"','"HU"']} # WOSIS API testing = True wosisDB = None ########################### VARIABLES (end) ############################### ########################### FUNCTIONS (start) ############################### def insert_organization(dicRecord): # Contact.Organization similars = levenshtein_search('"Contact"."Organization"', "Name1", "OrganizationId", dicRecord['Name1']) for k, v in similars: print '| OrganizationId: %s | Name1: %s' % (k, v[0:80]) OrganizationId = raw_input('* Type OrganizationId number from the list above or press enter to continue: ') if OrganizationId == '': similars = levenshtein_search('"Contact"."Organization"', "Name2", "OrganizationId", dicRecord['Name2']) for k, v in similars: print '| OrganizationId: %s | Name2: %s' % (k, v[0:80]) OrganizationId = raw_input('* Type OrganizationId number from the list above or press enter to continue: ') if OrganizationId == '': similars = levenshtein_search('"Contact"."Organization"', "Acronym", "OrganizationId", dicRecord['Acronym']) for k, v in similars: print '| OrganizationId: %s | Acronym: %s' % (k, v[0:80]) OrganizationId = raw_input('* Type OrganizationId number from the list above or press enter to continue: ') if OrganizationId == '': dicRecord = { k : v for k,v in dicRecord.iteritems() if v != '' } OrganizationId = insertOrganization(dicRecord) return OrganizationId def insert_analytical_method(OrganizationId, publication, analytical_method): # Reference.Publication if len(publication['Title']) > 0: similars = levenshtein_search('"Reference"."Publication"', "Title", "PublicationId", publication['Title']) for k, v in similars: print '| PublicationId: %s | Title: %s' % (k, v[0:80]) PublicationId = raw_input('* Type PublicationId number from the list above or press enter to continue: ') if PublicationId == '': PublicationId = insertPublication(publication) # Reference.Reference ReferenceId = insertReference('1', PublicationId) # Analysis.AnalyticalMethod analytical_method['ReferenceId'] = ReferenceId else: PublicationId = ReferenceId = '' del analytical_method['attribute'] # for now analythical methods table do not have this column del analytical_method['category1'] # for now analythical methods table do not have this column del analytical_method['category2'] # for now analythical methods table do not have this column del analytical_method['category3'] # for now analythical methods table do not have this column analytical_method = { k : v for k,v in analytical_method.iteritems() if v != '' } AnalyticalMethodId = insertAnalyticalMethod(analytical_method) # Analysis.Organization_x_AnalyticalMethod dicRecord = {'OrganizationId':OrganizationId, 'AnalyticalMethodId':AnalyticalMethodId, 'Notes':''} dicRecord = { k : v for k,v in dicRecord.iteritems() if v != '' } OrganizationAnalyticalMethodId = insertOrganizationAnalyticalMethod(dicRecord) print 'Analytical method -', analytical_method['Name'], '- inserted.' return PublicationId, ReferenceId, AnalyticalMethodId, OrganizationAnalyticalMethodId def insert_reference(ReferenceTypeId, reference_dic): reference_dic = { k : v for k,v in reference_dic.iteritems() if v != '' } if ReferenceTypeId == '1': TypeReferenceId = insertPublication(reference_dic) elif ReferenceTypeId == '2': TypeReferenceId = insertURL(reference_dic) elif ReferenceTypeId == '3': #TypeReferenceId = insertMap(reference_dic) #THIS FUNCTIONS DO NOT EXIST IN THE API! pass elif ReferenceTypeId == '4': TypeReferenceId = OrganizationId elif ReferenceTypeId == '5': #TypeReferenceId = insertDigitalMedia(reference_dic) #THIS FUNCTIONS DO NOT EXIST IN THE API! pass ReferenceId = insertReference(ReferenceTypeId, TypeReferenceId) return ReferenceId def search_similar_attribute(): search = 'y' while search == 'y' or search == '': keyword = raw_input('* Type string to look for: ') similars = {} sql = ''' SELECT "AttributeId", "ReferenceId", "Name", "Description" FROM "Attribute"."Attribute" WHERE "Name" ILIKE '%''' + keyword + '''%' OR "Description" ILIKE '%''' + keyword + '''%' ORDER BY "AttributeId" ''' wosisDB.cursor.execute(sql) lines = wosisDB.cursor.fetchall() if len(lines) >= 1: for line in lines: similars[line[0]] = [line[1], line[2]] print '| AttributeId: %s | Name: %s | Description: %s' % (line[0], line[2], line[3][0:80]) AttributeId = raw_input('* Type AttributeId number from the list above or press enter to continue: ') if AttributeId == '': search = raw_input('* Search again (Y/n)? ') else: search = 'no' if len(lines) == 0: print ' No match found!' AttributeId = '' search = raw_input('* Search again (Y/n)? ') return AttributeId def legend(OrganizationId): file_name = '/tmp/legend.txt' file = open(file_name, "w") # DATA TYPE file.write('\nDATA TYPE\n\n') sql = ''' SELECT "DataTypeId", "Description" FROM "Attribute"."DataType" ORDER BY "DataTypeId" ''' wosisDB.cursor.execute(sql) lines = wosisDB.cursor.fetchall() for line in lines: cod = line[0] des = line[1] file.write('%s - %s\n' %(cod, des)) #replaces all instances # DATA UNITS file.write('\n\nDATA UNITS\n\n') sql = ''' SELECT "UnitId", "Unit", "Short" FROM "Attribute"."Unit" ORDER BY "UnitId" ''' wosisDB.cursor.execute(sql) lines = wosisDB.cursor.fetchall() for line in lines: cod = line[0] des1 = line[1] des2 = line[2] file.write('%s - %s(%s)\n' %(cod, des1, des2)) # ANALYTICAL METHODS analytical_method_dic = {} analytical_method_dic[''] = '' file.write('\n\nANALYTICAL METHODS\n\n') sql = ''' SELECT DISTINCT o."OrganizationAnalyticalMethodId", a."AnalyticalMethodId", a."Name", a."Description" FROM "Analysis"."AnalyticalMethod" AS a, "Analysis"."Organization_x_AnalyticalMethod" AS o WHERE a."AnalyticalMethodId" = o."AnalyticalMethodId" AND o."OrganizationId" = %s ORDER BY "AnalyticalMethodId" ''' % OrganizationId wosisDB.cursor.execute(sql) lines = wosisDB.cursor.fetchall() for line in lines: cod1 = line[0] cod2 = line[1] des1 = line[2] des2 = line[3] file.write('%s - %s (%s)\n' %(cod2, str(des1), str(des2)[0:100])) analytical_method_dic[str(cod2)] = str(cod1) file.close() return analytical_method_dic ########################### FUNCTIONS (end) ############################### def wosis_uploader(): # Contact.Organization print ''' ================================ ORGANIZATION ================================ ''' OrganizationId = insert_organization(organization_dic) #Analysis.AnalyticalMethod print ''' ================================ ANALYTICAL METHODS ================================ ''' insert_analytical_method(OrganizationId, {}, {'Name':'Unknown', 'Description':'Unknown'}) insert_analytical_method(OrganizationId, {}, {'Name':'Not apply', 'Description':'Not apply'}) insert_analytical_method(OrganizationId, publication_dic_01, analytical_method_dic_01) insert_analytical_method(OrganizationId, publication_dic_02, analytical_method_dic_02) insert_analytical_method(OrganizationId, publication_dic_03, analytical_method_dic_03) insert_analytical_method(OrganizationId, publication_dic_02, analytical_method_dic_04) insert_analytical_method(OrganizationId, publication_dic_05, analytical_method_dic_05) insert_analytical_method(OrganizationId, publication_dic_06, analytical_method_dic_06) insert_analytical_method(OrganizationId, publication_dic_07, analytical_method_dic_07) insert_analytical_method(OrganizationId, publication_dic_03, analytical_method_dic_08) insert_analytical_method(OrganizationId, publication_dic_09, analytical_method_dic_09) insert_analytical_method(OrganizationId, publication_dic_03, analytical_method_dic_10) insert_analytical_method(OrganizationId, publication_dic_05, analytical_method_dic_11) insert_analytical_method(OrganizationId, publication_dic_05, analytical_method_dic_12) insert_analytical_method(OrganizationId, publication_dic_05, analytical_method_dic_13) insert_analytical_method(OrganizationId, publication_dic_14, analytical_method_dic_14) insert_analytical_method(OrganizationId, publication_dic_15, analytical_method_dic_15) insert_analytical_method(OrganizationId, publication_dic_03, analytical_method_dic_16) insert_analytical_method(OrganizationId, publication_dic_17, analytical_method_dic_17) insert_analytical_method(OrganizationId, publication_dic_17, analytical_method_dic_18) # DIF.DIF print ''' ================================ DIF ================================ ''' dif_dic['Originating_Center'] = OrganizationId dif_dic = { k : v for k,v in dif_dic.iteritems() if v != '' } DIF_Id = insertDIF(dif_dic) # Reference.Reference print ''' ================================ REFERENCE ================================ ''' ReferenceId = insert_reference(ReferenceTypeId, reference_dic) # Profile.Profile print ''' ================================ PROFILES ================================ ''' profile_dic = {} # this dictionary is going to store dataset_id and ProfileId sql = ''' SELECT %s, %s, %s FROM %s ORDER BY %s ''' %(tables_dic[xy_table], x_column, y_column, xy_table, tables_dic[xy_table]) wosisDB.cursor.execute(sql) lines = wosisDB.cursor.fetchall() for line in lines: i = line[0] x = line[1] y = line[2] dicRecord = {'CountryId':CountryId, 'ReferenceId':ReferenceId, 'x':x, 'y':y, 'LocationLoD':LocationLoD, 'Shared':Shared} ProfileId = insertProfile(dicRecord) # Profile.Profile_x_DIF dicRecord = {'ProfileId':ProfileId, 'DIF_Id':DIF_Id, 'ProfileName':str(organization_dic['Acronym']) + ' - ' + str(i)} dicRecord = { k : v for k,v in dicRecord.iteritems() if v != '' } ProfileDIFId = insertProfileDIF(dicRecord) profile_dic[i] = ProfileId print ' Number of inserted profiles: %s' % len(lines) # iterate source tables print ''' ================================ ATTRIBUTES ================================ ''' legend(OrganizationId) #subprocess.Popen('firefox -new-tab file:///tmp/legend.txt' , shell=True) subprocess.Popen('gedit /tmp/legend.txt' , shell=True) for table in tables_dic: table_destination = '' print ' Importing data from table: %s' % table while table_destination not in ['p', 'l']: table_destination = raw_input('\n* This attributes are going to ProfileAttribute(p) or LayerAttribute(l)? ') sql = ''' SELECT column_name FROM information_schema.columns WHERE table_schema = '%s' AND table_name = '%s' ORDER BY ordinal_position ''' % (table.split('.')[0], table.split('.')[1]) wosisDB.cursor.execute(sql) lines = wosisDB.cursor.fetchall() for line in lines: column = line[0] print '\n Column: %s' % column # Attribute.Attribute print ' Checking if already exists.' AttributeId = search_similar_attribute() if AttributeId == '': attribute_name = raw_input('* What is the new attribute name? ') attribute_description = raw_input('* What is the new attribute description? ') dicRecord = {'ReferenceId':ReferenceId,'Name':attribute_name,'Description':attribute_description} dicRecord = { k : v for k,v in dicRecord.iteritems() if v != '' } AttributeId = insertAttribute(dicRecord) # Attribute.ValueDescriptor DataTypeId = raw_input('* What is the data type? ') UnitId = raw_input('* What is the unit? ') DomainId = raw_input('* What is the domain, if it has one? ') MinimumValue = raw_input('* What is the minimum value (integer)? ') MaximumValue = raw_input('* What is the maximum value (integer)? ') Hidden = raw_input('* Will it be hidden (t/f)? ') dicRecord = {'AttributeId':AttributeId, 'UnitId':UnitId, 'DataTypeId': DataTypeId, 'DomainId':DomainId, 'MinimumValue':MinimumValue, 'MaximumValue':MaximumValue, 'Hidden':Hidden} dicRecord = { k : v for k,v in dicRecord.iteritems() if v != '' } ValueDescriptorId = insertValueDescriptor(dicRecord) #Analysis.AnalyticalMethod analytical_method_dic = legend(OrganizationId) exists = 'no' while exists != 'yes': AnalyticalMethodId = raw_input('* What is the Analytical Method? ') if AnalyticalMethodId in analytical_method_dic.keys(): exists = 'yes' if AnalyticalMethodId not in analytical_method_dic.keys() or AnalyticalMethodId == '': exists = 'no' # Insert data sql = ''' SELECT %s, "%s" FROM %s ''' % (tables_dic[table], column, table) wosisDB.cursor.execute(sql) lines = wosisDB.cursor.fetchall() print ' Inserting %s rows of data.' % len(lines), for line in lines: table_id = line[0] value = line[1] # Profile.ProfileAttribute if table_destination == 'p': dicRecord = {'ProfileId':profile_dic[table_id], 'ValueDescriptorId':ValueDescriptorId, 'Value':value, 'ValueGroup':'', 'OrganizationAnalyticalMethodId':analytical_method_dic[AnalyticalMethodId], 'LoD':'', 'Trust': 'a', 'Quality':''} dicRecord = { k : v for k,v in dicRecord.iteritems() if v != '' } ProfileAttributeId = insertProfileAttribute(dicRecord) # Profile.LayerAttribute if table_destination == 'l': dicRecord = {'ProfileId':profile_dic[table_id], 'ValueDescriptorId':ValueDescriptorId, 'Value':value, 'ValueGroup':'', 'OrganizationAnalyticalMethodId':analytical_method_dic[AnalyticalMethodId], 'LoD':'', 'Trust': 'a', 'Quality':'', 'LabNotes':'', 'UpperBoundaryCm':layer_attribute_tables_dic[table][0], 'LowerBoundaryCm':layer_attribute_tables_dic[table][1], 'BinaryDataId':''} dicRecord = { k : v for k,v in dicRecord.iteritems() if v != '' } LayerAttributeId = insertLayerAttribute(dicRecord) print 'Done' postDB.cursor.commit() def main(): global wosisDB host = raw_input('Host:') database = raw_input('Database:') password = getpass.getpass('Password:') outputDB ="host = %s dbname =%s user = postgres password =%s" % (host,database,password) wosisDB = WOSISAPI.WOSIS(outputDB,testing=testing) wosis_uploader() del wosisDB if __name__ == "__main__": main()