[postgis-users] Searching city by coordinates

Luca Bertoncello lucabert at lucabert.de
Thu Feb 25 23:31:11 PST 2021


Hi list!

I'm trying to create a little service on my server, like an API by 
GoogleMaps, to get the city name using the coordinates of a point.
So I created a table "cities" as:

CREATE TABLE cities AS
SELECT
     NEXTVAL('cities_seq') AS id,
     planet_osm_polygon.way,
     planet_osm_polygon.admin_level,
     planet_osm_point.name,
     planet_osm_point.place,
     CASE
      WHEN (planet_osm_point.tags->'population' ~ '^[0-9]{1,8}$') THEN 
(planet_osm_point.tags->'population')::INTEGER ELSE 0
     END as population,
     (ST_Area(ST_Transform(planet_osm_polygon.way, 4326)::geography) / 
1000000) AS km2
   FROM planet_osm_polygon
   JOIN (
     SELECT name, MAX(admin_level) AS al
     FROM planet_osm_polygon
     WHERE boundary = 'administrative' AND admin_level IN ('4', '6', '8') 
AND osm_id < 0 GROUP BY name
   ) size USING(name)
   JOIN planet_osm_point USING (name)
WHERE planet_osm_polygon.boundary = 'administrative' AND
planet_osm_polygon.admin_level = size.al AND
planet_osm_polygon.osm_id < 0;

So I should have a list of all cities in my database (Europe) with the 
boundaries, the name, population, extension, etc.
Now I'm trying to search the city for a given point with the query:

select admin_level, name, place, population, km2 from cities where 
St_Contains(way, 'SRID=3857;POINT(8.555603027343752 
45.75985868785576)'::geometry);

but this query does not return any result. I know which city is at these 
coordinates (Arona, in Italy) and I see, the city is in the table, so 
I'm sure the problem is in my query, but I can't find it...

Could someone help me?
Second question: which index should I create to speed these queries up? 
Just an Index on the column "way"?

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


More information about the postgis-users mailing list