예제 #1
0
def get_placerefs():
    '''Map place IDs to manor IDs. Replaces duplicate place IDs using place mapper.'''
    print "get_placerefs()"
    # open tab file
    fn = os.path.join(os.path.dirname(__file__), 'new_data/ByPlaceForAHRC.txt')
    f = open(fn, 'r')
    file_lines = f.readlines()
    cursor.execute('delete from domes_manor_place;')
    # get values from tab file 
    for (counter, line) in enumerate(file_lines): 
           if counter==0:
               continue
           values = util.line_to_values(line,13)
           manor_id = values[1]
           place_id = values[12]
           if not place_id:
               continue
           # Check if this place ID has been stored in our mapper, replace it if so
           if place_id in place_mapper.keys():
               place_id = place_mapper[place_id]
           sql_string = "INSERT INTO domes_manor_place (manor_id, place_id) SELECT "
           sql_string += manor_id + ", " + place_id + " WHERE ("
           sql_string += manor_id + ", " + place_id + ") NOT IN "
           sql_string += "(SELECT manor_id, place_id FROM domes_manor_place);"
           cursor.execute(sql_string)
    conn.commit()
예제 #2
0
def get_images():
    print 'get_images()'
    fn = os.path.join(os.path.dirname(__file__), 'new_data/ImagesForAHRC.txt')
    f = open(fn, 'r')
    file_lines = f.readlines()
    # clear any existing entries
    cursor.execute('delete from domes_image;')
    for (counter, line) in enumerate(file_lines): 
        if counter == 0:
            continue
        else:   
            values = util.line_to_values(line,10)
            structidx = values[0]
            county = values[1]
            phillimore = util.postgres_escape(values[2])
            imagesub = util.postgres_escape(values[3])
            if not values[4]:
                continue
            image = util.postgres_escape(values[4].replace('\\', '/'))
            marked = util.postgres_escape(values[5])
            x1 = values[6]
            y1 = values[7]
            x2 = values[8]
            y2 = values[9]
            ld_file = None
            # don't add entries that don't have a corresponding manor id
            sql_string = 'SELECT COUNT(*) AS COUNT FROM domes_manor WHERE structidx=' + structidx
            cursor.execute(sql_string)
            count = cursor.fetchone()[0]
            if count > 0:
                sql_string = "INSERT INTO domes_image (manor_id,phillimore,imagesub,image,ld_file_id,"
                sql_string += "marked,x1,y1,x2,y2) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                cursor.execute(sql_string, (structidx,phillimore,imagesub,image,ld_file,marked,x1,y1,x2,y2))
    conn.commit()
예제 #3
0
def get_livestock():
    print "get_livestock()"
    fn = os.path.join(os.path.dirname(__file__), 'new_data/LivestockAnna.txt')
    f = open(fn, 'r')
    file_lines = f.readlines()
    for (counter, line) in enumerate(file_lines): 
        if counter == 0:
            continue
        else:
            values = util.line_to_values(line,23)
            structidx = values[0]
            cobs_1086 = values[3]
            cobs_1066 = values[4]
            cattle_1086  = values[5]
            cattle_1066 = values[6]
            cows_1086 = values[7]
            cows_1066 = values[8]
            pigs_1086 = values[9]
            pigs_1066 = values[10]
            sheep_1086 = values[11]
            sheep_1066 = values[12]
            goats_1086  = values[13]
            goats_1066  = values[14]
            beehives_1086 = values[15]
            beehives_1066 = values[16]
            wild_mares_1086 = values[17]
            wild_mares_1066 = values[18]
            other_1086 = values[19]
            other_code_1086 = util.postgres_escape(values[20])
            other_1066 = values[21]
            other_codes_1066 = util.postgres_escape(values[22]) 
            sql_string = "UPDATE domes_manor SET cobs_1086=%s, cobs_1066=%s, "
            sql_string += "cattle_1086=%s, cattle_1066=%s, cows_1086=%s, cows_1066=%s, pigs_1086=%s, "
            sql_string += "pigs_1066=%s, sheep_1086=%s, sheep_1066=%s, goats_1086=%s, goats_1066=%s, "
            sql_string += "beehives_1086=%s, beehives_1066=%s, wild_mares_1086=%s, wild_mares_1066=%s, "
            sql_string += "other_1086=%s, other_code_1086=%s, other_1066=%s, other_codes_1066=%s "
            sql_string += " WHERE structidx=%s;"
            cursor.execute(sql_string, (cobs_1086, cobs_1066, \
                           cattle_1086, cattle_1066, cows_1086, cows_1066, pigs_1086, pigs_1066, sheep_1086, sheep_1066, \
                           goats_1086, goats_1066, beehives_1086, beehives_1066, wild_mares_1086, wild_mares_1066, \
                           other_1086, other_code_1086, other_1066, other_codes_1066, structidx))
    conn.commit()
예제 #4
0
def get_people():
    print 'get_people()'
    # open tab file
    fn = os.path.join(os.path.dirname(__file__), 'new_data/UniqueNames.txt')
    f = open(fn, 'r')
    file_lines = f.readlines()
    # clear any existing entries
    cursor.execute('delete from domes_person;')
    for (counter, line) in enumerate(file_lines): 
        if counter == 0:
            continue
        else:
            values = util.line_to_values(line,6)
            #print values
            namesidx = values[0]
            name = values[1]
            if name is None:
                continue
            name_slug = slugify(name)[:49]
            name = util.postgres_escape(name)
            name = name.decode("utf-8")
            namecode = util.postgres_escape(values[2])
            gendercode = util.postgres_escape(gender_codes[values[3]])
            churchcode = util.postgres_escape(church_codes[values[4]])
            #print namecode, gendercode, churchcode
            xrefs = values[5]
            if xrefs:
                #print xrefs
                xrefs = xrefs.decode("utf-8").encode("utf-8")
            xrefs = util.postgres_escape(xrefs)
            sql_string = "INSERT INTO domes_person (namesidx, name, name_slug, "
            sql_string += "namecode, gendercode, churchcode, xrefs) "
            sql_string += " VALUES (%s,%s,%s,%s,%s,%s,%s)"
            cursor.execute(sql_string, (namesidx, name, name_slug,\
                                  namecode, gendercode, churchcode, xrefs))
    conn.commit()
예제 #5
0
def get_places():
    '''
    Turn PlacesForAHRC into a set of unique places - i.e. places with 
    unique name and grid combination.
    If we find multiple places with the same vill/grid combination (this is 
    quite common, as places are often listed under different counties or areas)
    we update the County/Area references, and store the IDs temporarily in the
    place_mapper dict, and use this to map from Manors to Places.
    '''
    print "get_places()"
    global place_mapper
    # open tab file
    fn = os.path.join(os.path.dirname(__file__), 'new_data/PlacesForAHRC.txt')
    f = open(fn, 'r')
    file_lines = f.readlines()
    cursor.execute('delete from domes_place;')
    for (counter, line) in enumerate(file_lines): 
           if counter==0:
               continue
           values = util.line_to_values(line,9)
           id = values[0]
           print id
           county = values[1]
           if not county: # TODO: check this
               continue
           else:
               county = util.postgres_escape(county, False)
           phillimore = util.postgres_escape(values[2], False)
           hundred = util.postgres_escape(values[3], False)
           hundred_slug = slugify(hundred)
           hundred, status = place_status(hundred)
           sql_string = "INSERT INTO domes_hundred (name,name_slug,status) SELECT "
           sql_string += hundred + ", '" + hundred_slug + "', " + status + " WHERE "
           sql_string += hundred + " NOT IN (SELECT NAME FROM domes_hundred);"
           cursor.execute(sql_string)
           vill = values[4]
           # `' = place no longer exists but can be identified on ground 
           # [] name inside brackets = lost, only approximately located
           if vill=="" or not vill:
                continue
           if vill[0]=="`":
                pass
           vill_slug = slugify(vill)[:49]
           vill = util.postgres_escape(vill, False)
           vill, status = place_status(vill)
           area = util.postgres_escape(values[5], False)
           area_slug = slugify(area)
           # Create an area entry if one doesn't exist already.
           if area!="NULL": 
               sql_string = "INSERT INTO domes_area (name,name_slug) SELECT "
               sql_string += area + ", '" + area_slug + "' WHERE "
               sql_string += area + " NOT IN (SELECT NAME FROM domes_area);"
               cursor.execute(sql_string)
           xrefs = util.postgres_escape(values[6], False)
           grid = util.postgres_escape(values[7], False)
           os_codes = util.postgres_escape(values[8], False)
           if grid!="NULL" and grid!=None:
               location = util.convert_os_to_coords(grid.strip("'"))
           else:
               grid="'XX0000'"
               location="NULL"
               # TODO: But! Sometimes there are places that cannot be
               # located on the ground. Sometimes, though, there are 
               # multiple possible places, referenced in PlaceForms.
               # This is the 'Great and Little Abington' problem. 
               # Deal with this somehow. 
           # Check for existing entries with the same name/grid/vill combo.
           sql_string = "SELECT * from domes_place WHERE grid="
           sql_string += grid + " AND vill=" + vill + ";"
           cursor.execute(sql_string)
           results = cursor.fetchall()
           if len(results)==0:
               sql_string = "INSERT INTO domes_place (id,phillimore,hundred_id,"
               sql_string += "vill,vill_slug,xrefs,grid,os_codes,location,status) VALUES ("
               sql_string += id + ", " + phillimore + ", " + hundred + ", "
               sql_string += vill + ", '" + vill_slug + "', " + xrefs + ", "
               sql_string += grid + ", " + os_codes + ", " + location + ", " + status + ");"
               cursor.execute(sql_string)
           else: # duplicate found - just update county and area refs with existing ID 
               place_mapper[id] = str(results[0][0])
               id = str(results[0][0])
           sql_string = "SELECT * from domes_place_county WHERE place_id="
           sql_string += id + " AND county_id=" + county + ";"
           cursor.execute(sql_string)
           results = cursor.fetchall()
           if len(results)==0:
              sql_string = "INSERT INTO domes_place_county (place_id,county_id) VALUES ("      
              sql_string += id + ", " + county + ");"
              cursor.execute(sql_string)
           sql_string = "SELECT * from domes_place_area WHERE area_id="
           sql_string += area + " AND place_id=" + id + ";"
           cursor.execute(sql_string)
           results = cursor.fetchall()
           if len(results)==0:
               sql_string = "INSERT INTO domes_place_area (place_id,area_id) VALUES ("      
               sql_string += id + ", " + area + ");"
    conn.commit()
예제 #6
0
def get_trwowners():
    print "get_trwowners()"
    fn = os.path.join(os.path.dirname(__file__), 'new_data/TRWForAHRC.txt')
    f = open(fn, 'r')
    file_lines = f.readlines()
    cursor.execute('delete from domes_manor_lord86;')
    cursor.execute('delete from domes_manor_teninchief;')
    unique_names, match_names = util.get_unique_names()
    missing_lord86 = [None, '140050', '150500', '258850', '278150', '298890',\
         '322550', '373650', '39050', '405550', '414040', '519750', '554950',\
         '62450', '85555', '92360']
    missing_teninchief = [None, '10350', '10400', '106500', '106550', \
         '141150', '183350', '20150', '239550', '287950', '288050', '352600',\
         '425400', '440530', '444200', '501850', '519750', '82260', '85600',\
         '92360']
    for (counter, line) in enumerate(file_lines): 
        if counter == 0:
            continue
        else:   
            values = util.line_to_values(line,15)
            trw_id = values[0]
            structidx = values[1]
            #demesne86 = util.postgres_escape(demesne_code[values[7]])
            idxteninchief = values[13]
            if idxteninchief in missing_teninchief:
                continue
            if idxteninchief not in unique_names:
                match_found = False
                for potential_match in match_names:
                   if idxteninchief==potential_match['matchidx']:
                       idxteninchief=potential_match['unique']
                       match_found = True
                       break
                if not match_found:
                    if idxteninchief not in missing_teninchief:
                        missing_teninchief.append(idxteninchief)
            idxlord86 = values[14]
            if idxlord86 in missing_lord86:
                continue
            if (idxlord86 not in unique_names):
                match_found = False
                for potential_match in match_names:
                   if idxlord86==potential_match['matchidx']:
                       idxlord86=potential_match['unique']
                       match_found = True
                       break
                if not match_found:
                    if idxlord86 not in missing_lord86:
                        missing_lord86.append(idxlord86)
            if idxlord86:
                sql_string = "SELECT manor_id from domes_manor_lord86 WHERE manor_id="
                sql_string += structidx + " AND person_id=" + idxlord86 + ";"
                cursor.execute(sql_string)
                if not cursor.fetchall():
                    sql_string = "INSERT INTO domes_manor_lord86 (manor_id, person_id) VALUES ("
                    sql_string += structidx + ", " + idxlord86 + ");"
                    cursor.execute(sql_string)
            if idxteninchief:
                sql_string = "SELECT manor_id from domes_manor_teninchief WHERE manor_id="
                sql_string += structidx + " AND person_id=" + idxteninchief + ";"
                cursor.execute(sql_string)
                if not cursor.fetchall():
                    sql_string = "INSERT INTO domes_manor_teninchief (manor_id, person_id) VALUES ("
                    sql_string += structidx + ", " + idxteninchief + ");"
                    cursor.execute(sql_string)
    print "missing lord86s", missing_lord86
    print "missing teninchiefs", missing_teninchief
    conn.commit()
예제 #7
0
def get_treowners():
    print "get_treowners()"
    # open tab file
    fn = os.path.join(os.path.dirname(__file__), 'new_data/TREForAHRC.txt')
    f = open(fn, 'r')
    file_lines = f.readlines()
    cursor.execute('delete from domes_manor_lord66;')
    cursor.execute('delete from domes_manor_overlord66;')
    unique_names, match_names = util.get_unique_names()
    missing_lord66 = ['114700', '120750', '120810', '126950', '156850',\
               '165150', '167070', '171720', '185950', '23300', '23350',\
                '252200', '259390', '272000', '372010', '382300', '397560',\
                '399450', '431700', '476750', '48700', '495170', '519100',\
                '524260', '532050', '576050', '62450', '85500']
    missing_overlord66 = ['126950', '156850', '159250', '160400', '259380',\
                   '259390', '344560', '344670', '50650', '50830', '530050',\
                   '532050', '574150']
    for (counter, line) in enumerate(file_lines): 
        if counter == 0:
            continue
        else:   
            values = util.line_to_values(line,14)
            tre_id = values[0]
            structidx = values[1]
            if not structidx:
                continue
            idxoverlord66 = values[7]
            if idxoverlord66 in missing_overlord66:
                continue
            if idxoverlord66 is not None:
                if (idxoverlord66 not in unique_names): # if this ID isn't in our unique list
                    match_found = False
                    for potential_match in match_names: 
                       if idxoverlord66==potential_match['matchidx']:
                           idxoverlord66=potential_match['unique']
                           match_found = True
                           break
                    if not match_found:
                        #print 'match not found for idxoverlord66 ' + str(idxoverlord66)
                        if idxoverlord66 not in missing_overlord66:
                             missing_overlord66.append(idxoverlord66)
            idxlord66 = values[8]
            if idxlord66 in missing_lord66:
                continue
            if idxlord66 is not None:
                if (idxlord66 not in unique_names):
                    match_found = False
                    for potential_match in match_names:
                       if idxlord66==potential_match['matchidx']:
                           idxlord66=potential_match['unique']
                           match_found = True
                           break
                    if not match_found:
                       #missing_lord66.append(idxlord66)
                       if idxlord66 not in missing_lord66:
                            missing_lord66.append(idxlord66)
            if idxlord66:
                sql_string = "SELECT manor_id from domes_manor_lord66 WHERE manor_id="
                sql_string += structidx + " AND person_id=" + idxlord66 + ";"
                cursor.execute(sql_string)
                if not cursor.fetchall():
                    sql_string = "INSERT INTO domes_manor_lord66 (manor_id, person_id) VALUES ("
                    sql_string += structidx + ", " + idxlord66 + ");"
                    cursor.execute(sql_string)
            if idxoverlord66:
                sql_string = "SELECT manor_id from domes_manor_overlord66 WHERE manor_id="
                sql_string += structidx + " AND person_id=" + idxoverlord66 + ";"
                cursor.execute(sql_string)
                if not cursor.fetchall():
                    sql_string = "INSERT INTO domes_manor_overlord66 (manor_id, person_id) VALUES ("
                    sql_string += structidx + ", " + idxoverlord66 + ");"
                    cursor.execute(sql_string)
    print "missing lord66s", missing_lord66
    print "missing overlord66s", missing_overlord66
    conn.commit()
예제 #8
0
def get_manors():
    print "get_manors()"
    fn = os.path.join(os.path.dirname(__file__), 'new_data/ManorsForAHRC.txt')
    f = open(fn, 'r')
    file_lines = f.readlines()
    cursor.execute('delete from domes_manor;')
    for (counter, line) in enumerate(file_lines): 
        if counter == 0:
            continue
        else:
            values = util.line_to_values(line,56)
            # basics
            structidx = values[0]
            county = values[1] 
            phillimore = util.postgres_escape(values[2])
            headofmanor = values[3]
            # print headofmanor
            # print util.postgres_escape(headofmanor)
            # headofmanor = None
            waste = util.postgres_escape(values[49])
            waste66 = util.postgres_escape(values[50])
            wasteqr = util.postgres_escape(values[51])
            waste86 = util.postgres_escape(values[52])
            duplicates = util.postgres_escape(values[53])
            subholdings = util.postgres_escape(values[54])
            notes = util.postgres_escape(values[55])
            # valuation
            geld = values[4]
            gcode = util.postgres_escape(values[5])
            villtax = values[6]
            taxedon = values[7]
            value86 = values[44]
            value66 = values[45]
            valueqr = values[46]
            value_string = util.postgres_escape(values[47])
            render = util.postgres_escape(values[48])
            # land
            lordsland = values[8]
            newland = values[9]
            ploughlands = values[10]
            pcode = util.postgres_escape(values[11])
            lordsploughs = values[12]
            mensploughs = values[13]
            totalploughs = values[14]
            lordsploughspossible = values[15]
            mensploughspossible = values[16]
            totalploughspossible = values[17] #30
            # people
            villagers = values[18]
            smallholders = values[19]
            slaves = values[20]
            femaleslaves = values[21]
            freemen = values[22]
            free2men = values[23]
            priests = values[24]
            cottagers = values[25]
            otherpop = values[26]
            miscpop = values[27]
            miscpopcategories = util.postgres_escape(values[28])
            burgesses = values[29]#42
            #other stuff in the manor - mills, pasture etc
            mills = values[30]
            millvalue = values[31]
            meadow = util.postgres_escape(values[32])
            meadowunits = util.postgres_escape(values[33])
            pasture = util.postgres_escape(values[34])
            pastureunits = util.postgres_escape(values[35])
            woodland = util.postgres_escape(values[36])
            woodlandunits = util.postgres_escape(values[37])
            fisheries = values[38]
            salthouses = values[39]
            payments = values[40]
            paymentsunits = util.postgres_escape(values[41])
            churches = values[42]
            churchland = values[43]#56
            sql_string = "INSERT INTO domes_manor (structidx, county_id, phillimore, headofmanor,"
            sql_string += "duplicates, subholdings, notes, waste, waste66, wasteqr, waste86,"
            sql_string += "geld,gcode,villtax,taxedon,value86,value66,valueqr,value_string,render,"#20
            sql_string += "lordsland,newland,ploughlands,"
            sql_string += "pcode,lordsploughs,mensploughs,totalploughs,lordsploughspossible,"
            sql_string += "mensploughspossible,totalploughspossible,"#30
            sql_string += "villagers,smallholders,slaves,femaleslaves,"
            sql_string += "freemen,free2men,priests,cottagers,otherpop,miscpop,miscpopcategories,burgesses,"
            sql_string += "mills,millvalue,meadow,meadowunits,"#46
            sql_string += "pasture,pastureunits,woodland,woodlandunits,fisheries,salthouses,payments,"
            sql_string += "paymentsunits,churches,churchland) VALUES ("#56
            sql_string += "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,"
            sql_string += "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,"
            sql_string += "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,"
            sql_string += "%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            cursor.execute(sql_string, (structidx,county,phillimore,headofmanor,duplicates,subholdings,notes,\
                          waste,waste66,wasteqr,waste86,geld,gcode,villtax,\
                          taxedon,value86,value66,valueqr,value_string,render,lordsland,\
                          newland,ploughlands,pcode,lordsploughs,mensploughs,totalploughs,lordsploughspossible, \
                          mensploughspossible,totalploughspossible,villagers,smallholders,slaves,femaleslaves,freemen, \
                          free2men,priests,cottagers,otherpop,miscpop,miscpopcategories,burgesses,\
                          mills,millvalue,meadow,meadowunits,pasture,pastureunits,woodland,\
                          woodlandunits,fisheries,salthouses,payments,paymentsunits,churches,churchland))
    conn.commit()
예제 #9
0
# We'll use this in TREOwners and TRWOwners
##############################################################
import os, sys
import util

names_in = os.path.join(os.path.dirname(__file__), 'new_data/NamesForAHRC.txt')
names_file = open(names_in, 'r')
file_lines = names_file.readlines()
unique_people = []
name_matches = []
print len(file_lines)-1," lines in file"
for (counter, line) in enumerate(file_lines): 
    if counter == 0:
        continue
    else:
        values = util.line_to_values(line,8)
        row = []
        probable = False
        row.append(values[0]) #NamesIdx
        if (c in values[1] for c in '(){}<>'): # only a probable match if any of these chars are there
            probable = True
        stripped_name = values[1].replace("(", "").replace(")", "").replace("{", "").replace("}", "").replace("<", "").replace(">", "")
        row.append(stripped_name) #Name
        # (ditch phillimore and county refs, in cols 2 and 3)
        row.append(values[4]) #NameCode
        row.append(values[5]) #GenderCode
        row.append(values[6]) #ChurchCode
        row.append(values[7]) #Xrefs
        match_found = False
        #print 'looking up ' + name
        for d in unique_people: # unique names already found
예제 #10
0
# We'll use this in TREOwners and TRWOwners
##############################################################
import os, sys
import util

names_in = os.path.join(os.path.dirname(__file__), 'new_data/NamesForAHRC.txt')
names_file = open(names_in, 'r')
file_lines = names_file.readlines()
unique_people = []
name_matches = []
print len(file_lines) - 1, " lines in file"
for (counter, line) in enumerate(file_lines):
    if counter == 0:
        continue
    else:
        values = util.line_to_values(line, 8)
        row = []
        probable = False
        row.append(values[0])  #NamesIdx
        if (c in values[1] for c in '(){}<>'
            ):  # only a probable match if any of these chars are there
            probable = True
        stripped_name = values[1].replace("(", "").replace(")", "").replace(
            "{", "").replace("}", "").replace("<", "").replace(">", "")
        row.append(stripped_name)  #Name
        # (ditch phillimore and county refs, in cols 2 and 3)
        row.append(values[4])  #NameCode
        row.append(values[5])  #GenderCode
        row.append(values[6])  #ChurchCode
        row.append(values[7])  #Xrefs
        match_found = False