def _findCoordinatesByAddress(dictionary):
if dictionary.has_key("districtNumber") and dictionary["districtNumber"] != "":
dictionary["districtNumber"] = "Praha " + dictionary["districtNumber"]
first = True
con = psycopg2.connect(host=DATABASE_HOST, database=DATABASE_NAME, port= PORT, user=USER_NAME, password=PASSWORD)
cur = con.cursor()
query = "SELECT latitude, longitude, gid, nazev_obce, nazev_casti_obce, nazev_ulice, cislo_domovni, typ_so, cislo_orientacni, znak_cisla_orientacniho, psc, nazev_mop FROM " + TABLE_NAME + " WHERE "
for key in dictionary:
if dictionary[key] != "":
if first:
query += ITEM_TO_DBFIELDS[key] + " = '" + dictionary[key] + "'"
first = False
else:
query += " AND " + ITEM_TO_DBFIELDS[key] + " = '" + dictionary[key] + "'"
query += "LIMIT 25"
cur.execute(query)
rows = cur.fetchall()
coordinates = []
for row in rows:
if (row[0] is not None) and (row[1] is not None):
(houseNumber, recordNumber) = HTTPShared.analyseRow(row[7], numberToString(row[6]))
coordinates.append((str("{:10.2f}".format(row[0])).strip(),str("{:10.2f}".format(row[1])).strip(), row[2], row[3], noneToString(row[4]), noneToString(row[5]), houseNumber, recordNumber, numberToString(row[8]), noneToString(row[9]), numberToString(row[10]), numberValue(noneToString(row[11]))))
else:
pass #co se ma stat kdyz adresa nema souradnice?
return coordinates
def _findCoordinates(ID):
con = psycopg2.connect(host=DATABASE_HOST, database=DATABASE_NAME, port= PORT, user=USER_NAME, password=PASSWORD)
cur = con.cursor()
cur.execute("SELECT latitude, longitude, gid, nazev_obce, nazev_casti_obce, nazev_ulice, cislo_domovni, typ_so, cislo_orientacni, znak_cisla_orientacniho, psc, nazev_mop FROM " + TABLE_NAME + " WHERE gid = "+ str(ID))
row = cur.fetchone()
if row and row[0] is not None and row[1] is not None:
(houseNumber, recordNumber) = HTTPShared.analyseRow(row[7], numberToString(row[6]))
c = (str("{:10.2f}".format(row[1])).strip(), str("{:10.2f}".format(row[0])).strip(), row[2], row[3], noneToString(row[4]), noneToString(row[5]), houseNumber, recordNumber, numberToString(row[8]), noneToString(row[9]), numberToString(row[10]), numberValue(noneToString(row[11])))
return [c]
else:
return []
def _findAddress(ID):
con = psycopg2.connect(host=DATABASE_HOST, database=DATABASE_NAME, port= PORT, user=USER_NAME, password=PASSWORD)
cur = con.cursor()
cur.execute("SELECT nazev_ulice, cislo_domovni, typ_so, cislo_orientacni, znak_cisla_orientacniho, psc, nazev_obce, nazev_casti_obce, nazev_mop FROM " + TABLE_NAME + " WHERE gid = "+ str(ID))
row = cur.fetchone()
if row:
(houseNumber, recordNumber) = HTTPShared.analyseRow(row[2], numberToString(row[1]))
a= numberValue(noneToString(row[8]))
return Address(noneToString(row[0]),houseNumber,recordNumber,numberToString(row[3]), noneToString(row[4]),numberToString(row[5]),noneToString(row[6]),noneToString(row[7]),a)
else:
return None
def _findCoordinatesByAddress(dictionary):
if dictionary.has_key(
"districtNumber") and dictionary["districtNumber"] != "":
dictionary["districtNumber"] = "Praha " + dictionary["districtNumber"]
first = True
con = psycopg2.connect(host=DATABASE_HOST,
database=DATABASE_NAME,
port=PORT,
user=USER_NAME,
password=PASSWORD)
cur = con.cursor()
query = "SELECT latitude, longitude, gid, nazev_obce, nazev_casti_obce, nazev_ulice, cislo_domovni, typ_so, cislo_orientacni, znak_cisla_orientacniho, psc, nazev_mop FROM " + TABLE_NAME + " WHERE "
for key in dictionary:
if dictionary[key] != "":
if first:
query += ITEM_TO_DBFIELDS[key] + " = '" + dictionary[key] + "'"
first = False
else:
query += " AND " + ITEM_TO_DBFIELDS[key] + " = '" + dictionary[
key] + "'"
query += "LIMIT 25"
cur.execute(query)
rows = cur.fetchall()
coordinates = []
for row in rows:
if (row[0] is not None) and (row[1] is not None):
(houseNumber,
recordNumber) = HTTPShared.analyseRow(row[7],
numberToString(row[6]))
coordinates.append(
(str("{:10.2f}".format(row[0])).strip(),
str("{:10.2f}".format(row[1])).strip(), row[2], row[3],
noneToString(row[4]), noneToString(row[5]), houseNumber,
recordNumber, numberToString(row[8]), noneToString(row[9]),
numberToString(row[10]), numberValue(noneToString(row[11]))))
else:
pass #co se ma stat kdyz adresa nema souradnice?
return coordinates
def _findAddress(ID):
con = psycopg2.connect(host=DATABASE_HOST,
database=DATABASE_NAME,
port=PORT,
user=USER_NAME,
password=PASSWORD)
cur = con.cursor()
cur.execute(
"SELECT nazev_ulice, cislo_domovni, typ_so, cislo_orientacni, znak_cisla_orientacniho, psc, nazev_obce, nazev_casti_obce, nazev_mop FROM "
+ TABLE_NAME + " WHERE gid = " + str(ID))
row = cur.fetchone()
if row:
(houseNumber,
recordNumber) = HTTPShared.analyseRow(row[2], numberToString(row[1]))
a = numberValue(noneToString(row[8]))
return Address(noneToString(row[0]), houseNumber, recordNumber,
numberToString(row[3]), noneToString(row[4]),
numberToString(row[5]), noneToString(row[6]),
noneToString(row[7]), a)
else:
return None
def _findCoordinates(ID):
con = psycopg2.connect(host=DATABASE_HOST,
database=DATABASE_NAME,
port=PORT,
user=USER_NAME,
password=PASSWORD)
cur = con.cursor()
cur.execute(
"SELECT latitude, longitude, gid, nazev_obce, nazev_casti_obce, nazev_ulice, cislo_domovni, typ_so, cislo_orientacni, znak_cisla_orientacniho, psc, nazev_mop FROM "
+ TABLE_NAME + " WHERE gid = " + str(ID))
row = cur.fetchone()
if row and row[0] is not None and row[1] is not None:
(houseNumber,
recordNumber) = HTTPShared.analyseRow(row[7], numberToString(row[6]))
c = (str("{:10.2f}".format(row[1])).strip(),
str("{:10.2f}".format(row[0])).strip(), row[2], row[3],
noneToString(row[4]), noneToString(row[5]), houseNumber,
recordNumber, numberToString(row[8]), noneToString(row[9]),
numberToString(row[10]), numberValue(noneToString(row[11])))
return [c]
else:
return []
def buildGIDsTable():
logger.info("Building table ac_gids")
logger.info("------------------------")
connection = psycopg2.connect(host=config.databaseHost,
database=config.databaseName,
port=config.databasePort,
user=config.databaseUserName,
password=config.databasePassword)
try:
createTempTable(connection)
cursor = getAddressRows(connection)
try:
if cursor == None: return
logger.info("Inserting rows")
logger.info("----------------------")
insertCursor = connection.cursor()
builder = HTTPShared.MimeBuilder("texttoonerow")
row_count = 0
gaugecount = 0
for row in cursor:
row_count += 1
gaugecount += 1
try:
street, houseNumber, locality, zipCode, orientationNumber, orientationNumberCharacter, localityPart, typ_so, nazev_mop, gid = row
houseNumber, recordNumber = HTTPShared.analyseRow(
typ_so, houseNumber)
districtNumber = HTTPShared.extractDictrictNumber(
nazev_mop)
rowLabel = compileaddress.compileAddress(
builder, street, houseNumber, recordNumber,
orientationNumber, orientationNumberCharacter, zipCode,
locality, localityPart, districtNumber)
insertSQL = "INSERT INTO ac_gids (gid, address) VALUES (%s, '%s')" % (
gid, rowLabel)
insertCursor.execute(insertSQL)
connection.commit()
if gaugecount >= 1000:
gaugecount = 0
logger.info(str(row_count) + " rows")
except psycopg2.Error as e:
logPsycopg2Error(e)
logger.error(
str(row_count) + " " + insertSQL + " failed. ")
exitApp()
pass
logger.info("Done - %d rows inserted." % row_count)
finally:
cursor.close()
#renameTempTable(connection)
logger.info("Building table ac_gids done.")
finally:
connection.close()
pass