Hackernoon logoBuilding a Pincode Mapper and Routing engine with Postgis and pgrouting by@sangarshanan

Building a Pincode Mapper and Routing engine with Postgis and pgrouting

Author profile picture



šŸŒŽ Storing and Processing GIS dataĀ :

Storing and Processing GIS data has become really important nowadays as many organizations deal with geospatial data all the time. As you might know postgres offers a beautiful extension called Postgis that can be easily used to store, query and process spatial information. Pgrouting extends postgis to provide geospatial routing functionality.

Sometimes we have location data with respect to time. Such data is called spatiotemporal data and this data is very useful to track something or someone with respect to both space andĀ time.

Trajectories in Postgis can be used to handle spatiotemporal data but if your data is huge then you could make use of Geomesa or Geospark with Apache Accumulo and Geoserver to handle your spatiotemporal Bigdata.

QGIS along with Timemanager plugin is a really cool open source way to visualize spatiotemporal data. (https://github.com/anitagraser). You could also check out Topi Tjukanov who has done some really cool stuff with qgis and timemanager. His articles and animations have helped a lotĀ šŸ™šŸ¼šŸ™šŸ¼

Special shoutout to mah boy Kepler.gl (https://github.com/uber/kepler.gl)

šŸŒŽ Pincode Mapping forĀ India

India has post offices and each one has its own service area. The service areas encompassed by every post office have their own unique Pincode so that a post office knows its bounds. So historically we have divided our cities and strategically placed post offices that could service the entire city. This data is valuable. It would be really cool if we could extrapolate the pincode from a given latitude and longitude.

DatasetĀ :

  1. Pincode Boundaries data for cities (https://github.com/datameet/PincodeBoundary)
  2. Pincode mapped to LatLongs (https://github.com/sanand0/pincode/blob/master/data/IN.csv)


  1. With the Pincode Boundaries dataset we can isolate the latlong to a specific city using a bounding box and check which pincode polygon contains the given latlong (Pretty straightforward) but this method would prove to be ambiguous for the latlongs located on the edges of the polygons (Literal edge casesā€¦smh)
  2. With the Pincode latlong dataset we can write a spatial KDtree algorithm to check for the nearest latlong in the database to the given latlong and map it to the corresponding pincode. This method is not as accurate as we could have several cases where a latlong is closer to another pincode than itā€™s actualĀ pincode

A simple function combining both algorithms intoĀ one

import geopandas as gpd
from shapely.geometry import Point, Polygon
import pandas as pd
import numpy as np
from scipy import spatial
import pandas as pd
df = pd.read_csv('allindpincode.csv')
latlongs = df.iloc[:,2:]
d =  np.array(latlongs)
tree = spatial.KDTree(d)
data = gpd.read_file('allcitiescombined.geojson')
def getpincode(lat , long):
    lat = float(lat)
    long = float(long)
    p = Point(long,lat)
    for i in range(0,len(data)):
        if p.within(data['geometry'][i]) is True:
            pin = int(data['pin_code'][i])
            pin = 0
    if pin !=0:
        return pin
        latlongs = np.array([lat,long])
        result = tree.query(latlongs)
        pin = int(data1.iloc[[result[1]]]['postalcode'])       
    return pin

I have written a simple python package encompassing these ideas to extrapolate pincode from a given latlong (FOR INDIANĀ CITIES)

pip install git+https://github.com/Sangarshanan/Pincode-Mapping.git


from geopincoder import pincode_mapper as pm
pincode = pm.geocode.to_pincode(28.7041, 77.1025)
šŸŒŽ Building a Routing Engine with Postgis and Pgrouting

Building a routing engine is pretty easy especially with OSM being an immensely useful openly available source of road networkĀ data.

Extract theĀ .OSM file of any region you want to build a routing Engine for using osm extractor and selecting the boundingĀ box


Now go to Postgres and create a new database or useĀ existing

Create the extensions for postgis and pgrouting (install pgrouting first)


We are using osm2pgrouting to easily import OpenStreetMap data into a pgRouting database (https://github.com/pgRouting/osm2pgrouting)

Follow the instructions in the github page to compile the tool(osm2pgrouting) by making use of boost, libpqxx, expat andĀ cmake

osm2pgrouting ā€” f Bangalore.osm ā€” conf osm2pgrouting/mapconfig_for_cars.xml ā€” dbname pgroute ā€” username postgres ā€” clean

The above given command takes the OSM file that I had extracted for Bangalore and configures the road network that allow cars. Now the road network is saved as Nodes and Edges in the pgroute databaseĀ .

We can also perform incremental addition of data without usingā€Šā€”ā€Šclean

Now using the database with the road network data, it becomes quite easy to extrapolate the road path between twoĀ latlongs

  1. Connect to the database with the tables generated using OSM2pgrouting
  2. Take the two latlongs (Source and Destination) and find the nearest node to both thoseĀ latlongs
  3. Now that we know the nodes, we have to find the path between these nodes. (Imagine the road network as a simple graph and our problem is to find the shortest path between two nodes in theĀ graph)
  4. We can make use of any path finding algorithm (Dijkstra algorithm) to calculate the edges the path contains between the givenĀ nodes

The above attached gist gives us the function that can extrapolate the distance between two latlongs and gives you the answer in kilometers (SorryĀ America)

You could also check out the flask app that me and my buds built to do Pincode mapping andĀ Routing


Although itā€™s much easier to use Google maps or other APIā€™s to solve this problem building your very own routing engine is kindaĀ fun

I recently also had the opportunity to use Graphhopper (https://github.com/graphhopper/graphhopper), which is a cooler open source alternative API to googleĀ maps

Setting up a routing engine with Graphhopper is super easy. Just clone the repo, copy the osm file there and run the shellĀ script

git clone git://github.com/graphhopper/graphhopper.git
cd graphhopper; git checkout master
./graphhopper.sh -a web -i bangalore.som

After running these you will see ā€˜Started server at HTTP 8989ā€™, now go to http://localhost:8989/ and you should see a map of Bangalore. You should be able to click on the two points in the map and a routeĀ appears.

The below attached python script can help you save the road network as a gpx plot and plot the same in a Jupyter notebook using ipyleaflet

Hope thisĀ helps

Check out some of the useful links that I copy pastedĀ from

Peace āœŒļø


The Noonification banner

Subscribe to get your daily round-up of top tech stories!