0

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?

1 Answer 1

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 ) );
1
  • I'd still be interested in converting to WKB before sending a request (doing large inserts/updates over HTTP).
    – vpgcloud
    Jun 27, 2017 at 14:31

Your Answer

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

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