使用ST_GeomFromText(PostGIS)从不一致的字符串中获取双精度值

问题描述

我有一个PostgreSQL表，其中的列包含以下格式的字符串:

I have a PostgreSQL table with a column containing strings in the following format:

    '3.985 58.934, 4.56 61.2323, 5.4 63.234355, 3.25982 59.309'
    '3.985 58.934, 4.56 61.2323, 5.4 63.234355, 3.25982 59.309, 4.5632 58.32423'
    'and so on...'

如您所见，该列包含不同长度的坐标，作为一个行中的字符串.我想使用PostGIS功能:

As you can see the column contains coordinates in different lengths as strings in one line. I would like to use the PostGIS function:

    ST_GeomFromText('LINESTRING(3.985 58.934, 4.56 61.2323, 5.4 63.234355, 3.25982
    59.309)', 4326)

现在出现以下问题:

PostGIS函数需要正确格式的双精度值，如上所示.当我不知道字符串将要多久以及每个坐标有多少个字符时，如何从字符串中生成双精度值?

The PostGIS functions requires double precision values in the right format as shown above. How can I generate double precision values from strings when I don't know how long the strings will be and how many characters each coordinate has?

谢谢您的帮助！

简

推荐答案

根据文档 a>，ST_GeomFromText需要(text, integer)，而不是(double precision).

According to the docs, ST_GeomFromText requires (text, integer), not (double precision).

您需要做的只是CONCAT()，它应该可以工作.

All you need to do is CONCAT() and it should work.

ST_GeomFromText(CONCAT('LINESTRING(', "YourTable"."YourString", ')'), 4326);

用于测试

SELECT
    ST_GeomFromText('LINESTRING(3.985 58.934, 4.56 61.2323, 5.4 63.234355, 3.25982 59.309)', 4326),
    CONCAT('LINESTRING(', "T1"."C1", ')'),
    ST_GeomFromText(CONCAT('LINESTRING(', "T1"."C1", ')'), 4326)
FROM
    (
        SELECT '3.985 58.934, 4.56 61.2323, 5.4 63.234355, 3.25982 59.309, 4.5632 58.32423' AS "C1"
    ) AS "T1";

---

只是为了娱乐

我决定将您的字符串转换为最初请求的DOUBLE PRECISION，然后再转换为字符串...最终结果与上面相同，但是它做了很多工作.不过，这正是您要的.

---

Just for fun

I decided to convert your string into the originally requested DOUBLE PRECISION and then back into a string... the end result is the same as above, but it does it with a lot more work. Nevertheless, it is what you asked for.

SELECT
    ST_GeomFromText(CONCAT('LINESTRING(', (SELECT STRING_AGG(ARRAY_TO_STRING("Line"."Points", ' '), ',') FROM REGEXP_MATCHES(ARRAY_TO_STRING(REGEXP_SPLIT_TO_ARRAY("T1"."C1", E', | ')::DOUBLE PRECISION[], ' '), '(\d*\.\d*) (\d*\.\d*)', 'g') AS "Line"("Points")), ')'), 4326)
FROM
    (
        SELECT '3.985 58.934, 4.56 61.2323, 5.4 63.234355, 3.25982 59.309, 4.5632 58.32423'::TEXT AS "C1"
    ) AS "T1";

这篇关于使用ST_GeomFromText(PostGIS)从不一致的字符串中获取双精度值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！