1

Hello i am new in forum an in Postgesql,postgis and pgRouting. I try to make a pgrouting for my city roads.

I use googlemaps to make a lines with coordinates of the roads and export the results in kml file. example <MultiGeometry><LineString><coordinates>22.950164627408352,40.592917358186945 22.950657309819846,40.592514769916995</coordinates></LineString></MultiGeometry>

but i have problem with the insert in postgresql The queries for table and make the geometry for roads.

 CREATE TABLE roads(
    "id" integer PRIMARY KEY,
        "Name" text NOT NULL,
    "Lenght (m)" numeric CHECK("Lenght (m)" > 0)
);

SELECT AddGeometryColumn('','roads','the_geom','4326','MULTILINESTRING',2);

After i use an insert like this but i have problem with MULTILINESTRING

    INSERT INTO roads VALUES(1,'Kwnn/nou Kanarh',1000,ST_SetSRID(ST_GeomFromKML('<MultiGeometry><LineString><coordinates> 
26.683023,37.7939654,0.0 26.6839457,37.7941011,0.0 26.6857266,37.7944063,0.0 26.687851,37.7948472,0.0 26.69034,37.7955084,0.0 26.6947603,37.7972888,0.0 26.6975284,37.7980687,0.0 26.6992879,37.7984926,0.0 26.701498,37.7993573,0.0 26.7026782,37.79989990000001,0.0 26.703794,37.8000355,0.0 26.7052317,37.8002729,0.0 26.7067122,37.8006375,0.0 26.7095018,37.8014089,0.0
</coordinates></LineString></MultiGeometry>'),4326));

Update After the inserts i create topology and other step from the http://workshop.pgrouting.org/chapters/topology.html for make a pgRouting for the insert data but when i run the pgr_dijkstra the query is

SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra('
            SELECT id AS id,
                     source::integer,
                     target::integer,
                     length::double precision AS cost
                     FROM roads',
            30, 60, false, false);

Give me this error ERROR: Target vertex was not found. I dont find the reason

  • It isn't entirely clear from this what your problem is. With the insert, the routing? – John Powell Sep 27 '14 at 18:16
  • it dont execute inserts and tells me that does not fit Multilinestring with linestring(from kml) – vagelis Sep 27 '14 at 22:59
1

For pgRouting it's not a good idea to have "real" multi-geometries. It's better to only use LineString.

Also your KML geometry contains a Z value, so you either use a 3D geometry or you use ST_Force_2D to ignore the Z values. Otherwise it will return an error like "Geometry has Z dimension but column does not".

I modified your SQL slightly, and this should be a working example:

CREATE TABLE roads(
    "id" serial,
    "name" text NOT NULL,
    "length" numeric CHECK("length" > 0),
    "the_geom" geometry("LineString",4326)
);

... to create the table with a 2D LineString geometry column. Then insert a record with:

INSERT INTO roads("name","length","the_geom") 
    VALUES(
        'Kwnn/nou Kanarh', 1000, 
        ST_Force_2D(
            ST_SetSRID(
                ST_GeomFromKML('<LineString><coordinates> 
                    26.683023,37.7939654,0.0 
                    26.6839457,37.7941011,0.0 
                    26.6857266,37.7944063,0.0 
                    26.687851,37.7948472,0.0 
                    26.69034,37.7955084,0.0 
                    26.6947603,37.7972888,0.0 
                    26.6975284,37.7980687,0.0 
                    26.6992879,37.7984926,0.0 
                    26.701498,37.7993573,0.0 
                    26.7026782,37.79989990000001,0.0 
                    26.703794,37.8000355,0.0 
                    26.7052317,37.8002729,0.0 
                    26.7067122,37.8006375,0.0 
                    26.7095018,37.8014089,0.0
                </coordinates></LineString>'
            ),4326)
        )
    );

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.