0

i am running a dating website, or better say, try to. I want to be as generic
as possible in terms of, coverage for countries.

Since it's a local dating oriented website, i have to keep track of cities
and so on so i am running into a few problems:

  1. I need to have information about cities, people could join from
    (a country i don't know anything about, i really can't decide about a city
    or anything)

  2. When people join from different countries, they would like to see people
    near by. How would you approach this sort or problem?

This may seem like 2 easy points but they really make me some trouble these
days. ** Try to approach them as Database related issues **

I circled around SE and find this to be the best point to ask, not any other
SE website.

Thank you very much

Herr
  • 2,725
  • 3
  • 30
  • 36
  • You should try to find a list of countries & city's with their coordinates. Then you could write a database function that can calculate distances between cities from a certain country. Maybe google maps has this in its API, I'm not sure. – Jens Mar 20 '11 at 08:35
  • Hallo Jens, Danke! What about hosting them all in the same database? Does it make sense? Or should i split the countries? – Herr Mar 20 '11 at 08:44
  • You can put all of the cities in 1 table. You don't know how many countries you will have so it's hard to make a table for each country (or am I not understanding your question?) – Jens Mar 20 '11 at 08:57

2 Answers2

2

The full name of a city in the USA is {country_name, state_name, city_name}. I say that assuming that, on a global scale, what I call state_name is not unique. I can easily imagine some region of a Latin American country being named "Nevada" or "Colorado".

You also have the problem of spelling. An American living in Germany might say she lived in Cologne, North Rhine-Westphalia, Germany. A German living there would call it "Köln".

"Nearby" is an even tougher nut to crack. I used to live in a big city. It wasn't unusual to drive an hour on an Interstate highway to pick up my date. I thought of that as "nearby". Now I live in a small city. Now 30 minutes seems like a long drive, but it's probably only 15 or 20 miles instead of 65 or 70.

If you live in a border town, 15 miles might be in a different country. Maybe two different countries.

I think your best bet is to use a geolocation API or service to get the latitude and longitude of new cities when they're inserted. The calculation of distance given two points is straightforward, but it leads to badly performing queries unless you use a bounding box. (You don't want to calculate the distance to every city in Texas to find people "near" Waco.)

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
1

Get the latitude/longitude for all of the cities. Don't worry about exact distances.

Play with excel and find the maximum change for latitude and/or longitude (Δlat or Δlong) for given distances (10miles, 25 miles, 50 miles), and then when you search for others, just search the database for within (Firstuser'sLongitude +/- Δlong) and within (Firstuser'sLatitude +/-Δlong).

I don't think people will mind the difference between 10miles and 10*2^0.5 miles.

James
  • 11
  • 1