[pgrouting-dev] pgrouting in batch mode ?

[Stephen Woodbridge]

Hello all,

I looked into some potential ways to solve this problem in the past and 
will through out some ideas for your consideration in no particular order.

* in my address standardizer I created a postgresql session cache and 
stored lots of data there, that was then accessible by other queries 
using the same session, and was freed when the connection was closed. 
This same mechanism could be used to store a graph. Postgresql has 
various cache life cycles, like at the query level, or the connection 
level, and maybe at the database level.

* one could serialize the graph and store it in a blob, and then have 
the queries fetch the blob and unserialize it for the query. Not sure 
about the cost of unserialize would be. There are also costs associated 
toasting/untoasting blobs.

* sqlite/spatialite does not have the cost of toasting/untoasting blobs 
so it might be considerably faster than postgresql for saving large 
amounts of data in blobs

* it is also possible to build a a graph in a fixed segment of memory 
and use mem-mapped I/O to save it to a file and later open the file and 
map it back to memory into a fixed segment of memory to use at a later 
date. This is extremely fast, yes works on Windows also, but the down 
side is you have to manage the memory segment yourself instead of using 
nice C++ structures so this is probably a non-starter. There may be C++ 
libraries that facilitate this for C++ code, but I have not looked.

Vicky, if you want to follow up on any of these at a later time, let me 
know. I do not plan to attend the meeting, just throwing some ideas of 
the fence.

Best regards,
   -Steve

On 5/17/2020 11:48 AM, Vicky Vergara wrote:
> Hello all,
>
> MobilityDB [1] [2] will be using pgRouting to test using the benchmarks
> mentioned on attached mail
>
> [1] https://github.com/ULB-CoDE-WIT/MobilityDB
> [2] https://docs.mobilitydb.com/nightly/
>
> The Agenda for monday meeting May 18, 2pm UTC = 9am in Mexico = 7:30pm 
> in India will be:
> - Review of work done to have travis working
> - Understanding MobilityDB + pgRouting + problem description
> - Discussion of a possible solution to the problem
>
>
>
> On Sat, May 16, 2020 at 1:53 AM Esteban Zimanyi 
> <estebanzimanyi at gmail.com <mailto:estebanzimanyi at gmail.com>> wrote:
>
>     Dear Vicky
>
>     In mobility applications we need to generate data according to a
>     given scenario at various scale factors to make simulations. This
>     requires to send thousands of requests to pgrouting and this
>     number of requests increases with the scale factor used for the
>     generation.
>
>     As a concrete example, the BerlinMOD data generator
>     http://dna.fernuni-hagen.de/secondo/BerlinMOD/BerlinMOD.html
>     uses a workweek scenario in which persons go in the morning to
>     work, return back to home in the afternoon, and possibly do an
>     extra trip after working hours for leisure. As another example, we
>     described in
>     https://www.mdpi.com/2220-9964/8/4/170/htm
>     a scenario in which a home appliances shop needs to deliver the
>     appliances to the customers and thus vehicles are loaded at a
>     warehouse in the morning and spent the day delivering to the
>     clients given a specified schedule to finally return back to the
>     warehouse at the end of the day.
>
>     We have done a first profiling of these data generators and
>     realized that 87% of the time is spent in pgrouting building the
>     graph thousands of times, at each query sent to pgrouting. On the
>     other hand, the real work of MobilityDB only accounts for 0.11%
>
>     VirtualBox_mobilitydb-dev_13_05_2020_17_11_39 (1).png
>
>     For this reason we would need an API in which we build the graph
>     once at the beginning of the simulation, send to pgrouting
>     thousands of calls to pgr_dijkstra, pgr_aStar, pgr_dijkstraVia,
>     etc. and then delete the graph, free the memory and start the
>     simulation.
>
>     Any idea how to achieve this ?
>
>     Many thanks for your valuable help.
>
>     Esteban
>
>     ------------------------------------------------------------
>     Prof. Esteban Zimanyi
>     Department of Computer & Decision Engineering (CoDE) CP 165/15
>     Universite Libre de Bruxelles
>     Avenue F. D. Roosevelt 50
>     B-1050 Brussels, Belgium
>     fax: + 32.2.650.47.13
>     tel: + 32.2.650.31.85
>     e-mail: ezimanyi at ulb.ac.be <mailto:ezimanyi at ulb.ac.be>
>     Internet: http://cs.ulb.ac.be/members/esteban/
>     ------------------------------------------------------------
>
>
>
> -- 
> Georepublic UG (haftungsbeschränkt)
> Salzmannstraße 44,
> 81739 München, Germany
>
> Vicky Vergara
> Operations Research
>
> eMail: vicky at georepublic.de  <http://georepublic.de>
> Web:https://georepublic.info
>
> Tel: +49 (089) 4161 7698-1
> Fax: +49 (089) 4161 7698-9
>
> Commercial register: Amtsgericht München, HRB 181428
> CEO: Daniel Kastl
>
>
> _______________________________________________
> pgrouting-dev mailing list
> pgrouting-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/pgrouting-dev