def buildAddress(self, builder, candidates, withID, withDistance = False):
items = []
for item in candidates:
if item[4] == "č.p.":
houseNumber = str(item[5])
recordNumber = ""
else:
houseNumber = ""
recordNumber = str(item[5])
mop = noneToString(item[9])
if mop != "":
pom = mop.split()
districtNumber = pom[1]
else:
districtNumber = ""
subStr = compileaddress.compileAddress(
builder,
noneToString(item[3]),
houseNumber,
recordNumber,
noneToString(item[6]),
noneToString(item[7]),
str(item[8]),
noneToString(item[1]),
noneToString(item[2]),
districtNumber
)
if withID:subStr = self.addId("id", str(item[0]), subStr, builder)
if withDistance:subStr = self.addId("distance", str(item[10]), subStr, builder)
items.append(subStr)
return items
def buildAddress(self, builder, candidates, withID, withDistance=False):
items = []
for item in candidates:
if item[4] == "č.p.":
houseNumber = str(item[5])
recordNumber = ""
else:
houseNumber = ""
recordNumber = str(item[5])
mop = noneToString(item[9])
if mop != "":
pom = mop.split()
districtNumber = pom[1]
else:
districtNumber = ""
subStr = compileaddress.compileAddress(builder,
noneToString(item[3]),
houseNumber, recordNumber,
noneToString(item[6]),
noneToString(item[7]),
str(item[8]),
noneToString(item[1]),
noneToString(item[2]),
districtNumber)
if withID: subStr = self.addId("id", str(item[0]), subStr, builder)
if withDistance:
subStr = self.addId("distance", str(item[10]), subStr, builder)
items.append(subStr)
return items
def getFullTextAutocompleteResults(queryParams, nameToken, resultFormat, maxCount = 10):
hasNumber, searchSQL = parseFullTextToken(queryParams, nameToken)
rows = []
if searchSQL != "":
searchSQL += " limit " + str(maxCount)
try:
db = PostGISDatabase()
cursor = db.conection.cursor()
cursor.execute(searchSQL)
except psycopg2.Error as e:
import sys
return[e.pgerror, e.pgcode]
rowCount = 0
for row in cursor:
rowCount += 1
htmlItems = []
for i in range(len(row)):
if True:
htmlItems.append(row[i])
else:
item = row[i]
htmlItems.append(item)
rowLabel = None
if hasNumber:
street, houseNumber, locality, zipCode, orientationNumber, orientationNumberCharacter, localityPart, typ_so, nazev_mop = row
houseNumber, recordNumber = analyseRow(typ_so, houseNumber)
districtNumber = extractDictrictNumber(nazev_mop)
rowLabel = compileaddress.compileAddress(builder, street, houseNumber, recordNumber, orientationNumber, orientationNumberCharacter, zipCode, locality, localityPart, districtNumber)
if resultFormat.lower() == "addressparts":
idValue = itemToStr(street) + "," + itemToStr(houseNumber) + "," + itemToStr(recordNumber) + "," + itemToStr(orientationNumber) + "," + \
itemToStr(orientationNumberCharacter) + "," + itemToStr(zipCode) + "," + \
itemToStr(locality) + "," + itemToStr(localityPart) + "," + itemToStr(districtNumber)
else:
idValue = rowLabel[rowLabel.find(", ") + 2:]
else:
idValue = row[0] + ", " + row[1]
if rowLabel == None:
rowLabel = ", ".join(htmlItems)
rowValue = rowLabel
value = '{"id":"' + idValue + '","label":"' + rowLabel + '","value":"' + rowValue + '"}'
rows.append(value)
if rowCount >= maxCount:
break
return rows
def IDCheckServiceHandler(queryParams, response, builder):
response.mimeFormat = builder.getMimeFormat()
address = RUIANConnection.findAddress(sharetools.getKeyValue(queryParams, "AddressPlaceId"))
response.handled = True
if address:
html = compileaddress.compileAddress(builder, address.street, address.houseNumber, address.recordNumber, address.orientationNumber, address.orientationNumberCharacter, address.zipCode, address.locality, address.localityPart, address.districtNumber)
response.htmlData = builder.listToResponseText([html])
return response
def testcompileAddress(self):
for s in addressCompileSamples:
self.assertEqual(
AT.compileAddress(AT.TextFormat.plainText,
s.street, s.houseNumber, s.recordNumber, s.orientationNumber, s.zipCode, s.locality, s.localityPart, s.districtNumber),
s.textResult
)
pass
def IDCheckServiceHandler(queryParams, response, builder):
response.mimeFormat = builder.getMimeFormat()
address = RUIANConnection.findAddress(
sharetools.getKeyValue(queryParams, "AddressPlaceId"))
response.handled = True
if address:
html = compileaddress.compileAddress(
builder, address.street, address.houseNumber, address.recordNumber,
address.orientationNumber, address.orientationNumberCharacter,
address.zipCode, address.locality, address.localityPart,
address.districtNumber)
response.htmlData = builder.listToResponseText([html])
return response
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
def getFullTextAutocompleteResults(queryParams,
nameToken,
resultFormat,
maxCount=10):
hasNumber, searchSQL = parseFullTextToken(queryParams, nameToken)
rows = []
if searchSQL != "":
searchSQL += " limit " + str(maxCount)
try:
db = PostGISDatabase()
cursor = db.conection.cursor()
cursor.execute(searchSQL)
except psycopg2.Error as e:
import sys
return [e.pgerror, e.pgcode]
rowCount = 0
for row in cursor:
rowCount += 1
htmlItems = []
for i in range(len(row)):
if True:
htmlItems.append(row[i])
else:
item = row[i]
htmlItems.append(item)
rowLabel = None
if hasNumber:
street, houseNumber, locality, zipCode, orientationNumber, orientationNumberCharacter, localityPart, typ_so, nazev_mop = row
houseNumber, recordNumber = analyseRow(typ_so, houseNumber)
districtNumber = extractDictrictNumber(nazev_mop)
rowLabel = compileaddress.compileAddress(
builder, street, houseNumber, recordNumber,
orientationNumber, orientationNumberCharacter, zipCode,
locality, localityPart, districtNumber)
if resultFormat.lower() == "addressparts":
idValue = itemToStr(street) + "," + itemToStr(houseNumber) + "," + itemToStr(recordNumber) + "," + itemToStr(orientationNumber) + "," + \
itemToStr(orientationNumberCharacter) + "," + itemToStr(zipCode) + "," + \
itemToStr(locality) + "," + itemToStr(localityPart) + "," + itemToStr(districtNumber)
else:
idValue = rowLabel[rowLabel.find(", ") + 2:]
else:
idValue = row[0] + ", " + row[1]
if rowLabel == None:
rowLabel = ", ".join(htmlItems)
rowValue = rowLabel
value = '{"id":"' + idValue + '","label":"' + rowLabel + '","value":"' + rowValue + '"}'
rows.append(value)
if rowCount >= maxCount:
break
return rows
def getFillResults(queryParams):
sqlItems = {
"HouseNumber":
"cast(cislo_domovni as text) like '%s%%' and typ_so='č.p.'",
"RecordNumber":
"cast(cislo_domovni as text) ilike '%s%%' and typ_so<>'č.p.'",
"OrientationNumber": "cast(cislo_orientacni as text) like '%s%%'",
"OrientationNumberCharacter": "znak_cisla_orientacniho = '%s'",
"ZIPCode": "cast(psc as text) like '%s%%'",
"Locality": "nazev_obce ilike '%%%s%%'",
"Street": "nazev_ulice ilike '%%%s%%'",
"LocalityPart": "nazev_casti_obce ilike '%%%s%%'",
"DistrictNumber": "nazev_mop = 'Praha %s'"
}
firstRow = int(getQueryValue(queryParams, FIRSTROW_QUERYPARAM, "0"))
if firstRow < 0: firstRow = 0
pageSize = int(getQueryValue(queryParams, PAGESIZE_QUERYPARAM, "15"))
limitValue = 100
if limitValue < firstRow + pageSize:
limitValue = firstRow + pageSize
fields = " cislo_domovni, cislo_orientacni, znak_cisla_orientacniho, psc, nazev_obce, nazev_casti_obce, nazev_mop, nazev_ulice, typ_so "
result = ""
sqlParts = []
for key in sqlItems:
value = getQueryValue(queryParams, key, "")
if value != "":
sqlParts.append(sqlItems[key] % (value))
if len(sqlParts) == 0: return ""
sqlBase = u" from %s where " % (ADDRESSPOINTS_TABLENAME) + " and ".join(
sqlParts)
searchSQL = u"select %s %s order by nazev_obce, nazev_casti_obce, psc, nazev_ulice, nazev_mop, typ_so, cislo_domovni, cislo_orientacni, znak_cisla_orientacniho limit %d" % (
fields, sqlBase, limitValue)
rows = selectSQL(searchSQL)
resultArray = []
rowNumber = 0
for row in rows:
if rowNumber >= firstRow:
htmlItems = []
for i in range(len(row)):
htmlItems.append(numberToString(row[i]))
houseNumber, orientationNumber, orientationNumberCharacter, zipCode, locality, localityPart, nazev_mop, street, typ_so = row
houseNumber, recordNumber = analyseRow(typ_so, houseNumber)
districtNumber = extractDictrictNumber(nazev_mop)
rowLabel = compileaddress.compileAddress(
builder, street, houseNumber, recordNumber, orientationNumber,
orientationNumberCharacter, zipCode, locality, localityPart,
districtNumber)
resultArray.append(":".join(htmlItems) + ":" + rowLabel)
if len(resultArray) >= pageSize: break
rowNumber = rowNumber + 1
rowCount = rows.rowcount
if rowCount == limitValue:
countSQL = u"select count(*) " + sqlBase
countRows = selectSQL(countSQL)
countRow = countRows.fetchone()
rowCount = countRow[0]
result = "%d#%d#%d#%s" % (firstRow, len(resultArray), rowCount,
";".join(resultArray))
return result
def getFillResults(queryParams):
sqlItems = {
"HouseNumber" : "cast(cislo_domovni as text) like '%s%%' and typ_so='č.p.'",
"RecordNumber" : "cast(cislo_domovni as text) ilike '%s%%' and typ_so<>'č.p.'",
"OrientationNumber" : "cast(cislo_orientacni as text) like '%s%%'",
"OrientationNumberCharacter" : "znak_cisla_orientacniho = '%s'",
"ZIPCode" : "cast(psc as text) like '%s%%'",
"Locality" : "nazev_obce ilike '%%%s%%'",
"Street" : "nazev_ulice ilike '%%%s%%'",
"LocalityPart" : "nazev_casti_obce ilike '%%%s%%'",
"DistrictNumber" : "nazev_mop = 'Praha %s'"
}
firstRow = int(getQueryValue(queryParams, FIRSTROW_QUERYPARAM, "0"))
if firstRow < 0: firstRow = 0
pageSize = int(getQueryValue(queryParams, PAGESIZE_QUERYPARAM, "15"))
limitValue = 100
if limitValue < firstRow + pageSize:
limitValue = firstRow + pageSize
fields = " cislo_domovni, cislo_orientacni, znak_cisla_orientacniho, psc, nazev_obce, nazev_casti_obce, nazev_mop, nazev_ulice, typ_so "
result = ""
sqlParts = []
for key in sqlItems:
value = getQueryValue(queryParams, key, "")
if value != "":
sqlParts.append(sqlItems[key] % (value))
if len(sqlParts) == 0: return ""
sqlBase = u" from %s where " % (ADDRESSPOINTS_TABLENAME) + " and ".join(sqlParts)
searchSQL = u"select %s %s order by nazev_obce, nazev_casti_obce, psc, nazev_ulice, nazev_mop, typ_so, cislo_domovni, cislo_orientacni, znak_cisla_orientacniho limit %d" % (fields, sqlBase, limitValue)
rows = selectSQL(searchSQL)
resultArray = []
rowNumber = 0
for row in rows:
if rowNumber >= firstRow:
htmlItems = []
for i in range(len(row)):
htmlItems.append(numberToString(row[i]))
houseNumber, orientationNumber, orientationNumberCharacter, zipCode, locality, localityPart, nazev_mop, street, typ_so = row
houseNumber, recordNumber = analyseRow(typ_so, houseNumber)
districtNumber = extractDictrictNumber(nazev_mop)
rowLabel = compileaddress.compileAddress(builder, street, houseNumber, recordNumber, orientationNumber, orientationNumberCharacter, zipCode, locality, localityPart, districtNumber)
resultArray.append(":".join(htmlItems) + ":" + rowLabel)
if len(resultArray) >= pageSize: break
rowNumber = rowNumber + 1
rowCount = rows.rowcount
if rowCount == limitValue:
countSQL = u"select count(*) " + sqlBase
countRows = selectSQL(countSQL)
countRow = countRows.fetchone()
rowCount = countRow[0]
result = "%d#%d#%d#%s" % (firstRow, len(resultArray), rowCount, ";".join(resultArray))
return result