Geo-Radius Filtering Module

Author(s)

• Sanket Mal
• Ayan Ghosh
• Ashik Ikbal

Last Updated Date

2025-11-14

---

SRS References

---

Version History

Version	Date	Changes	Author
1.0	2025-11-14	Initial draft for geo-radius filtering for auctions and inventory	Development Team

---

Feature Overview

Objective:
Implement a geo-radius filtering system that enables the platform to display auctions within a configured radius from dealer showrooms or auction locations. This feature uses geospatial calculations to filter data based on proximity.

Scope:
This feature covers:

• Display auctions within a configured radius from dealer showroom locations
• Haversine formula-based distance calculation
• Configurable radius settings
• Integration with existing auction management, dealer management

Dependencies:

• Auction Management System – To fetch and filter active auctions
• Dealer Management System – To access dealer showroom addresses

---

Requirements

Functional Requirements

1. Support geo-radius filtering for dealer-based auction display
2. Calculate distances using the Haversine formula
3. Filter data based on configurable radius values
4. Efficiently handle large datasets of zip codes and locations

Non-Functional Requirements

1. Performance: Distance calculations should be optimized for large datasets
2. Accuracy: Location-based filtering should be accurate within acceptable tolerance
3. Scalability: System should handle multiple concurrent radius calculations
4. Maintainability: Radius configuration should be easily adjustable

---

Workflow

The geo-radius filtering logic is integrated into the following existing APIs:

1. Dealer Service → UpdateDealerRadius API - Update radius for specific dealer
2. Dealer Service → UpdateDealer API - Update radius,latitude & longitude for specific dealer all addresses and update isprimary field
3. Auction Service → CreateMultiRequirementAuction API - Send notifications to dealers based on zipcode radius
4. Auction Service → GetPublicPaginatedAuctionList - Show auctions based on zipcode radius

---

Database Schema

zipcodedetails Table

CREATE TABLE IF NOT EXISTS public.zipcodedetails
(
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    zipcode varchar(10) NOT NULL,
    lat double precision NOT NULL,
    lng double precision NOT NULL,
    createdat timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updatedat timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT zipcodedetails_pkey PRIMARY KEY (id),
    CONSTRAINT zipcodedetails_zip_code_key UNIQUE (zipcode)
);

CREATE INDEX idx_zipcodedetails_lat_lng ON public.zipcodedetails(lat, lng);

ALTER TABLE public.dealermaster ADD COLUMN IF NOT EXISTS radius double precision DEFAULT 100.0;

ALTER TABLE public.dealeraddress ADD COLUMN IF NOT EXISTS latitude double precision;
ALTER TABLE public.dealeraddress ADD COLUMN IF NOT EXISTS longitude double precision;
ALTER TABLE public.dealeraddress ADD COLUMN IF NOT EXISTS radius DOUBLE precision;
ALTER TABLE public.dealeraddress ADD COLUMN IF NOT EXISTS isprimary BOOLEAN DEFAULT FALSE;


ALTER TABLE public.auctionmaster ADD COLUMN IF NOT EXISTS latitude double precision;
ALTER TABLE public.auctionmaster ADD COLUMN IF NOT EXISTS longitude double precision;

Table Description:

• id: Unique identifier (UUID)
• zip_code: Zip/Postal code (unique)
• lat: Latitude coordinate
• lng: Longitude coordinate
• created_at: Timestamp of record creation
• updated_at: Timestamp of last update

---

Flow 1: Update Dealer Radius (UpdateDealerRadius API)

API: Dealer Service → UpdateDealerRadius

When to Apply: When a dealer wants to update their service radius

Implementation Logic:

Step 1: Receive Radius Update Request

• Accept dealer ID and new radius value (in KM)
• Validate radius value (must be positive number, e.g., max 500 KM)

Step 2: Update Dealer Radius

• Update the radius field in dealermaster table for the specified dealer
• Example: UPDATE dealermaster SET radius = 150 WHERE dealerid = '{dealerId}'

Step 3: Return Response

• Return success message confirming radius update
• Include updated radius value in response

---

Flow 2: Update Dealer Information (UpdateDealer API)

API: Dealer Service → UpdateDealer

When to Apply: When a dealer updates their profile information including addresses

Implementation Logic:

Step 1: Receive Dealer Update Request

• Accept dealer information including addresses with zip codes
• Validate all required fields

Step 2: Fetch Latitude & Longitude from zipcodedetails

• For each address in the update request:
  • Query zipcodedetails table using the provided zip code
  • Retrieve lat and lng values
  • Auto-populate latitude and longitude fields for the address

Step 3: Update isprimary Field

• Ensure only one address has isprimary = true
• If a new primary address is specified, set all other addresses' isprimary = false

Step 4: Update Dealer and Address Records

• Update dealer information in dealermaster table
• Update or insert addresses in dealeraddresses table with populated lat/lng values

Step 5: Return Response

• Return success message confirming dealer update
• Include updated dealer information with addresses

---

Flow 3: Send Notification to Dealers When New Auction Created (CreateMultiRequirementAuction API)

API: Auction Service → CreateMultiRequirementAuction

When to Apply: After a new auction is successfully created

Implementation Logic:

Step 1: Get Auction Zip Code

• Extract the lat/long from the newly created auction

Step 2: Fetch All Dealer Address

• Query the dealeraddresses table to retrieve all dealer addresses
• For each primary address, fetch latitude, longitude, and radius
• If radius does not exist in dealeraddresses, fetch it from dealermaster table for that dealer
• Example:

  SELECT da.dealerid, da.latitude, da.longitude,
         COALESCE(da.radius, dm.radius) as radius
  FROM dealeraddresses da
  LEFT JOIN dealermaster dm ON da.dealerid = dm.dealerid

For each dealer's primary showroom:

• Use dealer's lat/long and auction's lat/long

• Calculate distance using Haversine formula

• If distance ≤ dealer's configured radius:

  • Add dealer to EligibleDealers list

• Send notifications only to dealers in EligibleDealers list

---

Flow 4: Show Auctions Based on Zip Code Radius (GetPublicPaginatedAuctionList)

API: Auction Service → GetPaginatedAuctionList

When to Apply: When a dealer requests the auction list

Implementation Logic:

Step 1: Fetch Primary Dealer Address

• Query dealeraddresses table for the requesting dealer's primary address where isprimary = true
• Retrieve latitude, longitude, and radius from the primary address
• Example: SELECT latitude, longitude, radius FROM dealeraddresses WHERE dealerid = '{dealerId}' AND isprimary = true

Step 2: Get Configured Radius

• If radius exists in the dealer address record:
  • Use the radius from dealeraddresses table
• If radius is null or not set in dealer address:
  • Fallback to fetch radius from dealermaster table
  • Example: SELECT radius FROM dealermaster WHERE dealerid = '{dealerId}'
• Result: Radius = 100 KM (from either source)

Step 3: Apply Location and Radius Filter (Optional)

• If filter parameters include specific location (from dealeraddress) and radius:
  • Use the provided location and radius from filter instead of dealer's primary address
  • This allows flexible searching with custom location and radius

Step 4: Fetch and Filter Auctions Within Radius

• Fetch all active auctions from auctionmaster where Status = 'Active'
• For each auction:
  • Calculate distance between dealer's location (or filter location) and auction's lat/long using Haversine formula
  • If distance ≤ configured radius:
    • Include auction in result set

Step 5: Apply Pagination and Return Auctions

• Apply pagination to filtered auctions

• Return paginated list of auctions within the specified radius

  ( *Use Efficient Query For This)

---

Haversine Formula Implementation

The Haversine formula calculates the great-circle distance between two points on a sphere given their longitudes and latitudes. (We will use query for efficiency)

public static class GeoCalculator
{
    private const double EarthRadiusKm = 6371.0;

    public static double CalculateDistance(double lat1, double lon1, double lat2, double lon2)
    {
        // Convert degrees to radians
        double lat1Rad = DegreesToRadians(lat1);
        double lon1Rad = DegreesToRadians(lon1);
        double lat2Rad = DegreesToRadians(lat2);
        double lon2Rad = DegreesToRadians(lon2);

        // Calculate differences
        double dLat = lat2Rad - lat1Rad;
        double dLon = lon2Rad - lon1Rad;

        // Apply Haversine formula
        double a = Math.Sin(dLat / 2) * Math.Sin(dLat / 2) +
                   Math.Cos(lat1Rad) * Math.Cos(lat2Rad) *
                   Math.Sin(dLon / 2) * Math.Sin(dLon / 2);

        double c = 2 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1 - a));

        // Calculate distance
        double distance = EarthRadiusKm * c;

        return distance;
    }

    private static double DegreesToRadians(double degrees)
    {
        return degrees * Math.PI / 180.0;
    }
}

---

Development Tasks & Estimates

No	Task Name	Estimate (Hours)	Dependencies	Notes
1	Database schema updates (dealermaster, dealeraddress, auctionmaster)	3 hours	None	Add radius, lat/lng, isprimary columns
2	Populate zipcodedetails table with lat/lng data	2 hours	Task 1	Import and validate zip code coordinates
3	Implement UpdateDealerRadius API	4 hours	Task 1	Dealer-only access, JWT-based auth, 1-1000 KM validation
4	Implement UpdateDealer API with lat/lng auto-population	6 hours	Task 1, Task 2	Fetch from zipcodedetails, manage isprimary field
5	Implement Haversine distance calculation utility	3 hours	None	SQL-based and C# implementation
6	Implement CreateMultiRequirementAuction notification logic	5 hours	Task 4, Task 5	Filter dealers by radius, send notifications
7	Implement GetPublicPaginatedAuctionList radius filtering	8 hours	Task 5	Efficient query with Haversine, pagination support
8	Optimize distance calculation queries for large datasets	6 hours	Task 5, Task 7	Performance tuning, indexing, query optimization
9	Integration testing with Dealer and Auction services	8 hours	All previous tasks	Test all 4 flows end-to-end
10	Unit testing and edge case handling	6 hours	All previous tasks	Test boundary conditions, null handling
11	Documentation and API documentation	3 hours	All previous tasks	Complete technical documentation
	Total	54 hours

---

Implementation Notes

Performance Optimization Considerations

1. Database Optimization:

   • Add spatial indexes on latitude/longitude columns
   • Consider using PostGIS or similar spatial database extensions for better performance
   • Pre-calculate and store distances for frequently accessed locations

2. Query Optimization:

   • Use batch processing for distance calculations
   • Implement pagination for large result sets
   • Consider async processing for complex calculations

Error Handling

1. Handle missing latitude/longitude data gracefully
2. Validate radius configuration values (must be positive)
3. Handle empty result sets appropriately
4. Log distance calculation errors for monitoring