""" Management command to import biotope polygon data from habitats_and_biotopes app into eventhub. Usage: python manage.py import_biotopes_to_eventhub """ from django.core.management.base import BaseCommand from django.db import transaction from django.db.models import Min, Max from habitats_and_biotopes.models import ( GwyneddNrwPh1IntertidalBiotope, GwyneddNrwPh1IntertidalBiotopeSites, GwyneddNrwPh1IntertidalBiotopeNotes ) from eventhub.models import Event, EventAttribute class Command(BaseCommand): help = 'Import biotope polygons from habitats_and_biotopes into eventhub' def handle(self, *args, **options): # Get or create the project Event (event_type='project') project_event, project_event_created = Event.objects.get_or_create( name="NRW Intertidal Biotopes – Gwynedd", event_type='project', defaults={ 'parent': None, } ) if project_event_created: self.stdout.write(self.style.SUCCESS(f'Created project event: {project_event.name}')) else: self.stdout.write(f'Using existing project event: {project_event.name}') # Get biotope polygon records for specific site numbers only allowed_site_numbers = ['9.14.1', '9.14.2', '9.15.1', '9.16.1', '9.17.1'] # Step 1: Create Site events first self.stdout.write('Creating Site events...') site_events = {} sites_created = 0 sites_reused = 0 with transaction.atomic(): for site_number in allowed_site_numbers: # Get site record try: site_record = GwyneddNrwPh1IntertidalBiotopeSites.objects.get(siteno=site_number) except GwyneddNrwPh1IntertidalBiotopeSites.DoesNotExist: self.stdout.write(self.style.WARNING(f'Site {site_number} not found in sites table, skipping...')) continue except Exception as e: # Handle case where table doesn't exist or other database errors if 'does not exist' in str(e).lower() or 'undefinedtable' in str(e).lower(): self.stdout.write(self.style.ERROR( f'Table gwynedd_nrw_ph1_intertidal_biotope_sites does not exist. ' f'Creating Site event {site_number} without geometry from sites table.' )) # Create site event without geometry from sites table site_record = None else: raise # Convert geom to WKT if available footprint_wkt = None if site_record and site_record.geom: try: footprint_wkt = site_record.geom.wkt except (AttributeError, Exception): footprint_wkt = None # Compute dates from child biotope records biotope_dates = GwyneddNrwPh1IntertidalBiotope.objects.filter( siteno=site_number ).aggregate( min_start=Min('visit'), max_end=Max('last_visit') ) # Create or get Site event with project_event as parent site_event, site_created = Event.objects.get_or_create( name=f"Site {site_number}", event_type='site', parent=project_event, defaults={ 'start_date': biotope_dates['min_start'], 'end_date': biotope_dates['max_end'], 'location_note': f"siteno={site_number}", 'footprintWKT': footprint_wkt, } ) if site_created: sites_created += 1 else: sites_reused += 1 # Update dates and geometry if needed if biotope_dates['min_start'] and site_event.start_date != biotope_dates['min_start']: site_event.start_date = biotope_dates['min_start'] if biotope_dates['max_end'] and site_event.end_date != biotope_dates['max_end']: site_event.end_date = biotope_dates['max_end'] if footprint_wkt and site_event.footprintWKT != footprint_wkt: site_event.footprintWKT = footprint_wkt if site_event.parent != project_event: site_event.parent = project_event site_event.save() site_events[site_number] = site_event self.stdout.write(f'Created {sites_created} new Site events, reused {sites_reused} existing') # Step 2: Create biotope events with Site as parent biotopes = GwyneddNrwPh1IntertidalBiotope.objects.filter(siteno__in=allowed_site_numbers) total_count = biotopes.count() self.stdout.write(f'Processing {total_count} biotope records...') events_created = 0 events_reused = 0 records_processed = 0 for biotope in biotopes: records_processed += 1 # Build event name from siteno and f2004_code name_parts = [] if biotope.siteno: name_parts.append(str(biotope.siteno)) if biotope.f2004_code: name_parts.append(str(biotope.f2004_code)) if not name_parts: # Fallback if both are missing if biotope.polygonid: event_name = f"Polygon {biotope.polygonid}" elif biotope.gid: event_name = f"Polygon {biotope.gid}" else: event_name = "Unknown Polygon" else: event_name = " – ".join(name_parts) # Build location note location_parts = [] if biotope.siteid is not None: location_parts.append(f"siteid={biotope.siteid}") if biotope.siteno: location_parts.append(f"siteno={biotope.siteno}") location_note = ", ".join(location_parts) if location_parts else None # Convert geom to WKT if available footprint_wkt = None if biotope.geom: try: # geom is a MultiPolygonField, which has a .wkt property footprint_wkt = biotope.geom.wkt except (AttributeError, Exception): footprint_wkt = None # Get the parent Site event for this biotope parent_site = site_events.get(biotope.siteno) if not parent_site: self.stdout.write(self.style.WARNING(f'No Site event found for {biotope.siteno}, skipping biotope...')) continue # Create or get the Event using natural key (name, parent) # Use a combination that uniquely identifies the polygon event, event_created = Event.objects.get_or_create( name=event_name, parent=parent_site, defaults={ 'event_type': 'biotope_mapping_unit', 'start_date': biotope.visit, 'end_date': biotope.last_visit, 'location_note': location_note, 'footprintWKT': footprint_wkt, 'habitat': biotope.habitat, 'fieldNotes': biotope.notes, } ) if event_created: events_created += 1 else: events_reused += 1 # Update dates if they exist and are different if biotope.visit and event.start_date != biotope.visit: event.start_date = biotope.visit if biotope.last_visit and event.end_date != biotope.last_visit: event.end_date = biotope.last_visit if location_note and event.location_note != location_note: event.location_note = location_note # Update geometry, habitat, and notes if footprint_wkt and event.footprintWKT != footprint_wkt: event.footprintWKT = footprint_wkt if biotope.habitat and event.habitat != biotope.habitat: event.habitat = biotope.habitat if biotope.notes and event.fieldNotes != biotope.notes: event.fieldNotes = biotope.notes # Update parent if it's not set correctly if event.parent != parent_site: event.parent = parent_site event.save() # Delete existing attributes for this event and recreate them # This ensures idempotency - running the command multiple times gives the same result EventAttribute.objects.filter(event=event).delete() # Create EventAttribute records for non-null/empty values attributes_to_create = [] if biotope.f2004_code: attributes_to_create.append( EventAttribute( event=event, attribute_type='biotopeCode', attribute_value=str(biotope.f2004_code) ) ) if biotope.biotope: attributes_to_create.append( EventAttribute( event=event, attribute_type='historicBiotopeCode', attribute_value=str(biotope.biotope) ) ) if biotope.lifeform: attributes_to_create.append( EventAttribute( event=event, attribute_type='lifeform', attribute_value=str(biotope.lifeform) ) ) if biotope.maptext: attributes_to_create.append( EventAttribute( event=event, attribute_type='mapText', attribute_value=str(biotope.maptext) ) ) if biotope.areaha is not None: attributes_to_create.append( EventAttribute( event=event, attribute_type='eco:geospatialScopeAreaValue', attribute_value=str(biotope.areaha), unit='ha' ) ) if biotope.data_type: attributes_to_create.append( EventAttribute( event=event, attribute_type='eco:protocolNames', attribute_value=str(biotope.data_type) ) ) # Bulk create attributes if attributes_to_create: EventAttribute.objects.bulk_create(attributes_to_create) # Step 3: Import notes for each site self.stdout.write('Importing notes for sites...') notes_created = 0 notes_reused = 0 for site_number, site_event in site_events.items(): # Get all notes for this site site_notes = GwyneddNrwPh1IntertidalBiotopeNotes.objects.filter(siteno=site_number) for note_record in site_notes: # Convert point geometry to WKT if available footprint_wkt = None if note_record.geom: try: footprint_wkt = note_record.geom.wkt except (AttributeError, Exception): footprint_wkt = None # Build event name from targetnote or other identifiers if note_record.targetnote is not None: event_name = f"Note {note_record.targetnote}" elif note_record.id: event_name = f"Note {note_record.id}" elif note_record.objectid is not None: event_name = f"Note {note_record.objectid}" else: event_name = f"Note for Site {site_number}" # Create or get the Note event note_event, note_created = Event.objects.get_or_create( name=event_name, parent=site_event, event_type='note', defaults={ 'footprintWKT': footprint_wkt, 'location_note': f"siteno={site_number}", } ) if note_created: notes_created += 1 else: notes_reused += 1 # Update geometry if needed if footprint_wkt and note_event.footprintWKT != footprint_wkt: note_event.footprintWKT = footprint_wkt note_event.save() # Delete existing attributes for this note event and recreate them EventAttribute.objects.filter(event=note_event).delete() # Create EventAttribute records for the note attributes note_attributes_to_create = [] if note_record.targetnote is not None: note_attributes_to_create.append( EventAttribute( event=note_event, attribute_type='targetnote', attribute_value=str(note_record.targetnote) ) ) if note_record.targetno0: note_attributes_to_create.append( EventAttribute( event=note_event, attribute_type='targetno0', attribute_value=str(note_record.targetno0) ) ) if note_record.targetno1: note_attributes_to_create.append( EventAttribute( event=note_event, attribute_type='targetno1', attribute_value=str(note_record.targetno1) ) ) if note_record.accesslink: note_attributes_to_create.append( EventAttribute( event=note_event, attribute_type='accesslink', attribute_value=str(note_record.accesslink) ) ) if note_record.osgridref: note_attributes_to_create.append( EventAttribute( event=note_event, attribute_type='osgridref', attribute_value=str(note_record.osgridref) ) ) # Bulk create note attributes if note_attributes_to_create: EventAttribute.objects.bulk_create(note_attributes_to_create) self.stdout.write(f'Created {notes_created} new Note events, reused {notes_reused} existing') # Step 4: Update Site event dates from all child biotopes self.stdout.write('Updating Site event dates from child biotopes...') for site_number, site_event in site_events.items(): # Recompute dates from all child biotope events child_dates = Event.objects.filter( parent=site_event, event_type='biotope_mapping_unit' ).aggregate( min_start=Min('start_date'), max_end=Max('end_date') ) updated = False if child_dates['min_start'] and site_event.start_date != child_dates['min_start']: site_event.start_date = child_dates['min_start'] updated = True if child_dates['max_end'] and site_event.end_date != child_dates['max_end']: site_event.end_date = child_dates['max_end'] updated = True if updated: site_event.save() # Step 5: Update project event dates from all child Site events self.stdout.write('Updating project event dates from child Site events...') site_dates = Event.objects.filter( parent=project_event, event_type='site' ).aggregate( min_start=Min('start_date'), max_end=Max('end_date') ) updated = False if site_dates['min_start'] and project_event.start_date != site_dates['min_start']: project_event.start_date = site_dates['min_start'] updated = True if site_dates['max_end'] and project_event.end_date != site_dates['max_end']: project_event.end_date = site_dates['max_end'] updated = True if updated: project_event.save() # Step 6: Clean up orphaned events (events that no longer exist in source data) self.stdout.write('Cleaning up orphaned events...') orphaned_biotopes = 0 orphaned_notes = 0 orphaned_sites = 0 # Track which events should exist expected_biotope_events = set() expected_note_events = set() # Collect all expected biotope events for biotope in biotopes: parent_site = site_events.get(biotope.siteno) if not parent_site: continue name_parts = [] if biotope.siteno: name_parts.append(str(biotope.siteno)) if biotope.f2004_code: name_parts.append(str(biotope.f2004_code)) if not name_parts: if biotope.polygonid: event_name = f"Polygon {biotope.polygonid}" elif biotope.gid: event_name = f"Polygon {biotope.gid}" else: event_name = "Unknown Polygon" else: event_name = " – ".join(name_parts) expected_biotope_events.add((event_name, parent_site.id)) # Collect all expected note events for site_number, site_event in site_events.items(): site_notes = GwyneddNrwPh1IntertidalBiotopeNotes.objects.filter(siteno=site_number) for note_record in site_notes: if note_record.targetnote is not None: event_name = f"Note {note_record.targetnote}" elif note_record.id: event_name = f"Note {note_record.id}" elif note_record.objectid is not None: event_name = f"Note {note_record.objectid}" else: event_name = f"Note for Site {site_number}" expected_note_events.add((event_name, site_event.id)) # Delete orphaned biotope events for site_number, site_event in site_events.items(): existing_biotopes = Event.objects.filter( parent=site_event, event_type='biotope_mapping_unit' ) for biotope_event in existing_biotopes: if (biotope_event.name, site_event.id) not in expected_biotope_events: # Delete associated EventAttributes first EventAttribute.objects.filter(event=biotope_event).delete() biotope_event.delete() orphaned_biotopes += 1 # Delete orphaned note events for site_number, site_event in site_events.items(): existing_notes = Event.objects.filter( parent=site_event, event_type='note' ) for note_event in existing_notes: if (note_event.name, site_event.id) not in expected_note_events: # Delete associated EventAttributes first EventAttribute.objects.filter(event=note_event).delete() note_event.delete() orphaned_notes += 1 # Delete orphaned site events (sites not in allowed_site_numbers) existing_sites = Event.objects.filter( parent=project_event, event_type='site' ) expected_site_names = {f"Site {site_num}" for site_num in allowed_site_numbers} for site_event in existing_sites: if site_event.name not in expected_site_names: # Delete all children first (biotopes and notes) children = Event.objects.filter(parent=site_event) for child in children: EventAttribute.objects.filter(event=child).delete() children.delete() # Delete site event site_event.delete() orphaned_sites += 1 if orphaned_biotopes > 0 or orphaned_notes > 0 or orphaned_sites > 0: self.stdout.write(f'Deleted {orphaned_biotopes} orphaned biotope events') self.stdout.write(f'Deleted {orphaned_notes} orphaned note events') self.stdout.write(f'Deleted {orphaned_sites} orphaned site events') else: self.stdout.write('No orphaned events found') # Print summary self.stdout.write(self.style.SUCCESS('\n' + '='*50)) self.stdout.write(self.style.SUCCESS('Import Summary:')) self.stdout.write(self.style.SUCCESS('='*50)) self.stdout.write(f'Site events created: {sites_created}') self.stdout.write(f'Site events reused: {sites_reused}') if orphaned_sites > 0: self.stdout.write(f'Site events deleted: {orphaned_sites}') self.stdout.write(f'Biotope records processed: {records_processed}') self.stdout.write(f'Biotope events created: {events_created}') self.stdout.write(f'Biotope events reused: {events_reused}') if orphaned_biotopes > 0: self.stdout.write(f'Biotope events deleted: {orphaned_biotopes}') self.stdout.write(f'Note events created: {notes_created}') self.stdout.write(f'Note events reused: {notes_reused}') if orphaned_notes > 0: self.stdout.write(f'Note events deleted: {orphaned_notes}') self.stdout.write(self.style.SUCCESS('='*50))