1

I have a Postgres database with PostGIS extension on it. Also, I have a table with column lat with type (varchar 40). Column values are like this:

52.1432514
52.1431855
52.1431586

I want to convert the whole column to geometry type. When I do ALTER TABLE m_copy ALTER COLUMN latitude TYPE geometry USING (latitude::geometry); I see an error:

ERROR: parse error - invalid geometry
SQL state: XX000
Hint: "52.1432514" <-- parse error at position 11 within geometry

What am I doing wrong and what is the right query?

P.S Also I have a lon column. Maybe it is better to combine that columns?

  • 4
    The latitude value alone doesn't define a point, and needs to be combined with longitude for sure. You should create a new geometry column; I think the syntax you need is either ST_MakePoint or ST_PointFromText? – Erica Jul 24 '14 at 18:45
2

You probably should just create a new column and I would suggest if you are new to PostGIS, you probably want to use geography instead of geometry. So somethng like

ALTER TABLE m_copy ADD COLUMN geog geography(POINT,4326);
UPDATE m_copy SET geog = ST_SetSRID(ST_Point(longitude,latitude),4326)::geography;

If you decide to use geometry, just replace the geography (and geog) with geometry and geom

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.