def unit_search(request):
q = request.params['term']
srs = int(request.params['srs']) if 'srs' in request.params else 4326
dbsession = DBSession()
fields = (
Unit,
func.st_xmin(
func.st_transform(func.st_setsrid(UnitPolygon.geom, 4326),
srs)).label('left'),
func.st_xmax(
func.st_transform(func.st_setsrid(UnitPolygon.geom, 4326),
srs)).label('right'),
func.st_ymin(
func.st_transform(func.st_setsrid(UnitPolygon.geom, 4326),
srs)).label('bottom'),
func.st_ymax(
func.st_transform(func.st_setsrid(UnitPolygon.geom, 4326),
srs)).label('top'),
func.st_x(func.st_transform(func.st_setsrid(UnitPoint.geom, 4326),
srs)).label('x'),
func.st_y(func.st_transform(func.st_setsrid(UnitPoint.geom, 4326),
srs)).label('y'),
)
result = dbsession.query(*fields) \
.outerjoin((UnitPolygon, UnitPolygon.unit_id == Unit.id)) \
.outerjoin((UnitPoint, UnitPoint.unit_id == Unit.id)) \
.filter(Unit.name.ilike('%' + q + '%')) \
.order_by(Unit.id_level4, Unit.name).limit(10)
rows = []
for r in result:
itm = r.Unit.as_dict()
itm['value'] = r.Unit.name
if r.left and r.top:
itm['extent'] = (r.left, r.bottom, r.right, r.top)
if r.x and r.y:
itm['x'] = r.x
itm['y'] = r.y
rows.append(itm)
return rows
def unit_search(request):
q = request.params['term']
srs = int(request.params['srs']) if 'srs' in request.params else 4326
dbsession = DBSession()
fields = (
Unit,
func.st_xmin(func.st_transform(func.st_setsrid(UnitPolygon.geom, 4326), srs)).label('left'),
func.st_xmax(func.st_transform(func.st_setsrid(UnitPolygon.geom, 4326), srs)).label('right'),
func.st_ymin(func.st_transform(func.st_setsrid(UnitPolygon.geom, 4326), srs)).label('bottom'),
func.st_ymax(func.st_transform(func.st_setsrid(UnitPolygon.geom, 4326), srs)).label('top'),
func.st_x(func.st_transform(func.st_setsrid(UnitPoint.geom, 4326), srs)).label('x'),
func.st_y(func.st_transform(func.st_setsrid(UnitPoint.geom, 4326), srs)).label('y'),
)
result = dbsession.query(*fields) \
.outerjoin((UnitPolygon, UnitPolygon.unit_id == Unit.id)) \
.outerjoin((UnitPoint, UnitPoint.unit_id == Unit.id)) \
.filter(Unit.name.ilike('%' + q + '%')) \
.order_by(Unit.id_level4, Unit.name).limit(10)
rows = []
for r in result:
itm = r.Unit.as_dict()
itm['value'] = r.Unit.name
if r.left and r.top:
itm['extent'] = (r.left, r.bottom, r.right, r.top)
if r.x and r.y:
itm['x'] = r.x
itm['y'] = r.y
rows.append(itm)
return rows
def __call__(self):
tableinfo = TableInfo.from_layer(self.layer)
tableinfo.setup_metadata(self.layer._tablename)
table = tableinfo.table
columns = [
table.columns.id,
]
where = []
srsid = self.layer.srs_id if self._srs is None else self._srs.id
geomcol = table.columns.geom
geomexpr = func.st_transform(geomcol, srsid)
if self._clip_by_box is not None:
if _clipbybox2d_exists():
clip = func.st_setsrid(
func.st_makeenvelope(*self._clip_by_box.bounds),
self._clip_by_box.srid)
geomexpr = func.st_clipbybox2d(geomexpr, clip)
else:
clip = func.st_setsrid(
func.st_geomfromtext(self._clip_by_box.wkt),
self._clip_by_box.srid)
geomexpr = func.st_intersection(geomexpr, clip)
if self._simplify is not None:
geomexpr = func.st_simplifypreservetopology(
geomexpr, self._simplify)
if self._geom:
if self._single_part:
class geom(ColumnElement):
def __init__(self, base):
self.base = base
@compiles(geom)
def compile(expr, compiler, **kw):
return "(%s).geom" % str(compiler.process(expr.base))
columns.append(
func.st_asewkb(geom(func.st_dump(geomexpr))).label('geom'))
else:
columns.append(func.st_asewkb(geomexpr).label('geom'))
if self._geom_len:
columns.append(
func.st_length(
func.geography(func.st_transform(geomexpr,
4326))).label('geom_len'))
if self._box:
columns.extend((
func.st_xmin(geomexpr).label('box_left'),
func.st_ymin(geomexpr).label('box_bottom'),
func.st_xmax(geomexpr).label('box_right'),
func.st_ymax(geomexpr).label('box_top'),
))
selected_fields = []
for f in tableinfo.fields:
if not self._fields or f.keyname in self._fields:
columns.append(table.columns[f.key].label(f.keyname))
selected_fields.append(f)
if self._filter_by:
for k, v in six.iteritems(self._filter_by):
if k == 'id':
where.append(table.columns.id == v)
else:
where.append(table.columns[tableinfo[k].key] == v)
if self._filter:
token = []
for k, o, v in self._filter:
supported_operators = (
"eq",
"ge",
"gt",
"ilike",
"in",
"le",
"like",
"lt",
"ne",
"notin",
"startswith",
)
if o not in supported_operators:
raise ValueError(
"Invalid operator '%s'. Only %r are supported." %
(o, supported_operators))
if v and o in ['in', 'notin']:
v = v.split(',')
if o in [
"ilike",
"in",
"like",
"notin",
"startswith",
]:
o += "_op"
op = getattr(db.sql.operators, o)
if k == "id":
token.append(op(table.columns.id, v))
else:
token.append(op(table.columns[tableinfo[k].key], v))
where.append(db.and_(*token))
if self._filter_sql:
token = []
for _filter_sql_item in self._filter_sql:
if len(_filter_sql_item) == 3:
table_column, op, val = _filter_sql_item
if table_column == 'id':
token.append(op(table.columns.id, val))
else:
token.append(
op(table.columns[tableinfo[table_column].key],
val))
elif len(_filter_sql_item) == 4:
table_column, op, val1, val2 = _filter_sql_item
token.append(
op(table.columns[tableinfo[table_column].key], val1,
val2))
where.append(db.and_(*token))
if self._like:
token = []
for f in tableinfo.fields:
token.append(
cast(table.columns[f.key],
db.Unicode).ilike("%" + self._like + "%"))
where.append(db.or_(*token))
if self._intersects:
intgeom = func.st_setsrid(
func.st_geomfromtext(self._intersects.wkt),
self._intersects.srid)
where.append(
func.st_intersects(
geomcol, func.st_transform(intgeom, self.layer.srs_id)))
order_criterion = []
if self._order_by:
for order, colname in self._order_by:
order_criterion.append(
dict(asc=db.asc, desc=db.desc)[order](
table.columns[tableinfo[colname].key]))
order_criterion.append(table.columns.id)
class QueryFeatureSet(FeatureSet):
fields = selected_fields
layer = self.layer
_geom = self._geom
_geom_len = self._geom_len
_box = self._box
_limit = self._limit
_offset = self._offset
def __iter__(self):
query = sql.select(
columns,
whereclause=db.and_(*where),
limit=self._limit,
offset=self._offset,
order_by=order_criterion,
)
rows = DBSession.connection().execute(query)
for row in rows:
fdict = dict(
(f.keyname, row[f.keyname]) for f in selected_fields)
if self._geom:
geom = geom_from_wkb(row['geom'].tobytes(
) if six.PY3 else six.binary_type(row['geom']))
else:
geom = None
calculated = dict()
if self._geom_len:
calculated['geom_len'] = row['geom_len']
yield Feature(
layer=self.layer,
id=row.id,
fields=fdict,
geom=geom,
calculations=calculated,
box=box(row.box_left, row.box_bottom, row.box_right,
row.box_top) if self._box else None)
@property
def total_count(self):
query = sql.select([
func.count(table.columns.id),
],
whereclause=db.and_(*where))
res = DBSession.connection().execute(query)
for row in res:
return row[0]
return QueryFeatureSet()
def __call__(self):
tab = sql.table(self.layer.table)
tab.schema = self.layer.schema
tab.quote = True
tab.quote_schema = True
select = sa.select([], tab)
def addcol(col):
select.append_column(col)
idcol = sql.column(self.layer.column_id)
addcol(idcol.label('id'))
geomcol = sql.column(self.layer.column_geom)
geomexpr = sa.func.st_transform(geomcol, self.layer.srs_id)
if self._geom:
addcol(sa.func.st_astext(geomexpr).label('geom'))
fieldmap = []
for idx, fld in enumerate(self.layer.fields, start=1):
if not self._fields or fld.keyname in self._fields:
clabel = 'f%d' % idx
addcol(sql.column(fld.keyname).label(clabel))
fieldmap.append((fld.keyname, clabel))
if self._filter_by:
for k, v in self._filter_by.iteritems():
if k == 'id':
select.append_whereclause(idcol == v)
else:
select.append_whereclause(sql.column(k) == v)
if self._like:
l = []
for fld in self.layer.fields:
if fld.datatype == FIELD_TYPE.STRING:
if fld.keyname == self._like_column or self._like_column is None:
l.append(sql.cast(
sql.column(fld.keyname),
sa.Unicode).ilike(
'%' + self._like + '%'))
select.append_whereclause(sa.or_(*l))
if self._intersects:
intgeom = sa.func.st_setsrid(sa.func.st_geomfromtext(
self._intersects.wkt), self._intersects.srid)
select.append_whereclause(sa.func.st_intersects(
geomcol, sa.func.st_transform(
intgeom, self.layer.geometry_srid)))
if self._box:
addcol(func.st_xmin(geomexpr).label('box_left'))
addcol(func.st_ymin(geomexpr).label('box_bottom'))
addcol(func.st_xmax(geomexpr).label('box_right'))
addcol(func.st_ymax(geomexpr).label('box_top'))
gt = self.layer.geometry_type
select.append_whereclause(sa.func.geometrytype(sql.column(
self.layer.column_geom)).in_((gt, 'MULTI' + gt)))
select.append_order_by(idcol)
class QueryFeatureSet(FeatureSet):
layer = self.layer
_geom = self._geom
_box = self._box
_fields = self._fields
_limit = self._limit
_offset = self._offset
def __iter__(self):
if self._limit:
query = select.limit(self._limit).offset(self._offset)
else:
query = select
conn = env.postgis_layer.connection[self.layer.connection].connect()
try:
for row in conn.execute(query):
fdict = dict([(k, row[l]) for k, l in fieldmap])
if self._geom:
geom = geom_from_wkt(row['geom'])
else:
geom = None
yield Feature(
layer=self.layer, id=row['id'],
fields=fdict, geom=geom,
box=box(
row['box_left'], row['box_bottom'],
row['box_right'], row['box_top']
) if self._box else None
)
finally:
conn.close()
@property
def total_count(self):
conn = env.postgis_layer.connection[self.layer.connection].connect()
try:
result = conn.execute(sa.select(
[sql.text('COUNT(id)'), ],
from_obj=select.alias('all')))
for row in result:
return row[0]
finally:
conn.close()
return QueryFeatureSet()
def __call__(self):
tab = self.layer._sa_table(True)
idcol = tab.columns[self.layer.column_id]
columns = [idcol.label('id')]
where = []
geomcol = tab.columns[self.layer.column_geom]
srs = self.layer.srs if self._srs is None else self._srs
if srs.id != self.layer.geometry_srid:
geomexpr = func.st_transform(geomcol, srs.id)
else:
geomexpr = geomcol
if self._geom:
if self._geom_format == 'WKB':
geomexpr = func.st_asbinary(geomexpr, 'NDR')
else:
geomexpr = func.st_astext(geomexpr)
columns.append(geomexpr.label('geom'))
fieldmap = []
for idx, fld in enumerate(self.layer.fields, start=1):
if self._fields is None or fld.keyname in self._fields:
clabel = 'f%d' % idx
columns.append(
getattr(tab.columns, fld.column_name).label(clabel))
fieldmap.append((fld.keyname, clabel))
if self._filter_by:
for k, v in self._filter_by.items():
if k == 'id':
where.append(idcol == v)
else:
field = self.layer.field_by_keyname(k)
where.append(tab.columns[field.column_name] == v)
if self._filter:
token = []
for k, o, v in self._filter:
supported_operators = (
'eq',
'ne',
'isnull',
'ge',
'gt',
'le',
'lt',
'like',
'ilike',
)
if o not in supported_operators:
raise ValueError(
"Invalid operator '%s'. Only %r are supported." %
(o, supported_operators))
if o == 'like':
o = 'like_op'
elif o == 'ilike':
o = 'ilike_op'
elif o == "isnull":
if v == 'yes':
o = 'is_'
elif v == 'no':
o = 'isnot'
else:
raise ValueError(
"Invalid value '%s' for operator '%s'." % (v, o))
v = db.sql.null()
op = getattr(db.sql.operators, o)
if k == 'id':
column = idcol
else:
field = self.layer.field_by_keyname(k)
column = tab.columns[field.column_name]
token.append(op(column, v))
where.append(db.and_(True, *token))
if self._like:
token = []
for fld in self.layer.fields:
token.append(
db.sql.cast(tab.columns[fld.column_name],
db.Unicode).ilike('%' + self._like + '%'))
where.append(db.or_(*token))
if self._intersects:
reproject = self._intersects.srid is not None \
and self._intersects.srid != self.layer.geometry_srid
int_srs = SRS.filter_by(id=self._intersects.srid).one() \
if reproject else self.layer.srs
int_geom = func.st_geomfromtext(self._intersects.wkt)
if int_srs.is_geographic:
# Prevent tolerance condition error
bound_geom = func.st_makeenvelope(-180, -89.9, 180, 89.9)
int_geom = func.st_intersection(bound_geom, int_geom)
int_geom = func.st_setsrid(int_geom, int_srs.id)
if reproject:
int_geom = func.st_transform(int_geom,
self.layer.geometry_srid)
where.append(func.st_intersects(geomcol, int_geom))
if self._box:
columns.extend((
func.st_xmin(geomexpr).label('box_left'),
func.st_ymin(geomexpr).label('box_bottom'),
func.st_xmax(geomexpr).label('box_right'),
func.st_ymax(geomexpr).label('box_top'),
))
gt = self.layer.geometry_type
where.append(func.geometrytype(geomcol) == gt)
order_criterion = []
if self._order_by:
for order, k in self._order_by:
field = self.layer.field_by_keyname(k)
order_criterion.append(
dict(asc=db.asc,
desc=db.desc)[order](tab.columns[field.column_name]))
order_criterion.append(idcol)
class QueryFeatureSet(FeatureSet):
layer = self.layer
_geom = self._geom
_geom_format = self._geom_format
_box = self._box
_fields = self._fields
_limit = self._limit
_offset = self._offset
def __iter__(self):
query = sql.select(*columns) \
.where(db.and_(True, *where)) \
.limit(self._limit) \
.offset(self._offset) \
.order_by(*order_criterion)
conn = self.layer.connection.get_connection()
try:
result = conn.execute(query)
for row in result.mappings():
fdict = dict((k, row[l]) for k, l in fieldmap)
if self._geom:
if self._geom_format == 'WKB':
geom_data = row['geom'].tobytes()
geom = Geometry.from_wkb(geom_data,
validate=False)
else:
geom = Geometry.from_wkt(row['geom'],
validate=False)
else:
geom = None
yield Feature(
layer=self.layer,
id=row['id'],
fields=fdict,
geom=geom,
box=box(row['box_left'], row['box_bottom'],
row['box_right'], row['box_top'])
if self._box else None)
except SQLAlchemyError as exc:
raise ExternalDatabaseError(sa_error=exc)
finally:
conn.close()
@property
def total_count(self):
conn = self.layer.connection.get_connection()
try:
query = sql.select(func.count(idcol)) \
.where(db.and_(True, *where))
result = conn.execute(query)
return result.scalar()
except SQLAlchemyError as exc:
raise ExternalDatabaseError(sa_error=exc)
finally:
conn.close()
return QueryFeatureSet()
def __call__(self):
tab = sql.table(self.layer.table)
tab.schema = self.layer.schema
tab.quote = True
tab.quote_schema = True
select = sa.select([], tab)
def addcol(col):
select.append_column(col)
idcol = sql.column(self.layer.column_id)
addcol(idcol.label('id'))
geomcol = sql.column(self.layer.column_geom)
geomexpr = sa.func.st_transform(geomcol, self.layer.srs_id)
if self._geom:
addcol(sa.func.st_astext(geomexpr).label('geom'))
fieldmap = []
for idx, fld in enumerate(self.layer.fields, start=1):
if not self._fields or fld.keyname in self._fields:
clabel = 'f%d' % idx
addcol(sql.column(fld.keyname).label(clabel))
fieldmap.append((fld.keyname, clabel))
if self._filter_by:
for k, v in self._filter_by.iteritems():
if k == 'id':
select.append_whereclause(idcol == v)
else:
select.append_whereclause(sql.column(k) == v)
if self._like:
l = []
for fld in self.layer.fields:
if fld.datatype == FIELD_TYPE.STRING:
if fld.keyname == self._like_column or self._like_column is None:
l.append(
sql.cast(sql.column(fld.keyname),
sa.Unicode).ilike('%' + self._like + '%'))
select.append_whereclause(sa.or_(*l))
if self._intersects:
intgeom = sa.func.st_setsrid(
sa.func.st_geomfromtext(self._intersects.wkt),
self._intersects.srid)
select.append_whereclause(
sa.func.st_intersects(
geomcol,
sa.func.st_transform(intgeom, self.layer.geometry_srid)))
if self._box:
addcol(func.st_xmin(geomexpr).label('box_left'))
addcol(func.st_ymin(geomexpr).label('box_bottom'))
addcol(func.st_xmax(geomexpr).label('box_right'))
addcol(func.st_ymax(geomexpr).label('box_top'))
gt = self.layer.geometry_type
select.append_whereclause(
sa.func.geometrytype(sql.column(self.layer.column_geom)).in_(
(gt, 'MULTI' + gt)))
select.append_order_by(idcol)
class QueryFeatureSet(FeatureSet):
layer = self.layer
_geom = self._geom
_box = self._box
_fields = self._fields
_limit = self._limit
_offset = self._offset
def __iter__(self):
if self._limit:
query = select.limit(self._limit).offset(self._offset)
else:
query = select
conn = env.postgis_layer.connection[
self.layer.connection].connect()
try:
for row in conn.execute(query):
fdict = dict([(k, row[l]) for k, l in fieldmap])
if self._geom:
geom = geom_from_wkt(row['geom'])
else:
geom = None
yield Feature(
layer=self.layer,
id=row['id'],
fields=fdict,
geom=geom,
box=box(row['box_left'], row['box_bottom'],
row['box_right'], row['box_top'])
if self._box else None)
finally:
conn.close()
@property
def total_count(self):
conn = env.postgis_layer.connection[
self.layer.connection].connect()
try:
result = conn.execute(
sa.select([
sql.text('COUNT(id)'),
],
from_obj=select.alias('all')))
for row in result:
return row[0]
finally:
conn.close()
return QueryFeatureSet()