LBS

with

Location Based Services

LBS

MAP

GIS

Geographic  Information  System

Data with geographic

coordinates on the

surface of earth

Data and procedure to provide information for decision making

Information System with Geographical reference data

What is Geo/Spatial Data?

Information about a physical object that can be represented by numerical values in a geographic coordinate system

Bengaluru

12.9716° N, 77.5946° E

[Road, Lake, City, Building, Mountain]

MAP

What we see

What it is

Vector

Raster

Raster Data

Raster data is made up of pixels (or cells), and each pixel has an associated value

  • Printing
  • Visualisation

Vector Data

Vector data consists of individual points, which are stored as pairs of (x, y) co-ordinates

  • Spatial Operations

Vector Data

POINT

Forum mall, India Gate, provision store  etc

Vector Data

Line

MG Road, Railway track,rivers & waterways etc

Vector Data

POLYGON

Koramangala, Cubbon park, Hussain Sagar Lake etc

can we

use

traditional  DBMS?

Yes, But there are some challenges

  • Storing, organizing and management of spatial information.
     
  • Lack of Spatial reference and projection support.
     
  • Querying relationship between stores spatial objects.
     
  • Performance: Special index for spatial information.

Let's store some spatial data

+------+-----------+-----------+
| PtId | lat       | long      |
|------+-----------+-----------+
| 1    | 12.943360 | 77.575836 |
+------+-----------+-----------+
+------+------------+------------+------------+------------+
| LiId | lat1       | long1      | lat2       | long2      |
|------+------------+------------+------------+------------+
| 1    | 12.9436851 | 77.5739291 | 12.9436799 | 77.5769091 |
+------+------------+------------+------------+------------+
+----+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
|PoId| lat1    | long1   | lat2    | long2   | lat3    | long3   | lat4    | long4   | lat4    | long4   |
|----+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
|1   |12.943578|77.574098|12.943473|77.576606|12.940647|77.576815|12.940618|77.573956|12.943578|77.574098|
+----+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+

How about whole bengaluru data?

Let's mark a point

Expected

Mapped

EARTH

REAL EARTH

GEOID

FLAT EARTH

Spatial Reference System

  • Global system used to locate geographical entities
     
  • Provides a framework to define positions on the Earth‘s surface
     
  • Referred by using a SRID integer(used to unambiguously identify projection)

Let's query some data

Point Inside Polygon?

Lines Intersecting?

Areas next to each other?

Why PostgreSQL?

cost

feature

Geometry Datatypes to store

spatial data

+------+-----------------------------+
| PtId | geom                        |
|------+-----------------------------+
| 1    | POINT(77.575836 12.943360)  |
+------+-----------------------------+
+------+---------------------------------------------------------+
| LiId | geom                                                    |
|------+---------------------------------------------------------+
| 1    | LINESTRING(77.5739291 12.9436851, 77.5769091 12.9436799 |
+------+---------------------------------------------------------+
+----+---------------------------------------------------------------------------------------------------------------+
|PoId| geom                                                                                                          |
|----+---------------------------------------------------------------------------------------------------------------+
|1   | POLYGON(77.574098 12.943578,77.576606 12.943473, 77.576815 12.940647,77.573956 12.940618,77.574098 12.943578) |
+----+---------------------------------------------------------------------------------------------------------------+

Lot of spatial functions

  • ST_Within
  • ST_Contains
  • ST_Intersects
  • ST_Distance....

Spatial Indexing

 Generic Index STructure aka GIST

How ?

PostgreSQL

GIS

PostGIS

Enable PostGIS extension

CREATE EXTENSION postgis;
+----------+-------------------+--------+----------+
| Schema   | Name              | Type   | Owner    |
|----------+-------------------+--------+----------|
| public   | geography_columns | view   | postgres |
| public   | geometry_columns  | view   | postgres |
| public   | raster_columns    | view   | postgres |
| public   | raster_overviews  | view   | postgres |
| public   | spatial_ref_sys   | table  | postgres |
+----------+-------------------+--------+----------+

Creating Spatial Tables

Geometry as Datatype

CREATE TABLE spatial_table (id INTEGER, name VARCHAR, geom Geometry);
CREATE TABLE spatial_table (id INTEGER, name VARCHAR, geom Geometry(Geometry, 4326));
CREATE TABLE spatial_table (id INTEGER, name VARCHAR, geom Geometry(POINT, 4326));

Creating Spatial Tables

With AddGeometryColumn function

CREATE TABLE city_bus_stops (id INTEGER, name VARCHAR, area VARCHAR);
SELECT AddGeometryColumn (‘city_bus_stops’, ‘geom’, 4326, ‘point’, 2);

Creating Spatial Tables

Geometry as DDL

CREATE TABLE city_bus_stops (id INTEGER, name VARCHAR, area VARCHAR);
ALTER TABLE city_bus_stops ADD COLUMN geom Geometry(Point, 4326);

Inserting spatial data

INSERT INTO city_bus_stops VALUES (1, ‘majestic’, ‘chikpete’, 
ST_GeomFromText(‘POINT(77.572344 12.977720)’, 4326));
INSERT INTO city_bus_routes (road_id, route, geom ) VALUES 
(3, ‘201A’, ST_GeomFromText('LINESTRING(77.573552 12.917705, 
77.577482 12.917532,  77.581901 12.917109, 77.585854 12.917109,
77.599612 12.916774 )',4326));
INSERT INTO city_bus_terminal (id, terminal, geom ) VALUES 
(1, ‘BSK’, ST_GeomFromText('POLYGON(77.573325 12.917824, 77.573325 12.917235,
77.572644 12.917206, 77.572648 12.917895, 77.573325 12.917824 )',4326));

Retrieving spatial data

SELECT geom FROM city_bus_stops LIMIT 1;
+---------------------------------------------------+
| geom                                              |
|---------------------------------------------------|
| 0101000020E6100000F583BA48A16453404D4A41B797F42940|
+---------------------------------------------------+
SELECT ST_AsText(geom) FROM city_bus_stops LIMIT 1;
+-------------------------------------------------------+
| st_astext                                             |
|-------------------------------------------------------|
| POINT(77.572344 12.97772)                             |
+-------------------------------------------------------+

Creating GIST Index

CREATE INDEX  geom_gist_idx ON city_bus_stops USING GIST(geom);

Nearest neighbours

SELECT name, ST_Distance(geom, S
T_GeomFromText('POINT(77.699095 12.950225)', 4326)) 
AS dist FROM poi ORDER BY dist LIMIT 1; 

-- 2189.862 ms
SELECT id, name FROM poi ORDER BY 
geom <-> ST_GeomFromText
('POINT(77.699095 12.950225)', 4326) LIMIT 1;

-- 4.445 ms

Neighbours within 1KM

SELECT id, name, geom FROM banks WHERE ST_Dwithin(geom,
ST_GeomFromText('POINT(77.699095 12.950225)',4326), 1000);
--898.85 ms

Distance between neighbours

SELECT st_distance(st_geomfromtext('point(77.699019 12.950331)' , 4326),
st_geomfromtext('point(77.701124 12.956919)', 4326));
-- 0.385 ms (degree)
+--------------------+
| st_distance        |
|--------------------|
| 0.47093311627      |
+--------------------+
SELECT st_distance(
st_geomfromtext('point(77.699019 12.950331)' , 4326)::geography,
st_geomfromtext('point(77.701124 12.956919)', 4326)::geography); 
-- 0.385 ms (meters)
+--------------------+
| st_distance        |
|--------------------|
| 763.78171535       |
+--------------------+

Length of a Road

SELECT ST_Length(ST_GeomFromText('linestring(
77.699318 12.949941, 77.699085 12.950016, 
77.699185 12.950351, 77.699392 12.950347)', 4326)::geography);

-- 0.553 ms
+-----------------------+
| st_length             |
|-----------------------|
| 87.6907059567         |
+-----------------------+

Distance travelled

SELECT ST_Length(ST_MakeLine(array[
ST_MakePoint(77.701013,12.956829),
ST_MakePoint(77.700884,12.955345),
ST_MakePoint(77.700541,12.953703),
ST_MakePoint(77.700197,12.952542),
ST_MakePoint(77.700927,12.952469),
ST_MakePoint(77.700809,12.951946),
ST_MakePoint(77.700069,12.952061),
ST_MakePoint(77.699725,12.950096),
ST_MakePoint(77.699073,12.950015)
])::geography);

-- 2.197 ms
+-----------------------+
| st_length             |
|-----------------------|
| 996.009065322         |
+-----------------------+

Area coverage

SELECT st_area(geog)/POWER(0.3048,2)
AS sqft FROM (SELECT(
ST_GeomFromText('Polygon((
77.699053 12.950017,
77.698877 12.950060,
77.698981 12.950400, 
77.699134 12.950355, 
77.699053 12.950017))',
4326)::geography)) AS foo(geog);

-- 2.361 ms
+-----------------------+
| sqft                  |
|-----------------------|
| 7731.38488018         |
+-----------------------+

Longest Road in Bengaluru

SELECT name, SUM(ST_Length(geom::geography)) AS len FROM roads 
WHERE name IS NOT NULL GROUP BY name ORDER BY len DESC LIMIT 1;
-- 3863.677 ms

Many more...

PgRouting

Enable PgRouting extension

CREATE EXTENSION pgrouting;
-- roads
+--------------+-------------------------+-------------+
| Column       | Type                    | Modifiers   |
|--------------+-------------------------+-------------|
| id           | bigint                  |             |
| source       | bigint                  |             |
| target       | bigint                  |             |
| one_way      | bigint                  |             |
| cost         | bigint                  |             |
| reverse_cost | bigint                  |             |
| the_geom     | geometry(Geometry,4326) |             |
+--------------+-------------------------+-------------+

Shortest path from jayanagar to marathalli

SELECT * FROM pgr_dijkstra('SELECT gid, source, target, cost, 
reverse_cost FROM roads',645, 803, directed:=true);

-- 3355.638 ms

Shortest path from jayanagar to marathalli via majestic

SELECT seq, node, edge, pgr.cost, agg_cost, st_astext(the_geom) AS geom 
FROM pgr_dijkstravia('SELECT gid id, source, target, cost, reverse_cost FROM roads', 
array[645, 885, 803], directed:=true) AS pgr JOIN roads r ON pgr.edge = r.gid;

-- 3668.633 ms

Shortest path from  marathalli to jayanagr,malleswarm, whitefield 

SELECT seq, node, edge, pgr.cost, agg_cost, st_astext(the_geom) AS geom 
FROM pgr_dijkstra('SELECT gid id, source, target, cost, reverse_cost FROM roads',645, 
array[804, 803], directed:=true) AS pgr JOIN roads r ON pgr.edge = r.gid;

-- 3757.580 ms

Driving distance from rajajinagr to marathalli

SELECT * FROM pgr_drivingdistance('SELECT id, source, target, cost, reverse_cost FROM roads',
645, 803);

Places I can drive from my place within 5 minutes

SELECT 1 AS id, ST_SetSRID(pgr_pointsAsPolygon( $$SELECT dd.seq AS id, S
T_X(v.the_geom) AS x, ST_Y(v.the_geom) AS y FROM pgr_drivingDistance 
($sub$SELECT id AS id, source, target, cost_s AS cost, reverse_cost_s 
AS reverse_cost FROM ways$sub$,(SELECT n.id FROM ways_vertices_pgr 
AS n ORDER BY ST_SetSRID(ST_Point(77.699095 12.950225),4326) <-> 
n.the_geom LIMIT 1),2*60, true ) AS dd INNER JOIN ways_vertices_pgr 
AS v ON dd.node = v.id$$), 4326) AS geom;

Travelling Salesman Problem

SELECT * FROM tsp('SELECT distinct source AS source_id, x1::double precision AS x, 
y1::double_precision AS y FROM my_routes WHERE source IN (201, 202, 203, 204)',
'201, 202, 203, 204',645);

Agriculture

Defence

Urban development

Areal Survey

Self driving cars

Questions?