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()
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()
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()
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()
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()
