I have an INSERT INTO query that takes an input from a html form and insert it in a table on postgres, one of the columns's value is a GeoJSON. Now i'm trying to prepare a trigger function that take the json after the INSERT query and convert it to geometry and insert this geometry in another column on the same table.
SELECT ST_GeomFromText(
ST_AsText(
CAST(ST_GeomFromGeoJSON(t1."column-name") AS TEXT)
), 4326
)
FROM (SELECT "column-name"
FROM table-name
ORDER BY id DESC
LIMIT 1) AS t1;
With this query i take the GeoJSON from the last row inserted and convert it to a geometry, but i don't know how to insert this geometry value in another column.
I manage to come up with a query that resolve my problem:
UPDATE table-name
SET column-name = (SELECT ST_GeomFromText(
ST_AsText(
CAST(ST_GeomFromGeoJSON(t1.column-name) AS TEXT)
), 4326
)
FROM (SELECT column-name
FROM table-name
ORDER BY id DESC
LIMIT 1) AS t1)
WHERE id IN(SELECT id
FROM table-name
ORDER BY id DESC
LIMIT 1
);
With this query i take a GeoJSON within, from the last row inserted in the table, and convert it to geometry and insert this geometry in another column of the same row from where i had taked the GeoJson.
Importante: the column that stores the GeoJSON has the data type of character varying.
Hope it has made sense, english is not my first language.