1

I am trying to run a ST_MakeLine query that will return the shortest distance between given coordinates The code is as follows:

SELECT ST_MakeLine(route.geom)  as test
FROM (SELECT *
      FROM pgr_fromAtoB('roads2_noded',73.1789,33.5183,73.1768,33.5191)
      ORDER BY seq
     ) As route

The result looks like this:

enter image description here

it's clear that the result does not show null. But when I view it in the Geometry Viewer, it gives the following message:

enter image description here

Can someone tell me what this message means and how can I find a workaround to this problem? The geometry type of my roads shapefile is MULTILINESTRING.

St_AsText (route.geom) shows the following result:

SELECT ST_AsText( ST_MakeLine(route.geom)) FROM (SELECT *FROM pgr_fromAtoB('roads2_noded',73.1789,33.5183,73.1768,33.5191)
      ORDER BY seq
     ) As route

enter image description here

Using simple Select from pgr_fromAtoB gives:

SELECT * FROM pgr_fromAtoB('roads2_noded',73.1789,33.5183,73.1768,33.5191) ORDER BY seq

enter image description here

The roads2_noded is the roads shapefile I am working on shown below:

enter image description here

enter image description here

10
  • 1
    Can you please show the result of ST_AsText(route.geom)?
    – Taras
    Aug 20, 2020 at 12:57
  • ST_AsText shows nothing...No Column entry at all. I have posted a picture in the question above as well
    – Mohid
    Aug 21, 2020 at 8:13
  • okay. can you show the result of SELECT * FROM pgr_fromAtoB('roads2_noded',33.5183,73.1789,33.5191,73.1768) ORDER BY seq? What does it provide?
    – Taras
    Aug 21, 2020 at 8:20
  • I've added the results to the question
    – Mohid
    Aug 21, 2020 at 8:30
  • 1
    I just tried it and it worked. Thank You so much.
    – Mohid
    Aug 21, 2020 at 17:01

1 Answer 1

1

pg_Routing functions cannot be used for MultiLineStrings. Therefore, first create a new table with all the data entries of the roads2 table. Then add the columns of roads2 to the new table. For example id, sub_id, name, distance ,geom etc. Then use the following query to convert the MultiLineString geometry of roads2 to LineString.

ALTER TABLE my_table
ALTER COLUMN geom TYPE geometry(linestring,4326) USING ST_GeometryN(geom, 1);

After that add the geom column of roads2 to the new table. After that apply the road network and topology queries i.e. pgr_nodeNetwork and pgr_createTopology and create the function pgr_fromAtoB Then run the ST_MakeLine query:

SELECT ST_MakeLine(route.geom)  as test
FROM (SELECT *
      FROM pgr_fromAtoB('my_table_noded',73.1789,33.5183,73.1768,33.5191)
      ORDER BY seq
     ) As route

The result will show the geometry:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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