1

I have a csv file that I want to bring into a PostGIS database through DB Manager in QGIS. I have a column/ field called "Location" that contains geometry (e.g. (40.789423 -75.628493 232.95452117919922 5). The first two are lon/lat, the second is altitude and third is error for the GPS unit it was collected with. I ONLY need the first two pieces of data (i.e. lon/lat).

When I pull this data into PostGIS database with DB Manager, it will not recognize this column as geometry, but identifies it as TEXT (varchar). I would like to change this, but am having trouble. I prefer to use a GUI, but it will not convert. I am trying to figure out the correct SQL code to do this. Here's my most recent attempt:

ALTER TABLE "schema"."table" ALTER "Location" TYPE geometry USING ("Location"::geometry)

However, this returns the following error message:

parse error - invalid geometry HINT: "40.789423 " <-- parse error at position 10 within geometry

I believe that this is because there is an extra space after the "40.78923". Does this seem correct? If so, how do I fix this?

  • Update. My original data appears to be in POINTZM format (lon, lat, altitude, measure). I tried the following: ALTER TABLE "schema"."table" ALTER "Location" TYPE geometry(POINT) USING ST_GeomFromText ('POINTZM("Location")') – spondylusprinceps May 19 '16 at 15:53
  • did you ever get one of these solutions to work? – DPSSpatial May 25 '16 at 16:04
1

Please try

ALTER TABLE "schema"."table" 
ALTER "Location" TYPE geometry(Point) 
USING ST_Force2D(ST_GeomFromText('POINTZM(' || "Location" || ')'))

ST_Force2D is optional but is used to only keep the lon.lat values.

  • Thanks for your time, thibautg. I get the following error. operator is not unique: unknown || geometry HINT: Could not choose a best candidate operator. You might need to add explicit type casts. – spondylusprinceps May 19 '16 at 15:09
  • Update. My original data appears to be in POINTZM format (lon, lat, altitude, measure). I tried the following: ALTER TABLE "schema"."table" ALTER "Location" TYPE geometry(POINT) USING ST_GeomFromText ('POINTZM("Location")') I got this error: parse error - invalid geometry HINT: "POINTZM("L" <-- parse error at position 10 within geometry Any thoughts @thibautg? – spondylusprinceps May 19 '16 at 15:58
  • I've updated my answer to first create a POINTZM and then convert it to 2D. – thibautg May 19 '16 at 18:19
  • You need the double pipe || to concatenate in PostgreSQL. Do not forget to enclose your text in single quotes and your column names in double quotes. – thibautg May 19 '16 at 18:22
  • Other question: does your "Location" column contains the parenthesis ()? If yes then you must omit them in the concatenation. You should then write ALTER TABLE "schema"."table" ALTER "Location" TYPE geometry(Point) USING ST_Force2D(ST_GeomFromText('POINTZM' || "Location")) – thibautg May 19 '16 at 18:51
0

If you open your CSV up as 'space delimited' in for example LibreOffice Calc, the data is separated nicely...

I took your example string and put it in notepad with headers for each of the 4 data points:

enter image description here

Then I opened it up in LibreOffice Calc and specified 'space delimited':

enter image description here

And it looks fine:

enter image description here

So from there, I would export it out again as a 'comma delimited' CSV, import that new file into PostgreSQL, and follow the steps you're trying to do already...

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.