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)