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?


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

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

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);
  • 3
    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(). – Vince Mar 29 '20 at 0:50

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.