2

I'm working with point coordinates in PostGIS and I have a problem. My goal is to generate linestrings by using ST_MakeLine geometry constructor (see snippet 1). Source data is pre-generated ST_MakePoint statements and can contain multiple points (see example from snippet 2). Due that, I have to use ARRAY constructor inside ST_MakeLine geometry constructor.

-- snippet 1 (example of geometry contstructor I'm trying to use)

SELECT ST_SetSRID(ST_MakeLine(ARRAY[<point_data_here>]),4326)
-- snippet 2 (data in column, type text)

ST_MakePoint(62.577460, 29.789088), ST_MakePoint(62.577431, 29.788905), ST_MakePoint(62.577109, 29.789399), ST_MakePoint(62.576793, 29.790482)
-- snippet 3 (this works well, returns geometry as expected)

SELECT ST_SetSRID(ST_MakeLine(ARRAY[ST_MakePoint(62.577460, 29.789088), ST_MakePoint(62.577431, 29.788905), ST_MakePoint(62.577109, 29.789399), ST_MakePoint(62.576793, 29.790482)]),4326) 

My exact question is, how can I embed text data shown in snippet 2 in ARRAY constructor shown in snippet 1? If I do it by manyally copying and pasteing (see snippet 3), there are no problems. But how can I do SELECT statement (data from column which have type of text) inside that ARRAY constructor?

2
  • just to be absolutely sure: that column value is actually a string with those statements, e.g. 'ST_MakePoint(62.577460, 29.789088)'? lol, now that's a pain in the.
    – geozelot
    Oct 8, 2019 at 8:13
  • Yes, it is a text string (concatenated and combined earlier from list of coordinates). Single quotes are used while generating it and type of the column is text. Oct 8, 2019 at 8:34

1 Answer 1

1

You could tediously extract those numbers from the string using PostgreSQLs regex functions, but I suggest to simply add a geometry column and update within a DO block using EXECUTE:

ALTER TABLE <your_table>
  ADD COLUMN geom GEOMETRY(LINESTRING, 4326)
;

DO
  $$
  DECLARE r RECORD;
  BEGIN
    FOR r IN SELECT <id>, <statements> FROM <your_table>
    LOOP
      EXECUTE CONCAT(
        'UPDATE <your_table>
         SET    geom = ST_SetSRID(ST_MakeLine(ARRAY[', r.<statements>, ']), 4326)
         WHERE  <id> = ', r.<id>
      );
    END LOOP;
  END
  $$
;

Don't forget to replace all <> enclosed identifiers with your actual names.


Obviously, the pre-concatenating of actual SQL statements is a bad idea. If you can, avoid it; anything is better really, but I suggest to form a valid EWKT string instead, e.g.

'SRID=4326;LINESTRING(62.577460 29.789088, 62.577431 29.788905, 62.577109 29.789399, ...)'

in that pre-processing step of yours (note that there is no , between X and Y coordinates) and

SELECT <wkt_string>::GEOMETRY AS geom     -- or ST_GeomFromEWKT(<wkt_string>) AS geom
FROM   <your_table>
;
1
  • Thanks for the answer ThingumaBob! I totally agree you line related to pre-concatenating actual statements is a bad idea. But, I was desperate while trying to find workaround. I used your EWKT example to solve this, so huge thanks to you dude! Actually I created function which "reads" coordinates, concatenates those into EWKT string and creates WKB geometry. Pretty much easier to call that function instead of complex query while updating values is table. Oct 11, 2019 at 6:31

Your Answer

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

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