Convert string with multiple values to double precision (PostgreSQL)

As the function name suggests, ST_GeomFromText requires text—more specifically, well-known text. Picking out double precision values is unnecessary. You just need to glue togther the pieces with a concatenation operator ||:

SELECT ST_GeomFromText('POINT(' || meta_string_value || ')', 4326) AS geom
FROM (
    SELECT '9.94 54.839'::text AS meta_string_value
    UNION ALL SELECT '9.873684 54.63'::text AS meta_string_value
) AS t1;

I answered your other (extremely similar) question: Obtain double precision values from inconsitent strings for using ST_GeomFromText (PostGIS) and the answer is the same.

Just to Recap

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

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

Solution

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

Also, if you have several values (points) that need to be connected to form a line (or polygon) take a look at STRING_AGG(text, text).

Assumes a string "pos" of latitude longitude(lat lon). Split the string and cast it to float, use strip to remove any unwanted whitespaces.

tmp = pos.split()
lat = float(tmp[0].strip())
lon = float(tmp[1].strip())

You could then do something like:

latlonString = 'POINT(%f %f)' % (lat,lon)

Using the variable in a query:

ST_GeomFromText(latlonString,4326)

For example:

SELECT id
FROM features
WHERE pos = ST_GeomFromText(latlonString,4326)

For a string of multiple coordinate pairs; xyString = "x1 y1 x2 y2" NOTE! not separated by comma:

xyString = '3.584731 60.739211 3.590472 60.738030 3.592740 60.736220'
xy = xyString.split()
tuples  = [i+' '+j for i,j in zip(xy[::2],xy[1::2])]
multiPointString = ','.join(map(str,tuples))
polygonString = 'POLYGON((%s))' % (multiPointString)
print polygonString

print:

POLYGON((3.584731 60.739211,3.590472 60.738030,3.592740 60.736220))

If you have a polygon string like this: "x1 y1, x2 y2, ....", just edit the line:

xy = xyString.replace(",", " ").split()

This should be sufficient if you use psycopg2 as postgresql adapter. Maybe it doesn't answer all your questions, but hope this helps.

With your hints I was able to get a point coordinate with the following syntax:

    SELECT ST_GeomFromText(((('POINT('|| split_part(t1.meta_string_value, ' ', 1)::double
    precision) || ' ') || split_part(t1.meta_string_value, ' ', 2)::double precision) ||
    ')', 4326) AS geom FROM points t1

Now I have a new problem. In the case above only two double precision values (LON, LAT) are needed to create a geometry out of the coordinates. What can I do if I would like to create a line or a polygon out of a list of coordinates like for example:

    9.873684 54.63, 9.8432 54.8792, 9.032 54.32, 8.9 54.987

The proble is that I don't know how much coordinates a line or polygon will have...

Any ideas about it?