def test_unionagg(self): """ Testing the `Union` aggregate. """ tx = Country.objects.get(name='Texas').mpoly # Houston, Dallas -- Ordering may differ depending on backend or GEOS version. union = GEOSGeometry('MULTIPOINT(-96.801611 32.782057,-95.363151 29.763374)') qs = City.objects.filter(point__within=tx) with self.assertRaises(ValueError): qs.aggregate(Union('name')) # Using `field_name` keyword argument in one query and specifying an # order in the other (which should not be used because this is # an aggregate method on a spatial column) u1 = qs.aggregate(Union('point'))['point__union'] u2 = qs.order_by('name').aggregate(Union('point'))['point__union'] self.assertTrue(union.equals(u1)) self.assertTrue(union.equals(u2)) qs = City.objects.filter(name='NotACity') self.assertIsNone(qs.aggregate(Union('point'))['point__union'])
def get_area_notes(self, obj): from leasing.serializers.area_note import AreaNoteSerializer area_notes = None combined_area = obj.lease_areas.aggregate( union=Union("geometry"))["union"] if combined_area: area_notes = AreaNote.objects.filter( geometry__intersects=combined_area) return AreaNoteSerializer(area_notes, many=True).data
def as_competition_info(self): roi = self.regions.aggregate(roi=Union("geom"))["roi"] return calculateprizes.CompetitionInfo( id=self.id, name=self.name, criteria=self.criteria, winner_threshold=self.winner_threshold, start_date=self.start_date, end_date=self.end_date, age_groups=self.age_groups, region_of_interest=roi)
def collection_collect_metadata(collection, collect_footprint=True, collect_begin_time=True, collect_end_time=True, product_summary=False, coverage_summary=False, use_extent=False): """ Collect metadata """ if collect_footprint or collect_begin_time or collect_end_time: aggregates = {} if collect_footprint: if use_extent: aggregates["extent"] = Extent("footprint") else: aggregates["footprint"] = Union("footprint") if collect_begin_time: aggregates["begin_time"] = Min("begin_time") if collect_end_time: aggregates["end_time"] = Max("end_time") values = EOObject.objects.filter( Q(coverage__collections=collection) | Q(product__collections=collection)).aggregate(**aggregates) if collect_footprint: if use_extent: collection.footprint = Polygon.from_bbox(values["extent"]) else: collection.footprint = values["footprint"] if collect_begin_time: collection.begin_time = values["begin_time"] if collect_end_time: collection.end_time = values["end_time"] if product_summary or coverage_summary: collection_metadata, _ = CollectionMetadata.objects.get_or_create( collection=collection) if product_summary: collection_metadata.product_metadata_summary = json.dumps( _collection_metadata(collection, ProductMetadata, 'product'), indent=4, sort_keys=True) if coverage_summary: collection_metadata.coverage_metadata_summary = json.dumps( _collection_metadata(collection, CoverageMetadata, 'coverage'), indent=4, sort_keys=True) collection_metadata.save()
def get_zoomed_out(self, modify_for_shape=False): if modify_for_shape: filters.modify_queryparams_for_detail(self.request.query_params) eigen_perceel_queryset = self.filter(geo_models.EigenPerceel) filters.modify_queryparams_for_overview(self.request.query_params) niet_eigen_perceel_queryset = self.filter(geo_models.NietEigenPerceelGroep) else: filters.modify_queryparams_for_overview(self.request.query_params) eigen_perceel_queryset = self.filter(geo_models.EigenPerceelGroep) niet_eigen_perceel_queryset = self.filter(geo_models.NietEigenPerceelGroep) appartementen = [] eigenpercelen = eigen_perceel_queryset.aggregate(geom=Union('geometrie')) niet_eigenpercelen = niet_eigen_perceel_queryset.aggregate(geom=Union('geometrie')) extent = self._get_extent() return {"extent": extent, "appartementen": appartementen, "eigenpercelen": eigenpercelen['geom'], "niet_eigenpercelen": niet_eigenpercelen['geom']}
def test_geoagg_subquery(self): tx = Country.objects.get(name='Texas') union = GEOSGeometry('MULTIPOINT(-96.801611 32.782057,-95.363151 29.763374)') # Use distinct() to force the usage of a subquery for aggregation. with CaptureQueriesContext(connection) as ctx: self.assertIs(union.equals( City.objects.filter(point__within=tx.mpoly).distinct().aggregate( Union('point'), )['point__union'], ), True) self.assertIn('subquery', ctx.captured_queries[0]['sql'])
def test_geoagg_subquery(self): ks = State.objects.get(name='Kansas') union = GEOSGeometry('MULTIPOINT(-95.235060 38.971823)') # Use distinct() to force the usage of a subquery for aggregation. with CaptureQueriesContext(connection) as ctx: self.assertIs( union.equals( City.objects.filter( point__within=ks.poly).distinct().aggregate( Union('point'), )['point__union'], ), True) self.assertIn('subquery', ctx.captured_queries[0]['sql'])
def filter_areas(queryset, filter): ''' Filter chains with area filters (spatial filtering) ''' # filter by origin / destination nodes = ['origin', 'destination'] for node in nodes: area_ids = filter[node].pop('selectedAreas', []) if area_ids: area = Area.objects.filter(id__in=area_ids)\ .aggregate(area=Union('geom'))['area'] # check where with respect to the area inOrOut = filter[node].pop('inOrOut', 'in') kwargs = {node + '__geom__within': area} if inOrOut == 'in': queryset = queryset.filter(**kwargs) else: queryset = queryset.exclude(**kwargs) # filter by flows area_ids = filter['flows'] if area_ids: area = Area.objects.filter(id__in=area_ids)\ .aggregate(area=Union('geom'))['area'] # select routings & check if they intersect the area ids = queryset.values_list('routing__id', flat=True).distinct() routings = Routing.objects.filter(id__in=ids)\ .filter(geom__intersects=area)\ .values_list('id', flat=True) # filter flows: # 1) with origin / destination within area OR # 2) with routing intersecting the area queryset = queryset.filter((Q(origin__geom__within=area) \ & Q(destination__geom__within=area)) | Q(routing__id__in=routings)) return queryset
def test_union(self): """ Testing the Union aggregate of 3D models. """ # PostGIS query that returned the reference EWKT for this test: # `SELECT ST_AsText(ST_Union(point)) FROM geo3d_city3d;` self._load_city_data() ref_ewkt = 'SRID=4326;MULTIPOINT(-123.305196 48.462611 15,-104.609252 38.255001 1433,-97.521157 34.464642 380,-96.801611 32.782057 147,-95.363151 29.763374 18,-95.23506 38.971823 251,-87.650175 41.850385 181,174.783117 -41.315268 14)' ref_union = GEOSGeometry(ref_ewkt) union = City3D.objects.aggregate(Union('point'))['point__union'] self.assertTrue(union.hasz) self.assertEqual(ref_union, union)
def get_building_intersection(self): """Get the intersection of this parcel's geometry and the unioned geometry of all buildings intersecting this parcel Returns: django.contrib.gis.geos.collections.MultiPolygon """ buildings = self.get_buildings() if buildings.count() > 0: # Union the geometry of all buildings intersecting the parcel unioned_buildings = buildings.aggregate( unioned=Union('the_geom'))['unioned'] # Take the intersection of the parcel and the unioned building geometry return unioned_buildings.intersection(self.the_geom)
def convex_hulls(self): if self.site_id.exclude(polygon=None): geojson = json.loads( self.site_id.exclude(polygon=None) .aggregate(combined=Union('polygon'))['combined'] .convex_hull.geojson ) geojson['properties'] = { 'name': "Convex hull of all related Sites" } geojson = json.dumps(geojson) return geojson else: return None
def get_queryset(self): code_postal = get_object_or_404(CodePostal, code=self.kwargs["code"]) geom = code_postal.communes.aggregate(geom=Union( Cast("geometry", output_field=MultiPolygonField( geography=False))))["geom"] if geom: return queryset_elus_proches( self.request.user.person, geom).filter(commune__geometry__dwithin=( geom, D(km=5), )) return EluMunicipal.objects.none()
def test04_related_aggregate(self): "Testing the `extent` and `unionagg` GeoQuerySet aggregates on related geographic models." # This combines the Extent and Union aggregates into one query aggs = City.objects.aggregate(Extent('location__point'), Union('location__point')) # One for all locations, one that excludes Roswell. all_extent = (-104.528060913086, 33.0583305358887, -79.4607315063477, 40.1847610473633) txpa_extent = (-97.51611328125, 33.0583305358887, -79.4607315063477, 40.1847610473633) e1 = City.objects.extent(field_name='location__point') e2 = City.objects.exclude(name='Roswell').extent( field_name='location__point') e3 = aggs['location__point__extent'] # The tolerance value is to four decimal places because of differences # between the Oracle and PostGIS spatial backends on the extent calculation. tol = 4 for ref, e in [(all_extent, e1), (txpa_extent, e2), (all_extent, e3)]: for ref_val, e_val in zip(ref, e): self.assertAlmostEqual(ref_val, e_val, tol) # These are the points that are components of the aggregate geographic # union that is returned. p1 = Point(-104.528056, 33.387222) p2 = Point(-97.516111, 33.058333) p3 = Point(-79.460734, 40.18476) # Creating the reference union geometry depending on the spatial backend, # as Oracle will have a different internal ordering of the component # geometries than PostGIS. The second union aggregate is for a union # query that includes limiting information in the WHERE clause (in other # words a `.filter()` precedes the call to `.unionagg()`). if SpatialBackend.oracle: ref_u1 = MultiPoint(p3, p1, p2, srid=4326) ref_u2 = MultiPoint(p3, p2, srid=4326) else: ref_u1 = MultiPoint(p1, p2, p3, srid=4326) ref_u2 = MultiPoint(p2, p3, srid=4326) u1 = City.objects.unionagg(field_name='location__point') u2 = City.objects.exclude(name='Roswell').unionagg( field_name='location__point') u3 = aggs['location__point__union'] self.assertEqual(ref_u1, u1) self.assertEqual(ref_u2, u2) self.assertEqual(ref_u1, u3)
def site_extent(): u = Site.objects.filter(polygon__isvalid=True).aggregate(Union('polygon'))['polygon__union'] try: u.transform(ct=3035) except Exception as e: print(e) u = None if u: try: sq_m = "{:,.0f}".format(u.area/1000000) except Exception as e: sq_m = "{}".format(e) return sq_m else: return "{} there is currently something going wrong"
def save_polygons(area, **args): print('Working on', area.type.code, area.name, '...', end=' ') args['area__type'] = area_type geometry = Geometry.objects.filter(**args) p = geometry.aggregate(Union('polygon'))['polygon__union'] if options['commit']: area.polygons.all().delete() if p.geom_type == 'Polygon': shapes = [p] else: shapes = p for g in shapes: area.polygons.create(polygon=g) done.append(area.id) print('done')
def set_geom(self): if self.communes.count() == 1: commune = self.communes.all()[0] if commune.geom.__class__.__name__ == 'MultiPolygon': self.geom = commune.geom elif commune.geom.__class__.__name__ == 'Polygon': # Bien que normalement, on ne devrait pas avoir de type Polygon self.geom = MultiPolygon(commune.geom) elif self.communes.count() > 1: geom__union = self.communes.aggregate(Union('geom'))['geom__union'] if geom__union: try: self.geom = geom__union except TypeError: self.geom = MultiPolygon(geom__union) super().save(update_fields=('geom', ))
def get_convex_hull(self): if Site.objects.exclude(polygon=None): sites = [x.id for x in Site.objects.exclude(polygon=None) if x.polygon.valid] valid_sites = Site.objects.filter(id__in=sites) geojson = json.loads( valid_sites.exclude(polygon=None) .aggregate(combined=Union('polygon'))['combined'] .convex_hull.geojson ) geojson['properties'] = { 'name': "Convex hull of all {} objects".format(self.__name__) } geojson = json.dumps(geojson) return geojson else: None
def geocodeToMultiPolygon(self, geocode_list, value_name): try: result = Geocode.objects.filter(value_name=value_name, code__in=geocode_list) if len(result) > 0: result = result.aggregate(Union('geom'))['geom__union'] try: if result.geom_type != 'MultiPolygon': geom = MultiPolygon(result) else: geom = result except Exception, e: logging.error('Error in geocodeToMultiPolygon') geom = None return geom else:
def test_union(self): """ Testing the Union aggregate of 3D models. """ # PostGIS query that returned the reference EWKT for this test: # `SELECT ST_AsText(ST_Union(point)) FROM geo3d_city3d;` self._load_city_data() ref_ewkt = ( "SRID=4326;MULTIPOINT(-123.305196 48.462611 15,-104.609252 38.255001 1433," "-97.521157 34.464642 380,-96.801611 32.782057 147,-95.363151 29.763374 18," "-95.23506 38.971823 251,-87.650175 41.850385 181,174.783117 -41.315268 14)" ) ref_union = GEOSGeometry(ref_ewkt) union = City3D.objects.aggregate(Union("point"))["point__union"] self.assertTrue(union.hasz) # Ordering of points in the resulting geometry may vary between implementations self.assertEqual({p.ewkt for p in ref_union}, {p.ewkt for p in union})
def convex_hull_archents(self): if self.has_archent.exclude(polygon=None): try: geojson = json.loads( self.has_archent.exclude(polygon=None) .aggregate(combined=Union('polygon'))['combined'] .convex_hull.geojson ) geojson['properties'] = { 'name': "Convex hull of all Archaeological Entities" } geojson = json.dumps(geojson) except: geojson = None return geojson else: return None
def test_unionagg_tolerance(self): City.objects.create( point=fromstr('POINT(-96.467222 32.751389)', srid=4326), name='Forney', ) tx = Country.objects.get(name='Texas').mpoly # Tolerance is greater than distance between Forney and Dallas, that's # why Dallas is ignored. forney_houston = GEOSGeometry( 'MULTIPOINT(-95.363151 29.763374, -96.467222 32.751389)', srid=4326, ) self.assertIs( forney_houston.equals( City.objects.filter(point__within=tx).aggregate( Union('point', tolerance=32000), )['point__union'], ), True, )
def get_matching_basis_of_rents(self, obj): from leasing.serializers.basis_of_rent import BasisOfRentSerializer q = Q() property_identifiers = obj.lease_areas.values_list("identifier", flat=True) if property_identifiers: q = Q(property_identifiers__identifier__in=property_identifiers) combined_area = obj.lease_areas.aggregate( union=Union("geometry"))["union"] if combined_area: q |= Q(geometry__intersects=combined_area) if not q: return [] return BasisOfRentSerializer(BasisOfRent.objects.filter(q), many=True).data
def geophysical_extent(): excavation = SkosConcept.objects.filter(pref_label='geophysical survey') u = generous_concept_filter( ResearchEvent.objects.filter(polygon__isvalid=True), 'research_method', excavation ).aggregate(Union('polygon'))['polygon__union'] try: u.transform(ct=3035) except Exception as e: print(e) u = None if u: try: sq_m = "{:,.0f}".format(u.area/1000000) except Exception as e: sq_m = "{}".format(e) return sq_m else: return "there is currently something going wrong"
def test_unionagg_tolerance(self): City.objects.create( point=fromstr("POINT(-96.467222 32.751389)", srid=4326), name="Forney", ) tx = Country.objects.get(name="Texas").mpoly # Tolerance is greater than distance between Forney and Dallas, that's # why Dallas is ignored. forney_houston = GEOSGeometry( "MULTIPOINT(-95.363151 29.763374, -96.467222 32.751389)", srid=4326, ) self.assertIs( forney_houston.equals_exact( City.objects.filter(point__within=tx).aggregate( Union("point", tolerance=32000), )["point__union"], tolerance=10e-6, ), True, )
def update_national_boundary_file(): """ Generate a national boundary by joining together all CCG boundaries and write it to disk Run with: echo 'import frontend.management.commands.infer_practice_boundaries as c;' \ 'c.update_national_boundary_file()' \ | ./manage.py shell """ ccgs_without_boundary = PCT.objects.filter(org_type="CCG", close_date__isnull=True, boundary__isnull=True) if ccgs_without_boundary.exists(): raise RuntimeError(""" Some active CCGs missing boundary data, meaning we can't reliably synthesize a national boundary by aggregating CCGs """) boundary = PCT.objects.filter(boundary__isnull=False).aggregate( boundary=Union("boundary"))["boundary"] with open(NATIONAL_BOUNDARY_FILE, "wb") as f: f.write(boundary.geojson)
def check_geodata(request): json_layer_stringified = request.GET.get('aoi') mpoly = GEOSGeometry(json_layer_stringified) user_profile = Profile.objects.get(pk=request.user.pk) user_download_area = user_profile.download_region.all() if user_download_area.exists(): country_layer = user_download_area.filter( geom__intersects=mpoly).aggregate(area=Union('geom'))["area"] if country_layer is not None: aoi_intersection_layer = mpoly.intersection(country_layer) return JsonResponse(aoi_intersection_layer.geojson, safe=False) else: return JsonResponse({ "status": False, "message": "Please draw a rectangle inside your allowed download region.", "title": 'Area of Interest not valid!' }) else: return JsonResponse(mpoly.geojson, safe=False)
def filter_outside_boundary(self, queryset, field_name, boundary_uuid): """Filter records that fall outside the specified boundary.""" redis_conn = get_redis_connection('boundaries') bounds_hexewkb = redis_conn.get(boundary_uuid) one_month_seconds = 30 * 24 * 60 * 60 if bounds_hexewkb is None: try: boundary = Boundary.objects.get(pk=boundary_uuid) except ValidationError: raise ParseError('outside_boundary was passed an invalid UUID') except Boundary.DoesNotExist: raise NotFound('Boundary not found') unioned_bounds = boundary.polygons.aggregate( all_polys=Union('geom'))['all_polys'] # Full resolution is very slow, so simplify down to roughly 100m (DRIVER is in lat/lon). unioned_bounds = unioned_bounds.simplify(tolerance=0.001, preserve_topology=True) redis_conn.set(boundary_uuid, str(unioned_bounds.hexewkb), one_month_seconds) else: redis_conn.expire(boundary_uuid, one_month_seconds) unioned_bounds = GEOSGeometry(bounds_hexewkb) return queryset.exclude(geom__intersects=unioned_bounds)
def get_context_data(self, **kwargs): context = super(PolyFitsArchEnts, self).get_context_data() sites = Site.objects.exclude(polygon=None) errors = [] for x in Site.objects.exclude(polygon=None): archs = None poly = None try: archs = x.has_archent.exclude(polygon=None).aggregate(combined=Union('polygon')) except: archs = None if archs: archs = archs['combined'] poly = x.polygon if archs: try: if Site.objects.filter(id=x.id).filter(polygon__covers=archs): pass else: errors.append(x) except: pass context['errors'] = errors return context
def filter_queryset(self, request, queryset, view): qs = queryset duration_min = request.GET.get('duration_min', None) if duration_min is not None: qs = qs.filter(duration__gte=duration_min) duration_max = request.GET.get('duration_max', None) if duration_max is not None: qs = qs.filter(duration__lte=duration_max) length_min = request.GET.get('length_min', None) if length_min is not None: qs = qs.filter(length__gte=length_min) length_max = request.GET.get('length_max', None) if length_max is not None: qs = qs.filter(length__lte=length_max) difficulty_min = request.GET.get('difficulty_min', None) if difficulty_min is not None: qs = qs.filter(difficulty__cirkwi_level__gte=difficulty_min) difficulty_max = request.GET.get('difficulty_max', None) if difficulty_max is not None: qs = qs.filter(difficulty__cirkwi_level__lte=difficulty_max) ascent_min = request.GET.get('ascent_min', None) if ascent_min is not None: qs = qs.filter(ascent__gte=ascent_min) ascent_max = request.GET.get('ascent_max', None) if ascent_max is not None: qs = qs.filter(ascent__lte=ascent_max) city = request.GET.get('city', None) if city is not None: cities_list = [int(c) for c in city.split(',')] union_geom = City.objects.filter( reduce(operator.or_, (Q(**{'code': c}) for c in cities_list))).aggregate( Union('geom'))['geom__union'] qs = qs.filter(geom__intersects=union_geom) district = request.GET.get('district', None) if district is not None: districts_list = [int(d) for d in district.split(',')] union_geom = District.objects.filter( reduce(operator.or_, (Q(**{'pk': d}) for d in districts_list))).aggregate( Union('geom'))['geom__union'] qs = qs.filter(geom__intersects=union_geom) structure = request.GET.get('structure', None) if structure is not None: qs = qs.filter(structure__pk=structure) accessibilities = request.GET.get('accessibility', None) if accessibilities is not None: list_accessibilities = [int(a) for a in accessibilities.split(',')] qs = qs.filter(accessibilities__in=list_accessibilities) themes = request.GET.get('theme', None) if themes is not None: list_themes = [int(t) for t in themes.split(',')] qs = qs.filter(themes__in=list_themes) portals = request.GET.get('portal', None) if portals is not None: list_portals = [int(p) for p in portals.split(',')] qs = qs.filter(portal__in=list_portals) route = request.GET.get('route', None) if route is not None: qs = qs.filter(route__pk=route) labels = request.GET.get('label', None) if labels is not None: list_labels = [int(label) for label in labels.split(',')] qs = qs.filter(portal__in=list_labels) q = request.GET.get('q', None) if q is not None: qs = qs.filter( Q(name__icontains=q) | Q(description__icontains=q) | Q(description_teaser__icontains=q) | Q(ambiance__icontains=q)) return qs
def test_unionagg_tolerance_escaping(self): tx = Country.objects.get(name='Texas').mpoly with self.assertRaises(DatabaseError): City.objects.filter(point__within=tx).aggregate( Union('point', tolerance='0.05))), (((1'), )