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