Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    1

    Separate Address Table, Latitude, Longitude.

    Hi All. We are designing a DB for a social networking application. Users (Registered Users), Retail Outlets, etc are several entities we need to store. We need to store Address of each of these entities. Also we need to store latitude and longitude of Registered Users, Retail Outlets, etc.

    Initially we designed the tables as shown below. We created a separate Address tables as the format of the address will be same for Users, Retail Outlets and other entities.

    User(UserID, UserName, AddressID)
    RetailOutlet(RetailOutletID, OutletName, AddressID)
    Address(AddressID, CityID, AddressLine1, AddressLine2, Latitude, Longitude)

    Now we realized that we need to search for Retail Outlets near to User's current location(CurrentLatitude, CurrentLongitude)
    So we would need to run a query like
    SELECT OutletName from RetaileOutlet INNER JOIN Address ON RetailOutlet.AddressID = Address.AddressId WHERE Address.Latitude BETWEEN CurrentLatitude - 10 and CurrentLatitude + 10 and Address.Longitude BETWEEN CurrentLongitude - 10 and CurrentLongitude + 10

    Now we think that above query would run slow because the number of users will be much more than the number of retail outlets. From DB Design/Performance perspective which of the following should be done.

    a. Create index on Latitude, Longitude in Address table and on AddressID in RetailOutlet table. So there would be no change in table structure.

    b. Move Latitude, Longitude from Address table to RetailOutlet and User tables. Also create index on Latitude, Longitude in RetailOutlet table. So the table structure would be as shown below.
    User(UserID, UserName, AddressID, Latitude, Longitude)
    RetailOutlet(RetailOutletID, OutletName, AddressID, Latitude, Longitude)
    Address(AddressID, CityID, AddressLine1, AddressLine2)


    c. Change table structure as shown below and create index on Lat, Lng in UserAddress table and on UserAddressID in User table.
    User(UserID, UserName, UserAddressID)
    UserAddress(AddressID, CityID, AddressLine1, AddressLine2, Latitude, Longitude)
    RetailOutlet(RetailOutletID, OutletName, AddressID)
    RetailOutletAddress(RetailOutletAddressID, CityID, AddressLine1, AddressLine2, Latitude, Longitude)

    d. Introduce DB redundancy by copying Lat, Lng from Address table in the RetailOutlet table and create index on Lat, Lng in RetailOutlet table. With this option there will be no change in Address table and we will be able to use it for storing User address without change. Also we may be able to use it for storing Address of other entities.
    User(UserID, UserName, AddressID)
    RetailOutlet(RetailOutletID, OutletName, AddressID, Latitude, Longitude)
    Address(AddressID, CityID, AddressLine1, AddressLine2, Latitude, Longitude)

  2. #2
    Join Date
    Feb 2012
    Posts
    76
    My order of preference is a, b, d, c; since I prefer consistency and dislike redundancy. However, what you should really do is sit down with your db and a reasonable set of data and explain your queries and test them with various indices.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    depends on the db you are using

    if its MySQL then you can use the spatial extensions (assuming you want to do some form of bearing and or distance calculations). however that does mean you MUST use MyISAM as the table type where you store the lat/long. not an issue (you can have a 1:1 from an innodb to Myisam

    other db's have similar mechanisms to support geo location services
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •