def tables_columns():
    tables_sql = (
        r"""SELECT tbl_name FROM sqlite_master WHERE (type='table' or type='view') and not (name in"""
        + SQLiteInternalTables()
        + r""") ORDER BY tbl_name"""
    )
    connection_ok, tables = utils.sql_load_fr_db(tables_sql)

    if not connection_ok:
        textstring = u"""Cannot get data from sql """ + utils.returnunicode(tables_sql)
        utils.MessagebarAndLog.critical(bar_msg=u"Error, sql failed, see log message panel", log_msg=textstring)
        return []

    tables_dict = {}

    tablenames = [col[0] for col in tables]
    for tablename in tablenames:
        columns_sql = """PRAGMA table_info (%s)""" % tablename
        connection_ok, columns = utils.sql_load_fr_db(columns_sql)

        if not connection_ok:
            textstring = u"""Cannot get data from sql """ + utils.returnunicode(columns_sql)
            utils.MessagebarAndLog.critical(bar_msg=u"Error, sql failed, see log message panel", log_msg=textstring)
            continue
        tables_dict[tablename] = tuple(sorted(tuple(columns), key=itemgetter(1)))

    return tables_dict
 def calculateaveflow(self):
     PyQt4.QtGui.QApplication.setOverrideCursor(PyQt4.QtCore.Qt.WaitCursor)
     date_from = self.FromDateTime.dateTime().toPyDateTime()
     date_to = self.ToDateTime.dateTime().toPyDateTime()
     #Identify distinct set of obsid and instrumentid with Accvol-data and within the user-defined date_time-interval:
     sql= """select distinct obsid, instrumentid from(select * from w_flow where flowtype = "Accvol" and date_time >="%s" and date_time <="%s" and obsid IN %s)"""%(date_from,date_to,(str(self.observations)).encode('utf-8').replace('[','(').replace(']',')'))
     #utils.pop_up_info(sql)#debug
     uniqueset = utils.sql_load_fr_db(sql)[1]  # The unique set of obsid and instrumentid is kept in uniqueset
     negativeflow = False
     for pyobsid, pyinstrumentid in uniqueset:
         sql= """select date_time, reading from w_flow where flowtype = 'Accvol' and obsid='%s' and instrumentid='%s' and date_time >='%s' and date_time <='%s' order by date_time"""%(pyobsid,pyinstrumentid,date_from,date_to)
         recs = utils.sql_load_fr_db(sql)[1] 
         """Transform data to a numpy.recarray"""
         My_format = [('date_time', datetime.datetime), ('values', float)] #Define format with help from function datetime
         table = np.array(recs, dtype=My_format)  #NDARRAY
         table2=table.view(np.recarray)   # RECARRAY   Makes the two columns into callable objects, i.e. write table2.values
         j=0
         for row in table2:#This is where Aveflow is calculated for each obs and also written to db
             if j>0:#first row is "start-value" for Accvol and there is no Aveflow to be calculated
                 Volume = (table2.values[j] - table2.values[j-1])*1000#convert to L since Accvol is supposed to be in m3
                 """ Get help from function datestr2num to get date and time into float"""
                 DeltaTime = 24*3600*(datestr2num(table2.date_time[j]) - datestr2num(table2.date_time[j-1]))#convert to seconds since numtime is days
                 Aveflow = Volume/DeltaTime#L/s
                 if Aveflow<0:
                     negativeflow = True
                 sql = """insert or ignore into w_flow(obsid,instrumentid,flowtype,date_time,reading,unit) values('%s','%s','Aveflow','%s','%s','l/s')"""%(pyobsid,pyinstrumentid,table2.date_time[j],Aveflow)
                 utils.sql_alter_db(sql)
             j = j + 1
     if negativeflow:
         self.iface.messageBar().pushMessage("Info","Please notice that negative flow was encountered.", 0, duration=5)
     PyQt4.QtGui.QApplication.restoreOverrideCursor()
     self.close()
Exemple #3
0
 def remove_layers(self):
     try:#qgis>2.6
         remove_group = self.root.findGroup(self.group_name)
         self.root.removeChildNode(remove_group)
     except: #qgis < 2.4
         """ FIRST search for and try to remove old layers """        
         ALL_LAYERS = self.iface.mapCanvas().layers()
         if self.group_name == 'Midvatten_OBS_DB':
             for lyr in ALL_LAYERS:
                 name = lyr.name()
                 if (name in self.default_layers) or (name in self.default_nonspatlayers):
                     QgsMapLayerRegistry.instance().removeMapLayers( [lyr.id()] )
                 """ THEN remove old group """
         elif self.group_name == 'Midvatten_data_domains':
             conn_ok, dd_tables = utils.sql_load_fr_db("select name from sqlite_master where name like 'zz_%'")
             if not conn_ok:
                 return
             d_domain_tables = [str(dd_table[0]) for dd_table in dd_tables]
             for lyr in ALL_LAYERS:         
                 name = lyr.name()
                 if name in d_domain_tables:
                     QgsMapLayerRegistry.instance().removeMapLayers( [lyr.id()] )                    
         while self.group_name in self.legend.groups():
             group_index = self.legend.groups().index(self.group_name) 
             self.legend.removeGroup(group_index)
 def calcall(self):
     obsar = utils.sql_load_fr_db('select distinct obsid from w_flow where flowtype="Accvol"')[1]
     self.observations= []
     i=0
     for obs in obsar:
             self.observations.append(str(obs[0]))#we cannot send unicode as string to sql because it would include the u'
             i+=1
     self.calculateaveflow()
Exemple #5
0
 def selection_layer_in_db_or_not(self): #this is not used, it might be if using layer_styles stored in the db
     sql = r"""select name from sqlite_master where name = 'layer_styles'"""
     result = utils.sql_load_fr_db(sql)[1]
     if len(result)==0:#if it is an old database w/o styles
         update_db = utils.askuser("YesNo","""Your database was created with plugin version < 1.1 when layer styles were not stored in the database. You can update this database to the new standard with layer styles (symbols, colors, labels, input forms etc) stored in the database. This will increase plugin stability and multi-user experience but it will also change the layout of all your forms for entering data into the database. Anyway, an update of the database is recommended. Do you want to add these layer styles now?""",'Update database with layer styles?')
         if update_db.result == 1:
             from create_db import AddLayerStyles
             AddLayerStyles(self.settingsdict['database'])
             self.add_layers_new_method()
         else:
             self.add_layers_old_method()
     else:
         self.add_layers_new_method()
 def load_data_domains(self):
     #utils.pop_up_info(msg='This feature is not yet implemented',title='Hold on...')
     #return
     QApplication.setOverrideCursor(QCursor(Qt.WaitCursor))
     err_flag = utils.verify_msettings_loaded_and_layer_edit_mode(qgis.utils.iface, self.ms)#verify midv settings are loaded
     if err_flag == 0:
         conn_ok, dd_tables = utils.sql_load_fr_db("select name from sqlite_master where name like 'zz_%'")
         if not conn_ok:
             return
         d_domain_tables = [str(dd_table[0]) for dd_table in dd_tables]
         err_flag = utils.verify_msettings_loaded_and_layer_edit_mode(qgis.utils.iface, self.ms, d_domain_tables)#verify none of the tables are already loaded and in edit mode
         if err_flag == 0:
             LoadLayers(qgis.utils.iface, self.ms.settingsdict,'Midvatten_data_domains')
     QApplication.restoreOverrideCursor()#now this long process is done and the cursor is back as normal
def staff_list():
    """
    :return: A list of staff members from the staff table
    """
    sql = "SELECT distinct staff from zz_staff"
    sql_result = utils.sql_load_fr_db(sql)
    connection_ok, result_list = sql_result

    if not connection_ok:
        textstring = """Failed to get existing staff from staff table from sql """ + sql
        qgis.utils.iface.messageBar().pushMessage("Error", textstring, 2, duration=10)
        return False, tuple()

    return True, utils.returnunicode(tuple([x[0] for x in result_list]), True)
Exemple #8
0
    def get_distinct_values(tablename, columnname):
        if not tablename or not columnname:
            return []
        sql = '''SELECT distinct "%s" from "%s"'''%(columnname, tablename)
        connection_ok, result = utils.sql_load_fr_db(sql)

        if not connection_ok:
            textstring = u"""Cannot get data from sql """ + utils.returnunicode(sql)
            utils.MessagebarAndLog.critical(
                bar_msg=u"Error, sql failed, see log message panel",
                log_msg=textstring)
            return []

        values = [col[0] for col in result]
        return values
    def upgrade_db(self, set_locale=False):
        from_db = None
        #get full path to the original db to be updated
        if self.db:
            use_current_db = utils.Askuser(
                "YesNo", """Do you want to upgrade %s?""" % self.db,
                'Current database?')
            if use_current_db.result == 0:
                from_db = None
            elif use_current_db.result == 1:
                from_db = self.db
            elif use_current_db.result == '':
                return
        if not from_db:
            from_db = QFileDialog.getOpenFileName(
                None, 'Ange tolknings-db som ska exporteras', '',
                "Spatialite (*.sqlite)")[0]
        if not from_db:
            QApplication.restoreOverrideCursor()
            return None

        #get EPSG in the original db
        EPSG = utils.sql_load_fr_db(
            """SELECT srid FROM geom_cols_ref_sys WHERE Lower(f_table_name) = Lower('gvmag') AND Lower(f_geometry_column) = Lower('geometry')""",
            from_db)

        #preparations to create new db of new design
        if not set_locale:
            set_locale = utils.getcurrentlocale()

        filenamepath = os.path.join(os.path.dirname(__file__), "metadata.txt")
        iniText = QSettings(filenamepath, QSettings.IniFormat)
        verno = str(iniText.value('version'))

        #now create database of the updated design
        from .create_tolkn_db import newdb
        newdbinstance = newdb(verno,
                              user_select_CRS=False,
                              EPSG_code=EPSG[1][0][0],
                              set_locale=set_locale)

        #transfer data to the new database
        foo = utils.UpgradeDatabase(from_db, newdbinstance.dbpath, EPSG)

        #set new database as the current db and load these layers
        if not newdbinstance.dbpath == '':
            self.db = newdbinstance.dbpath
        self.load_the_layers()
Exemple #10
0
 def ChangedLocale(self):    # TODO: remove in version 1.4
     sql = u"select description from about_db where description like 'locale:%'"
     connection_ok, result = utils.sql_load_fr_db(sql)
     if not self.locale_combobox.currentText():
         return
     if connection_ok:
         print(str(result))
         if len(result) > 1:
             utils.MessagebarAndLog.info(bar_msg=u'More than one row with locale found in db. No update can be done.')
             return
         if len(result) == 1:
             sql = u"update or ignore about_db set description='locale:%s'"%self.locale_combobox.currentText()
             sql += u" where description like 'locale:%'"
             utils.sql_alter_db(sql)
         elif len(result) == 0:
             sql = u"insert or ignore into about_db (description) values ('locale:%s')"%self.locale_combobox.currentText()
             utils.sql_alter_db(sql)
 def load_data_domains(self):
     #utils.pop_up_info(msg='This feature is not yet implemented',title='Hold on...')
     #return
     QApplication.setOverrideCursor(QCursor(Qt.WaitCursor))
     err_flag = utils.verify_msettings_loaded_and_layer_edit_mode(
         qgis.utils.iface, self.ms)  #verify midv settings are loaded
     if err_flag == 0:
         conn_ok, dd_tables = utils.sql_load_fr_db(
             "select name from sqlite_master where name like 'zz_%'")
         if not conn_ok:
             return
         d_domain_tables = [str(dd_table[0]) for dd_table in dd_tables]
         err_flag = utils.verify_msettings_loaded_and_layer_edit_mode(
             qgis.utils.iface, self.ms, d_domain_tables
         )  #verify none of the tables are already loaded and in edit mode
         if err_flag == 0:
             LoadLayers(qgis.utils.iface, self.ms.settingsdict,
                        'Midvatten_data_domains')
     QApplication.restoreOverrideCursor(
     )  #now this long process is done and the cursor is back as normal
    def upgrade_db(self, set_locale=False):
        from_db = None
        #get full path to the original db to be updated
        if self.db:
            use_current_db = utils.Askuser("YesNo","""Do you want to upgrade %s?"""%self.db,'Current database?')
            if use_current_db.result == 0:
                from_db = None
            elif use_current_db.result == 1:
                from_db = self.db
            elif use_current_db.result == '':
                return
        if not from_db:
            from_db = QFileDialog.getOpenFileName(None, 'Ange tolknings-db som ska exporteras','',"Spatialite (*.sqlite)")[0]
        if not from_db:
            QApplication.restoreOverrideCursor()
            return None

        #get EPSG in the original db
        EPSG = utils.sql_load_fr_db("""SELECT srid FROM geom_cols_ref_sys WHERE Lower(f_table_name) = Lower('gvmag') AND Lower(f_geometry_column) = Lower('geometry')""",from_db)

        #preparations to create new db of new design
        if not set_locale:
            set_locale = utils.getcurrentlocale()

        filenamepath = os.path.join(os.path.dirname(__file__),"metadata.txt" )
        iniText = QSettings(filenamepath , QSettings.IniFormat)
        verno = str(iniText.value('version'))

        #now create database of the updated design
        from .create_tolkn_db import newdb
        newdbinstance = newdb(verno, user_select_CRS=False, EPSG_code = EPSG[1][0][0], set_locale=set_locale)

        #transfer data to the new database
        foo = utils.UpgradeDatabase(from_db,newdbinstance.dbpath, EPSG)

        #set new database as the current db and load these layers
        if not newdbinstance.dbpath=='':
            self.db = newdbinstance.dbpath
        self.load_the_layers()
Exemple #13
0
    def add_layers_new_method(self):
        try:#qgis>=2.4
            if self.group_name == 'Midvatten_OBS_DB':
                position_index = 0
            else:
                position_index = 1
            MyGroup = self.root.insertGroup(position_index, self.group_name)
        except:#qgis < 2.4
            MyGroup = self.legend.addGroup (self.group_name,1,-1)
        uri = QgsDataSourceURI()
        uri.setDatabase(self.settingsdict['database'])
        canvas = self.iface.mapCanvas()
        layer_list = []
        map_canvas_layer_list=[]

        if self.group_name == 'Midvatten_OBS_DB':
            for tablename in self.default_nonspatlayers: #first load all non-spatial layers
                uristring= 'dbname="' + self.settingsdict['database'] + '" table="' + tablename + '"'
                layer = QgsVectorLayer(uristring,tablename, 'spatialite')
                layer_list.append(layer)

            for tablename in self.default_layers:    # then all the spatial ones
                uri.setDataSource('',tablename, 'Geometry')
                layer = QgsVectorLayer(uri.uri(), tablename, 'spatialite') # Adding the layer as 'spatialite' instead of ogr vector layer is preferred
                layer_list.append(layer)

        elif self.group_name == 'Midvatten_data_domains': #if self.group_name == 'Midvatten_data_domains':
            conn_ok, dd_tables = utils.sql_load_fr_db("select name from sqlite_master where name like 'zz_%'")
            if not conn_ok:
                return
            d_domain_tables = [str(dd_table[0]) for dd_table in dd_tables]
            for tablename in d_domain_tables:
                uristring= 'dbname="' + self.settingsdict['database'] + '" table="' + tablename + '"'
                layer = QgsVectorLayer(uristring,tablename, 'spatialite')
                layer_list.append(layer)

        #now loop over all the layers and set styles etc
        for layer in layer_list:
            if not layer.isValid():
                utils.pop_up_info(layer.name() + ' is not valid layer')
                print(layer.name() + ' is not valid layer')
                pass
            else:
                map_canvas_layer_list.append(QgsMapCanvasLayer(layer))
                try:#qgis>=2.4
                    QgsMapLayerRegistry.instance().addMapLayers([layer],False)
                    MyGroup.insertLayer(0,layer)
                    #MyGroup.addLayer(layer)
                except:#qgis<2.4
                    QgsMapLayerRegistry.instance().addMapLayers([layer])
                    group_index = self.legend.groups().index(self.group_name) 
                    self.legend.moveLayer (self.legend.layers()[0],group_index)

                if self.group_name == 'Midvatten_OBS_DB':
                    layer.setEditorLayout(1)#perhaps this is unnecessary since it gets set from the loaded qml below?

                #now try to load the style file
                stylefile_sv = os.path.join(os.sep,os.path.dirname(__file__),"..","definitions",layer.name() + "_sv.qml")
                stylefile = os.path.join(os.sep,os.path.dirname(__file__),"..","definitions",layer.name() + ".qml")
                if  utils.getcurrentlocale()[0] == 'sv_SE' and os.path.isfile( stylefile_sv ): #swedish forms are loaded only if locale settings indicate sweden
                    try:
                        layer.loadNamedStyle(stylefile_sv)
                    except:
                        try:
                            layer.loadNamedStyle(stylefile)
                        except:
                            pass
                else:
                    try:
                        layer.loadNamedStyle(stylefile)
                    except:
                        pass

                if layer.name() == 'obs_points':#zoom to obs_points extent
                    obsp_lyr = layer
                    canvas.setExtent(layer.extent())
                elif layer.name() == 'w_lvls_last_geom':#we do not want w_lvls_last_geom to be visible by default
                    self.legend.setLayerVisible(layer,False)
                else:
                    pass

        #finally refresh canvas
        canvas.refresh()
Exemple #14
0
    def add_layers(self):
        obs_db_existing = self.root.findGroup('Midvatten_OBS_DB')
        if obs_db_existing:
            position_index = 1
        else:
            position_index = 0

        MyGroup = qgis.core.QgsLayerTreeGroup(name=self.group_name,
                                              checked=True)
        self.root.insertChildNode(position_index, MyGroup)
        MySubGroup = MyGroup.addGroup('värdeförråd')
        uri = QgsDataSourceUri()
        uri.setDatabase(self.dbpath)
        canvas = self.iface.mapCanvas()
        layer_list = []
        layer_name_list = []
        layer_dict = dict()  # name as key and layer as value

        # first add all data domains (beginning with zz_ in the database)
        conn_ok, dd_tables = utils.sql_load_fr_db(
            "select name from sqlite_master where name like 'zz_%'",
            self.dbpath)
        if not conn_ok:
            return
        d_domain_tables = [str(dd_table[0]) for dd_table in dd_tables]
        for tablename in d_domain_tables:
            uristring = 'dbname="' + self.dbpath + '" table="' + tablename + '"'
            layer = QgsVectorLayer(uristring, tablename, 'spatialite')
            layer_list.append(layer)
            layer_name_list.append(layer.name())

        # Then some specific tables
        for tablename in ['tillromr_summaflode'
                          ]:  #, 'profil' # Implementera vid behov
            try:
                uristring = 'dbname="' + self.dbpath + '" ' + r"""table='{}'""".format(
                    tablename)
                layer = QgsVectorLayer(uristring, tablename, 'spatialite')
                layer_list.append(layer)
            except:
                pass

        #then load all spatial layers
        layers = default_layers(
        )  # ordered dict with layer-name:(zz_layer-name,layer_name_for_map_legend)
        for tablename, tup in list(layers.items()):
            try:
                uri.setDataSource('', tablename, 'geometry')
                layer = QgsVectorLayer(
                    uri.uri(), tablename, 'spatialite'
                )  # Adding the layer as 'spatialite' instead of ogr vector layer is preferred
                if layer.isValid():
                    layer_list.append(layer)
                    layer_name_list.append(layer.name())
                else:
                    qgis.utils.iface.messageBar().pushMessage(
                        "Warning",
                        "Table %s was not valid. DB probably created w old plugin version."
                        % str(tablename),
                        1,
                        duration=5)
            except:
                qgis.utils.iface.messageBar().pushMessage(
                    "Warning",
                    "Table %s not found in db. DB probably created w old plugin version."
                    % str(tablename),
                    1,
                    duration=5)
        #now loop over all the layers and set styles etc
        for layer in layer_list:
            QgsProject.instance().addMapLayers([layer], False)
            if layer.name() in d_domain_tables:
                MySubGroup.insertLayer(0, layer)
            else:
                MyGroup.insertLayer(0, layer)

            layer_dict[layer.name()] = layer

            #now try to load the style file
            stylefile = os.path.join(os.sep, os.path.dirname(__file__),
                                     "sql_strings",
                                     layer.name() + ".qml")
            try:
                layer.loadNamedStyle(stylefile)
            except:
                pass
            if layer.name() == 'gvmag':  #zoom to gvmag extent
                canvas.setExtent(layer.extent())
            else:
                pass

            if layer.name() in defs.unchecked_layers():
                #QgsProject.instance().layerTreeRoot().findLayer(layer.id()).setItemVisibilityChecked(False)
                MyGroup.findLayer(layer.id()).setItemVisibilityChecked(False)
                #w_lvls_last_geom.setItemVisibilityCheckedRecursive(False)

        MySubGroup.setExpanded(False)

        # fix value relations
        for lyr in list(layers.keys()):
            if lyr in layer_name_list:
                if not layers[lyr][0] == None:
                    #self.create_layer_value_relations(layer_dict[lyr], layer_dict[layers[lyr]], layer_dict[lyr].dataProvider().fieldNameIndex('typ'), 'typ','beskrivning')
                    self.create_layer_value_relations(
                        layer_dict[lyr], layer_dict[layers[lyr][0]],
                        layer_dict[lyr].dataProvider().fieldNameIndex('typ'),
                        'typ', 'beskrivning')

        #special fix for gvflode
        self.create_layer_value_relations(
            layer_dict['gvflode'], layer_dict['zz_gvmag'],
            layer_dict['gvflode'].dataProvider().fieldNameIndex('intermag'),
            'typ', 'beskrivning')
        for projektdependent_layer in ['profillinje'
                                       ]:  #, 'profil' # Implementera vid behov
            if projektdependent_layer in layer_dict:
                self.create_layer_value_relations(
                    layer_dict[projektdependent_layer],
                    layer_dict['zz_projekt'],
                    layer_dict[projektdependent_layer].dataProvider(
                    ).fieldNameIndex('projekt'), 'pkuid', 'namn')

        #last, rename to readable names in map legend
        for layer in layer_list:
            #for lyr in layers.keys():
            if layer.name() in layers:
                try:
                    layer.setLayerName(layers[layer.name()][1])
                except:
                    pass

        #finally refresh canvas
        canvas.refresh()
def PlotTypesDict(international="no"):
    """
    This dictionary is used by sectionplot (matplotlib) to compare with all possible geoshorts in stratigraphy table
    (Also used to generate dictionaries for stratigraphy plot (Qt))
    Default method is to read the database table zz_strat and generate the dictionary from columns 'strat' and 'geoshorts'
    The user may update these fields in the zz_strat table to use other stratigraphy units and other abbreviations (in geoshorts)
    Fallback method use dictionary defined in the code below
    """
    # success, Dict = utils.create_dict_from_db_2_cols(('strata','geoshort','zz_strat'))
    success, Dict = utils.get_sql_result_as_dict("select strata, geoshort from zz_strat")
    succss_strata, strata_order = utils.sql_load_fr_db("select strata from zz_stratigraphy_plots order by ROWID")
    if not success:
        print("fallback method using PlotTypesDict from code")
        if international == "no" and utils.getcurrentlocale() == "sv_SE":
            """
            Dict = {u"Okänt" : u"not in ('berg','b','rock','ro','grovgrus','grg','coarse gravel','cgr','grus','gr','gravel','mellangrus','grm','medium gravel','mgr','fingrus','grf','fine gravel','fgr','grovsand','sag','coarse sand','csa','sand','sa','mellansand','sam','medium sand','msa','finsand','saf','fine sand','fsa','silt','si','lera','ler','le','clay','cl','morän','moran','mn','till','ti','torv','t','peat','pt','fyll','fyllning','f','made ground','mg','land fill')",
            "Berg"  : u"in ('berg','b','rock','ro')",
            "Grovgrus" : u"in ('grovgrus','grg','coarse gravel','cgr')",
            "Grus" : u"in ('grus','gr','gravel')",
            "Mellangrus" : u"in ('mellangrus','grm','medium gravel','mgr')",
            "Fingrus" : u"in ('fingrus','grf','fine gravel','fgr')",
            "Grovsand" : u"in ('grovsand','sag','coarse sand','csa')",
            "Sand" : u"in ('sand','sa')",
            "Mellansand" : u"in ('mellansand','sam','medium sand','msa')",
            "Finsand" : u"in ('finsand','saf','fine sand','fsa')",
            "Silt" : u"in ('silt','si')",
            "Lera" : u"in ('lera','ler','le','clay','cl')",
            u"Morän" : u"in ('morän','moran','mn','till','ti')",
            "Torv" : u"in ('torv','t','peat','pt')",
            "Fyll":u"in ('fyll','fyllning','f','made ground','mg','land fill')"}
            """
            dictionary = OrderedDict(
                [
                    (
                        u"Okänt",
                        u"not in ('berg','b','rock','ro','grovgrus','grg','coarse gravel','cgr','grus','gr','gravel','mellangrus','grm','medium gravel','mgr','fingrus','grf','fine gravel','fgr','grovsand','sag','coarse sand','csa','sand','sa','mellansand','sam','medium sand','msa','finsand','saf','fine sand','fsa','silt','si','lera','ler','le','clay','cl','morän','moran','mn','till','ti','torv','t','peat','pt','fyll','fyllning','f','made ground','mg','land fill')",
                    ),
                    ("Berg", u"in ('berg','b','rock','ro')"),
                    ("Grovgrus", u"in ('grovgrus','grg','coarse gravel','cgr')"),
                    ("Grus", u"in ('grus','gr','gravel')"),
                    ("Mellangrus", u"in ('mellangrus','grm','medium gravel','mgr')"),
                    ("Fingrus", u"in ('fingrus','grf','fine gravel','fgr')"),
                    ("Grovsand", u"in ('grovsand','sag','coarse sand','csa')"),
                    ("Sand", u"in ('sand','sa')"),
                    ("Mellansand", u"in ('mellansand','sam','medium sand','msa')"),
                    ("Finsand", u"in ('finsand','saf','fine sand','fsa')"),
                    ("Silt", u"in ('silt','si')"),
                    ("Lera", u"in ('lera','ler','le','clay','cl')"),
                    (u"Morän", u"in ('morän','moran','mn','till','ti')"),
                    ("Torv", u"in ('torv','t','peat','pt')"),
                    ("Fyll", u"in ('fyll','fyllning','f','made ground','mg','land fill')"),
                ]
            )
        else:
            """
            Dict = {u"Unknown" : u"not in ('berg','b','rock','ro','grovgrus','grg','coarse gravel','cgr','grus','gr','gravel','mellangrus','grm','medium gravel','mgr','fingrus','grf','fine gravel','fgr','grovsand','sag','coarse sand','csa','sand','sa','mellansand','sam','medium sand','msa','finsand','saf','fine sand','fsa','silt','si','lera','ler','le','clay','cl','morän','moran','mn','till','ti','torv','t','peat','pt','fyll','fyllning','f','made ground','mg','land fill')",
            "Rock"  : u"in ('berg','b','rock','ro')",
            "Coarse gravel" : u"in ('grovgrus','grg','coarse gravel','cgr')",
            "Gravel" : u"in ('grus','gr','gravel')",
            "Medium gravel" : u"in ('mellangrus','grm','medium gravel','mgr')",
            "Fine gravel" : u"in ('fingrus','grf','fine gravel','fgr')",
            "Coarse sand" : u"in ('grovsand','sag','coarse sand','csa')",
            "Sand" : u"in ('sand','sa')",
            "Medium sand" : u"in ('mellansand','sam','medium sand','msa')",
            "Fine sand" : u"in ('finsand','saf','fine sand','fsa')",
            "Silt" : u"in ('silt','si')",
            "Clay" : u"in ('lera','ler','le','clay','cl')",
            "Till" : u"in ('morän','moran','mn','till','ti')",
            "Peat" : u"in ('torv','t','peat','pt')",
            "Fill":u"in ('fyll','fyllning','f','made ground','mg','land fill')"}
            """
            dictionary = OrderedDict(
                [
                    (
                        "Unknown",
                        u"not in ('berg','b','rock','ro','grovgrus','grg','coarse gravel','cgr','grus','gr','gravel','mellangrus','grm','medium gravel','mgr','fingrus','grf','fine gravel','fgr','grovsand','sag','coarse sand','csa','sand','sa','mellansand','sam','medium sand','msa','finsand','saf','fine sand','fsa','silt','si','lera','ler','le','clay','cl','morän','moran','mn','till','ti','torv','t','peat','pt','fyll','fyllning','f','made ground','mg','land fill')",
                    ),
                    ("Rock", u"in ('berg','b','rock','ro')"),
                    ("Coarse gravel", u"in ('grovgrus','grg','coarse gravel','cgr')"),
                    ("Gravel", u"in ('grus','gr','gravel')"),
                    ("Medium gravel", u"in ('mellangrus','grm','medium gravel','mgr')"),
                    ("Fine gravel", u"in ('fingrus','grf','fine gravel','fgr')"),
                    ("Coarse sand", u"in ('grovsand','sag','coarse sand','csa')"),
                    ("Sand", u"in ('sand','sa')"),
                    ("Medium sand", u"in ('mellansand','sam','medium sand','msa')"),
                    ("Fine sand", u"in ('finsand','saf','fine sand','fsa')"),
                    ("Silt", u"in ('silt','si')"),
                    ("Clay", u"in ('lera','ler','le','clay','cl')"),
                    ("Till", u"in ('morän','moran','mn','till','ti')"),
                    ("Peat", u"in ('torv','t','peat','pt')"),
                    ("Fill", u"in ('fyll','fyllning','f','made ground','mg','land fill')"),
                ]
            )
    else:
        """manually create dictionary to reuse old code"""
        dictionary = OrderedDict({})
        # all_geoshorts = r"""not in ('"""
        # for key, value in sorted(Dict.iteritems()):
        for strata in strata_order:
            tl = r"""in ('"""
            for geoshort in Dict[strata[0]]:
                tl += geoshort[0] + r"""', '"""
                # all_geoshorts+=geoshort[0] + r"""', '"""
            tl = utils.rstrip(r""", '""", tl) + r""")"""
            # all_geoshorts = utils.rstrip(r""", '""",all_geoshorts) + r""")"""
            dictionary[strata[0]] = tl
        # all_geoshorts+=r"""')"""
    return dictionary