from datetime import datetime
from geoalchemy2 import Geometry
from sqlalchemy import ForeignKey
from sqlalchemy.orm import deferred, column_property
from sqlalchemy.sql import func
from sqlalchemy.dialects.postgresql import JSONB
from utils_flask_sqla.serializers import serializable
from utils_flask_sqla_geo.serializers import geoserializable
from .env import db
from sqlalchemy.ext.hybrid import hybrid_property
@serializable
[docs]
class BibAreasTypes(db.Model):
[docs]
__tablename__ = "bib_areas_types"
[docs]
__table_args__ = {"schema": "ref_geo"}
[docs]
id_type = db.Column(db.Integer, primary_key=True)
[docs]
type_name = db.Column(db.Unicode)
[docs]
type_code = db.Column(db.Unicode)
[docs]
type_desc = db.Column(db.Unicode)
[docs]
ref_name = db.Column(db.Unicode)
[docs]
ref_version = db.Column(db.Integer)
[docs]
num_version = db.Column(db.Unicode)
[docs]
size_hierarchy = db.Column(db.Integer)
[docs]
cor_areas = db.Table(
"cor_areas",
db.Column(
"id_area_group",
db.Integer,
ForeignKey("ref_geo.l_areas.id_area"),
primary_key=True,
),
db.Column(
"id_area",
db.Integer,
ForeignKey("ref_geo.l_areas.id_area"),
primary_key=True,
),
schema="ref_geo",
)
@geoserializable
[docs]
class LAreas(db.Model):
[docs]
__tablename__ = "l_areas"
[docs]
__table_args__ = {"schema": "ref_geo"}
[docs]
id_area = db.Column(db.Integer, primary_key=True)
[docs]
id_type = db.Column(db.Integer, ForeignKey("ref_geo.bib_areas_types.id_type"))
[docs]
area_name = db.Column(db.Unicode)
[docs]
area_code = db.Column(db.Unicode)
[docs]
geom = db.Column(Geometry("MULTIPOLYGON"))
[docs]
centroid = db.Column(Geometry("POINT"))
[docs]
geom_4326 = deferred(db.Column(Geometry("MULTIPOLYGON", 4326)))
[docs]
source = db.Column(db.Unicode)
[docs]
enable = db.Column(db.Boolean, nullable=False, default=True)
[docs]
area_type = db.relationship("BibAreasTypes", lazy="select")
[docs]
parent_areas = db.relationship(
"LAreas",
secondary=cor_areas,
primaryjoin=id_area == cor_areas.c.id_area,
secondaryjoin=cor_areas.c.id_area_group == id_area,
backref="child_areas",
lazy="raise",
)
@serializable
[docs]
class BibLinearsTypes(db.Model):
[docs]
__tablename__ = "bib_linears_types"
[docs]
__table_args__ = {"schema": "ref_geo"}
[docs]
id_type = db.Column(db.Integer, primary_key=True)
[docs]
type_name = db.Column(db.Unicode(length=200), nullable=False)
[docs]
type_code = db.Column(db.Unicode(length=25), nullable=False)
[docs]
type_desc = db.Column(db.Unicode)
[docs]
ref_name = db.Column(db.Unicode(length=200))
[docs]
ref_version = db.Column(db.Integer)
[docs]
num_version = db.Column(db.Unicode(length=50))
[docs]
cor_linear_group = db.Table(
"cor_linear_group",
db.Column(
"id_group",
db.Integer,
ForeignKey("ref_geo.t_linear_groups.id_group"),
primary_key=True,
),
db.Column(
"id_linear",
db.Integer,
ForeignKey("ref_geo.l_linears.id_linear"),
primary_key=True,
),
schema="ref_geo",
)
[docs]
cor_linear_area = db.Table(
"cor_linear_area",
db.Column(
"id_area",
db.Integer,
ForeignKey("ref_geo.l_areas.id_area"),
primary_key=True,
),
db.Column(
"id_linear",
db.Integer,
ForeignKey("ref_geo.l_linears.id_linear"),
primary_key=True,
),
schema="ref_geo",
)
@geoserializable
[docs]
class LLinears(db.Model):
[docs]
__tablename__ = "l_linears"
[docs]
__table_args__ = {"schema": "ref_geo"}
[docs]
id_linear = db.Column(db.Integer, primary_key=True)
[docs]
id_type = db.Column(
db.Integer, ForeignKey("ref_geo.bib_linears_types.id_type"), nullable=False
)
[docs]
linear_name = db.Column(db.Unicode(length=250))
[docs]
linear_code = db.Column(db.Unicode(length=25))
[docs]
geom = db.Column(Geometry("GEOMETRY"))
[docs]
source = db.Column(db.Unicode(length=250))
[docs]
enable = db.Column(db.Boolean, nullable=False, default=True)
[docs]
additional_data = db.Column(JSONB)
[docs]
type = db.relationship("BibLinearsTypes")
[docs]
groups = db.relationship(
"TLinearGroups", secondary=cor_linear_group, backref="linears", lazy="raise"
)
[docs]
areas = db.relationship("LAreas", secondary=cor_linear_area, backref="linears", lazy="raise")
@serializable
[docs]
class TLinearGroups(db.Model):
[docs]
__table_name__ = "t_linear_groups"
[docs]
__table_args__ = {"schema": "ref_geo"}
[docs]
id_group = db.Column(db.Integer, primary_key=True)
[docs]
name = db.Column(db.Unicode(length=250))
[docs]
code = db.Column(db.Unicode(length=25), unique=True)
@serializable
[docs]
class BibPointsTypes(db.Model):
[docs]
__tablename__ = "bib_points_types"
[docs]
__table_args__ = {"schema": "ref_geo"}
[docs]
id_type = db.Column(db.Integer, primary_key=True)
[docs]
type_name = db.Column(db.Unicode(length=200), nullable=False)
[docs]
type_code = db.Column(db.Unicode(length=25), nullable=False)
[docs]
type_desc = db.Column(db.Unicode)
[docs]
ref_name = db.Column(db.Unicode(length=200))
[docs]
ref_version = db.Column(db.Integer)
[docs]
num_version = db.Column(db.Unicode(length=50))
@geoserializable
[docs]
class LPoints(db.Model):
[docs]
__tablename__ = "l_points"
[docs]
__table_args__ = {"schema": "ref_geo"}
[docs]
id_point = db.Column(db.Integer, primary_key=True)
[docs]
id_type = db.Column(db.Integer, ForeignKey("ref_geo.bib_points_types.id_type"), nullable=False)
[docs]
point_name = db.Column(db.Unicode(length=250))
[docs]
point_code = db.Column(db.Unicode(length=25))
[docs]
geom = db.Column(Geometry("GEOMETRY"))
[docs]
source = db.Column(db.Unicode(length=250))
[docs]
enable = db.Column(db.Boolean, nullable=False, default=True)
[docs]
additional_data = db.Column(JSONB)
[docs]
type = db.relationship("BibPointsTypes")
[docs]
geom_4326 = column_property(
func.ST_TRANSFORM(geom, 4326),
deferred=True,
)
@serializable
[docs]
class LiMunicipalities(db.Model):
[docs]
__tablename__ = "li_municipalities"
[docs]
__table_args__ = {"schema": "ref_geo"}
[docs]
id_municipality = db.Column(db.String(25), primary_key=True)
[docs]
id_area = db.Column(db.Integer)
[docs]
status = db.Column(db.Unicode)
[docs]
insee_com = db.Column(db.Unicode)
[docs]
nom_com = db.Column(db.Unicode)
[docs]
insee_arr = db.Column(db.Unicode)
[docs]
nom_dep = db.Column(db.Unicode)
[docs]
insee_dep = db.Column(db.Unicode)
[docs]
nom_reg = db.Column(db.Unicode)
[docs]
insee_reg = db.Column(db.Unicode)
[docs]
code_epci = db.Column(db.Unicode)
[docs]
plani_precision = db.Column(db.Float)
[docs]
siren_code = db.Column(db.Unicode)
[docs]
canton = db.Column(db.Unicode)
[docs]
population = db.Column(db.Integer)
[docs]
multican = db.Column(db.Unicode)
[docs]
cc_nom = db.Column(db.Unicode)
[docs]
cc_siren = db.Column(db.BigInteger)
[docs]
cc_nature = db.Column(db.Unicode)
[docs]
cc_date_creation = db.Column(db.Unicode)
[docs]
cc_date_effet = db.Column(db.Unicode)
[docs]
insee_commune_nouvelle = db.Column(db.Unicode)
@hybrid_property
[docs]
def nom_com_dept(self):
return "{} ({})".format(self.nom_com, self.insee_dep)