1

i know there are lot of similar question but after long time searchin none of them worked for me thats why i'm posting,

I want to get city name with lat and lon from planet_osm_polygon table where are polygons stored including polygons of cities, here is my code:

SELECT name
  FROM planet_osm_polygon
  where place = 'city'
 and ST_CONTAINS(ST_Transform(way,4326), ST_SetSRID(ST_Point(41.693459100461496,44.8014495),4326));

(the logic is.. if polygon contains given point, return its name if its a city)

What is the problem? i transformed both geometries to 4326 but still not working. P.S "way" is a column of geometry(polygon).

Query always returns empty result

Edit

City is not missing from planet_osm_polygon and its geometry column really is type of polygon, here is some query results:

Cities query

In geometry viewer it looks correct

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
Martian
  • 99
  • 1
  • 11
  • I'm not really familiar with the database structure. The city node https://www.openstreetmap.org/node/1614277087 is probably not contained in planet_osm_polygon since it is a node, not a polygon. Or is this supposed to return the corresponding relation https://www.openstreetmap.org/relation/1996871? – scai Jan 07 '20 at 10:04
  • 2
    Can you share 1) sample data, 2) the error you're getting and 3) the expected result? – Jim Jones Jan 07 '20 at 12:21
  • @scai thanks for taking a time to help me, I updated post and added some more details. – Martian Jan 07 '20 at 14:53
  • 1
    @JimJones i updated post, check it out – Martian Jan 07 '20 at 14:54
  • @Martian it would be nice to actually see the geometries in a more friendly format, e.g. WKT `SELECT name,ST_AsText(way) FROM planet_osm_polygon` - and please post the result as text, not in a screenshot :) – Jim Jones Jan 07 '20 at 15:57

1 Answers1

2

I believe you're switching x and y in your coordinate pairs. WGS84 expects longitude, latitude, not the other way around:

SELECT name
FROM planet_osm_polygon
WHERE place = 'city'
AND ST_Contains(ST_Transform(way,4326), ST_SetSRID(ST_MakePoint(44.80,41.69),4326));

On a side note: Consider reducing the precision of your coordinates. With so many decimal numbers you're entering the microscopy realm -> 41.693459100461496

Sample data

CREATE TEMPORARY TABLE planet_osm_polygon (name TEXT, way GEOMETRY,place TEXT);
INSERT INTO planet_osm_polygon 
VALUES ('Georgia',ST_SetSRID('POLYGON((43.87 42.22,45.43 42.22,45.43 41.50,43.87 41.50,43.87 42.22))'::GEOMETRY,4289),'city');

The coordinates correspond to the following BBOX in the SRS 4289:

enter image description here

Query - point inside the BBOX, setting either BBOX and given point to WGS84

SELECT name
FROM planet_osm_polygon
WHERE place = 'city'
AND ST_Contains(ST_Transform(way,4326), ST_SetSRID(ST_MakePoint(44.80,41.69),4326));

  name   
---------
 Georgia
(1 Zeile)

enter image description here

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • 1
    yes i was copy - pasting coordinates from google maps for testing and forgot to switch coordinates, thanks. – Martian Jan 07 '20 at 16:50