2

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 edited with what I think you are looking for. – John Powell Oct 7 '14 at 13:27
2

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

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.