0

I'm trying to update a column public_geometry in a table by creating a point from longitude and latitude cols from the same table. The database is PostgreSQL with Pgadmin as program to handle the databasse:

 UPDATE sirene_test.etablisssement_actif
SET geom=ST_GeometryFromText('POINT('|| latitude  ||' '|| longitude ||')', 4326);

I got this Error :

ERREUR:  parse error - invalid geometry
HINT:  "POINT( )" <-- parse error at position 9 within geometry
********** Erreur **********

ERREUR: parse error - invalid geometry
État SQL :XX000
Astuce : "POINT( )" <-- parse error at position 9 within geometry
PolyGeo
  • 65,136
  • 29
  • 109
  • 338
zak
  • 43
  • 3
  • What do you get by select 'POINT('|| latitude ||' '|| longitude ||')' from sirene_test.etablisssement_actif limit 3? – user30184 Nov 13 '19 at 10:18
  • I got a column with point( logitude , latitude ) . or empty ! "POINT(48.934248 2.258772)" "POINT(50.444846 2.611103)" "POINT(4.931439 -52.317082)" "POINT( )" – zak Nov 13 '19 at 10:30
  • 2
    Filter the empty ones out where latitude is not null and longitude is not null. – user30184 Nov 13 '19 at 11:06
  • 4
    Well-Known Text uses coordinate order {longitude,latitude}, and the canonical way to do this is ST_SetSRID(ST_MakePoint(longitude,latitude),4326) – Vince Nov 13 '19 at 11:25
  • See https://gis.stackexchange.com/questions/24486/inserting-point-into-postgis – Vince Nov 13 '19 at 12:03
  • 3
    In order to prevent experts from guessing what is wrong with your data, please provide a part of it by clicking on the download link, and maybe you will be explained and helped to find the right solution... – Cyril Mikhalchenko Nov 13 '19 at 12:10
  • Maybe the solution looks like this: UPDATE sirene_test.etablisssement_actif SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) WHERE ST_GeometryType(geom)='ST_Point'; – Cyril Mikhalchenko Nov 13 '19 at 12:21

0 Answers0