def overlapping(cls, klass, queryset):
from .models import Path, Topology, PathAggregation
all_objects = klass.objects.existing()
is_generic = klass.KIND == Topology.KIND
single_input = isinstance(queryset, QuerySet)
if single_input:
topology_pks = [
str(pk) for pk in queryset.values_list('pk', flat=True)
]
else:
topology_pks = [str(queryset.pk)]
if len(topology_pks) == 0:
return all_objects.filter(pk__in=[])
sql = """
WITH topologies AS (SELECT id FROM %(topology_table)s WHERE id IN (%(topology_list)s)),
-- Concerned aggregations
aggregations AS (SELECT * FROM %(aggregations_table)s a, topologies t
WHERE a.evenement = t.id),
-- Concerned paths along with (start, end)
paths_aggr AS (SELECT a.pk_debut AS start, a.pk_fin AS end, p.id, a.ordre AS order
FROM %(paths_table)s p, aggregations a
WHERE a.troncon = p.id
ORDER BY a.ordre)
-- Retrieve primary keys
SELECT t.id
FROM %(topology_table)s t, %(aggregations_table)s a, paths_aggr pa
WHERE a.troncon = pa.id AND a.evenement = t.id
AND least(a.pk_debut, a.pk_fin) <= greatest(pa.start, pa.end)
AND greatest(a.pk_debut, a.pk_fin) >= least(pa.start, pa.end)
AND %(extra_condition)s
ORDER BY (pa.order + CASE WHEN pa.start > pa.end THEN (1 - a.pk_debut) ELSE a.pk_debut END);
""" % {
'topology_table': Topology._meta.db_table,
'aggregations_table': PathAggregation._meta.db_table,
'paths_table': Path._meta.db_table,
'topology_list': ','.join(topology_pks),
'extra_condition':
'true' if is_generic else "kind = '%s'" % klass.KIND
}
cursor = connection.cursor()
cursor.execute(sql)
result = cursor.fetchall()
pk_list = uniquify([row[0] for row in result])
# Return a QuerySet and preserve pk list order
# http://stackoverflow.com/a/1310188/141895
ordering = 'CASE %s END' % ' '.join([
'WHEN %s.id=%s THEN %s' % (Topology._meta.db_table, id_, i)
for i, id_ in enumerate(pk_list)
])
queryset = all_objects.filter(pk__in=pk_list).extra(
select={'ordering': ordering}, order_by=('ordering', ))
return queryset
def overlapping(cls, klass, queryset):
from .models import Path, Topology, PathAggregation
all_objects = klass.objects.existing()
is_generic = klass.KIND == Topology.KIND
single_input = isinstance(queryset, QuerySet)
if single_input:
topology_pks = [str(pk) for pk in queryset.values_list('pk', flat=True)]
else:
topology_pks = [str(queryset.pk)]
if len(topology_pks) == 0:
return all_objects.filter(pk__in=[])
sql = """
WITH topologies AS (SELECT id FROM %(topology_table)s WHERE id IN (%(topology_list)s)),
-- Concerned aggregations
aggregations AS (SELECT * FROM %(aggregations_table)s a, topologies t
WHERE a.evenement = t.id),
-- Concerned paths along with (start, end)
paths_aggr AS (SELECT a.pk_debut AS start, a.pk_fin AS end, p.id, a.ordre AS order
FROM %(paths_table)s p, aggregations a
WHERE a.troncon = p.id
ORDER BY a.ordre)
-- Retrieve primary keys
SELECT t.id
FROM %(topology_table)s t, %(aggregations_table)s a, paths_aggr pa
WHERE a.troncon = pa.id AND a.evenement = t.id
AND least(a.pk_debut, a.pk_fin) <= greatest(pa.start, pa.end)
AND greatest(a.pk_debut, a.pk_fin) >= least(pa.start, pa.end)
AND %(extra_condition)s
ORDER BY (pa.order + CASE WHEN pa.start > pa.end THEN (1 - a.pk_debut) ELSE a.pk_debut END);
""" % {
'topology_table': Topology._meta.db_table,
'aggregations_table': PathAggregation._meta.db_table,
'paths_table': Path._meta.db_table,
'topology_list': ','.join(topology_pks),
'extra_condition': 'true' if is_generic else "kind = '%s'" % klass.KIND
}
cursor = connection.cursor()
cursor.execute(sql)
result = cursor.fetchall()
pk_list = uniquify([row[0] for row in result])
# Return a QuerySet and preserve pk list order
# http://stackoverflow.com/a/1310188/141895
ordering = 'CASE %s END' % ' '.join(['WHEN %s.id=%s THEN %s' % (Topology._meta.db_table, id_, i)
for i, id_ in enumerate(pk_list)])
queryset = all_objects.filter(pk__in=pk_list).extra(
select={'ordering': ordering}, order_by=('ordering',))
return queryset
def path_area_edges(cls, path):
return cls.objects.existing()\
.select_related('restricted_area')\
.select_related('restricted_area__area_type')\
.filter(aggregations__path=path).distinct('pk')
@classmethod
def topology_area_edges(cls, topology):
return cls.overlapping(topology)\
.select_related('restricted_area')\
.select_related('restricted_area__area_type')
if settings.TREKKING_TOPOLOGY_ENABLED:
Path.add_property('area_edges', RestrictedAreaEdge.path_area_edges)
Path.add_property('areas', lambda self: uniquify(map(attrgetter('restricted_area'), self.area_edges)))
Topology.add_property('area_edges', RestrictedAreaEdge.topology_area_edges)
Topology.add_property('areas', lambda self: uniquify(map(attrgetter('restricted_area'), self.area_edges)))
Intervention.add_property('area_edges', lambda self: self.topology.area_edges if self.topology else [])
Intervention.add_property('areas', lambda self: self.topology.areas if self.topology else [])
Project.add_property('area_edges', lambda self: self.edges_by_attr('area_edges'))
Project.add_property('areas', lambda self: uniquify(map(attrgetter('restricted_area'), self.area_edges)))
else:
Topology.add_property('areas', lambda self: intersecting(RestrictedArea, self))
TouristicContent.add_property('areas', lambda self: intersecting(RestrictedArea, self))
TouristicEvent.add_property('areas', lambda self: intersecting(RestrictedArea, self))
class City(models.Model):
code = models.CharField(primary_key=True, max_length=6, db_column='insee')
def cities(self):
return uniquify(intersecting(City, self.zoning_property, distance=0))
def districts(self):
return uniquify(intersecting(District, self.zoning_property, distance=0))
def areas(self):
return uniquify(intersecting(RestrictedArea, self.zoning_property, distance=0))
def path_area_edges(cls, path):
return cls.objects.existing()\
.select_related('restricted_area')\
.select_related('restricted_area__area_type')\
.filter(aggregations__path=path).distinct('pk')
@classmethod
def topology_area_edges(cls, topology):
return cls.overlapping(topology)\
.select_related('restricted_area')\
.select_related('restricted_area__area_type')
if settings.TREKKING_TOPOLOGY_ENABLED:
Path.add_property('area_edges', RestrictedAreaEdge.path_area_edges, _("Restricted area edges"))
Path.add_property('areas', lambda self: uniquify(map(attrgetter('restricted_area'), self.area_edges)),
_("Restricted areas"))
Topology.add_property('area_edges', RestrictedAreaEdge.topology_area_edges, _("Restricted area edges"))
Topology.add_property('areas', lambda self: uniquify(
intersecting(RestrictedArea, self)) if self.ispoint() else uniquify(
map(attrgetter('restricted_area'), self.area_edges)), _("Restricted areas"))
Intervention.add_property('area_edges', lambda self: self.topology.area_edges if self.topology else [],
_("Restricted area edges"))
Intervention.add_property('areas', lambda self: self.topology.areas if self.topology else [],
_("Restricted areas"))
Project.add_property('area_edges', lambda self: self.edges_by_attr('area_edges'), _("Restricted area edges"))
Project.add_property('areas', lambda self: uniquify(map(attrgetter('restricted_area'), self.area_edges)),
_("Restricted areas"))
else:
Topology.add_property('areas', lambda self: uniquify(intersecting(RestrictedArea, self, distance=0)),
_("Restricted areas"))
def path_area_edges(cls, path):
return cls.objects.existing()\
.select_related('restricted_area')\
.select_related('restricted_area__area_type')\
.filter(aggregations__path=path).distinct('pk')
@classmethod
def topology_area_edges(cls, topology):
return cls.overlapping(topology)\
.select_related('restricted_area')\
.select_related('restricted_area__area_type')
if settings.TREKKING_TOPOLOGY_ENABLED:
Path.add_property('area_edges', RestrictedAreaEdge.path_area_edges, _(u"Restricted area edges"))
Path.add_property('areas', lambda self: uniquify(map(attrgetter('restricted_area'), self.area_edges)),
_(u"Restricted areas"))
Topology.add_property('area_edges', RestrictedAreaEdge.topology_area_edges, _(u"Restricted area edges"))
Topology.add_property('areas', lambda self: uniquify(
intersecting(RestrictedArea, self)) if self.ispoint() else uniquify(
map(attrgetter('restricted_area'), self.area_edges)), _(u"Restricted areas"))
Intervention.add_property('area_edges', lambda self: self.topology.area_edges if self.topology else [],
_(u"Restricted area edges"))
Intervention.add_property('areas', lambda self: self.topology.areas if self.topology else [],
_(u"Restricted areas"))
Project.add_property('area_edges', lambda self: self.edges_by_attr('area_edges'), _(u"Restricted area edges"))
Project.add_property('areas', lambda self: uniquify(map(attrgetter('restricted_area'), self.area_edges)),
_(u"Restricted areas"))
else:
Topology.add_property('areas', lambda self: uniquify(intersecting(RestrictedArea, self, distance=0)),
_(u"Restricted areas"))
.filter(aggregations__path=path).distinct('pk')
@classmethod
def topology_area_edges(cls, topology):
if settings.TREKKING_TOPOLOGY_ENABLED:
qs = cls.overlapping(topology)
else:
qs = cls.objects.filter(geom__intersects=topology.geom)
return qs.select_related('restricted_area')\
.select_related('restricted_area__area_type')
Path.add_property('area_edges', RestrictedAreaEdge.path_area_edges)
Path.add_property(
'areas',
lambda self: uniquify(map(attrgetter('restricted_area'), self.area_edges)))
Topology.add_property('area_edges', RestrictedAreaEdge.topology_area_edges)
Topology.add_property(
'areas',
lambda self: uniquify(map(attrgetter('restricted_area'), self.area_edges)))
Intervention.add_property(
'area_edges', lambda self: self.topology.area_edges
if self.topology else [])
Intervention.add_property(
'areas', lambda self: self.topology.areas if self.topology else [])
Project.add_property('area_edges',
lambda self: self.edges_by_attr('area_edges'))
Project.add_property(
'areas',
lambda self: uniquify(map(attrgetter('restricted_area'), self.area_edges)))
.select_related('restricted_area')\
.select_related('restricted_area__area_type')\
.filter(aggregations__path=path).distinct('pk')
@classmethod
def topology_area_edges(cls, topology):
return cls.overlapping(topology)\
.select_related('restricted_area')\
.select_related('restricted_area__area_type')
if settings.TREKKING_TOPOLOGY_ENABLED:
Path.add_property('area_edges', RestrictedAreaEdge.path_area_edges,
_("Restricted area edges"))
Path.add_property(
'areas', lambda self: uniquify(
map(attrgetter('restricted_area'), self.area_edges)),
_("Restricted areas"))
Path.add_property(
'published_areas',
lambda self: [area for area in self.areas if area.published],
_("Published areas"))
Topology.add_property('area_edges', RestrictedAreaEdge.topology_area_edges,
_("Restricted area edges"))
Topology.add_property(
'areas', lambda self: uniquify(intersecting(RestrictedArea, self))
if self.ispoint() else uniquify(
map(attrgetter('restricted_area'), self.area_edges)),
_("Restricted areas"))
Intervention.add_property(
'area_edges', lambda self: self.target.area_edges
if self.target and self.target else [], _("Restricted area edges"))