Coordinates of WKT in PostgreSQL

Question

I have a table in PostgreSQL. And it has no X and Y coordinates. But, it has a geom column with the WKT. Also, I have created an empty column called LAT and other called LONG. So, what I want is to write the Latitude and Longitude of the WKT column for each point. How can I do that?

• Taras: What is the geometry type? Can you give an example?
• Mapperz: postgis.net/docs/ST_GeomFromWKB.html can create an instance of the appropriate geometry type.

Answer 1

The ST_GeomFromText function converts the WKT into a geometry object that PostGIS can understand:

UPDATE MyTable
SET Lat = ST_Y(ST_GeomFromText(WKT_column)),
 Lon = ST_X(ST_GeomFromText(WKT_column));

Answer 2

If you have a column to store point geometry in your table e.g. "geom", then you can use the ST_MakePoint function. I suppose you have 2D geometry and you have the latitude and longitude in "lat" and "lon" column as decimal degrees. I supposed latitudes and longitudes are in WGS84 (SRID=4326). The following SQL command will update geom column:

UPDATE your_table SET geom = ST_SetSRID(ST_MakePoint(lon, lat), 4326);

• Vince: This is the solution to a different problem. The OP is trying populate lon and lat from a geom, though it isn't clear if the geometery is a "WKT" text string of just the presentation of a geometry as WKT from the client. Either way, the solution would make use of ST_X() and ST_Y(), not ST_MakePoint().