I have this excel spreadsheet with latlong coordinates in it and imported it into Postgis

Problem is that I can't represent it on Geoserver because there is no typical Postgis geometry column

How to create this geometry column using the 2 coordinate fields?

  • 2
    Besides the valid answer given by @thibautg, another way is to use a SQL View – Devdatta Tengshe Jun 27 '16 at 9:54

1) Add a new geometry column:

ALTER TABLE mytable ADD COLUMN geom geometry(Point, 4326);

2) Update the new column with the geometry from your long and lat columns:

UPDATE mytable SET geom = ST_SetSRID(ST_MakePoint(long, lat), 4326);
| improve this answer | |
  • I assume I replace long and lat for the respective columns however I got ERROR: column "field4" does not exist. field4 is my longitude column – Luffydude Jun 27 '16 at 9:57
  • 1
    Yes. If your column name has capital letters, you must enclose the field name in double quotes. Ex: "Field4". – thibautg Jun 27 '16 at 10:00
  • 1
    It seems that your fields are stored in text and not in double precision. Try casting them: UPDATE mytable SET geom = ST_SetSRID(ST_MakePoint("Field4"::double precision, "Field 3"::double precision), 4326); – thibautg Jun 27 '16 at 10:19
  • 1
    I guess that the first line in your table contains your column's names because of a bad import (you probably used QGIS which does not always recognize column names correctly with Excel files). This is the reason your fields are called "Field4" etc. Try deleting the first line of your table, where you see the column names instead of values. Or import your excel file with the correct headers. – thibautg Jun 27 '16 at 10:27
  • 1
    Yes! it Worked! thanks. I did use the QGIS importer instead of the postgres one so extra props for guessing that one – Luffydude Jun 27 '16 at 10:36

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.