def getConversionInfo (request, **kwargs):
"""
Returns a json with the conversion table for a given map and the list of fields for that map
:param request:
:param kwargs:
:return:
"""
print "Retrieving conversion table..."
proxy_id = kwargs["proxy_id"]
meta_id = kwargs["meta_id"]
shape_id = kwargs["shape_id"]
# loading pre-existing conversion; should include modelid and actual conversion structure as fields with corresponding field and value conversion
try:
mapconv = proxy_core.getConversionTable(kwargs["proxy_id"], kwargs["meta_id"], kwargs["shape_id"])
print "Received conversion table: %s" % mapconv
if mapconv is None:
print "No conversion table for this map"
mapconv = {}
except Exception as ex:
print "Error when loading shape conversion table: %s" % ex
mapconv = {}
# very simple check to be sure the conversion table has the correct structure, i.e. is in the most recent format
if mapconv != {} and not (mapconv.has_key('modelid') and mapconv.has_key('fields')):
print "Conversion table is in the wrong format"
mapconv = {}
# creating the full list of fields for this map
sourcefields = []
if learnProxyType(getProxyManifest(proxy_id)) != 'query':
mapdata = proxy_core.convertShapeFileToJson(proxy_id, meta_id, shape_id, False)
for feature in mapdata ['features']:
for property in feature['properties'].keys():
if property not in sourcefields:
sourcefields.append(property)
else:
dbstructure = proxy_query.getPGStructure(proxy_id, meta_id, shape_id)
for field in dbstructure:
sourcefields.append(field[0])
args = {
"mapfields" : sourcefields,
"conversion" : mapconv,
}
print "Retrieved conversion structure:",args
return HttpResponse(json.dumps(args), mimetype="application/json")
def getReverseConversion (proxy_id, meta_id, map_id):
"""
Gets the fields conversion table, but with the values (our model) in place of the keys (external model)
:param proxy_id:
:param meta_id:
:param map_id:
:return:
"""
basetable = (proxy_core.getConversionTable (proxy_id, meta_id, map_id))
print "Found table as follows"
print basetable
basetable = basetable['fields']
reversetable = {}
for key in basetable.keys():
reversetable[basetable[key]['to']] = key
print "Returning reverse table"
return reversetable
def component_shapefile_table (request, **kwargs):
"""
Returns the conversion table for specific shapefile according to its structure, available model etc.
:param request:
:param kwargs:
:return:
"""
shapedata = None
shapetable = None
proxy_id = kwargs["proxy_id"]
meta_id = kwargs["meta_id"]
shape_id = kwargs["shape_id"]
shapedata = proxy_core.convertShapeFileToJson(proxy_id, meta_id, shape_id, False)
try:
shapetable = proxy_core.getConversionTable(kwargs["proxy_id"], kwargs["meta_id"], kwargs["shape_id"])
except Exception as ex:
print "Error when loading shape conversion table: %s" % ex
if shapetable is None:
shapetable = {}
try:
jsonresponse = urllib2.urlopen(proxyconf.URL_CONVERSIONS)
convtable = json.load(jsonresponse)
print "Received conversion table from server: %s" % convtable
except Exception as ex:
if isinstance(ex, urllib2.HTTPError):
errormess = ex.code
elif isinstance(ex, urllib2.URLError):
errormess = ex.reason
else:
errormess = ex.message
print "Error when requesting conversion table from %s: %s" % (proxyconf.URL_CONVERSIONS, errormess)
#todo: replace with handling at JS level
raise
conversionto = {}
for objecttype in convtable.keys():
conversionto[objecttype] = []
for property in convtable[objecttype].keys():
conversionto[objecttype].append(property)
conversionfrom = []
for feature in shapedata['features']:
for ckey in feature['properties'].keys():
if ckey not in conversionfrom:
conversionfrom.append(ckey)
args = {
"shapedata" : conversionfrom,
"conversion" : conversionto,
"shapetable": shapetable
}
print args
return HttpResponse(json.dumps(args), mimetype="application/json")
def makeSelectFromJson (proxy_id, meta_id, map_id, jsonmessage):
"""
Takes a JSON message and builds a PGSQL query string from it; this function is expected to be called via makeQueryOnMeta
:param jsonquery:
:return: array of features, NOT a featurecollection object
"""
DEC2FLOAT = psycopg2.extensions.new_type(
psycopg2._psycopg.DECIMAL.values, # oids for the decimal type
'DEC2FLOAT', # the new typecaster name
psycopg2.extensions.FLOAT) # the typecaster creating floats
# register the typecaster globally
psycopg2.extensions.register_type(DEC2FLOAT)
proj_WGS84 = 4326
# if the message is valid, we assemble the query
# we do a quick check to ensure the query can be satisfied by the data provided with this table
# i.e. if the columns chosen are supported in our translation
columns = []
for querybit in jsonmessage['query']['inventory']:
columns.append(querybit['column'])
revtable = getReverseConversion(proxy_id, meta_id, map_id)
print "Retrieved reverse table for makeSelectFromJson"
print revtable
# if a column is missing, we return an empty list without checking the DB
if not all (map(revtable.has_key, columns)):
return []
convtable = {}
for key in revtable.keys():
convtable[revtable[key]] = key
# now we can proceed with the actual query process
fp_conndata = open(os.path.join(proxyconf.baseproxypath, proxy_id, proxyconf.path_mirror, meta_id, map_id))
conn_data = json.load(fp_conndata)
fp_conndata.close()
connstring = makeConnectString(conn_data['connection'])
try:
conn = psycopg2.connect(connstring)
cur = conn.cursor()
print "Connection established"
except Exception as ex:
raise QueryFailedException ("Connessione fallita: %s" % ex)
#NOTE: bt as BETWEEN removed for now since it would require a specific syntax
operators = {
"gt": "> %s",
"ge": ">= %s",
"lt": "< %s",
"le": "<= %s",
"eq": "= %s",
"in": "<@ ARRAY [%s]"
}
wherestring = ""
sep = ""
for querybit in jsonmessage['query']['inventory']:
newbit = sep + " %s " + operators[querybit['operator']]
wherestring += newbit % (revtable[querybit['column']], querybit['params'])
sep = " AND "
if len(jsonmessage['query']['BB']) > 0:
#TODO: verify if we prefer to also have items that CROSS the bounding box or if we only what what is COMPLETELY inside (current)
bb = jsonmessage['query']['BB']
newbit = sep + " transform("+revtable['geometry'] +", "+str(proj_WGS84)+") @ ST_SetSRID(ST_MakeBox2D(ST_Point(%s, %s), ST_Point(%s , %s)), "+str(proj_WGS84)+")"
wherestring += cur.mogrify(newbit, (bb[0], bb[1], bb[2], bb[3]))
print "QUERY CONDITIONS: *************\n%s\n******************************" % wherestring
view_id = conn_data['query']['view']
schema_id = conn_data['query']['schema']
if schema_id != "" and schema_id is not None:
schema_id += "."
fields = []
selectcols = ""
sep = ""
for colname in convtable:
alias = convtable[colname]
fields.append(alias)
if convtable[colname] == "geometry":
colname = "ST_AsGeoJSON(transform("+colname+", 4326))"
selectcols += sep + colname + " AS " + alias
sep = ", "
if selectcols == "":
selectcols = " * "
if wherestring != "":
wherestring = " WHERE "+wherestring
limitstring = ""
offsetstring = ""
if jsonmessage['maxitems'] != 0:
limitstring = " LIMIT %s" % jsonmessage['maxitems']
if jsonmessage['offset'] != 0:
offsetstring = " OFFSET %s" % jsonmessage['offset']
querystring = 'SELECT '+selectcols+' FROM '+schema_id+view_id + wherestring + limitstring + offsetstring + ";"
print "REQUESTED SELECT: " + querystring
cur.execute(querystring)
results = cur.fetchall()
cur.close()
conn.close()
collection = []
#print "FIELDS: %s" % fields
#print "RESULTS: %s" % results
valuestable = proxy_core.getConversionTable(proxy_id, meta_id, map_id)
print "Received (linear) conv table %s" % valuestable
forced = valuestable['forcedfields']
print "Set forced values, moving to value conversion"
print "(revtable is now %s)" % revtable
fieldvalues = {}
for fedfield in revtable.keys():
clientfield = revtable[fedfield]
if len(valuestable['fields'][clientfield]['values']) > 0:
print "Adding %s to quick conv" % clientfield
fieldvalues[fedfield] = valuestable['fields'][clientfield]['values']
print "Quick conv table is %s " % fieldvalues
errors = 0
for row in results:
try:
data = {}
data['type'] = "Feature"
data['geometry'] = json.loads(row[fields.index('geometry')])
properties = {}
for key in forced.keys():
properties[key] = forced[key]['']
for field in fields:
if field != 'geometry':
clientvalue = row[fields.index(field)]
if field in fieldvalues.keys() and clientvalue in fieldvalues[field]:
properties[field] = fieldvalues[field][clientvalue]
else:
properties[field] = clientvalue
piperhash = hashlib.md5(json.dumps(data['geometry'])).hexdigest()
data['properties'] = properties
data['properties']['IDPiper'] = 'q'+piperhash[:11]
try:
for unmapped in valuestable['unmapped']:
data['properties'][unmapped] = None
except Exception as ex:
# supporting older conversion tables
pass
collection.append(data)
except Exception as ex:
errors += 1
print "Found %s elements with %s errors" % (len(collection), errors)
return collection
