class luftbilder_swisstopo_sw(Base, Vector):
__tablename__ = 'luftbilder_swisstopo_bw'
__table_args__ = ({
'schema': 'public',
'autoload': False,
'extend_existing': True
})
__template__ = 'templates/htmlpopup/lubis.mako'
__bodId__ = 'ch.swisstopo.lubis-luftbilder_schwarzweiss'
__returnedGeometry__ = 'the_geom_footprint'
__timeInstant__ = 'bgdi_flugjahr'
__extended_info__ = True
id = Column('ebkey', Text, primary_key=True)
the_geom = GeometryColumn('the_geom', Geometry(dimensions=2, srid=21781))
the_geom_footprint = GeometryColumn('the_geom_footprint',
Geometry(dimensions=2, srid=21781))
filename = Column('filename', Text)
inventarnummer = Column('inventarnummer', Integer)
bildnummer = Column('bildnummer', Integer)
flugdatum = Column('flugdatum', Text)
firma = Column('firma', Text)
filmart = Column('filmart', Text)
bgdi_flugjahr = Column('bgdi_flugjahr', Integer)
orientierung = Column('orientierung', Boolean)
originalsize = Column('originalsize', Text)
x = Column('x', Integer)
y = Column('y', Integer)
flughoehe = Column('flughoehe', Integer)
ort = Column('ort', Text)
massstab = Column('massstab', Integer)
filesize_mb = Column('filesize_mb', Text)
bgdi_imagemode = Column('bgdi_imagemode', Text)
image_height = Column('image_height', Integer)
image_width = Column('image_width', Integer)
class bildstreifen(Base, Vector):
__tablename__ = 'view_bildstreifen'
__table_args__ = ({'schema': 'ads40', 'autoload': False})
__template__ = 'templates/htmlpopup/lubis_bildstreifen.mako'
__bodId__ = 'ch.swisstopo.lubis-bildstreifen'
__returnedGeometry__ = 'the_geom_footprint'
__timeInstant__ = 'bgdi_flugjahr'
__extended_info__ = True
id = Column('bildstreifen_nr', Text, primary_key=True)
the_geom = GeometryColumn('the_geom', Geometry(dimensions=2, srid=21781))
the_geom_footprint = GeometryColumn('the_geom_footprint',
Geometry(dimensions=2, srid=21781))
flugdatum = Column('flugdatum', Text)
firma = Column('firma', Text)
filmart = Column('filmart', Text)
bgdi_flugjahr = Column('bgdi_flugjahr', Integer)
resolution = Column('resolution', Text)
objectid = Column('objectid', Text)
area = Column('area', Text)
gsd = Column('gsd', Numeric)
toposhop_length = Column('toposhop_length', Numeric)
toposhop_start_x = Column('toposhop_start_x', Integer)
toposhop_start_y = Column('toposhop_start_y', Integer)
toposhop_end_x = Column('toposhop_end_x', Integer)
toposhop_end_y = Column('toposhop_end_y', Integer)
toposhop_date = Column('toposhop_date', Text)
class RideGeo(Base):
__tablename__ = 'ride_geo'
__table_args__ = {
'mysql_engine': 'MyISAM',
'mysql_charset': 'utf8'
} # MyISAM for spatial indexes
ride_id = Column(BigInteger, ForeignKey('rides.id'), primary_key=True)
start_geo = GeometryColumn(Point(2), nullable=False)
end_geo = GeometryColumn(Point(2), nullable=False)
def __repr__(self):
return '<{0} ride_id={1} start={2}>'.format(self.__class__.__name__,
self.ride_id,
self.start_geo)
class RideGeo(Base):
__tablename__ = "ride_geo"
__table_args__ = {
"mysql_engine": "MyISAM",
"mysql_charset": "utf8",
} # MyISAM for spatial indexes
ride_id = Column(BigInteger, ForeignKey("rides.id"), primary_key=True)
start_geo = GeometryColumn(Point(2), nullable=False)
end_geo = GeometryColumn(Point(2), nullable=False)
def __repr__(self):
return "<{0} ride_id={1} start={2}>".format(self.__class__.__name__,
self.ride_id,
self.start_geo)
class Lake(Base):
__tablename__ = 'lakes'
lake_id = Column(Integer, primary_key=True)
lake_name = Column(String)
lake_geom = GeometryColumn(Geometry(2, wkt_internal=True), comparator=PGComparator)
road_id = Column(Integer, ForeignKey('ROADS.road_id'))
class Kulturgueter(Base, Vector):
__tablename__ = 'kgs'
__table_args__ = ({'schema': 'babs', 'autoload': False})
__template__ = 'templates/htmlpopup/kgs.mako'
__queryable_attributes__ = ['zkob']
__bodId__ = 'ch.babs.kulturgueter'
__extended_info__ = True
id = Column('kgs_nr', Integer, primary_key=True)
the_geom = GeometryColumn(Geometry(dimension=2, srid=21781))
zkob = Column('zkob', Text)
x = Column('x', Numeric)
y = Column('y', Numeric)
kategorie = Column('kategorie', Text)
gemeinde = Column('gemeinde', Text)
gemeinde_ehemalig = Column('gemeinde_ehemalig', Text)
objektart = Column('objektart', Text)
hausnr = Column('hausnr', Text)
adresse = Column('adresse', Text)
kurztexte = Column('kurztexte', Text)
kt_kz = Column('kt_kz', Text)
pdf_list = Column('pdf_list', Text)
link_uri = Column('link_uri', Text)
link_title = Column('link_title', Text)
link_2_uri = Column('link_2_uri', Text)
link_2_title = Column('link_2_title', Text)
link_3_uri = Column('link_3_uri', Text)
link_3_title = Column('link_3_title', Text)
class Item(Base):
__tablename__ = 'item'
id = Column(Integer, primary_key=True)
title = Column(String(50), nullable=False)
price = Column(String(20), nullable=False)
desc = Column(String(150), nullable=False)
location = GeometryColumn(Point(2), nullable=False)
user_id = Column(Integer, ForeignKey('user.id'))
def __init__(self, title, price, desc, location=''):
self.title = title
self.price = price
self.desc = desc
self.location = location
def json(self):
item_json = {
'title': self.title,
'price': self.price,
'desc': self.desc,
'user': self.user.json()
}
if deployed_on_sae:
item_json['latlng'] = {
'lat': dbsession.scalar(self.location.x),
'lng': dbsession.scalar(self.location.y)
}
return item_json
class Article(Base, GeometryTableMixIn):
__tablename__ = "articles"
exported_keys = ["title","links_count"]
id = Column(Integer, primary_key=True)
title = Column(Unicode())
links_count = Column(Integer)
the_geom = GeometryColumn(Geometry(srid=4326))
# @property
# def links_count(self):
# return len( Session.query(Link).filter(self.id == Link.id_parent).all() )
def get_linked_articles(self,as_features=False):
"""Return the articles linked from this article"""
articles = []
for link in Session.query(Link) \
.join((Article,Article.id == Link.id_parent)) \
.filter(Article.id == self.id).all():
if link.child_article:
if as_features:
articles.append(link.child_article.toFeature())
else:
articles.append(link.child_article)
if articles and as_features:
return FeatureCollection(articles)
else:
return articles
class OSRMEdgeFrequencies(Base):
__tablename__ = "edgefrequencies"
edge = Column(BigInteger, ForeignKey('osrmedges.hash'), primary_key=True)
forward = Column(Boolean, primary_key=True)
freq = Column(BigInteger)
geom = GeometryColumn(LineString(2), comparator=PGComparator)
edgeobj = relationship("OSRMEdge")
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
class emissionplan(Base, Vector):
__tablename__ = 'laerm_emplan_bahn_2015'
__table_args__ = ({'schema': 'diverse', 'autoload': False})
__bodId__ = 'ch.bav.laerm-emissionplan_eisenbahn_2015'
__template__ = 'templates/htmlpopup/emissionplan.mako'
__extended_info__ = True
id = Column('id', Integer, primary_key=True)
lin_nr_dfa = Column('lin_nr_dfa', Numeric)
von_m = Column('von_m', Numeric)
bis_m = Column('bis_m', Numeric)
lre_t = Column('lre_t', Numeric)
lre_n = Column('lre_n', Numeric)
k1_t = Column('k1_t', Numeric)
k1_n = Column('k1_n', Numeric)
fb1 = Column('fb1', Numeric)
fb2 = Column('fb2', Numeric)
grund1 = Column('grund1', Text)
grund2 = Column('grund2', Text)
linienbeze = Column('linienbeze', Text)
von_abkz = Column('von_abkz', Text)
von_bpk_bp = Column('von_bpk_bp', Text)
bis_abkz = Column('bis_abkz', Text)
bis_bpk_bp = Column('bis_bpk_bp', Text)
typ_aender = Column('typ_aender', Text)
datum = Column('datum', Text)
the_geom = GeometryColumn(Geometry(dimension=2, srid=21781))
class oekom_abstuerze(Base, Vector):
__tablename__ = 'oekom_abstuerze'
__table_args__ = ({'schema': 'wasser', 'autoload': False})
__bodId__ = 'ch.bafu.oekomorphologie-f_abstuerze'
__template__ = 'templates/htmlpopup/oekom_abstuerze.mako'
__extended_info__ = True
id = Column('bgdi_id', Integer, primary_key=True)
abstnr = Column('abstnr', Text)
absttyp_de = Column('absttyp_de', Text)
absttyp_fr = Column('absttyp_fr', Text)
abstmat_de = Column('abstmat_de', Text)
abstmat_fr = Column('abstmat_fr', Text)
absthoehe = Column(
'absthoehe',
Numeric,
)
bemerkung = Column('bemerkung', Text)
mass = Column(
'mass',
Numeric,
)
rechtswert = Column('rechtswert', Numeric)
hochwert = Column('hochwert', Numeric)
abschnr = Column('abschnr', Text)
notizen = Column('notizen', Text)
translid = Column('translid', Numeric)
loc_angle_geo = Column('loc_angle_geo', Numeric)
datum = Column('datum', Text)
absttyp = Column('absttyp', Numeric)
abstmat = Column('abstmat', Numeric)
the_geom = GeometryColumn(Geometry(dimension=2, srid=21781))
class oekom_abschnitte(Base, Vector):
__tablename__ = 'oekom_abschnitte'
__table_args__ = ({'schema': 'wasser', 'autoload': False})
__bodId__ = 'ch.bafu.oekomorphologie-f_abschnitte'
__template__ = 'templates/htmlpopup/oekom_abschnitte.mako'
__extended_info__ = True
id = Column('bgdi_id', Integer, primary_key=True)
abschnr = Column('abschnr', Text)
gsbreite = Column('gsbreite', Numeric)
breitenvar_de = Column('breitenvar_de', Text)
breitenvar_fr = Column('breitenvar_fr', Text)
sohlver_de = Column('sohlver_de', Text)
sohlver_fr = Column('sohlver_fr', Text)
lufbebre = Column('lufbebre', Numeric)
rufbebre = Column('rufbebre', Numeric)
oekomklasse_de = Column('oekomklasse_de', Text)
oekomklasse_fr = Column('oekomklasse_fr', Text)
bemerkung = Column('bemerkung', Text)
anfangsmass = Column('anfangsmass', Numeric)
endmass = Column('endmass', Numeric)
anfangsrechtswert = Column('anfangsrechtswert', Numeric)
anfangshochwert = Column('anfangshochwert', Numeric)
endrechtswert = Column('endrechtswert', Numeric)
endhochwert = Column('endhochwert', Numeric)
eindol_de = Column('eindol_de', Text)
eindol_fr = Column('eindol_fr', Text)
vnatabst_de = Column('vnatabst_de', Text)
vnatabst_fr = Column('vnatabst_fr', Text)
tiefenvar_de = Column('tiefenvar_de', Text)
tiefenvar_fr = Column('tiefenvar_fr', Text)
sohlmat = Column('sohlmat', Numeric)
lbukver_de = Column('lbukver_de', Text)
lbukver_fr = Column('lbukver_fr', Text)
rbukver_de = Column('rbukver_de', Text)
rbukver_fr = Column('rbukver_fr', Text)
lbukmat_de = Column('lbukmat_de', Text)
lbukmat_fr = Column('lbukmat_fr', Text)
rbukmat_de = Column('rbukmat_de', Text)
rbukmat_fr = Column('rbukmat_fr', Text)
luferber_de = Column('luferber_de', Text)
luferber_fr = Column('luferber_fr', Text)
ruferber_de = Column('ruferber_de', Text)
ruferber_fr = Column('ruferber_fr', Text)
lufbebew_de = Column('lufbebew_de', Text)
lufbebew_fr = Column('lufbebew_fr', Text)
rufbebew_de = Column('rufbebew_de', Text)
rufbebew_fr = Column('rufbebew_fr', Text)
bewalgen_de = Column('bewalgen_de', Text)
bewalgen_fr = Column('bewalgen_fr', Text)
bewmakro_de = Column('bewmakro_de', Text)
bewmakro_fr = Column('bewmakro_fr', Text)
totholz_de = Column('totholz_de', Text)
totholz_fr = Column('totholz_fr', Text)
notizen = Column('notizen', Text)
translid = Column('translid', Numeric)
datum = Column('datum', Text)
oekomklasse = Column('oekomklasse', Numeric)
sohlmat_de = Column('sohlmat_de', Text)
sohlmat_fr = Column('sohlmat_fr', Text)
the_geom = GeometryColumn(Geometry(dimension=2, srid=21781))
class Gebietsauslaesse(Base, Vector):
__tablename__ = 'outlets'
__table_args__ = ({'schema': 'wasser', 'autoload': False})
__bodId__ = 'ch.bafu.wasser-gebietsauslaesse'
__template__ = 'templates/htmlpopup/gebietsauslaesse.mako'
__extended_info__ = True
id = Column('bgdi_id', Integer, primary_key=True)
ezgnr = Column('ezgnr', Integer)
gwlnr = Column('gwlnr', Text)
measure = Column('measure', Integer)
gesamtflae = Column('gesamtflae', Text)
gewaessern = Column('gewaessern', Text)
anteil_ch = Column('anteil_ch', Text)
kanal_de = Column('kanal_de', Text)
kanal_fr = Column('kanal_fr', Text)
kanal_it = Column('kanal_it', Text)
kanal_rm = Column('kanal_rm', Text)
kanal_en = Column('kanal_en', Text)
meanalt = Column('meanalt', Text)
maxalt = Column('maxalt', Text)
mq_jahr = Column('mq_jahr', Text)
feuchtflae = Column('feuchtflae', Text)
wasserflae = Column('wasserflae', Text)
bebautefl = Column('bebautefl', Text)
landwirtsc = Column('landwirtsc', Text)
wald_natur = Column('wald_natur', Text)
the_geom = GeometryColumn(Geometry(dimensions=2, srid=21781))
class Jurisdiction(Base):
__tablename__ = 'jurisdiction'
id = Column(Integer, primary_key=True)
# Readable name, e.g., "Ward 4"
name = Column(Unicode)
# URI, e.g., http://muni.example.com/wards/4
uri = Column(Unicode)
# The type of entity this represents:
type_uri = Column(Unicode)
# When it was last updated:
updated = Column(DateTime, default=datetime.now)
# The geometry it represents; everything is a multipolygon:
geom = GeometryColumn(MultiPolygon(2))
# Someplace to stuff the properties:
properties_json = Column(Unicode)
@property
def properties(self):
"""Arbitrary JSON-encodable properties; these aren't used
internally anywhere, but do give you a location to drop in
some information"""
json = self.properties_json
if not json:
return None
return loads(json)
@properties.setter
def properties(self, value):
# Repeating the 'properties' name makes pyflakes unhappy,
# but declarative_base seems to require it for some reason.
if value is None:
self.properties_json = None
else:
self.properties_json = unicode(dumps(value))
class Feature(Base):
"""A GeoNames geogaphical feature.
"""
__tablename__ = "feature"
geonameid = Column(Integer, primary_key=True)
name = Column(Unicode)
asciiname = Column(Unicode)
alternatenames = Column(Unicode)
latitude = Column(Float)
longitude = Column(Float)
feature_class = Column("feature class", Unicode)
feature_code = Column("feature code", Unicode)
country_code = Column("country code", Unicode)
cc2 = Column(Unicode)
admin1_code = Column("admin1 code", Unicode)
admin2_code = Column("admin2 code", Unicode)
admin3_code = Column("admin3 code", Unicode)
admin4_code = Column("admin4 code", Unicode)
population = Column(Integer)
elevation = Column(Integer)
gtopo30 = Column(Integer)
timezone = Column(Unicode)
modification_date = Column("modification date", Date)
uf = Column(Unicode(2))
position = GeometryColumn(Point(2), comparator=SQLiteComparator)
class Spot(Base, GeometryTableMixIn):
__tablename__ = 'spots'
spot_id = Column(Integer, primary_key=True)
spot_height = Column(Numeric(asdecimal=False))
spot_goodness = Column(Boolean)
spot_location = GeometryColumn(Point(2))
class Squares(Base, JsonifyMixin):
__tablename__ = 'square'
id = Column(Integer, Sequence('square_id_seq', start=1), primary_key=True)
key_areas = relationship('Key_area',
secondary=square_keyarea_association,
backref='squares')
geom = GeometryColumn(Polygon(dimension=2, srid=3857))
@staticmethod
def add_from_file(associations_filename, shp_filename):
'''
Добавить данные из shp-файла shp_filename. Первое поле аттрибутивной таблицы--идентификатор.
Одновременно добавляются в таблицу связи данные из файла с разделителями associations_filename.
Файл filename в формате csv (разделитель табуляция), колонки:
square_id key_area_id
'''
import transaction
with transaction.manager:
dbsession = DBSession()
ogrData = ogr.Open(shp_filename)
layer = ogrData.GetLayer(0)
sq = layer.GetNextFeature()
while sq is not None:
id = sq.GetFieldAsString(0)
geom = sq.GetGeometryRef()
geom = geom.ExportToWkt()
square = Squares(id=id,
geom=WKTSpatialElement(geom, srid=3857))
dbsession.add(square)
sq = layer.GetNextFeature()
dbsession.flush()
reader = csv.reader(open(associations_filename), delimiter='\t')
reader.next()
records = [line for line in reader]
for id, key_area_id in records:
# Определим ключевоq уч-к по его id
key_a = dbsession.query(Key_area).filter_by(
id=key_area_id).one()
# Определим полигон по его id
square = dbsession.query(Squares).filter_by(id=id).one()
square.key_areas.append(key_a)
@staticmethod
def export_to_file(filename):
from nextgisbio.utils.dump_to_file import dump
fieldnames = ['square_id', 'key_area_id']
squares_from_db = DBSession().query(Squares).join(
Squares.key_areas).order_by(Squares.id).all()
squares = []
for square in squares_from_db:
for key_area in square.key_areas:
squares.append([square.id, key_area.id])
dump(filename, fieldnames, squares, is_array=True)
class Lake(Base):
__tablename__ = 'lakes'
lake_id = Column(Integer, primary_key=True)
lake_name = Column(String)
lake_geom = GeometryColumn(Polygon(2, srid=4326, spatial_index=False),
comparator=SQLiteComparator)
class RestrictionArea(Base):
__label__ = _(u'restrictionarea')
__plural__ = _(u'restrictionareas')
__tablename__ = 'restrictionarea'
__table_args__ = {'schema': _schema}
__acl__ = [
(Allow, AUTHORIZED_ROLE, ALL_PERMISSIONS),
]
id = Column(types.Integer, primary_key=True)
area = GeometryColumn(Polygon(srid=_srid))
name = Column(types.Unicode, label=_(u'Name'))
description = Column(types.Unicode, label=_(u'Description'))
readwrite = Column(types.Boolean, label=_(u'Read-write mode'), default=False)
# relationship with Role and Layer
roles = relationship(
'Role', secondary=role_ra,
backref='restrictionareas', cascade='save-update,merge,refresh-expire')
layers = relationship(
'Layer', secondary=layer_ra,
backref='restrictionareas', cascade='save-update,merge,refresh-expire')
def __init__(self, name='', description='', layers=[], roles=[],
area=None, readwrite=False):
self.name = name
self.description = description
self.layers = layers
self.roles = roles
self.area = area
self.readwrite = readwrite
def __unicode__(self):
return self.name or u'' # pragma: nocover
class Spot(Base, GeometryTableMixIn):
__tablename__ = 'spots'
spot_id = Column(Integer, primary_key=True)
spot_height = Column(Numeric(precision=10, scale=2, asdecimal=False))
spot_location = GeometryColumn(Point(2, spatial_index=False),
nullable=True)
class RoutingEdge(Base, GeometryTableMixIn):
__tablename__ = 'edges'
__table_args__ = {
"autoload": True,
"autoload_with": Session.bind
}
the_geom = GeometryColumn(Geometry(srid=4326))
class MappedClass(Base):
__tablename__ = "table"
id = Column(types.Integer, primary_key=True)
text = Column(types.Unicode)
geom = GeometryColumn(Geometry(dimension=2, srid=4326))
def __init__(self, feature):
self.id = feature.id
self.__update__(feature)
def __update__(self, feature):
geometry = feature.geometry
if geometry is not None and \
not isinstance(geometry, Default):
shape = asShape(feature.geometry)
self.geom = WKBSpatialElement(buffer(shape.wkb), srid=4326)
self.geom.shape = shape
self.text = feature.properties.get('text', None)
@property
def __geo_interface__(self):
id = self.id
geometry = self.geom.shape
properties = dict(text=self.text)
return Feature(id=id, geometry=geometry, properties=properties)
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)
class LightRailLine(Base, GeometryTableMixIn):
__tablename__ = 'light_rail_line'
__table_args__ = {
"schema": 'foss4g',
"autoload": True,
"autoload_with": Session.bind
}
the_geom = GeometryColumn(LineString(srid=4326))
class Road(Base):
__tablename__ = 'ROADS'
road_id = Column(Integer, primary_key=True)
road_name = Column(String)
road_geom = GeometryColumn(Geometry(2), comparator=PGComparator, nullable=False)
lakes = relationship('Lake', backref="road")
class Road(Base):
__tablename__ = 'roads'
road_id = Column(Integer, primary_key=True)
road_name = Column(String)
road_geom = GeometryColumn(LineString(2, srid=4326),
comparator=SQLiteComparator,
nullable=False)
class Museum(Base, GeometryTableMixIn):
__tablename__ = 'museum'
__table_args__ = {
"schema": 'foss4g',
"autoload": True,
"autoload_with": Session.bind
}
the_geom = GeometryColumn(Point(srid=4326))
class Road(Base):
__tablename__ = 'roads'
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 StudentUnion(Base, GeometryTableMixIn):
__tablename__ = 'student_union'
__table_args__ = {
"schema": 'foss4g',
"autoload": True,
"autoload_with": Session.bind
}
the_geom = GeometryColumn(Point(srid=4326))