1

I have a csv table. Each record includes a start node (lat1,lon1) and an end node (lat2,lon2). I have already imported the .csv into Postgresql 8.4. Now I would like set the geometry column. I tried to use this sql statement:

UPDATE mytable
SET the_geom = ST_GeomFromText('POINT(' || lat1 || ' ' || lon1 || ' ' ||
               lat2 || ' ' || lon2 || ')',4326);

but I receive this warning message:

WARNING:  OGC WKT expected, EWKT provided - use GeomFromEWKT() for this
            ERROR:  new row for relation "mytable" violates 
            check constraint "enforce_dims_the_geom"

Do you have any idea on how to add geometries in my case?

Thank you Selini

6

You're constructing a POINT with four ordinates. To the parser that looks like a POINT ZM. You want to construct a LINESTRING.

ST_SetSRID(ST_MakeLine(ST_MakePoint(lon1, lat1), ST_MakePoint(lon2, lat2)), 4326)
  • I run the query and I visualized the results in QGis. However each point is connected by a line. I tried to remove the ST_MakeLine but it doesn't work. Could you please tell which is sql statement that allows me to obtain points? – selini Sep 1 '12 at 9:53
  • You have two points, surely they are meant to form a line? If you want them to be pointilist, just wrap them into a union, so you get back a multipoint. – Paul Ramsey Sep 1 '12 at 23:21
  • ST_SetSRID(ST_Union(ST_MakePoint(lon1, lat1), ST_MakePoint(lon2, lat2)), 4326) – Paul Ramsey Sep 1 '12 at 23:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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