def getData(self, tableName, q, bbox=None):
geomColName = "the_geom"
#p = cherrypy.request.app.config['postgresql']
cur = self._getCursor()
whereString = ""
vals = []
for k in q.keys():
whereString += "%s ILIKE %s AND " %(k, "%s")
val = q[k]
vals.append(val)
whereString = whereString[:-5]
# bbox
if bbox != None:
# w,s,e,n
whereString += " AND %s.%s && ST_MakeEnvelope(%s, %s, %s, %s, 4326)" %(tableName, geomColName, "%s", "%s", "%s", "%s")
vals.extend(bbox)
sql = """
select *, ST_AsGeoJSON(%s) as the_geom
from cultplaces
where %s;
""" %(geomColName, whereString)
out = {
"type": "FeatureCollection",
"features": []
}
cur.execute(sql, vals)
features = []
for r in cur:
geom = r["the_geom"]
props = r.copy()
del props["the_geom"]
features.append({
"type": "Feature",
"id": "%s.%s" %(tableName, props["id"]),
"geometry": json.loads(geom),
"properties": props
})
out["features"] = features
# Close
closedbconnection(cur.connection)
# cur.connection.close()
# cur.close()
return out
def getData(self, tableName, q, bbox=None):
geomColName = "the_geom"
#p = cherrypy.request.app.config['postgresql']
cur = self._getCursor()
whereString = ""
vals = []
for k in q.keys():
whereString += "%s ILIKE %s AND " % (k, "%s")
val = q[k]
vals.append(val)
whereString = whereString[:-5]
# bbox
if bbox != None:
# w,s,e,n
whereString += " AND %s.%s && ST_MakeEnvelope(%s, %s, %s, %s, 4326)" % (
tableName, geomColName, "%s", "%s", "%s", "%s")
vals.extend(bbox)
sql = """
select *, ST_AsGeoJSON(%s) as the_geom
from cultplaces
where %s;
""" % (geomColName, whereString)
out = {"type": "FeatureCollection", "features": []}
cur.execute(sql, vals)
features = []
for r in cur:
geom = r["the_geom"]
props = r.copy()
del props["the_geom"]
features.append({
"type": "Feature",
"id": "%s.%s" % (tableName, props["id"]),
"geometry": json.loads(geom),
"properties": props
})
out["features"] = features
# Close
closedbconnection(cur.connection)
# cur.connection.close()
# cur.close()
return out
def match(q):
q = unicode(q.decode("utf8"))
q = q.upper()
conn = db.opendbconnection("search")
cur = conn.cursor()
sql = "SELECT objektnamn FROM poi_skane_adressregister_apl WHERE objektnamn LIKE (%s);"
safeParam = (q+"%", )
cur.execute(sql, safeParam)
#cur.execute("SELECT objektnamn FROM poi_hbg_sok WHERE objektnamn like '" + q + "%';")
out = ""
for record in cur:
out += record[0]
#print conn.encoding
cur.close()
db.closedbconnection(conn)
return out
def match(q):
q = unicode(q.decode("utf8"))
q = q.upper()
conn = db.opendbconnection("search")
cur = conn.cursor()
sql = "SELECT objektnamn FROM poi_skane_adressregister_apl WHERE objektnamn LIKE (%s);"
safeParam = (q + "%", )
cur.execute(sql, safeParam)
#cur.execute("SELECT objektnamn FROM poi_hbg_sok WHERE objektnamn like '" + q + "%';")
out = ""
for record in cur:
out += record[0]
#print conn.encoding
cur.close()
db.closedbconnection(conn)
return out
def locate(q):
q = unicode(q.decode("utf8"))
q = q.upper()
conn = db.opendbconnection()
cur = conn.cursor()
sql = "SELECT easting, northing, objektnamn, objekttyp FROM poi_skane_adressregister_apl WHERE objektnamn = (%s);"
safeParam = (q, )
cur.execute(sql, safeParam)
#cur.execute("SELECT objektnamn FROM poi_hbg_sok WHERE objektnamn like '" + q + "%';")
features = []
for record in cur:
coordinates=[record[0],record[1]]
geometry={}
geometry['type'] = 'Point'
geometry['coordinates'] = coordinates
properties={}
properties['name']=record[2]
properties['category']=record[3]
feature={}
feature['type']='Feature'
feature['geometry']=geometry
feature['properties']=properties
features=[feature]
out = {}
out['type'] = 'FeatureCollection'
out['features'] = features
cur.close()
db.closedbconnection(conn)
return out
def locate(q):
q = unicode(q.decode("utf8"))
q = q.upper()
conn = db.opendbconnection()
cur = conn.cursor()
sql = "SELECT easting, northing, objektnamn, objekttyp FROM poi_skane_adressregister_apl WHERE objektnamn = (%s);"
safeParam = (q, )
cur.execute(sql, safeParam)
#cur.execute("SELECT objektnamn FROM poi_hbg_sok WHERE objektnamn like '" + q + "%';")
features = []
for record in cur:
coordinates = [record[0], record[1]]
geometry = {}
geometry['type'] = 'Point'
geometry['coordinates'] = coordinates
properties = {}
properties['name'] = record[2]
properties['category'] = record[3]
feature = {}
feature['type'] = 'Feature'
feature['geometry'] = geometry
feature['properties'] = properties
features = [feature]
out = {}
out['type'] = 'FeatureCollection'
out['features'] = features
cur.close()
db.closedbconnection(conn)
return out
