1

I try to update a geometry point with data from another table (also a geometry point). Both are SRID 4326. The query is executing without errors, everything is updated but the geometry is just not updated.

Do I have to transform first to lat lng and then back again? Is it not possible to use a geometry type? I don't get it at the moment.

Example:

UPDATE location
    SET
    location_name = import_ogr.location_name,
    location_category_id = import_ogr.location_category_id::INTEGER,
    position_geo = import_ogr.wkb_geometry
    FROM importexport.import_ogr
    WHERE location.location_id = 23017;
5
  • Is your data in WKB like the name suggest or is a geometry ? Maybe try ST_GeomFromWKB May 19, 2021 at 12:57
  • Are you sure that the import_ogr geometry is not null? If it was a format/type issue, an error would have been thrown. Since other fields are updated, it means that either the source geometry is null or that there is a trigger on the location table that prevents geometry update.
    – JGH
    May 19, 2021 at 13:15
  • Also do note that you are not specifying a join condition between the two tables, so unless there is a single record in the import_ogr table, the query is using a random row for the update
    – JGH
    May 19, 2021 at 13:16
  • The data is made by ogr2ogr. In pgAdmin i see no difference between the 2 tables, but i'm not an expert. With ST_GeomFromWKB : WARNING: OGC WKB expected, EWKB provided - use GeometryFromEWKB(): UPDATE 1 Query returned successfully WARNING: OGC WKB expected, EWKB provided - use GeometryFromEWKB() for this UPDATE 1 Query returned successfully With GeometryFromEWKB() it's also Query returned successfully But the Point is not updated (Without error and all the other data are updated) Can it be a rights problem? With pgAdmin the geo column is locked with a lock symbol
    – Baba Wanga
    May 19, 2021 at 14:13
  • JGH! Your^'re right! It was the trigger! Thx for this eye opener!
    – Baba Wanga
    May 19, 2021 at 14:21

1 Answer 1

1

I had a trigger that updated the geo column with:

position_geo = ST_SetSRID(ST_MakePoint(NEW.lng,NEW.lat), 4326)

when updating lat and lng. That's preventing from updating the column.

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.