0

Trying to transform a set of points in a LINESTRING gives me an error

SELECT way_nodes.way_id, 
       ST_GeomFromText(
           CONCAT('LINESTRING(', 
                  GROUP_CONCAT(CONCAT(ST_X(location), ' ', ST_Y(location)) ORDER BY way_nodes.sequence_index SEPARATOR ','), 
                  ')'
           )
        ) 
       AS way_line
FROM way_nodes
JOIN nodes ON way_nodes.node_id = nodes.node_id
GROUP BY way_nodes.way_id;

Invalid GIS data provided to function st_geomfromtext.

table nodes schema

nodes {
  node_id,
  location: POINT
}

table way_nodes schema

way_nodes {
  way_id (refers to Table ways),
  node_id (refers to Table nodes),
  index
}

table ways schema

ways {
  way_id,
  way_line: LINESTRING <- line will be assembled after nodes and way nodes is completed
}

ways and nodes has a relation one to many

By removing ST_GeomFromText like this:

SELECT way_nodes.way_id, 
           CONCAT('LINESTRING(', 
                  GROUP_CONCAT(CONCAT(ST_X(location), ' ', ST_Y(location)) ORDER BY way_nodes.sequence_index SEPARATOR ','), 
                  ')'
        ) AS way_line
FROM way_nodes
JOIN nodes ON way_nodes.node_id = nodes.node_id
GROUP BY way_nodes.way_id;

It gives me a normal behavior like in this sample:

way_id, way_line
...
"387339983","LINESTRING(-38.7662264 -12.3939942,-38.7655391 -12.3924119)"
"387339984","LINESTRING(-38.7581998 -12.3989777,-38.7589334 -12.3996185)"
"387339986","LINESTRING(-38.7539888 -12.3981945,-38.7545895 -12.398515)"
"387339987","LINESTRING(-38.7584071 -12.3908112,-38.7589547 -12.3908011)"
"387339988","LINESTRING(-38.7569815 -12.3987725,-38.7563642 -12.3994388)"
"387339989","LINESTRING(-38.7598724 -12.3986238,-38.7593137 -12.3979369)"
"387339990","LINESTRING(-38.7600274 -12.3948289,-38.7607219 -12.3946722)"
...

I already tried to see what simple selection would carry some of this results but it returned me a normal object:

SELECT ST_GeomFromText('LINESTRING(-38.7598724 -12.3986238,-38.7593137 -12.3979369)') AS way_line;

Result from ST_GeomFromText with a sample using just GROUP_CONCAT

Juliana Aragão
  • 11
  • 2
  • You say _"gives me an error"_ but have not included the error. Not reproducible from information provided [db<>fiddle](https://dbfiddle.uk/ZUbM8eII) – user1191247 Jul 18 '23 at 11:13
  • @user1191247 you're right, just included the error right bellow the query – Juliana Aragão Jul 18 '23 at 14:55
  • 1
    Already found what the problem is, I had do set the group concat limit because its default is 1024 characters only `SET GLOBAL group_concat_max_len = DESIRED_LEN` – Juliana Aragão Jul 24 '23 at 12:52

0 Answers0