Building a Pincode Mapper and Routing engine with Postgis and pgrouting

🌎 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)

Algorithm

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])
        else:
            pin = 0
    if pin !=0:
        return pin
    else:
        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

Sangarshanan/pincode-mapping

from geopincoder import pincode_mapper as pm

pincode = pm.geocode.to_pincode(28.7041, 77.1025)

print(pincode)

🌎 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

https://www.openstreetmap.org/export

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

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

CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;

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

<a href="https://medium.com/media/8cc1e9595bf668f19f3446d6c35d70b9/href">https://medium.com/media/8cc1e9595bf668f19f3446d6c35d70b9/href</a>

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

Sangarshanan/godseye

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

<a href="https://medium.com/media/c24c295a6696793a8594576334e0385c/href">https://medium.com/media/c24c295a6696793a8594576334e0385c/href</a>

Hope this helps

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

• https://medium.com/@tjukanov/animated-routes-with-qgis-9377c1f16021
• https://eng.uber.com/engineering-an-efficient-route/
• http://kazuar.github.io/visualize-trip-with-flask-and-mapbox/
• http://map.project-osrm.org/

Peace ✌️