[postgis-users] Searching city by coordinates

[karsten]

Hi Luca 

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

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)
)

I am using wgs84 decimal degrees (SRID 4326) on both geometries to make sure
they should intersect (seems that you used for the point which is in decimal
degrees values SRID 3857 in your query (which is the code Google Mercator in
meters however ) 

Note also that for the function St_Contains - it seems that if any point for
the geometry is outside the other one the query will not return any results
see https://postgis.net/docs/ST_Contains.html
 
Cheers
Karsten


-----Original Message-----
From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf
Of Luca Bertoncello
Sent: Thursday, February 25, 2021 23:31
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] Searching city by coordinates

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)
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users