problem in pgRouting with data from google earth

Question

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

Answer 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)
        )
    );