2

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?

Improve this question
1
  • 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? Commented Jul 24, 2014 at 18:45

1 Answer 1

Reset to default
3

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

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.