Tell me more ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 1 down vote favorite

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

share|improve this question
1  
Loading from CSV to PostGIS spatialdbadvisor.com/postgis_tips_tricks/118/… – Mapperz♦ Aug 31 '12 at 15:17

1 Answer

up vote 4 down vote

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)
share|improve this answer
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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