How can I convert a set of coordinates from a linestring to wkb for storage in postgis?

Question

I have a set of coordinates that come from a kml file and I would like to convert them to wkb for storage in my postgis database. When I use st_geomfromkml, though, I get an error that says I have invalid geometry. The SQL state is XX000.

The error is thrown at the first string.

Here is an example of the coordinates for the linestring: -97.75197377744352 30.52666770876964 0, -97.75203852379295 30.52665828598795 0, -97.75215019046991 30.52664203611576 0.

An additional note, the data type of the column currently holding my coordinates is varchar. I would like to copy them as wkb to my geom column - srid 4326.

How can I convert these to successfully store them in my geom column?

Answer 1

You can use st_geomfromkml, but the syntax of the linestring is a bit different. It will work this way:

SELECT ST_SetSrid( ST_GeomFromKML('
        <LineString>
            <coordinates>-97.75197377744352,30.52666770876964 -97.75203852379295,30.52665828598795 -97.75215019046991,30.52664203611576</coordinates>
        </LineString>'), 4326 );

If you want to use your current linestring syntax, its more easy to use GeomFromText:

SELECT ST_GeomFromText('LINESTRING(-97.75197377744352 30.52666770876964 0, -97.75203852379295 30.52665828598795 0, -97.75215019046991 30.52664203611576 0)', 4326 );

If you don't use the third dimension in your geometry column, you could use ST_Force_2D():

SELECT ST_Force_2D( ST_GeomFromText('LINESTRING(-97.75197377744352 30.52666770876964 0, -97.75203852379295 30.52665828598795 0, -97.75215019046991 30.52664203611576 0)', 4326 ) );