Google Geocode循环调用返回undefined及15000+地址就近查询优化技术问询

Hey there! Let's tackle your two geospatial problems one by one—they’re both super common when working with large address datasets, so let’s break down what’s going wrong and how to fix it.

Problem 1: GeoCoding returns undefined in your loop #

Why this happens #

The Google Maps Geocoder’s geocode method is asynchronous. That means it doesn’t return results immediately—it uses a callback function to send data once the API call finishes. When you call GeoCoding(addrrs) in your loop, the function exits right away (before the callback runs), so it defaults to returning undefined. Your SaveToDb function then tries to split that undefined, which will throw errors too.

Fixes to implement #

We need to refactor the code to handle async properly, plus add safeguards for Google’s API rate limits (critical for 15k+ requests).

Step 1: Rewrite GeoCoding as a Promise

Wrap the geocoder call in a Promise so we can use async/await to wait for results:

function GeoCoding(addr) {
  return new Promise((resolve, reject) => {
    const geocoder = new google.maps.Geocoder();
    geocoder.geocode({ 'address': addr }, function (results, status) {
      if (status === google.maps.GeocoderStatus.OK) {
        const latitude = results[0].geometry.location.lat();
        const longitude = results[0].geometry.location.lng();
        resolve(`${latitude},${longitude}`);
      } else {
        reject(new Error(`Geocoding failed for ${addr}: ${status}`));
      }
    });
  });
}

Step 2: Make your loop async-aware

Update InitDatabaseCities to use await for both geocoding and saving, plus add a delay to avoid hitting rate limits:

async function InitDatabaseCities() { 
  const cit = CSVstring_to_Array(); 
  for (const ase of cit) { 
    const addrrs = `${ase.AREA}, ${ase.CITY}`; 
    try {
      // Wait for geocoding results
      const ltlg = await GeoCoding(addrrs);
      // Wait for the save to complete before moving to the next address
      await SaveToDb(ase.AREA, ase.CITY, ase.DIST_CENTER, ltlg.split(',')[0], ltlg.split(',')[1]);
      // Add a 200ms delay (adjust based on your API plan—free tier allows 5 requests/sec)
      await new Promise(resolve => setTimeout(resolve, 200));
    } catch (err) {
      console.error(err.message);
      // Log failed addresses so you can retry them later
    }
  } 
}

Step 3: Update SaveToDb to return a Promise

Since $.ajax is also async, wrap it in a Promise to ensure the loop waits for each save:

function SaveToDb(area, city, dist_center, lat, lng) { 
  return new Promise((resolve, reject) => {
    const url = "http://localhost:50264/api/service-areas/post"; 
    const data = new FormData(); 
    data.append('area', area); 
    data.append('city', city); 
    data.append('dist_center', dist_center); 
    data.append('lat', lat); 
    data.append('lng', lng); 
    $.ajax({ 
      url: url, 
      type: "Post", 
      dataType: "json", 
      data: data, 
      success: function (response) { 
        resolve(response);
      }, 
      error: function (xhr, status, err) { 
        reject(new Error(`Save failed: ${err}`));
      } 
    }); 
  });
}

Critical Notes #

• Rate Limits: Google’s free Geocoding API allows 2500 requests/day and 5/sec. For 15k addresses, you’ll need a paid plan or batch requests over multiple days.
• Error Handling: Some addresses might fail to geocode (invalid input, API errors). Log these instead of skipping them so you can retry later.

Problem 2: Find the nearest address to a given location (15k+ entries) #

First, a quick clarification: this isn’t the Traveling Salesman Problem (TSP). TSP is about finding the shortest route that visits all locations—your problem is a nearest neighbor search, which is much easier to optimize.

Why looping through all entries is bad #

A naive loop has a time complexity of O(n)—for every query, you calculate the distance to 15k addresses. This will be slow for repeated queries and won’t scale as your dataset grows.

Better Solutions #

Option 1: Use a Spatial Database (Best for Server-Side)

If you’re storing addresses in a database, add spatial support (e.g., PostgreSQL with PostGIS, MySQL Spatial Extensions):

1. Store lat/lng as a POINT geometry column.
2. Create a spatial index (like a GIST index in PostGIS) to speed up searches.
3. Use a query like this to find the nearest address to your target location (latZ, lngZ):

SELECT area, city, dist_center, 
       ST_Distance(geom, ST_SetSRID(ST_MakePoint(lngZ, latZ), 4326)) AS distance
FROM cities
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(lngZ, latZ), 4326)
LIMIT 1;

This uses the spatial index to find the nearest neighbor in O(log n) time—way faster than looping through all entries.

Option 2: Client-Side Spatial Indexing (Browser-Based)

Use a library like Turf.js which handles spatial indexing out of the box:

1. Convert your address list to GeoJSON features (assuming you’ve already geocoded them):

const citiesGeoJSON = {
  type: "FeatureCollection",
  features: cit.map(ase => ({
    type: "Feature",
    properties: { area: ase.AREA, city: ase.CITY, dist_center: ase.DIST_CENTER },
    geometry: {
      type: "Point",
      coordinates: [parseFloat(ase.lng), parseFloat(ase.lat)] // GeoJSON uses lng first, lat second
    }
  }))
};

2. Use Turf’s knn function to find the nearest address:

const targetPoint = turf.point([lngZ, latZ]); // Your target location (lng, lat)
const nearestAddress = turf.knn(citiesGeoJSON, targetPoint, 1)[0]; // Get top 1 nearest

console.log("Nearest Address:", nearestAddress.properties);
console.log("Distance (km):", turf.distance(targetPoint, nearestAddress));

Turf uses a spatial index under the hood to minimize distance calculations, making this efficient even for 15k entries.

Key Takeaway #

Spatial indexing is the way to go here. It cuts down the number of distance calculations you need to perform, making nearest neighbor searches fast and scalable.

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