Re: [OSM-talk] all POIs within a specific radius
On Mon, 2011-08-08 at 07:34 +0100, Graham Jones wrote: These questions might get a better response on the Dev list as they are quite technical. but it is an end user question - I would not like to waste the time of the devs. I am not very good at postgis so I may be wrong, but you might be comparing two identical points (way and way). The postgis manual seems to do select. ... AS.. for each side of the comparison. You could check this by using the coordinates of bangalore explicitly rather than getting them from the database. actually as Frederik pointed out the error was that the measurement has to be in degrees and not metres as I had assumed. It works like a charm. I quite fancy starting a tips and tricks wiki page for this sort of thing if you would like to ass the solution when you find it. Here it is (I am quite proud of the fact that I could do it with my rudimentary knowledge of sql - it only took 4 days to write it): select p.name, f.lat*.001 as lat, f.lon*.001 as lon from planet_osm_point p, planet_osm_nodes f where (p.place != 'city') and ST_DWithin(p.way, (select way from planet_osm_point where name like '%Mysuru%' limit 1),.15) and p.osm_id = f.id order by p.name; This selects all places within .15 degrees of Mysuru. ___ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] all POIs within a specific radius
I'm glad you sorted it - I will find some time soon for the wiki page. The reason I suggested the dev list is that I know quite a few people do not subscribe to talk any more - your questions would not be out of place there, but it is up to you! Regards Graham. On 9 August 2011 09:49, kenneth gonsalves law...@thenilgiris.com wrote: On Mon, 2011-08-08 at 07:34 +0100, Graham Jones wrote: These questions might get a better response on the Dev list as they are quite technical. but it is an end user question - I would not like to waste the time of the devs. I am not very good at postgis so I may be wrong, but you might be comparing two identical points (way and way). The postgis manual seems to do select. ... AS.. for each side of the comparison. You could check this by using the coordinates of bangalore explicitly rather than getting them from the database. actually as Frederik pointed out the error was that the measurement has to be in degrees and not metres as I had assumed. It works like a charm. I quite fancy starting a tips and tricks wiki page for this sort of thing if you would like to ass the solution when you find it. Here it is (I am quite proud of the fact that I could do it with my rudimentary knowledge of sql - it only took 4 days to write it): select p.name, f.lat*.001 as lat, f.lon*.001 as lon from planet_osm_point p, planet_osm_nodes f where (p.place != 'city') and ST_DWithin(p.way, (select way from planet_osm_point where name like '%Mysuru%' limit 1),.15) and p.osm_id = f.id order by p.name; This selects all places within .15 degrees of Mysuru. -- Graham Jones Hartlepool, UK. ___ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] all POIs within a specific radius
Hi, On 08/08/11 07:51, kenneth gonsalves wrote: I manufactured this SQL, but it selects *every* locality on the map: select name from planet_osm_point where place = 'locality' and ST_DWithin(way,(select way from planet_osm_point where name like 'Bangalore%' limit 1),300) order by name; Is it possible that your database is not in Mercator projection but in latitude/longitude (-l option on osm2pgsql)? In that case you would be selecting everything within 300 degrees of Bangalore, and you'd have to try a distance of about .0003 for something roughly like 300 metres. Bye Frederik ___ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] all POIs within a specific radius
On Mon, 2011-08-08 at 10:40 +0200, Frederik Ramm wrote: select name from planet_osm_point where place = 'locality' and ST_DWithin(way,(select way from planet_osm_point where name like 'Bangalore%' limit 1),300) order by name; Is it possible that your database is not in Mercator projection but in latitude/longitude (-l option on osm2pgsql)? In that case you would be selecting everything within 300 degrees of Bangalore, and you'd have to try a distance of about .0003 for something roughly like 300 metres. as usual you are right - thanks. ___ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] all POIs within a specific radius
On Tue, 2011-08-02 at 14:26 +0200, Frederik Ramm wrote: On 08/02/11 14:13, kenneth gonsalves wrote: actually I am looking for an sql query. That would typically be done using the st_dwithin function. That function internally makes a bounding box comparison, thereby taking advantage of any geographic indexes you might have, and only then checks the actual distance. I manufactured this SQL, but it selects *every* locality on the map: select name from planet_osm_point where place = 'locality' and ST_DWithin(way,(select way from planet_osm_point where name like 'Bangalore%' limit 1),300) order by name; any pointers? ___ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] all POIs within a specific radius
On 8/2/2011 7:05 AM, kenneth gonsalves wrote: hi, how would I get all POIs within a specific radius (in metres) from a specific point by querying the osm db? If a square rather than a circle is fine, try XAPI: http://wiki.openstreetmap.org/wiki/Xapi . ___ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] all POIs within a specific radius
On 08/02/2011 01:05 PM, kenneth gonsalves wrote: hi, how would I get all POIs within a specific radius (in metres) from a specific point by querying the osm db? Any kind of POI or a certain list of tags? Have you tried the Overpass API? http://wiki.openstreetmap.org/wiki/Overpass_API#Around Paul ___ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] all POIs within a specific radius
On Tue, 2011-08-02 at 07:49 -0400, Nathan Edgars II wrote: On 8/2/2011 7:05 AM, kenneth gonsalves wrote: hi, how would I get all POIs within a specific radius (in metres) from a specific point by querying the osm db? If a square rather than a circle is fine, try XAPI: http://wiki.openstreetmap.org/wiki/Xapi . actually I am looking for an sql query. ___ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] all POIs within a specific radius
On Tue, 2011-08-02 at 14:05 +0200, Paul Hartmann wrote: On 08/02/2011 01:05 PM, kenneth gonsalves wrote: hi, how would I get all POIs within a specific radius (in metres) from a specific point by querying the osm db? Any kind of POI or a certain list of tags? Have you tried the Overpass API? http://wiki.openstreetmap.org/wiki/Overpass_API#Around looks good - the exact query I want is to get all the localities within a radius of 30 kms from a particular point. I will explore this. ___ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] all POIs within a specific radius
Hi, On 08/02/11 14:13, kenneth gonsalves wrote: actually I am looking for an sql query. That would typically be done using the st_dwithin function. That function internally makes a bounding box comparison, thereby taking advantage of any geographic indexes you might have, and only then checks the actual distance. But note that the actual distance is always in projection units, i.e. if you have spherical mercator then it will be approximately meters, but not really meters. If you want proper meters, look up st_distance_sphere and st_distance_spheroid and/or think about converting from geometry to geography data type. Bye Frederik ___ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk
