Re: [OSRM-talk] Feed OSRM with Postgis

[Daniel Patterson]

Hi François,

  Ah.  Well, the step of deduplicating and finding connected nodes will not
go away - OSRM requires a *graph*, not disconnected geometry, so you won't
be able to get away from the problem of turning your LineStrings into
connected data.

  OSM's data structure (ways & nodes) has this structure built in, so OSRM
doesn't contain any of this kind of logic already.

  You might want to look at how pgRouting does this bit (they call it
"building the topology") -
http://docs.pgrouting.org/latest/en/pgRouting-concepts.html#build-a-routing-topology,
there might be some performance tips you can get from their approach.

daniel

On Fri, Mar 2, 2018 at 10:13 AM, François Lacombe  wrote:

> Hi Daniel,
>
> 2018-03-02 18:31 GMT+01:00 Daniel Patterson :
>
>> Well, it *could* be done.  It would all boil down to providing an
>> alternative for `ParseOSMData` here:
>> That function is responsible for parsing the OSM file, and
>> copying/converting the OSM fields into a memory structure called
>> `ExtractionContainers`:
>>
>
> Thank you, this is really useful information
>
>
>>
>> 0) You'll have to implement it - the core team have other priorities.
>>
>
> That's fair
> I could eventually propose a complete alternative to osrm-extract as to
> clearly separate responsibilities.
>
>
>> 1) I suspect you wouldn't see a huge performance improvement - I
>> suspect the overhead of querying postgis would dominate the extractor time.
>>
>
> I have about 5M ways and 40M nodes.
> Producing xml or pbf takes hours, while querying postgis takes about 3min.
> I would probably agree that the process of postgis output isn't well
> optimized
>
>
>> 2) You'll be on the hook for maintaining this code - the core team
>> haven't built this into the core tool because we don't need it, and it's a
>> big ask for us to maintain something we don't use.
>>
>>   I'd strongly consider trying to optimize your Postgres->OSM extraction
>> process.  Consider using `osmium` libraries to write out the data in PBF
>> form directly instead of XML - it's significantly smaller, which makes it
>> faster to move around and write to disk, and OSRM will import it more
>> quickly.
>>
>
> Problem isn't to get data from postgis, but to organize them as to fit in
> the xml :
> - Creating nodes records, out of Linestrings / polygon geometries
> - Search and deduplicate them, especially when 2 or more ways have nodes
> located on the same lat/lon point. Currently done with a GROUP BY on nodes
> geometry.
> - Create numeric and auto incremented ids since we use uuid in postgis
> (the easy part)
> - Iterate over all of this to produce a xml with Python. Didn't try c++
> libosmium for now but I know i should. That's the longer part in the
> current process.
>
> This takes hours, and I'll be really happy if I find a way to directly
> feed osrm graph without recreating such things.
>
> Simple suppositions:
> It would be so nice to not have to produce nodes out of geometries. It's
> the key point.
> I guess you don't have proper records for each nodes in .osrm files don't
> you ?
> Once they gone through profile's node_process, we only need their
> coordinates and not their tags any more.
> Then it would be great to only send tagged nodes (coming from dedicated
> postgis tables) to osrm-extract.
>
>
> Enjoy your weekend,
>
> François
>
> ___
> OSRM-talk mailing list
> OSRM-talk@openstreetmap.org
> https://lists.openstreetmap.org/listinfo/osrm-talk
>
>
___
OSRM-talk mailing list
OSRM-talk@openstreetmap.org
https://lists.openstreetmap.org/listinfo/osrm-talk

Re: [OSRM-talk] Feed OSRM with Postgis

[François Lacombe]

Hi Daniel,

2018-03-02 18:31 GMT+01:00 Daniel Patterson :

> Well, it *could* be done.  It would all boil down to providing an
> alternative for `ParseOSMData` here:
> That function is responsible for parsing the OSM file, and
> copying/converting the OSM fields into a memory structure called
> `ExtractionContainers`:
>

Thank you, this is really useful information


>
> 0) You'll have to implement it - the core team have other priorities.
>

That's fair
I could eventually propose a complete alternative to osrm-extract as to
clearly separate responsibilities.


> 1) I suspect you wouldn't see a huge performance improvement - I
> suspect the overhead of querying postgis would dominate the extractor time.
>

I have about 5M ways and 40M nodes.
Producing xml or pbf takes hours, while querying postgis takes about 3min.
I would probably agree that the process of postgis output isn't well
optimized


> 2) You'll be on the hook for maintaining this code - the core team
> haven't built this into the core tool because we don't need it, and it's a
> big ask for us to maintain something we don't use.
>
>   I'd strongly consider trying to optimize your Postgres->OSM extraction
> process.  Consider using `osmium` libraries to write out the data in PBF
> form directly instead of XML - it's significantly smaller, which makes it
> faster to move around and write to disk, and OSRM will import it more
> quickly.
>

Problem isn't to get data from postgis, but to organize them as to fit in
the xml :
- Creating nodes records, out of Linestrings / polygon geometries
- Search and deduplicate them, especially when 2 or more ways have nodes
located on the same lat/lon point. Currently done with a GROUP BY on nodes
geometry.
- Create numeric and auto incremented ids since we use uuid in postgis (the
easy part)
- Iterate over all of this to produce a xml with Python. Didn't try c++
libosmium for now but I know i should. That's the longer part in the
current process.

This takes hours, and I'll be really happy if I find a way to directly feed
osrm graph without recreating such things.

Simple suppositions:
It would be so nice to not have to produce nodes out of geometries. It's
the key point.
I guess you don't have proper records for each nodes in .osrm files don't
you ?
Once they gone through profile's node_process, we only need their
coordinates and not their tags any more.
Then it would be great to only send tagged nodes (coming from dedicated
postgis tables) to osrm-extract.


Enjoy your weekend,

François
___
OSRM-talk mailing list
OSRM-talk@openstreetmap.org
https://lists.openstreetmap.org/listinfo/osrm-talk

Re: [OSRM-talk] Feed OSRM with Postgis

[Daniel Patterson]

Hi François,

Well, it *could* be done.  It would all boil down to providing an
alternative for `ParseOSMData` here:


https://github.com/Project-OSRM/osrm-backend/blob/master/src/extractor/extractor.cpp#L211

That function is responsible for parsing the OSM file, and
copying/converting the OSM fields into a memory structure called
`ExtractionContainers`:

https://github.com/Project-OSRM/osrm-backend/blob/master/include/extractor/extraction_containers.hpp#L23

  If you could populate ExtractionContainers from somewhere else, you'd be
good to go.

  Happy to give advice on how to do this, but:

0) You'll have to implement it - the core team have other priorities.
1) I suspect you wouldn't see a huge performance improvement - I
suspect the overhead of querying postgis would dominate the extractor time.
2) You'll be on the hook for maintaining this code - the core team
haven't built this into the core tool because we don't need it, and it's a
big ask for us to maintain something we don't use.

  I'd strongly consider trying to optimize your Postgres->OSM extraction
process.  Consider using `osmium` libraries to write out the data in PBF
form directly instead of XML - it's significantly smaller, which makes it
faster to move around and write to disk, and OSRM will import it more
quickly.

daniel

On Fri, Mar 2, 2018 at 3:10 AM, François Lacombe 
wrote:

> Hi Daniel,
>
> Despite it's a pretty old thread regarding feeding OSRM with postgis
> geometries, I have new lights to bring up
>
> 2017-11-30 18:09 GMT+01:00 Daniel Hofmann :
>
>> OSRM is a routing engine for OpenStreetMap and the OpenStreetMap extracts
>> usually come in xml or pbf formats.
>>
>
> I find this a bit restrictive and I wonder why it's valuable to stuck osrm
> on osm data only.
> I work for a company which is interested to use osrm as a router for
> public works (not cycling nor car or whatever).
> We have lot of private data we process into a postgresql db in which osm
> data is also imported.
>
> OSM XML file production at the end of our process generate a lot of
> overhead as to feed osrm.
> This doesn't bring advantage at all.
>
>
>> We're using https://github.com/osmcode/libosmium in the osrm-extract
>> binary; you theoretically _could_ switch it out with calls to your database
>> but that will be a bigger lift I guess, and we don't want to add arbitrary
>> data source drivers to OSRM.
>>
>
> We think about creating another connector which would enable osrm-extract
> to get its data directly for pgsql.
> Currently we're not aware of how deep the xml/libosmium is linked to
> osrm-extract binary.
>
> Our use cases enlarge the potential and relevance of osrm in
> industrial/profesionnal use.
> We look to be as constructive as possible and it will be a pleasure to
> share since osrm is great tool.
>
>
> All the best
>
> François
>
>
> ___
> OSRM-talk mailing list
> OSRM-talk@openstreetmap.org
> https://lists.openstreetmap.org/listinfo/osrm-talk
>
>
___
OSRM-talk mailing list
OSRM-talk@openstreetmap.org
https://lists.openstreetmap.org/listinfo/osrm-talk

Re: [OSRM-talk] Feed OSRM with Postgis

[François Lacombe]

Hi Daniel,

Despite it's a pretty old thread regarding feeding OSRM with postgis
geometries, I have new lights to bring up

2017-11-30 18:09 GMT+01:00 Daniel Hofmann :

> OSRM is a routing engine for OpenStreetMap and the OpenStreetMap extracts
> usually come in xml or pbf formats.
>

I find this a bit restrictive and I wonder why it's valuable to stuck osrm
on osm data only.
I work for a company which is interested to use osrm as a router for public
works (not cycling nor car or whatever).
We have lot of private data we process into a postgresql db in which osm
data is also imported.

OSM XML file production at the end of our process generate a lot of
overhead as to feed osrm.
This doesn't bring advantage at all.


> We're using https://github.com/osmcode/libosmium in the osrm-extract
> binary; you theoretically _could_ switch it out with calls to your database
> but that will be a bigger lift I guess, and we don't want to add arbitrary
> data source drivers to OSRM.
>

We think about creating another connector which would enable osrm-extract
to get its data directly for pgsql.
Currently we're not aware of how deep the xml/libosmium is linked to
osrm-extract binary.

Our use cases enlarge the potential and relevance of osrm in
industrial/profesionnal use.
We look to be as constructive as possible and it will be a pleasure to
share since osrm is great tool.


All the best

François
___
OSRM-talk mailing list
OSRM-talk@openstreetmap.org
https://lists.openstreetmap.org/listinfo/osrm-talk

Re: [OSRM-talk] Feed OSRM with Postgis

[Daniel Hofmann]

OSRM is a routing engine for OpenStreetMap and the OpenStreetMap extracts
usually come in xml or pbf formats.

We're using https://github.com/osmcode/libosmium in the osrm-extract
binary; you theoretically _could_ switch it out with calls to your database
but that will be a bigger lift I guess, and we don't want to add arbitrary
data source drivers to OSRM.

Best,
Daniel J H


On Thu, Nov 30, 2017 at 5:59 PM, François Lacombe  wrote:

> Hi Daniel,
>
> Thank you for your answer.
>
> Wouldn't be a good idea to add ability for osrm-extract to directly get
> its data from a postgis DB ?
> OSM XML is really specific, and users may be interest to make different
> processing to raw osm extract prior to route on their data.
>
> For now I'll be writing a quick extractor from postgis to xml as
> recommended.
>
>
> All the best
>
> *François Lacombe*
>
> fl dot infosreseaux At gmail dot com
> www.infos-reseaux.com
> @InfosReseaux 
>
> 2017-11-29 16:35 GMT+01:00 Daniel Hofmann :
>
>> Yes, if all your data comes from a database the best way to get it into
>> OSRM is to map your data to the OpenStreetMap tagging schema.
>>
>> You can either create an .osm.xml or even an .osm.pbf if you need the
>> compression and compactness; you can use e.g. libosmium to write both
>> formats.
>>
>> Good luck,
>> Daniel J H
>>
>> On Wed, Nov 29, 2017 at 4:28 PM, François Lacombe <
>> fl.infosrese...@gmail.com> wrote:
>>
>>> Hi all,
>>>
>>> I was recently asked to feed OSRM with other data than OSM files which
>>> are stored in a PgSQL db.
>>> Is this even possible ?
>>>
>>> The data we want to process doesn't come from OSM db, should we produce
>>> an osm xml file with those data prior to use osrm-extract ?
>>>
>>> I know OSRM can query a postgis DB to adapt its results but this doesn't
>>> seem to prevent it to load a base graph out of OSM data.
>>>
>>>
>>> Many thanks for any answer
>>>
>>> François
>>>
>>> ___
>>> OSRM-talk mailing list
>>> OSRM-talk@openstreetmap.org
>>> https://lists.openstreetmap.org/listinfo/osrm-talk
>>>
>>>
>>
>> ___
>> OSRM-talk mailing list
>> OSRM-talk@openstreetmap.org
>> https://lists.openstreetmap.org/listinfo/osrm-talk
>>
>>
>
> ___
> OSRM-talk mailing list
> OSRM-talk@openstreetmap.org
> https://lists.openstreetmap.org/listinfo/osrm-talk
>
>
___
OSRM-talk mailing list
OSRM-talk@openstreetmap.org
https://lists.openstreetmap.org/listinfo/osrm-talk

Re: [OSRM-talk] Feed OSRM with Postgis

[Daniel Hofmann]

Yes, if all your data comes from a database the best way to get it into
OSRM is to map your data to the OpenStreetMap tagging schema.

You can either create an .osm.xml or even an .osm.pbf if you need the
compression and compactness; you can use e.g. libosmium to write both
formats.

Good luck,
Daniel J H

On Wed, Nov 29, 2017 at 4:28 PM, François Lacombe  wrote:

> Hi all,
>
> I was recently asked to feed OSRM with other data than OSM files which are
> stored in a PgSQL db.
> Is this even possible ?
>
> The data we want to process doesn't come from OSM db, should we produce an
> osm xml file with those data prior to use osrm-extract ?
>
> I know OSRM can query a postgis DB to adapt its results but this doesn't
> seem to prevent it to load a base graph out of OSM data.
>
>
> Many thanks for any answer
>
> François
>
> ___
> OSRM-talk mailing list
> OSRM-talk@openstreetmap.org
> https://lists.openstreetmap.org/listinfo/osrm-talk
>
>
___
OSRM-talk mailing list
OSRM-talk@openstreetmap.org
https://lists.openstreetmap.org/listinfo/osrm-talk

[OSRM-talk] Feed OSRM with Postgis

[François Lacombe]

Hi all,

I was recently asked to feed OSRM with other data than OSM files which are
stored in a PgSQL db.
Is this even possible ?

The data we want to process doesn't come from OSM db, should we produce an
osm xml file with those data prior to use osrm-extract ?

I know OSRM can query a postgis DB to adapt its results but this doesn't
seem to prevent it to load a base graph out of OSM data.


Many thanks for any answer

François
___
OSRM-talk mailing list
OSRM-talk@openstreetmap.org
https://lists.openstreetmap.org/listinfo/osrm-talk