0

I imported the OSM data into my Postgresql 9.5 database, I also installed the Postgis. So when I use these SQL-Syntax:

SELECT 
ST_Y(ST_Transform(point.way, 4326)) AS lat,
ST_X(ST_Transform(point.way, 4326)) AS long, 
poly.name as place, 
point.name as location
FROM planet_osm_point point left join planet_osm_polygon poly 
ON st_intersects(point.way, poly.way) 
WHERE poly.admin_level='8' 
AND point.sport='tennis'
AND point.name IS NOT NULL;

I get following table:

   lat        |       long       |            place            |   location              

-----------------------+----------------------------+---------------+----------------------------------- 48.1122856691474 | 11.4279876860241 | Gräfelfing | Tennis Grün-Weiß
48.180304681448 | 11.263177913097 | Fürstenfeldbruck | Tennisclub Fürstenfeldbruck e.V.

So next i want to search a radius with the specified location (lat,lon):

SELECT 
ST_Y(ST_Transform(point.way, 4326)) AS lat,
ST_X(ST_Transform(point.way, 4326)) AS long, 
poly.name as place, 
point.name as location
FROM planet_osm_point point left join planet_osm_polygon poly 
ON st_intersects(point.way, poly.way) 
WHERE poly.admin_level='8' 
AND point.sport='tennis'
AND ST_Intersects(poly.way,ST_Transform(ST_Buffer(ST_SetSRID(ST_Point(48.1122856691474,11.4279876860241),4326)::geography,500)::geometry,3857))
AND point.name IS NOT NULL;

But now, I received no results, I also used the location of the first result (48.1122856691474 | 11.4279876860241). Why can postgres find any results?


Using a tip from @MappaGnosis, my SQL syntax now looks like this:

SELECT 
name,
ST_Y(ST_Transform(way, 4326)) AS lat,
ST_X(ST_Transform(way, 4326)) AS long
FROM planet_osm_point 
WHERE ST_DWITHIN(way, ST_TRANSFORM(ST_SETSRID(ST_MAKEPOINT(11.4279876860241,48.1122856691474),4326),900913), 500);

And it works! Now together with my previous code:

SELECT 
ST_Y(ST_Transform(point.way, 4326)) AS lat,
ST_X(ST_Transform(point.way, 4326)) AS long, 
poly.name as place, 
point.name as location
FROM planet_osm_point point left join planet_osm_polygon poly 
ON st_intersects(point.way, poly.way) 
WHERE poly.admin_level='8' 
AND point.sport='tennis'
AND ST_DWITHIN(point.way, ST_TRANSFORM(ST_SETSRID(ST_MAKEPOINT(11.4279876860241,48.1122856691474),4326),900913), 500)
AND point.name IS NOT NULL LIMIT 20;
  • 1
    did you mean to cast your point as geography? ST_SetSRID(ST_Point(48.1122856691474,11.4279876860241),4326)::geography – kttii Sep 6 '16 at 17:58
  • What do you get when you do a simple query on the polygon table, eg SELECT * FROM planet_osm_polygon WHERE ST_Intersects(way,ST_Transform(ST_Buffer(ST_SetSRID(ST_Point(48.1122856691474,11.4279876860241),4326)::geography,500)::geometry,3857)) – MickyT Sep 6 '16 at 23:48
1

The reason it can't find any points is because you have made the classic mistake of getting the order of your coordinates the wrong way round when you make your buffer-point. The correct order in GIS is x,y which equates to long, lat (not lat, long - yes I know systems like Leaflet follow colloquial parlance of 'lat, long' but that is not in line with the correct GIS convention). To remember the correct order think: "you go along the hall then up the stairs".

Also, the approach of using Intersects + buffer is not performant and, while seemingly logical, is not recommended. The recommended approach is to use ST_DWithin (see the first link for the reasons why). You'll see that ST_DWithin avoids the messy double re-casting during the creation of your buffer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.