11.11 The Biggest Deals of the Year. 40% OFF on chosen cloud servers with a free 100 GB information exchange! Snap here to take in more. 

Consistently, the turnover for Techmekrz's yearly Double Eleven (Singles' Day) internet shopping celebration hits a record high, which represents a tremendous test to IT and coordinations frameworks. 

Because of the record-breaking yearly shopping celebration, distribution centers in China are completely loaded with bundles each year. Given the cosmic number of exchanges required amid this celebration, how does the coordinations business stay aware of the requests to guarantee convenient conveyance of items? 

Utilizing Database Technologies to Optimize Logistics 

The short response to this inquiry is by utilizing database advancements, for example, Techmekrz  ApsaraDB for RDS PostgreSQL. Clearly, there is much more to that. That is the reason today, I need to impart to you a portion of my insight and involvement in applying database advances to coordinations. I will center around PostgreSQL, Greenplum, and PostGIS. 

The coordinations business is catalyzed by internet business. For the coordinations business, the planning calculation for conveying and gathering packages is the way to the productive bundle appropriation and accumulation and in addition savvy activity of coordinations organizations. A decent calculation can help productivity and lower costs, and activate social assets to empower across the nation cooperation, which is essentially the whole plan of action of Didi taxi. 

Accepting the coordinations business for instance, I will break down how PostgreSQL and Greenplum are connected in the coordinations business for geological area data handling, ideal way computation, and machine learning. 

Component Analysis 

At first look, you may imagine that each of the a coordinations venture do is just sending and conveying bundles. Be that as it may, truth be told, settling on the correct way is the way to empowering an ideal conveyance framework. 

Allows first take a gander at a straightforward strategic process for instance. The coordinations procedure includes various components in connection to the area. 



Representative: sends packages to coordinations locales rather than distribution centers. In this way, the courses from destinations to distribution centers ought to likewise be planned, which isn't talked about thus. 

The planning technique is like dissemination: 



Methods for transportation 



In the event that the time quality is presented, there is more space for creative energy, as full support can be conceived like Didi Taxi does. 

We will begin with some basic components that emphasis just on the area. 

Sending a Parcel 

Senders frequently need to make a meeting with the representative for bundle sending. Furthermore, a sender can specifically convey a package to a coordinations site, so couple of calculations are included. 

The k-NearestNeighbor (KNN) calculation can take care of the issue of combining bundle conveyance and accumulation. Joined with conveyance way planning, the ideal representative can be chosen. 

For instance, thinking about the present area of the sender and the following area of the dispatch, the expense will be the least if messenger B is the one to gather the package as per the KNN calculation. 


Planning of Parcel Transfers between Warehouses 


Accept that the dabs appeared in the figure above are the areas of stockrooms. On the off chance that there is a way between two distribution centers, the two stockrooms are associated by lines. One distribution center covers a geometric zone. 

In light of areas of the sender and recipient and also the territory served by the stockroom, the dabs and planes are consolidated to pinpoint the distribution centers for the sender and beneficiary. 

A bundle is viewed as a dab and a distribution center is viewed as a plane. At the point when the sender sends a package, the sending and accepting data filled by the sender is converted into two longitudes and scopes, in view of which, combined with the coordinations organization's stockroom outline, comparing spots and planes are resolved and coordinated. Thus, the begin and end stockrooms are resolved for the bundle. 


Dab and plane judgment 


With a source and focus on, the ideal way for each bundle can be made sense of by the ideal way calculations from pgRouting. 

A demo is given later to disclose how to utilize pgRouting to compute the ideal way. 


The cargo auto shipping bundles between distribution centers can take off once the auto is completely stacked, or in clusters (considering time constraint). Rather than conveying the entire cargo from the begin point to the end point, a cargo auto is in charge of the entire trek between two hubs. 

In the event that a cargo auto is in charge of way A-B, it ships allocates hub An and hub B. 


Booking of Parcel Transfers from the Destination Warehouse to a Logistics Site 

After bundles land at the objective stockroom, they will be arranged and sent to locales for conveyance. 

This is additionally a procedure of deciding dabs and planes. The plane alludes to the zone secured by a coordinations site, while a bundle is viewed as a dab. In view of speck and plane judgment, the ideal site is chosen. 

The guideline for bundle exchange between distribution centers additionally can be utilized to figure the ideal way to transport a package from a stockroom to a site. In the meantime, a cargo auto is in charge of delivery divides two destinations. 


Conveyance is the thing that ought to be done in the last mile of sending a package to a beneficiary. To enhance conveyance planning, packages ought to be amassed dependent on their areas. 

The rule for package exchange between stockrooms can be connected once more, which implies the specks and planes ought to be resolved once more. Be that as it may, the objectives ought to be precise to private regions or littler zones. 


Notwithstanding the goal (amassed) of a bundle, the size and weight and in addition the transportation ability (volume and weight) of a dispatch ought to be considered. 

As appeared in the figure underneath, all packages at one coordinations site should be conveyed to the accompanying spots (amassed). Furthermore, the aggregate volume of bundles for each dab is appeared in numbers. 

Dissimilar to the way arranging made reference to over, this arranging procedure centers around various spots. 


The reason for figuring the ideal way for numerous goals is to guarantee that adjoining goals are consistent and that a dispatch conveys bundles to neighboring goals after packages are arranged by destinations. 

For instance, the coordinations site is in the middle, while different specks speak to the goals. The number around every goal alludes to the bundle volume that one dispatch can convey each time. The spotted line alludes to the packages that one messenger gathers for one conveyance. 

This technique guarantees the congruity of packages in every conveyance. 


A demo is given later to clarify the ideal way getting ready for different goals. 

Area Coordinate Conversion 

This article does not examine the transformation from areas into directions, while numerous route organizations give this capacity. 

A coordinations organization can utilize the dispatches' hand-held GPS gadgets to get exact directions. For instance, a messenger examines the package code while gathering the bundle to report the area data. 

After some fundamental information are acquired, through content examination and machine taking in, the areas can likewise be changed over into directions. 

On the off chance that there are huge essential information, the Greenplum information distribution center dependent on PostgreSQL can be utilized for content investigation and machine learning (MADlib and R programming dialect upheld). 

Greenplum bolsters content examination, land area data handling, MADlib machine learning library, custom capacity of R programming dialect, Python capacity, and parallel conveyed processing. The most essential thing is Greenplum is an open source information stockroom, which is the best decision for clients who need to investigate writings and geological areas. 

Ideal Path Calculation 

The information exchange between distribution centers is utilized a model here. 

The PostGIS and pgRouting of PostgreSQL are utilized. 


Fundamental information prerequisite, which alludes to the information about line portions and kilometers of the enacted transportation way between two stockrooms. 

Street connect ID (gid): one of a kind street ID 

Street class (class_id) 

Street connect (length): street length, in kilometers 

Street (name): street name 

Street geometry (the_geom): street portion (section among various focuses or between two) 

In the event that the table name is ways, the data about line portions between distribution centers is put away in the table. 

Table "public.ways" 

Segment | Type | Modifiers 

- + - - + - 

gid | bigint | 

class_id | number | not invalid 

length | twofold accuracy | 

name | character(200) | 

osm_id | bigint | 

the_geom | geometry(LineString,4326) | 


"ways_gid_idx" UNIQUE, btree (gid) 

"geom_idx" significance (the_geom) 

Produce Topology 

A substantial topology ought to be produced before an ideal way is created. 

Before a topology is produced, two fields ought to be added to store the begin and end code of a line fragment.

- Add "source" and "target" section 

Modify TABLE ways ADD COLUMN "source" whole number; 

Modify TABLE ways ADD COLUMN "target" whole number; 

It ought to be noticed that calling pgr_createTopology to create a topology is the way toward producing the main code for a line section. 


'<table>', which alludes to the table for which a topology ought to be created. 

drift resilience, which alludes to the permitted deviation when the finish of a line section can't be totally fit. For the most part, the unit for buoy resistance is degree or kilometer. 

Technology vs Humanity

'<geometry column>', which is the name of a line section. 

'<gid>') - gid 

For instance, there are three line sections: A, B, and C. Among them, the two closures of B can't be fit with the finishes of An and C. The deviations are 1 m and 10 m separately, so the buoy resilience ought to be set. 


Producing a line fragment is really to set the source ID and target ID. After the setting, the outcome might be appeared as pursues. 



- Run topology work 

SELECT pgr_createTopology('ways', 0.00001, 'the_geom', 'gid'); 

2. Create an Optimal Path 

pgRouting underpins numerous ideal way calculations, as depicted in the official documentation http://docs.pgrouting.org/2.2/en/doc/index.html 


Taking Shortest Path A* and Shortest Path Dijkstra as precedents, we will talk about how to produce an ideal way. http://workshop.pgrouting.org/parts/shortest_path.html 

What is the returning expense if there is a two-way line section between two stockrooms? In the event that we should concentrate more on car influx for returning, notwithstanding cost brought about by way length, the expense acquired by car influx ought to likewise be considered. 


The estimation result utilizing the returning expense by kilometers demonstrate that returning is upheld in this line fragment. 

Adjust TABLE ways ADD COLUMN reverse_cost twofold accuracy; 

Refresh ways SET reverse_cost = length; 

2.1 Example of Shortest Path Dijkstra calculation 


pgr_costResult[] pgr_dijkstra( 

content sql, which is utilized to ascertain the information wellspring of an ideal way and spoke to in SQL. For instance, 

- SELECT id (gid), source (begin point ID of a line fragment), target (end point ID of a line portion), cost (the expense from the begin point to the end point) [,reverse_cost (cost from the end point to the begin point)] FROM edge_table 

number source, which is the begin purpose of an arranged way. 

number target, which is the end purpose of an arranged way. 

boolean coordinated, - if the diagram is coordinated 

boolean has_rcost - assuming genuine, the reverse_cost segment of the SQL created set of columns will be utilized for the expense of the traversal of the edge the other way. 


Lines that make up a way. 

an arrangement of pgr-costResult (seq (grouping, id1 (begin point ID), id2 (target ID; - 1 shows the end point), cost (the cost spent hanging in the balance fragment)) pushes, that make up a way. 


The ideal way from 30 to 60. 

SELECT seq, id1 AS hub, id2 AS edge, cost FROM pgr_dijkstra(' 

SELECT gid AS id, 



length::double accuracy AS expense 

FROM ways', 

30, 60, false, false); 

seq | hub | edge | cost 

- + - + - + - - 

0 | 30 | 53 | 0.0591267653820616 

1 | 44 | 52 | 0.0665408320949312 

2 | 14 | 15 | 0.0809556879332114 


6 | 10 | 6869 | 0.0164274192597773 

7 | 59 | 72 | 0.0109385169537801 

8 | 60 | - 1 | 0 

(9 columns) 

2.2 Example of Shortest Path A* calculation 

The calculation is like the Shortest Path Dijkstra calculation, however the directions of the begin and end purposes of a line portion ought to be utilized for SQL. Different parameters are the equivalent as those utilized for pgr_dijkstra. 

Adjust TABLE ways ADD COLUMN x1 twofold exactness; 

Adjust TABLE ways ADD COLUMN y1 twofold exactness; 

Adjust TABLE ways ADD COLUMN x2 twofold exactness; 

Adjust TABLE ways ADD COLUMN y2 twofold exactness; 

Refresh ways SET x1 = ST_x(ST_PointN(the_geom, 1)), which is the facilitate x of the begin purpose of a line section. 

Refresh ways SET y1 = ST_y(ST_PointN(the_geom, 1)), which is the facilitate y of the begin purpose of a line section. 

Refresh ways SET x2 = ST_x(ST_PointN(the_geom, ST_NumPoints(the_geom))), which is the facilitate x of the end purpose of a line section. 

Refresh ways SET y2 = ST_y(ST_PointN(the_geom, ST_NumPoints(the_geom))), which is the facilitate y of the end purpose of a line section. 


pgr_costResult[] pgr_astar( 

sql content, - SELECT id, source, target, cost, x1, y1, x2, y2 [,reverse_cost] FROM edge_table, which contains the directions of the begin and end focuses. This calculation works somewhat quicker than Shortest Path A*. 

source whole number, 

target whole number, 

coordinated boolean, 

has_rcost boolean 


The arrival result is the equivalent as that of pgr_dijkstra. 

an arrangement of pgr_costResult (seq, id1, id2, cost) pushes, that make up a way. 


SELECT seq, id1 AS hub, id2 AS edge, cost FROM pgr_astar(' 

SELECT gid AS id, 



length::double accuracy AS expense, 

x1, y1, x2, y2 

FROM ways', 

30, 60, false, false); 


seq | hub | edge | cost 

- + - + - + - - 

0 | 30 | 53 | 0.0591267653820616 

1 | 44 | 52 | 0.0665408320949312 

2 | 14 | 15 | 0.0809556879332114 


6 | 10 | 6869 | 0.0164274192597773 

7 | 59 | 72 | 0.0109385169537801 

8 | 60 | - 1 | 0 

(9 columns) 

3. Produce Multiple Optimal Paths for Multiple Targets 

We can pick halfway focuses for route, which is basic for arranging various targets. For instance, you need to remain a night in your relative house in Jiangshan on your way from Hangzhou to Wanzai. 

In this model, the calculation utilized is Multiple Shortest Paths with kDijkstra. This calculation is utilized correspondingly to kDijkstra aside from one parameter. The objectives are communicated by clusters. 

Produce the portion based expense 

pgr_costResult[] pgr_kdijkstraCost(text sql, whole number source, 

integer[] targets, boolean coordinated, boolean has_rcost); 


Beginning from 10 and touching base at 60, 70, and 80. 

SELECT seq, id1 AS source, id2 AS target, cost FROM pgr_kdijkstraCost(' 

SELECT gid AS id, 



length::double accuracy AS expense 

FROM ways', 

10, array[60,70,80], false, false); 

seq | source | target | cost 

- + - + - + - - 

0 | 10 | 60 | 13.4770181770774 

1 | 10 | 70 | 16.9231630493294 

2 | 10 | 80 | 17.7035050077573 

(3 columns) 

Produce a way 

pgr_costResult[] pgr_kdijkstraPath(text sql, number source, 

integer[] targets, boolean coordinated, boolean has_rcost); 


Beginning from 10 and touching base at 60, 70, and 80. 

SELECT seq, id1 AS way, id2 AS edge, cost FROM pgr_kdijkstraPath(' 

SELECT gid AS id, 



length::double accuracy AS expense 

FROM ways', 

10, array[60,70,80], false, false); 

seq | way | edge | cost 

- + - + - + - - 

0 | 60 | 3163 | 0.427103399132954 

1 | 60 | 2098 | 0.441091435851107 


40 | 60 | 56 | 0.0452819891352444 

41 | 70 | 3163 | 0.427103399132954 

42 | 70 | 2098 | 0.441091435851107 


147 | 80 | 226 | 0.0730263299529259 

148 | 80 | 227 | 0.0741906229622583 

(149 columns) 


Advances specified in this article incorporate 

Speck and plane judgment: After deciding the spot and plane, perform total tasks dependent on planes. 

Way arranging 

Bolstered ideal calculations: Many calculations are upheld. 

UDF: PostgreSQL bolsters an assortment of dialects for custom capacities, including Python, R, and C. 

Machine learning: PostgreSQL and Greenplum bolster MADlib. For R clients of Greenplum, Greenplum can be utilized to mine parallel information certainly and a lot of information. 

Area facilitate change 

Bunching calculation: If the data about a private region is put away in the database as a spot rather than a plane, the K-Means grouping calculation of PostgreSQL or Greenplum can be utilized to decide the speck and plane for conveyance. A similar reason can be accomplished by amassing the package with the private region. 


SELECT kmeans(ARRAY[x, y, z], 10) OVER (), * FROM tests; 

SELECT kmeans(ARRAY[x, y], 2, ARRAY[0.5, 0.5, 1.0, 1.0]) OVER (), * FROM tests; 

SELECT kmeans(ARRAY[x, y, z], 2, ARRAY[ARRAY[0.5, 0.5], ARRAY[1.0, 1.0]]) OVER (PARTITION BY group_key), * FROM tests; 

The principal parameter is an exhibit that ought to be broke down through grouping examination. The second parameter is in the end isolated into classifications (when the outcome is yield, the class begins from 0. In the event that there are two classes, 0 and 1 are yield). 

The third parameter is a seed parameter, which can be an a couple of dimensional cluster. In the event that it is a one-dimensional exhibit, the third parameter ought to be the component number of the principal parameter times the estimation of the second component. (It tends to be viewed as that every classification is doled out with one seed.) 

The seed cluster that we ought to relegate is the dab exhibit comprising of private regions or places of business secured by a coordinations site. 

Regarding handling land area data, PostgreSQL has been a pioneer serving an enormous client base. PostGIS and pgRouting are two modules for handling topographical area data. 

PostGIS and pgrouting can be gotten from Techmekrz ApsaraDB for RDS PostgreSQL. 

Have a great time and you are welcome whenever to talk about your business necessities with Techmekrz. We anticipate seeing you once more.