Re: [OSM-dev-fr] utilisation psql et fichier sql
On lundi 19 mars 2012, didier2020 wrote: j'ai enregistré la requete dans un fichier mais quand j'utilise psql -d osm -U didier -f monfichier.sql j'ai un message erreur : erreur de syntaxe sur ou près de « SELECT » Tu peux nous faire passer ton fichier monfichier.sql ? Peut-être a-t-il été éditer avec un éditeur de texte foireux -- sly qui suis-je : http://sly.letuffe.org email perso : sylvain chez letuffe un point org ___ dev-fr mailing list dev-fr@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev-fr
Re: [OSM-dev-fr] utilisation psql et fichier sql
Le lundi 19 mars 2012 à 17:01 +0100, sly (sylvain letuffe) a écrit : On lundi 19 mars 2012, didier2020 wrote: j'ai enregistré la requete dans un fichier mais quand j'utilise psql -d osm -U didier -f monfichier.sql j'ai un message erreur : erreur de syntaxe sur ou près de « SELECT » Tu peux nous faire passer ton fichier monfichier.sql ? Peut-être a-t-il été éditer avec un éditeur de texte foireux pgadmin ! j'ai fait un copier coller du contenu vers gedit et ça fonctionne. pgadmin met un . en debut du fichier merci a vous 2. didier SELECT b1.osm_id AS id1, b2.osm_id AS id2, st_area(ST_Intersection(b1.way, b2.way)) as SurfaceInt, st_area(b1.way) as Surface1, st_area(b2.way) as Surface2 FROM planet_osm_polygon AS b1, planet_osm_polygon AS b2 WHERE st_isvalid(b1.way)='t' and st_isvalid(b2.way)='t' and b1.building'no' and b2.building'no' and b1.osm_idb2.osm_id AND st_intersects(b1.way, b2.way)='t' AND st_area(ST_Intersection(b1.way, b2.way))0; SELECT b1.osm_id AS id1, b2.osm_id AS id2, st_area(ST_Intersection(b1.way, b2.way)) as SurfaceInt, st_area(b1.way) as Surface1, st_area(b2.way) as Surface2 FROM planet_osm_polygon AS b1, planet_osm_polygon AS b2 WHERE st_isvalid(b1.way)='t' and st_isvalid(b2.way)='t' and b1.building'no' and b2.building'no' and b1.osm_idb2.osm_id AND st_intersects(b1.way, b2.way)='t' AND st_area(ST_Intersection(b1.way, b2.way))0; ___ dev-fr mailing list dev-fr@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev-fr
Re: [Potlatch-dev] [OpenStreetMap] #4307: Weird behavior when changing tags (ie name_1=* to alt_name=*)
#4307: Weird behavior when changing tags (ie name_1=* to alt_name=*) +--- Reporter: compdude | Owner: potlatch-dev@… Type: defect | Status: closed Priority: minor | Milestone: Component: potlatch2 | Version: Resolution: invalid|Keywords: weird, behavior, tag, change, advanced +--- Comment(by Richard): The TagGrid tab/enter behaviour has been fixed up really recently (in the past three days) so I guess it's possible that you caught an un-updated version of Potlatch first time round. -- Ticket URL: https://trac.openstreetmap.org/ticket/4307#comment:6 OpenStreetMap http://www.openstreetmap.org/ OpenStreetMap is a free editable map of the whole world ___ Potlatch-dev mailing list Potlatch-dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/potlatch-dev
Re: [OSM-dev] osm2pgsql diff imports benchmarks
On 3/18/2012 4:07 PM, Kai Krueger wrote: sylvain letuffe wrote It turned out that the index on the pending field wasn't used at all and led to a full scan of the planet_osm_ways table, increasing noticeably the diff import. I think I have seen that one before as well. If I remember correctly, it was sufficient to run a simple analyze on the table. No need to do a re-index. It probably thinks a too large proportion of the ways are pending and therefore decides it is best to do a seq scan. Possibly because before the going over pending ways stage about 50% of ways are pending during the import. Osm2pgsql should be doing a analyze at the end of the import though, so I am not sure why this is happening. I have a planet database that has been imported and has been attempting to catch up for a while now (3 days to go) and it's been showing extreme data throughput spikes when entering the pending ways phase, so I decided to check this out. An analyze command show that a sequential scan is, in fact, being done: gis= explain select id from planet_osm_ways where pending; Seq Scan on planet_osm_ways (cost=0.00..5253263.74 rows=65592587 width=4) Filter: pending I'm not sure why, but I got an error attempting to analyze verbose planet_osm_ways as www-data, but it seems to execute as the postgres user: gis= analyze verbose planet_osm_ways; WARNING: skipping planet_osm_ways --- only table or database owner can analyze it The analyze output really didn't tell me much: gis=# analyze verbose planet_osm_ways; INFO: analyzing public.planet_osm_ways INFO: planet_osm_ways: scanned 3 of 3941416 pages, containing 983808 live rows and 589826 dead rows; 3 rows in sample, 131170601 estimated total rows But a subsequent explain certainly looks promising: gis= explain select id from planet_osm_ways where pending; Index Scan using planet_osm_ways_idx on planet_osm_ways (cost=0.00..916513.78 rows=43724 width=4) Now to wait for my next 12 hour catchup chunk to get to the pending ways phase,probably in about 6 hours or so. Lynn (D) - KJ4ERJ PS. If anyone gets this far, are those dead rows a function of my running with autovaccuum off? And should I be doing a periodic vaccuum to clean out accumulated cruft? I was thinking that not too much in the osm planet would be deleting, but maybe my assumption is incorrect? ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] osm2pgsql diff imports benchmarks
Am 19.03.2012 12:37, schrieb Lynn W. Deffenbaugh (Mr): And should I be doing a periodic vaccuum to clean out accumulated cruft? I was thinking that not too much in the osm planet would be deleting, but maybe my assumption is incorrect? Yes and yes. osm2pgsql does not update rows, it deletes them and inserts the new version. You really need to run vacuum at least once per week, probably more right now. Simon ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] osm2pgsql diff imports benchmarks
gis= explain select id from planet_osm_ways where pending; Index Scan using planet_osm_ways_idx on planet_osm_ways (cost=0.00..916513.78 rows=43724 width=4) This is what I've got after a re-index : gis=# explain select id from planet_osm_ways where pending; QUERY PLAN -- Index Scan using planet_osm_ways_idx on planet_osm_ways (cost=0.00..1157.47 rows=1 width=4) (1 ligne) and that saves me around ~20 seconds per minute diff -- sly qui suis-je : http://sly.letuffe.org email perso : sylvain chez letuffe un point org ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Google Summer of Code
Hi, Thank you for your interest in applying for GSoC with Openstreetmap. This list will be fine to ask questions. Here are a few suggestions to get you started: - It is important to understand the fundamentals of what OSM is, so if you have not done so before, please start by creating an account and making some improvements to the map in your local area. - It would also be good to look at the OSM data structure. Details of the xml file format can be found on our wiki. - If you search for Nominatim on the OSM wiki you should find some information on the current service and links to the source code to see how it currently does searching to see how it could be improved. The project idea was suggested by 'sabas88' - could he/she provide some more information on the issues behind this project suggestion please? Hope that helps. Please feel free to ask more questions as you develop your proposal. Regards Graham On 19 March 2012 21:28, kabum uu.ka...@gmail.com wrote: Hi, I am interested in Nominatim (or alternative), but there isn't any mentor mentioned. Where could I discuss about the idea? Best regards, Morris Jobke ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev -- Graham Jones Hartlepool, UK. ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
