Example #1
0
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
Example #2
0
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
Example #3
0
    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()
Example #4
0
    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()
Example #5
0
    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()
Example #6
0
    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()