加速用于缺失值地理插值的Django数据库函数

Let's break down how to optimize your imputation logic directly in Django's database layer—this will drastically outperform pulling all data into Pandas for processing, especially with 5M+ records and 200k missing values.

Core Logic Recap #

For each property with missing floor area, we need to:

• Find same-industry properties within a specified radius that have valid floor area data
• Calculate the median of rent / floor_area (cost per square meter) from those neighbors
• Impute the missing floor area as current_rent / median_cost_per_sqm

Step 1: Ensure Your Model & Database Are Geospatially Enabled #

First, make sure your Django model uses a geospatial field for locations (requires PostGIS for PostgreSQL, which is ideal for radius-based queries):

from django.contrib.gis.db import models

class CommercialProperty(models.Model):
    industry = models.CharField(max_length=100)
    address = models.CharField(max_length=255)
    rent = models.DecimalField(max_digits=12, decimal_places=2)
    floor_area = models.DecimalField(max_digits=10, decimal_places=2, null=True, blank=True)
    location = models.PointField()  # Stores latitude/longitude as a geospatial point

Step 2: Add Critical Indexes to Speed Up Queries #

Database indexes are make-or-break here. Run these SQL commands (or use Django migrations) to optimize the radius and industry filters:

-- Accelerate geospatial distance checks
CREATE INDEX idx_commercialproperty_location ON myapp_commercialproperty USING GIST (location);

-- Optimize combined industry + location queries
CREATE INDEX idx_commercialproperty_industry_location ON myapp_commercialproperty USING GIST (industry, location);

-- Speed up rent/floor_area calculations for valid records
CREATE INDEX idx_commercialproperty_valid_floor_area ON myapp_commercialproperty (industry, floor_area, rent) WHERE floor_area IS NOT NULL;

Step 3: Batch Imputation with Database-Side Calculations #

Instead of pulling data into Pandas, we'll let PostgreSQL handle all the computation via Django's ORM with raw SQL subqueries. This avoids memory bottlenecks and leverages the database's optimized query planner.

Basic Imputation Query #

This updates all missing floor area records in one go, using neighbor median cost per sqm:

from django.db.models import F
from django.db.models.expressions import RawSQL

# Define your radius (e.g., 1000 meters)
RADIUS_METERS = 1000

# Batch update missing floor_area values
CommercialProperty.objects.filter(floor_area__isnull=True).update(
    floor_area=F('rent') / RawSQL(
        """
        SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY p.rent / p.floor_area)
        FROM myapp_commercialproperty p
        WHERE p.industry = myapp_commercialproperty.industry
          AND ST_DWithin(p.location, myapp_commercialproperty.location, %s)
          AND p.floor_area IS NOT NULL
        """,
        params=[RADIUS_METERS],
        output_field=models.DecimalField()
    )
)

Handle Edge Cases (No Neighbor Data) #

If a property has no same-industry neighbors with valid floor area, the subquery returns NULL. Add a fallback using the industry-wide median:

CommercialProperty.objects.filter(floor_area__isnull=True).update(
    floor_area=F('rent') / RawSQL(
        """
        SELECT COALESCE(
            -- First try: median from nearby same-industry properties
            (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY p.rent / p.floor_area)
             FROM myapp_commercialproperty p
             WHERE p.industry = myapp_commercialproperty.industry
               AND ST_DWithin(p.location, myapp_commercialproperty.location, %s)
               AND p.floor_area IS NOT NULL),
            -- Fallback: median from all same-industry properties
            (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY p.rent / p.floor_area)
             FROM myapp_commercialproperty p
             WHERE p.industry = myapp_commercialproperty.industry
               AND p.floor_area IS NOT NULL)
        )
        """,
        params=[RADIUS_METERS],
        output_field=models.DecimalField()
    )
)

Step 4: Optional: Split into Batches for Very Large Datasets #

If updating 200k records at once strains your database, split the operation into smaller batches:

batch_size = 10000
# Get min/max IDs for missing records
id_range = CommercialProperty.objects.filter(floor_area__isnull=True).aggregate(
    min_id=models.Min('id'),
    max_id=models.Max('id')
)
current_id = id_range['min_id']
max_id = id_range['max_id']

while current_id <= max_id:
    end_id = current_id + batch_size - 1
    CommercialProperty.objects.filter(
        floor_area__isnull=True,
        id__gte=current_id,
        id__lte=end_id
    ).update(
        # Use the same RawSQL as above here
        floor_area=F('rent') / RawSQL(...)
    )
    current_id = end_id + 1

Why This Beats Pandas #

• No Data Transfer: All computation stays in the database, avoiding the overhead of pulling 5M+ records into Python memory.
• Database Optimization: PostgreSQL's query planner uses the indexes we added to run radius and industry filters efficiently.
• Batch Processing: Updates run in bulk, which is far faster than looping through individual records in Pandas.

内容的提问来源于stack exchange，提问作者Turukawa