request error on postgreSQL using postgis (more than one row returned)

1
votes

I have a column containing all 37 000 city of my country on postgreSQL using postGIS and i'm trying to put their POINT (geometry column) with the postgis function ST_point(long, lat). They all have a different long and lat data.

Here is my request : 

update city
set geom_city = (select ST_SetSRID(ST_Point(city.city_long, city.city_lat),4326) from city);

Problem is that i've this error msg saying : more than one row returned by a subquery used as an expression postgreSQL

I found out on stackoverflow that I needed to pu the "Limit 1" at the end of my request like :

update city
set geom_city = (select ST_SetSRID(ST_Point(city.city_long, city.city_lat),4326) from city LIMIT 1);

which make my request working but does write in the geom_city column the same result for all the city.

Anyone knows ?

1
postgresqlpostgis

1 Answers

2
votes

The nested SELECT subquery is not needed, try this:

update city
set geom_city = ST_SetSRID(ST_Point(city_long, city_lat),4326) ;