def setup_spatial_table(package_extent_class, db_srid=None): if legacy_geoalchemy: package_extent_table = Table( 'package_extent', meta.metadata, Column('package_id', types.UnicodeText, primary_key=True), GeometryExtensionColumn('the_geom', Geometry(2, srid=db_srid)) ) meta.mapper( package_extent_class, package_extent_table, properties={'the_geom': GeometryColumn(package_extent_table.c.the_geom, comparator=PGComparator)} ) GeometryDDL(package_extent_table) else: # PostGIS 1.5 requires management=True when defining the Geometry # field management = (postgis_version()[:1] == '1') package_extent_table = Table( 'package_extent', meta.metadata, Column('package_id', types.UnicodeText, primary_key=True), Column('the_geom', Geometry('GEOMETRY', srid=db_srid, management=management)), ) meta.mapper(package_extent_class, package_extent_table) return package_extent_table
def _get_mapped_class_non_declarative(self): from sqlalchemy import Table, MetaData, Column, types, orm, schema from sqlalchemy.ext.associationproxy import association_proxy from papyrus.geo_interface import GeoInterface from geoalchemy import (GeometryExtensionColumn, GeometryColumn, Geometry) from geoalchemy.postgis import PGComparator md = MetaData() child1_table = Table( 'child1', md, Column('id', types.Integer, primary_key=True), Column('name', types.Unicode), Column('parent_id', types.Integer, schema.ForeignKey('parent.id'))) child2_table = Table('child2', md, Column('id', types.Integer, primary_key=True), Column('name', types.Unicode)) parent_table = Table( 'parent', md, Column('id', types.Integer, primary_key=True), Column('text', types.Unicode), GeometryExtensionColumn('geom', Geometry(dimension=2, srid=3000)), Column('child_id', types.Integer, schema.ForeignKey('child2.id'))) class Child1(object): def __init__(self, name): self.name = name orm.mapper(Child1, child1_table) class Child2(object): def __init__(self, name): self.name = name orm.mapper(Child2, child2_table) class Parent(GeoInterface): children = association_proxy('children_', 'name') child = association_proxy('child_', 'name') __add_properties__ = ('child', 'children') orm.mapper(Parent, parent_table, properties={ 'geom': GeometryColumn(parent_table.c.geom, comparator=PGComparator), 'children_': orm.relationship(Child1), 'child_': orm.relationship(Child2) }) return Parent
def define_spatial_tables(db_srid=None): global package_extent_table if not db_srid: db_srid = int(config.get('ckan.spatial.srid', DEFAULT_SRID)) else: db_srid = int(db_srid) package_extent_table = Table('package_extent', meta.metadata, Column('package_id', types.UnicodeText, primary_key=True), GeometryExtensionColumn('the_geom', Geometry(2,srid=db_srid))) meta.mapper(PackageExtent, package_extent_table, properties={ 'the_geom': GeometryColumn(package_extent_table.c.the_geom, comparator=PGComparator)}) # enable the DDL extension GeometryDDL(package_extent_table)
def _create_table(self, tablename): """ Test functions use this function to create a table object. Each test function should call this function only once. And there should not be two test functions that call this function with the same ptable_name value. """ import sqlahelper from sqlalchemy import Table, Column, ForeignKey, types from sqlalchemy.ext.declarative import declarative_base from geoalchemy import GeometryExtensionColumn, GeometryDDL from geoalchemy import (Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon) engine = sqlahelper.get_engine() Base = declarative_base(bind=engine) ctable = Table('%s_child' % tablename, Base.metadata, Column('id', types.Integer, primary_key=True), Column('name', types.Unicode), schema='public') ctable.create() ptable = Table(tablename, Base.metadata, Column('id', types.Integer, primary_key=True), Column('child1_id', types.Integer, ForeignKey('public.%s_child.id' % tablename)), Column('child2_id', types.Integer, ForeignKey('public.%s_child.id' % tablename)), GeometryExtensionColumn('point', Point), GeometryExtensionColumn('linestring', LineString), GeometryExtensionColumn('polygon', Polygon), GeometryExtensionColumn('multipoint', MultiPoint), GeometryExtensionColumn('multilinestring', MultiLineString), GeometryExtensionColumn('multipolygon', MultiPolygon), schema='public') GeometryDDL(ptable) ptable.create() self.metadata = Base.metadata
road_id = Column(Integer, primary_key=True) road_name = Column(Unicode(40)) road_geom = GeometryColumn(LineString(2, srid=4326, spatial_index=False), comparator=MySQLComparator, nullable=False) class Lake(Base): __tablename__ = 'lakes' lake_id = Column(Integer, primary_key=True) lake_name = Column(Unicode(40)) lake_geom = GeometryColumn(Polygon(2, srid=4326), comparator=MySQLComparator) spots_table = Table('spots', metadata, Column('spot_id', Integer, primary_key=True), Column('spot_height', Numeric(precision=10, scale=2)), GeometryExtensionColumn('spot_location', Point(2, srid=4326))) class Spot(object): def __init__(self, spot_id=None, spot_height=None, spot_location=None): self.spot_id = spot_id self.spot_height = spot_height self.spot_location = spot_location mapper(Spot, spots_table, properties={ 'spot_location': GeometryColumn(spots_table.c.spot_location, comparator=MySQLComparator)}) # enable the DDL extension, which allows CREATE/DROP operations # to work correctly. This is not needed if working with externally # defined tables.
comparator=MSComparator, nullable=False) class Lake(Base): __tablename__ = 'lakes' lake_id = Column(Integer, primary_key=True) lake_name = Column(String(255)) lake_geom = GeometryColumn(Polygon(2), comparator=MSComparator) spots_table = Table('spots', metadata, Column('spot_id', Integer, primary_key=True), Column('spot_height', Numeric(6, 2)), GeometryExtensionColumn('spot_location', Geometry(2))) class Spot(object): def __init__(self, spot_id=None, spot_height=None, spot_location=None): self.spot_id = spot_id self.spot_height = spot_height self.spot_location = spot_location mapper(Spot, spots_table, properties={ 'spot_location': GeometryColumn(spots_table.c.spot_location, comparator=MSComparator) })
def _create_layer(self, public=False, none_area=False, attr_list=False, exclude_properties=False): """ This function is central for this test class. It creates a layer with two features, and associates a restriction area to it. """ import transaction import sqlahelper from sqlalchemy import func from sqlalchemy import Column, Table, types, ForeignKey from sqlalchemy.ext.declarative import declarative_base from geoalchemy import (GeometryDDL, GeometryExtensionColumn, Point, WKTSpatialElement) from c2cgeoportal.models import DBSession, Layer, RestrictionArea self.__class__._table_index = self.__class__._table_index + 1 id = self.__class__._table_index engine = sqlahelper.get_engine() if not self.metadata: self.metadata = declarative_base(bind=engine).metadata tablename = "table_%d" % id table = Table('%s_child' % tablename, self.metadata, Column('id', types.Integer, primary_key=True), Column('name', types.Unicode), schema='public') table.create() ins = table.insert().values(name=u'c1é') c1_id = engine.connect().execute(ins).inserted_primary_key[0] ins = table.insert().values(name=u'c2é') c2_id = engine.connect().execute(ins).inserted_primary_key[0] table = Table(tablename, self.metadata, Column('id', types.Integer, primary_key=True), Column('child_id', types.Integer, ForeignKey('public.%s_child.id' % tablename)), Column('name', types.Unicode), GeometryExtensionColumn('geom', Point(srid=21781)), schema='public') GeometryDDL(table) table.create() ins = table.insert().values(child_id=c1_id, name='foo', geom=func.ST_GeomFromText( 'POINT(5 45)', 21781)) engine.connect().execute(ins).inserted_primary_key[0] ins = table.insert().values(child_id=c2_id, name='bar', geom=func.ST_GeomFromText( 'POINT(6 46)', 21781)) engine.connect().execute(ins).inserted_primary_key[0] if attr_list: ins = table.insert().values(child_id=c2_id, name='aaa,bbb,foo', geom=func.ST_GeomFromText( 'POINT(6 46)', 21781)) engine.connect().execute(ins).inserted_primary_key[0] layer = Layer() layer.id = id layer.geoTable = tablename layer.public = public if exclude_properties: layer.excludeProperties = 'name' DBSession.add(layer) if not public: ra = RestrictionArea() ra.name = u'__test_ra' ra.layers = [layer] ra.roles = [self.role] ra.readwrite = True if not none_area: poly = 'POLYGON((4 44, 4 46, 6 46, 6 44, 4 44))' ra.area = WKTSpatialElement(poly, srid=21781) DBSession.add(ra) self.layer_ids.append(self.__class__._table_index) transaction.commit() return id
if not orgid: log.error('No organization provided') return shape = asShape(geojson) extent = Session.query(OrganizationExtent)\ .filter(OrganizationExtent.organization_id == orgid).first() if not extent: extent = OrganizationExtent(organization_id=orgid) extent.the_geom = WKTSpatialElement(shape.wkt, db_srid) extent.save() db_srid = int(config.get('ckan.spatial.srid', DEFAULT_SRID)) organization_extent_table = Table( 'organization_extent', meta.metadata, Column('organization_id', types.UnicodeText, primary_key=True), GeometryExtensionColumn('the_geom', Geometry(2, srid=db_srid))) meta.mapper(OrganizationExtent, organization_extent_table, properties={ 'the_geom': GeometryColumn(organization_extent_table.c.the_geom, comparator=PGComparator) }) # enable the DDL extension GeometryDDL(organization_extent_table)