MySQL and splitting spacial data

Question

I have a MySQL table that we are using for storing of some latitude and longitude. The coordinates are defined as a 'geometry' type column and can contain any number of coordinate pairs.

When I run this code, I get the conversion of the geometry type to some text I should be able to parse out:

SELECT ST_AsText(ST_ExteriorRing(c.coordinate))
        from `app_coordinate` c, app_type_coordinate tc 
        where c.idtypecoord = tc.idtypecoord 
            and tc.description="safezone";

I get the following as a result:

LINESTRING(-17.386557 -66.150796,-17.388676 -66.150335,-17.388727 -66.14952,-17.385963 -66.147642,-17.386557 -66.150796)
LINESTRING(-17.388043 -66.157421,-17.387351 -66.154347,-17.391524 -66.153496,-17.3921 -66.156664,-17.388043 -66.157421)
LINESTRING(-17.389281 -66.1413,-17.387155 -66.141837,-17.386763 -66.142888,-17.389566 -66.142929,-17.389281 -66.1413)
LINESTRING(-17.389914 -66.143621,-17.390695 -66.143275,-17.390262 -66.1409,-17.389487 -66.141172,-17.389914 -66.143621)

Each LineString row has a set of coordinate pairs, which I need to split. So the first pair is #1, second pair is #2, etc. Thing is, I could have any number of pairs.

What I need to do is be able to split each linestring into pairs and store in to a variable, like pair1_x, pair1_y, pair2_x, pair2_y, pair3_x... even though I won't know how many pairs exist and I have two delimeters...a space between coordinates and a comma between pairs.

Answer 1

Here is what I did as a solution: modified the original statement with a couple of Replace() statements to remove some text from each row, used a group_concat() statement to concatenate multiple rows into one row, and added starting and trailing brackets (for formatting) with the concat() statement.

SET group_concat_max_len = 2048;

SELECT CONCAT('[', REPLACE(REPLACE(Group_concat(DISTINCT(ST_AsText(ST_ExteriorRing(c.coordinate))), ''), "LINESTRING(", ""), ")", ""), ']') as punto
    from `app_coordinate` c, app_type_coordinate tc 
    where c.idtypecoord = tc.idtypecoord 
        and tc.description="safezone";