Take from multilinestring the start and end points

Question

I have to use the A* algorithm and my geometry is in multilinestring and I want to take stratpoint(x1,y1) and endpoint(x2,y2) . I use this queries but the columns after the execute is still empty

UPDATE roads SET x1 = ST_x(ST_startpoint(geom));
UPDATE roads SET y1 = ST_y(ST_startpoint(geom));
UPDATE roads SET x2 = ST_x(ST_endpoint(geom));
UPDATE roads SET y2 = ST_y(ST_endpoint(geom));

after the execution give this message

Query returned successfully: 2584 rows affected, 101 ms execution time.

Then I make a select and the columns are empty

I searched the net and I didn't find anything, I don't know what I can do.

Answer 1

If your roads really consist of multi-geometries, then you will run into troubles with pgRouting. Then it's necessary to change your multi-linestrings into multiple regular linestrings. As already mentioned, you can use ST_Dump to achieve this.

But in reality I have rarely seen real multi-linestrings even if the geometry type says so. This is because shp2pgsql for example imports line data by default as "multilinestring" geometry type if you didn't specify explicitly to use LineString, even if there is no multi-geometry.

So I would first test, if you really have multi-linestrings with:

SELECT COUNT(
        CASE WHEN ST_NumGeometries(geom) > 1 THEN 1 END
    ) AS multi, COUNT(geom) AS total 
FROM roads;

In case multi is 0 you have no multi-linestrings at all and you can just update your geometry type to Linestring with your 1st geometry using ST_GeometryN:

ALTER TABLE roads
    ALTER COLUMN geom TYPE geometry(LineString, 4326) 
    USING ST_GeometryN(geom, 1);

Answer 2

ST_Startpoint can only be used for LINESTRING.

http://postgis.net/docs/manual-2.1/ST_StartPoint.html

Returns the first point of a LINESTRING geometry as a POINT or NULL if the input parameter is not a LINESTRING.

You have multilinestrings and therefore you are getting nulls. See also discussion about the matter from this closed PostGIS ticket.

Answer 3

If you have a multinestring you can use a trick, for the startpoint e.g.:

    SELECT gid, ST_astext(ST_PointN(the_geom,1)) AS start
FROM (SELECT gid, (ST_Dump(geom)).geom As the_geom
    FROM hausanschlusschacht_texturlinie) As foo

Answer 4

If you all you need is the endpoints of the lines of a multilinestring, you can use st_boundary. If the multilinestring is contiguous (all linestrings touch), it will return the endpoints of the merged lines (start and end points as a multipoint geometry). If there are non-touching linestrings in the multilinestring, it will return the start/endpoints for each linestring, all as one multipoint geometry. No information about whether each point is a startpoint or endpoint is included, so this is only useful if you don't need that information.

select st_boundary(geom), geom from joinodeletes;

For some reason, I am not able to add images, but here are some geometry samples:

Contiguous MultiLineString:

st_boundary

geom

MULTIPOINT ((487142.62846641004 4922792.527428288), (489147.7 4941934.5))

MULTILINESTRING ((487142.62846641 4922792.527428288, 487142.62846641004 4922792.527428288), (487142.62846641 4922792.527428288, 487143.5 4922796.5, 487147.2 4922830.5, 487146.4 4922868.2, 487149.2 4922971, 487152.30000000005 4922994.8, 487157.10000000003 4923012.3, 487153.9 4923033.4, 487175.7 4923084, 487183.10000000003 4923100.9, 487188.4 4923118.9, 487192.60000000003 4923140.600000001, 487194.7 4923172.3, 487195.30000000005 4923294.600000001, 487195.30000000005 4923315.7, 487196.9 4923330, 487202 4923350, 487219.7 4923432, 487221 4923438, 487228.9 4923646.9, 487230.2 4923682.3, 487231 4923701.5, 487232.2 4923735.5, 487245.5 4923795, 487286.30000000005 4923901, 487322.30000000005 4923962.5, 487406.5 4924066.5, 487580.80000000005 4924232.5, 487765.4 4924409.8, 487877.4 4924518.9, 487919 4924569, 487950.4 4924612.2, 487986.5 4924661.9, 488034.30000000005 4924736.3, 488063 4924781.100000001, 488086.7 4924818.100000001, 488159 4924938.5, 488248.10000000003 4925069.5, 488383.4 4925309, 488385.9 4925326.8, 488401.10000000003 4925435, 488401.2 4925617, 488402 4925707, 488403.60000000003 4925832.5, 488414.80000000005 4926648.5, 488416.60000000003 4926972.5, 488429.30000000005 4928383.2, 488432.7 4928751.100000001, 488435.60000000003 4929071.5, 488437.9 4929485.5, 488443.5 4929544.5, 488467.80000000005 4929617.5, 488503.30000000005 4929680, 488859.2 4930101.5, 488869.60000000003 4930114.4, 488894.5 4930145.5, 488920.80000000005 4930196.5, 488942.5 4930254, 489009.10000000003 4930488.100000001, 489046.30000000005 4930618.5, 489119.30000000005 4930860.5, 489142.80000000005 4931032, 489142.4 4931491.2, 489141.80000000005 4932146, 489141.7 4932268, 489140.60000000003 4933071, 489143.9 4933535.5, 489154.2 4933608, 489221.9 4933862, 489224.4 4933918, 489219.9 4933971.5, 489208.80000000005 4934017, 489166.80000000005 4934145.5, 489143.4 4934246.5, 489136.80000000005 4934776, 489137.7 4934973.5, 489141.2 4935839.600000001, 489143.10000000003 4936290.5, 489143.80000000005 4937095, 489145.30000000005 4938707.5, 489146.4 4940323.5, 489148.10000000003 4940731.8, 489149.7 4941124, 489150.2 4941267.5, 489150.2 4941303, 489150.5 4941379, 489151 4941533.5, 489151 4941552, 489151.10000000003 4941660, 489151.10000000003 4941728, 489148.7 4941901, 489147.7 4941934.5))

Non-Contiguous MultiLineString:

st_boundary

geom

MULTIPOINT ((234424.80000000002 4876524), (234788.1 4876500.5), (235334 4876499), (234828.2 4877511))

MULTILINESTRING ((234424.80000000002 4876524, 234447.30000000002 4876522.5, 234617 4876511.5, 234788.1 4876500.5), (235334 4876499, 235299.90000000002 4876511.5, 235035.30000000002 4876518.5, 234914.40000000002 4876521.5, 234789.1 4876525, 234793.5 4876641.5, 234796 4876704.5, 234798.1 4876760.5, 234802.90000000002 4876886, 234807.6 4877008.5, 234812.40000000002 4877133, 234817.90000000002 4877252, 234821.90000000002 4877353, 234828.2 4877511))