1

I created my PostGIS database by osm2pgsql tool and now I want create network suitable for routing. (I could not use directly osm2pgrouting because I need street names e.g. and so on).

For this network I this is the best solution use pgr_createTopology(). But now I could not use function for shortest path pgr_dijkstra() because table "planet_osm_lines" not contains column which will be represented cost of edge, which is required for this function.

Can you tell me ho to resolve this?

My table planet_osm_line's script:

CREATE TABLE public.planet_osm_line
(
    osm_id bigint,
    access text COLLATE "default".pg_catalog,
    "addr:housename" text COLLATE "default".pg_catalog,
    "addr:housenumber" text COLLATE "default".pg_catalog,
    "addr:interpolation" text COLLATE "default".pg_catalog,
    admin_level text COLLATE "default".pg_catalog,
    aerialway text COLLATE "default".pg_catalog,
    aeroway text COLLATE "default".pg_catalog,
    amenity text COLLATE "default".pg_catalog,
    area text COLLATE "default".pg_catalog,
    barrier text COLLATE "default".pg_catalog,
    bicycle text COLLATE "default".pg_catalog,
    brand text COLLATE "default".pg_catalog,
    bridge text COLLATE "default".pg_catalog,
    boundary text COLLATE "default".pg_catalog,
    building text COLLATE "default".pg_catalog,
    construction text COLLATE "default".pg_catalog,
    covered text COLLATE "default".pg_catalog,
    culvert text COLLATE "default".pg_catalog,
    cutting text COLLATE "default".pg_catalog,
    denomination text COLLATE "default".pg_catalog,
    disused text COLLATE "default".pg_catalog,
    embankment text COLLATE "default".pg_catalog,
    foot text COLLATE "default".pg_catalog,
    "generator:source" text COLLATE "default".pg_catalog,
    harbour text COLLATE "default".pg_catalog,
    highway text COLLATE "default".pg_catalog,
    historic text COLLATE "default".pg_catalog,
    horse text COLLATE "default".pg_catalog,
    intermittent text COLLATE "default".pg_catalog,
    junction text COLLATE "default".pg_catalog,
    landuse text COLLATE "default".pg_catalog,
    layer text COLLATE "default".pg_catalog,
    leisure text COLLATE "default".pg_catalog,
    lock text COLLATE "default".pg_catalog,
    man_made text COLLATE "default".pg_catalog,
    military text COLLATE "default".pg_catalog,
    motorcar text COLLATE "default".pg_catalog,
    name text COLLATE "default".pg_catalog,
    "natural" text COLLATE "default".pg_catalog,
    office text COLLATE "default".pg_catalog,
    oneway text COLLATE "default".pg_catalog,
    operator text COLLATE "default".pg_catalog,
    place text COLLATE "default".pg_catalog,
    population text COLLATE "default".pg_catalog,
    power text COLLATE "default".pg_catalog,
    power_source text COLLATE "default".pg_catalog,
    public_transport text COLLATE "default".pg_catalog,
    railway text COLLATE "default".pg_catalog,
    ref text COLLATE "default".pg_catalog,
    religion text COLLATE "default".pg_catalog,
    route text COLLATE "default".pg_catalog,
    service text COLLATE "default".pg_catalog,
    shop text COLLATE "default".pg_catalog,
    sport text COLLATE "default".pg_catalog,
    surface text COLLATE "default".pg_catalog,
    toll text COLLATE "default".pg_catalog,
    tourism text COLLATE "default".pg_catalog,
    "tower:type" text COLLATE "default".pg_catalog,
    tracktype text COLLATE "default".pg_catalog,
    tunnel text COLLATE "default".pg_catalog,
    water text COLLATE "default".pg_catalog,
    waterway text COLLATE "default".pg_catalog,
    wetland text COLLATE "default".pg_catalog,
    width text COLLATE "default".pg_catalog,
    wood text COLLATE "default".pg_catalog,
    z_order integer,
    way_area real,
    way "public.geometry"(230633732)
)
  • 1
    Try to create a new column and popolate it with ST_Length. – user30184 Nov 16 '16 at 21:38
1

You can specify any column or combination of attributes as costs. However, it's better to use osm2pgrouting to import OSM data, because it does a few pre-processing steps, that osm2psql doesn't do, and which are necessary to get a correct routing network.

If you need more attributes than the ones provided by osm2pgrouting, you can join your OSM tables, imported with osm2psql, with the network table ways using the attribute osm_id.

  • So I could import my osm file into postgis twice to same database but with different tools ? – Aligator Nov 18 '16 at 9:12
  • Yes, with the current release this is a way to get access to all OSM attributes. The next major release will allow the import of all attributes using osm2pgrouting only, but I can't tell when this will be available. – dkastl Nov 18 '16 at 15:59
  • okey thanks :) could you tell me if tha osm2pgsql is the best tool for getting "pure" osm data or could I use someting else? – Aligator Nov 18 '16 at 16:34
0

You can add a new column to your table that contains the cost for each row.

To add a new column you would do:

ALTER TABLE public.planet_osm_line
ADD COLUMN cost_dist double precision;

And then to populate the new column you would do:

UPDATE public.planet_osm_line
SET cost_dist = ST_Length(planet_osm_line.way);

Where ST_Length is the PostGIS function that takes your geometry column (way) as input and calculates the length of it. The output is in the same measurements as your projection.

0

I think osm2pgrouting is the better tool. When you use osm2pgsql to import the osm data to database, the relation tag information was ignored, pgr_createTopology() only use the geometry relation between nodes, but osm2pgrouting use the relation tag information.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.