[postgis-users] Searching city by coordinates

[Luca Bertoncello]

Am 26.02.2021 09:01, schrieb karsten:

Hi Karsten

> just to check first: is planet_osm_polygon.way a column holding a 
> PostGIS
> polygon geometry type ?

way is geometry(Geometry,3857) in planet_osm_polygon and in my "cities" 
table, too.

> As is it rather appears to me that you are trying to intersect below a 
> way
> (line) with a ´single point - which rarely will give you any hit = 
> result
> back ;)
> Overall you will need to feed the polygon geometry of teh city area
> into the query to get an intersect with the point
> 
> Maybe this query below with st_intersects
> https://postgis.net/docs/ST_Intersects.html can work for you ?
> To make it work though will need to replace 'polygon_geom'
> with what you have as the polygon geometry column of the city area in 
> your
> table (if you don't have any you will need to alter the table to have
> include it ;) )
> 
> select admin_level, name, place, population, km2 from cities where
> st_intersects(
> st_transform(polygon_geom,4326),
> ST_SetSRID(ST_MakePoint(8.555603027343752, 45.75985868785576),4326)
> )

Your query works for me. I just have to restrict the data having place 
not null and avoiding "state", etc...
I'll try your index, since now the query take very long.

Thanks a lot
Luca Bertoncello
(lucabert at lucabert.de)