CODEWITHSUNDEEP
postgresqlroutesopenstreetmappostgisgeoserver
Denis Stephanov
Denis Stephanov

Reputation: 5241

Routing network from OSM

I'm looking for some good tool to import map.osm to postgres and next create some routes which will be displayed by geoserver. I need route, with some text information about vertexes (e.g. city, address, address number, and so on...)

I found this:

osm2pgrouting - Import OSM data into pgRouting Database
osm2postgis -Import OSM data to PostGIS
osm2po - tool to convert OSM data into a routable format
osm4routing - OpenStreetMap data parser to turn them into a nodes-edges adapted for routing applications

I do not have many experiences with GIS, so how tool is the best for me? I try osm2pgrouting, but in result I have tables, which do not contains data about vertexes(only lat. and alt.) Thanks for answers. UPDATE App Info: I will be have web and android client where user enter text value of start and end node, and next over geoserver get wms with vertexes of entered route for example
My result from could be be some edges and nodes like this like this: sequence_num, edge_distance, and informations about edge vertexes like osm_id, some text value, lat alt, etc...

Upvotes: 2

Views: 2577

Answers (2)

mlinth
mlinth

Reputation: 3118

I think you have a lot of work to do before you get to a complete solution, but here are some pointers. I suggest you break down your project into smaller chunks and ask specific questions on any bits you might get stuck on.

First, you need to import your data. Then you'll need some pre-processing / cleaning. Then you need your routing queries and, finally, a way to use the outputs (with this last part determining to some extent the previous steps).

Import OSM data

As I described in an answer to your previous question here, you can use OGR2OGR to import OSM data to Postgis. You can use other programs, as you mention above, but I guess you'll get much the same results. I think the difference between the OGR2OGR tables and the osm2postgis ones is that some of the columns in the latter appear in the other_tags column. However, the data is still there, you just need slightly different queries.

Preparing data

I'm assuming you'll use pgrouting for the routing, but whatever you use, you'll need a network suitable for routing (in short, the edges have a start and end node, and the end nodes must connect with other start nodes). Pgrouting has tools to create what you need and validate it. E.g. you create integer columns source and target and the function pgr_createtopology will populate the columns for you.

OGR2OGR gives you tables "lines", "points", "multipolygons", "multilinestrings". I suggest you read up on OSM to understand exactly what is in these tables, but, roughly speaking, the lines contain your roads and the multipolygons contain, amongst other things, buildings with e.g. addresses. The addresses are in a hstore column called "other_tags".

The lines do not contain addresses! (although they do contain street names). So, if you want to do address-to-address routing you need to do some preparation. You can skip this if you can live with the street names.

  • Create your network (e.g. if you're routing for cars, you'll want to throw out pedestrian routes and so on)

  • Extract the desired addresses (including coordinates)

  • Either snap the addresses to the nearest node, or otherwise relate the address to the nearest node

Pgrouting will return the edges in your route, so you need the above to relate back to your addresses.

Routing

Your app is going to send to your server (in an as-yet unspecified way) a pair of addresses or coordinates and you need postgis to return the route. With pgrouting, that's quite easy and there are plenty of examples out there, for example here. You will need to write queries that join the output to your address table to give you the desired output.

pgrouting creates a vertices table. You can get the nearest vertex with the following query:

select id from vertices_pgr
order by the_geom <-> st_setsrid(st_point(lon,lat),4326)
limit 1

Using the output

Using WMS from geoserver is unlikely to be a good choice - you won't have the information on individual edges without a lot of messing about. You might consider geoJSON, which can be read by e.g. OpenLayers, Leaflet, or you can manipulate in Javascript. Postgres has lots of useful functions for working with json and geojson.

Conclusion

That's quite a lot of work and probably new stuff if you have little GIS knowledge, and it, er, basically recreates what you'd get from Graphhopper! Are you sure that's not a better way to go?

If you do decide to go this (or similar) route break things down into manageable chunks! First, figure out exactly what you're trying to achieve, then work backwards from there. If you do decide to use OSM / pgrouting, then play with the data and pgrouting first so you understand how it works before trying address matching etc.

Upvotes: 1

Tom-db
Tom-db

Reputation: 6868

The tools you listed are only for producing data, but I think you actually need a routing engine. Try Graphhopper: https://graphhopper.com

Using the WEB Api (more likely what you need), you don't need the import the data in your database. This is the easiest solution. You will have not control over the input openstreetmap data but this is fine if you don't have special requirements.

Import data and implement/integrate a routing engine directly in your application would be much more complicated.

Upvotes: 0

Related Questions