[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