Matching pincodes with latitude and longitude (India)

Question

I am trying to get corresponding latitudes and longitudes for a particular pincode for India.

For pincode I have the following file.

https://data.gov.in/sites/default/files/all_india_PO_list_without_APS_offices_ver2_lat_long.csv

The data has 15 columns. I just show a part of it so that you can see how this data looks like.

                   officename pincode officeType Deliverystatus divisionname   regionname
 1:             Achalapur B.O  504273        B.O       Delivery     Adilabad    Hyderabad
 2:                   Ada B.O  504293        B.O       Delivery     Adilabad    Hyderabad
 3:               Adegaon B.O  504307        B.O       Delivery     Adilabad    Hyderabad
 4: Adilabad Collectorate S.O  504001        S.O   Non-Delivery     Adilabad    Hyderabad
 5:              Adilabad H.O  504001        H.O       Delivery     Adilabad    Hyderabad

This file has multiple lat-long mapped to one pincode.

For my use, I need to have one lat-long for a particular pincode (I have two address X and Y) and then I use Haversine to calculate the distance between X and Y

Possible options for me

1. Take an average of lat-long for pincodes, then map them. Calculate Haversine distance between X and Y.
2. Tried to use geocode.

I am getting this error mainly because I am behind office firewall

Error in curl::curl_fetch_memory(url, handle = handle) : 
  Timeout was reached: [maps.googleapis.com] Connection timed out after 10000 milliseconds

3. Any other source on net or any other way to get 1:1 mapping between pincode and lat-long

Any help is appreciated!

Answer 1

Here is what I tried for you. Your data is called mydf here. First, get rows that have values in longitude and latitude. For each group that is defined by statename and pincode, find average values for longitude and latitude. This creates out.

library(dplyr)
library(tidyr)
library(purrr)

filter(mydf, complete.cases(latitude) & complete.cases(longitude)) %>% 
group_by(statename, pincode) %>% 
summarize(ave_long = mean(longitude),
          ave_lat = mean(latitude)) -> foo

Next step was to arrange foo in a way that we can calculate Haversine distance. I found a nice way to arrange this data. See the link below. We are creating all possible combinations of the data points here.

# Arrange this data in a way that we can calculate Haversine.
# We basically create all possible combinations of rows.
# This post gave me a hand: https://community.rstudio.com/t/create-all-possible-combinations-of-a-data-frame/26848/4

myrows <- foo %>%
          group_by_all() %>%
          group_split()

out <- t(combn(x = 1:nrow(foo), m = 2)) %>%
       as_tibble() %>%
       mutate_all(~ map(., ~ pluck(myrows, .x))) %>% 
       unnest() %>% 
       setNames(nm = c("start_state", "start_pincode",
                       "start_long", "start_lat",
                       "dest_state", "dest_pincode",
                       "dest_long", "dest_lat"))

We can use distHaversine() or distGeo(). But let's try something new. SymbolixAU wrote another function. Thank you, SymbolixAU!

# https://stackoverflow.com/questions/36817423/how-to-efficiently-calculate-distance-between-pair-of-coordinates-using-data-tab/42014364#42014364

dt.haversine <- function(lat_from, lon_from, lat_to, lon_to, r = 6378137){
                    radians <- pi/180
                    lat_to <- lat_to * radians
                    lat_from <- lat_from * radians
                    lon_to <- lon_to * radians
                    lon_from <- lon_from * radians
                    dLat <- (lat_to - lat_from)
                    dLon <- (lon_to - lon_from)
                    a <- (sin(dLat/2)^2) + (cos(lat_from) * cos(lat_to)) * (sin(dLon/2)^2)
                    return(2 * atan2(sqrt(a), sqrt(1 - a)) * r)
                  }

The final step is to calculate distances.

mutate(out,
       distance = dt.haversine(lon_from = start_long, lat_from = start_lat,
                               lon_to = dest_long, lat_to = dest_lat)) -> result

 # A tibble: 6,105 x 9
#   start_state start_pincode start_long start_lat dest_state dest_pincode dest_long dest_lat distance
#   <chr>               <int>      <dbl>     <dbl> <chr>             <int>     <dbl>    <dbl>    <dbl>
# 1 KARNATAKA          560001       77.6      13.0 KARNATAKA        560003      77.6     13.0    3544.
# 2 KARNATAKA          560001       77.6      13.0 KARNATAKA        560004      77.6     12.9    4554.
# 3 KARNATAKA          560001       77.6      13.0 KARNATAKA        560005      77.6     13.0    3178.
# 4 KARNATAKA          560001       77.6      13.0 KARNATAKA        560008      77.6     13.0    4844.
# 5 KARNATAKA          560001       77.6      13.0 KARNATAKA        560010      77.6     13.0    4618.
# 6 KARNATAKA          560001       77.6      13.0 KARNATAKA        560011      77.6     12.9    5510.
# 7 KARNATAKA          560001       77.6      13.0 KARNATAKA        560013      77.6     13.1    9491.
# 8 KARNATAKA          560001       77.6      13.0 KARNATAKA        560014      77.5     13.1   12047.
# 9 KARNATAKA          560001       77.6      13.0 KARNATAKA        560017      77.7     13.0    6831.
#10 KARNATAKA          560001       77.6      13.0 KARNATAKA        560021      77.6     13.0    5148.

Answer 2

Lat/Long based distances will never match with Google distances, since the latter calculates the path distance, whereas any mathematical formula between lat/long values will be a straight line (as the bird flies).