<html><head><meta http-equiv="Content-Type" content="text/html charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class=""><div class="">Bon.</div><div class=""><br class=""></div><div class="">J’ai tout remis à plat pour repenser le truc et parti de la table planet_osm_point pour que kosmtik / mapnik se concentre sur cette table.</div><div class="">Et ça donne la grosse requête avec x sous-requêtes ci-dessous.</div><div class=""><br class=""></div><div class="">Ça fonctionne très bien sur un dump Bretagne (administrative) et sous kosmtik.</div><div class="">\o/</div><div class=""><br class=""></div><div class="">Mais quand je teste avec kosmtik sur le serveur qui est branché sur la base OSM monde,</div><div class="">j’obtiens un méchant « Postgis Plugin: ERREUR: syntaxe en entrée invalide pour l'entier : « » in getAsyncResult » </div><br class=""><div class="">Je ne sais pas encore pourquoi…</div><div class=""><br class=""></div><div class=""><br class=""></div><div class=""><br class=""></div><div class=""><div class="">-- admin_places</div><div class="">-- on part de la table planet_osm_point qui contient la géométrie</div><div class="">-- et on fait une jointure sur</div><div class="">-- la jointure de la requête sans doublons avec la requête avec doublons MAIS admin_level</div><div class="">SELECT</div><div class=""> p.osm_id, p.way, COALESCE(p.tags -> 'name:br'::text,'???') as name, p.place as type,</div><div class=""> sub_admin.admin_level, sub_admin.admin_name</div><div class="">FROM planet_osm_point AS p</div><div class="">JOIN</div><div class="">(</div><div class=""> SELECT sub_unique.admin_centre_id, sub_unique.admin_level, sub_duplicate.admin_name</div><div class=""> FROM</div><div class=""> (</div><div class=""> -- table sans les niveaux dupliqués : on garde le plus élevé</div><div class=""> SELECT</div><div class=""> sub1.admin_centre_id, MAX(sub1.admin_level) AS admin_level</div><div class=""> FROM</div><div class=""> (</div><div class=""> -- table avec tous les niveaux administratifs cumulés </div><div class=""> -- préfectures</div><div class=""> SELECT</div><div class=""> substring((regexp_matches(members::text, 'n[0-9]*')::text) from 3 for (char_length(regexp_matches(members::text, 'n[0-9]*')::text))-3)::bigint as admin_centre_id,</div><div class=""> '4' as admin_level</div><div class=""> FROM planet_osm_rels WHERE tags::text ~ 'admin_level,6'</div><div class=""> UNION</div><div class=""> -- sous-préfectures</div><div class=""> SELECT</div><div class=""> substring((regexp_matches(members::text, 'n[0-9]*')::text) from 3 for (char_length(regexp_matches(members::text, 'n[0-9]*')::text))-3)::bigint as admin_centre_id,</div><div class=""> '3' as admin_level</div><div class=""> FROM planet_osm_rels WHERE tags::text ~ 'admin_level,7'</div><div class=""> UNION</div><div class=""> -- chefs-lieux de canton</div><div class=""> SELECT</div><div class=""> substring((regexp_matches(members::text, 'n[0-9]*')::text) from 3 for (char_length(regexp_matches(members::text, 'n[0-9]*')::text))-3)::bigint as admin_centre_id,</div><div class=""> '2' as admin_level</div><div class=""> FROM planet_osm_rels WHERE tags::text ~ 'political_division,canton'</div><div class=""> UNION</div><div class=""> -- communes</div><div class=""> SELECT</div><div class=""> substring((regexp_matches(members::text, 'n[0-9]*')::text) from 3 for (char_length(regexp_matches(members::text, 'n[0-9]*')::text))-3)::bigint as admin_centre_id,</div><div class=""> '1' as admin_level</div><div class=""> FROM planet_osm_rels WHERE tags::text ~ 'admin_level,8'</div><div class=""> ) AS sub1 </div><div class=""> GROUP BY sub1.admin_centre_id</div><div class=""> ) AS sub_unique</div><div class=""> LEFT JOIN</div><div class=""> (</div><div class=""> SELECT </div><div class=""> sub2.admin_centre_id, sub2.admin_level, sub2.admin_name</div><div class=""> FROM</div><div class=""> (</div><div class=""> -- table avec tous les niveaux administratifs cumulés </div><div class=""> -- préfectures</div><div class=""> SELECT</div><div class=""> substring((regexp_matches(members::text, 'n[0-9]*')::text) from 3 for (char_length(regexp_matches(members::text, 'n[0-9]*')::text))-3)::bigint as admin_centre_id,</div><div class=""> '4' as admin_level,</div><div class=""> 'préfecture' as admin_name</div><div class=""> FROM planet_osm_rels WHERE tags::text ~ 'admin_level,6'</div><div class=""> UNION</div><div class=""> -- sous-préfectures</div><div class=""> SELECT</div><div class=""> substring((regexp_matches(members::text, 'n[0-9]*')::text) from 3 for (char_length(regexp_matches(members::text, 'n[0-9]*')::text))-3)::bigint as admin_centre_id,</div><div class=""> '3' as admin_level,</div><div class=""> 'sous-préfecture' as admin_name</div><div class=""> FROM planet_osm_rels WHERE tags::text ~ 'admin_level,7'</div><div class=""> UNION</div><div class=""> -- chefs-lieux de canton</div><div class=""> SELECT</div><div class=""> substring((regexp_matches(members::text, 'n[0-9]*')::text) from 3 for (char_length(regexp_matches(members::text, 'n[0-9]*')::text))-3)::bigint as admin_centre_id,</div><div class=""> '2' as admin_level,</div><div class=""> 'chef-lieu de canton' as admin_name</div><div class=""> FROM planet_osm_rels WHERE tags::text ~ 'political_division,canton'</div><div class=""> UNION</div><div class=""> -- communes</div><div class=""> SELECT</div><div class=""> substring((regexp_matches(members::text, 'n[0-9]*')::text) from 3 for (char_length(regexp_matches(members::text, 'n[0-9]*')::text))-3)::bigint as admin_centre_id,</div><div class=""> '1' as admin_level,</div><div class=""> 'commune' as admin_name</div><div class=""> FROM planet_osm_rels WHERE tags::text ~ 'admin_level,8'</div><div class=""> ) AS sub2</div><div class=""> ) AS sub_duplicate</div><div class=""> -- le critère de jointure</div><div class=""> ON sub_unique.admin_centre_id = sub_duplicate.admin_centre_id AND sub_unique.admin_level = sub_duplicate.admin_level</div><div class="">) AS sub_admin</div><div class="">ON p.osm_id = sub_admin.admin_centre_id</div></div><div class=""><br class=""></div><div class=""><br class=""></div><div class=""><br class=""></div><div class=""><br class=""></div><br class=""><div><blockquote type="cite" class=""><div class="">Le 8 oct. 2018 à 22:10, Maël REBOUX <<a href="mailto:osm@breizhpositive.bzh" class="">osm@breizhpositive.bzh</a>> a écrit :</div><br class="Apple-interchange-newline"><div class=""><meta http-equiv="Content-Type" content="text/html charset=utf-8" class=""><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class=""><div class="">Je n’ai toujours pas trouver de solutions malgré différents essais.</div><div class="">J’aimerai éviter de créer une table en dur compliquée à maintenir à jour.</div><div class=""><br class=""></div><br class=""><div class=""><blockquote type="cite" class=""><div class="">Le 28 sept. 2018 à 08:09, Maël REBOUX <<a href="mailto:osm@breizhpositive.bzh" class="">osm@breizhpositive.bzh</a>> a écrit :</div><br class="Apple-interchange-newline"><div class=""><meta http-equiv="Content-Type" content="text/html charset=utf-8" class=""><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class=""><div class=""><div class="">je vois l’idée mais c’est pas encore ça.</div><div class=""><br class=""></div><div class="">…</div><div class=""><div class=""> "table": "( </div><div class="">SELECT DISTINCT way, COALESCE(tags -> 'name:br'::text) as name</div><div class="">FROM planet_osm_point</div><div class=""><div class="">JOIN (</div><div class=""> WITH numbered AS(</div><div class=""> SELECT row_number() OVER() AS row, entry</div><div class=""> FROM(</div><div class=""> SELECT unnest(members) AS entry</div><div class=""> FROM planet_osm_rels</div><div class=""> WHERE ARRAY['boundary','administrative']<@tags AND ARRAY['admin_level','6']<@tags) AS mylist)</div><div class=""> SELECT ltrim(a.entry,'n')::bigint AS osm_id</div><div class=""> FROM numbered AS a JOIN numbered AS b</div><div class=""> ON a.row = b.row-1 AND b.entry = 'admin_centre'</div><div class="">) x</div></div><div class="">USING(osm_id)</div><div class=""> ) AS data",</div><div class=""> <font color="#ff2600" class="">"geometry_table":"numbered",</font></div><div class=""> "key_field": "",</div><div class=""> "geometry_field": "way",</div><div class=""> "asynchronous_request": "true",</div><div class=""> "max_async_connection": "4",</div><div class=""> "simplify_geometries": "true",</div><div class=""> "extent_cache": "auto",</div><div class=""> "extent": "-1363990,3994624,1824475,9411676"</div><div class=""> },</div></div><div class="">…</div><div class=""><br class=""></div><div class="">donne une erreur différente :</div><div class=""><br class=""></div><blockquote class="" style="margin: 0px 0px 0px 40px; border: none; padding: 0px;">Postgis Plugin: ERROR: relation "numbered" does not exist<br class="">LINE 1: SELECT ST_SRID("way") AS srid FROM numbered WHERE "way" IS N...<br class=""> ^<br class="">in executeQuery Full sql was: 'SELECT ST_SRID("way") AS srid FROM numbered WHERE "way" IS NOT NULL LIMIT 1;'<br class=""><br class=""></blockquote><div class="">logique : la géométrie n’est pas dans « numbered » (c’est pour récupérer le admin_level dans la table des relations)</div><div class="">mais dans planet_osm_point</div><div class=""><br class=""></div><div class="">donc on essaie :</div><div class=""><br class=""></div><blockquote class="" style="margin: 0px 0px 0px 40px; border: none; padding: 0px;"><div class=""> "table": "( </div><div class="">SELECT DISTINCT way, COALESCE(tags -> 'name:br'::text) as name</div><div class="">FROM planet_osm_point</div><div class="">JOIN (</div><div class=""> WITH numbered AS(</div><div class=""> SELECT row_number() OVER() AS row, entry</div><div class=""> FROM(</div><div class=""> SELECT unnest(members) AS entry</div><div class=""> FROM planet_osm_rels</div><div class=""> WHERE ARRAY['boundary','administrative']<@tags AND ARRAY['admin_level','8']<@tags) AS mylist)</div><div class=""> SELECT ltrim(a.entry,'n')::bigint AS osm_id</div><div class=""> FROM numbered AS a JOIN numbered AS b</div><div class=""> ON a.row = b.row-1 AND b.entry = 'admin_centre'</div><div class="">) x</div><div class="">USING(osm_id)</div><div class=""> ) AS data",</div><div class=""> "geometry_table":"<b class=""><font color="#ff2600" class="">planet_osm_point</font></b>",</div><div class=""> "key_field": "",</div><div class=""> "geometry_field": "way",</div><div class=""> "asynchronous_request": "true",</div><div class=""> "max_async_connection": "4",</div><div class=""> "simplify_geometries": "true",</div><div class=""> "extent_cache": "auto",</div><div class=""> "extent": "-1363990,3994624,1824475,9411676"</div><div class=""> },</div></blockquote><div class=""><br class=""></div><div class="">et là ça passe : plus d’erreur MAIS je récupère pas de géométrie non plus :(</div></div><br class=""><div style="" class=""><blockquote type="cite" class=""><div class="">Le 27 sept. 2018 à 09:31, Christian Quest <<a href="mailto:cquest@openstreetmap.fr" class="">cquest@openstreetmap.fr</a>> a écrit :</div><br class="Apple-interchange-newline"><div class=""><div dir="ltr" class=""><div dir="ltr" class=""><div class="">Les requêtes SQL sont ré-empaquetées par le driver postgis de mapnik et là je pense qu'il ne sait pas détecter que c'est dans "data" qu'il va trouver "way".</div><div class=""><br class=""></div><div class="">Essaye en ajoutant un paramètre "<span class="gmail-pl-s">geometry_table": "data" voire aussi "geometry_field": "way"<br class=""></span></div><div class=""><span class="gmail-pl-s"><br class=""></span></div><div class=""><span class="gmail-pl-s">Pour l'ensemble des paramètres qu'on peut passer, voir: <a href="https://github.com/mapnik/mapnik/wiki/PostGIS" class="">https://github.com/mapnik/mapnik/wiki/PostGIS</a><br class=""></span></div><div class=""><span class="gmail-pl-s"><br class=""></span></div><div class="">Je pense aussi qu'un && !bbox! quelque part sera peut être nécessaire pour être sûr que la requête soit limitée à l'emprise à rendre...</div><div class=""><br class=""></div></div></div><br class=""><div class="gmail_quote"><div dir="ltr" class="">Le jeu. 27 sept. 2018 à 07:45, Maël REBOUX <<a href="mailto:osm@breizhpositive.bzh" class="">osm@breizhpositive.bzh</a>> a écrit :<br class=""></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div style="word-wrap:break-word" class=""><div class="">J’ai oublié de mettre la déclaration de la couche dans le projet :</div><div class=""><br class=""></div><div class=""><br class=""></div><blockquote style="margin:0 0 0 40px;border:none;padding:0px" class=""><div class=""><div class=""><font face="Courier New" class=""> {</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "id": "places_admin_6",</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "name": "places_admin_6",</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "class": "",</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "Datasource": {</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "type": "postgis",</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "host": "db.openstreetmap.local",</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "user": "osm",</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "password": "osm",</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "dbname": "osm",</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "table": "( </font></div></div><div class=""><div class=""><font face="Courier New" class="">SELECT DISTINCT way, COALESCE(tags -> 'name:br'::text) as name</font></div></div><div class=""><div class=""><font face="Courier New" class="">FROM planet_osm_point</font></div></div><div class=""><div class=""><font face="Courier New" class="">JOIN (</font></div></div><div class=""><div class=""><font face="Courier New" class=""> WITH c AS(</font></div></div><div class=""><div class=""><font face="Courier New" class=""> SELECT row_number() OVER() AS row, entry</font></div></div><div class=""><div class=""><font face="Courier New" class=""> FROM(</font></div></div><div class=""><div class=""><font face="Courier New" class=""> SELECT unnest(members) AS entry</font></div></div><div class=""><div class=""><font face="Courier New" class=""> FROM planet_osm_rels</font></div></div><div class=""><div class=""><font face="Courier New" class=""> WHERE ARRAY['boundary','administrative']<@tags AND ARRAY['admin_level','6']<@tags) AS mylist)</font></div></div><div class=""><div class=""><font face="Courier New" class=""> SELECT ltrim(a.entry,'n')::bigint AS osm_id</font></div></div><div class=""><div class=""><font face="Courier New" class=""> FROM c AS a JOIN c AS b</font></div></div><div class=""><div class=""><font face="Courier New" class=""> ON a.row = b.row-1 AND b.entry = 'admin_centre'</font></div></div><div class=""><div class=""><font face="Courier New" class="">) x</font></div></div><div class=""><div class=""><font face="Courier New" class="">USING(osm_id)</font></div></div><div class=""><div class=""><font face="Courier New" class=""> ) AS data",</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "key_field": "",</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "geometry_field": "way",</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "asynchronous_request": "true",</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "max_async_connection": "4",</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "simplify_geometries": "true",</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "extent_cache": "auto",</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "extent": "-1363990,3994624,1824475,9411676"</font></div></div><div class=""><div class=""><font face="Courier New" class=""> },</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "geometry": "point",</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "srs-name": "3857",</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "srs": "+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0.0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over",</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "extent": [ -10, 34, 20, 70 ],</font></div></div><div class=""><div class=""><font face="Courier New" class=""> "advanced": {}</font></div></div><div class=""><div class=""><font face="Courier New" class=""> }</font></div></div></blockquote><div class=""><br class=""></div><br class=""><div class=""><blockquote type="cite" class=""><div class="">Le 27 sept. 2018 à 00:10, Maël REBOUX <<a href="mailto:osm@breizhpositive.bzh" target="_blank" class="">osm@breizhpositive.bzh</a>> a écrit :</div><br class="m_-6982431379115045215Apple-interchange-newline"><div class=""><div style="word-wrap:break-word" class="">Bonjour tous,<div class=""><br class=""></div><div class="">Je cherche à faire apparaître sur une carte en ligne les préfectures et leur nom en breton.</div><div class=""><br class=""></div><div class="">La façon d’y arriver est « connue » : il faut faire une jointure entre la table planet_osm_point (qui contient le point et le nom) et la table planet_osm_rels (qui elle contient l’info admin_level).</div><div class=""><br class=""></div><div class="">Cela donne une requête qui s’exécute très bien en temps normal (pgAdmin ou dans une vue ou une requête d’insertion de données / create table) :</div><div class=""><br class=""></div><blockquote style="margin:0 0 0 40px;border:none;padding:0px" class=""><div class=""><font face="Courier New" class="">SELECT DISTINCT way, COALESCE(tags -> 'name:br'::text) as name</font></div><div class=""><font face="Courier New" class="">FROM planet_osm_point</font></div><div class=""><font face="Courier New" class="">JOIN (</font></div><div class=""><font face="Courier New" class=""> WITH numbered AS(</font></div><div class=""><font face="Courier New" class=""> SELECT row_number() OVER() AS row, entry</font></div><div class=""><font face="Courier New" class=""> FROM(</font></div><div class=""><font face="Courier New" class=""> SELECT unnest(members) AS entry</font></div><div class=""><font face="Courier New" class=""> FROM planet_osm_rels</font></div><div class=""><font face="Courier New" class=""> WHERE ARRAY['boundary','administrative']<@tags AND ARRAY['admin_level','6']<@tags) AS mylist)</font></div><div class=""><font face="Courier New" class=""> SELECT ltrim(a.entry,'n')::bigint AS osm_id</font></div><div class=""><font face="Courier New" class=""> FROM numbered AS a JOIN numbered AS b</font></div><div class=""><font face="Courier New" class=""> ON a.row = b.row-1 AND b.entry = 'admin_centre'</font></div><div class=""><font face="Courier New" class="">) x</font></div><div class=""><font face="Courier New" class="">USING(osm_id);</font></div></blockquote><div class=""><br class=""></div><div class="">source : <a href="https://dba.stackexchange.com/questions/104943/osm2pgsql-select-relation-member-by-role" target="_blank" class="">https://dba.stackexchange.com/questions/104943/osm2pgsql-select-relation-member-by-role</a></div><div class=""><br class=""></div><div class=""><br class=""></div><div class="">Mais si je mets cette requête dans une déclaration de couche pour un projet mml servi par kosmtik j’obtiens une erreur dans kosmtik :</div><div class=""><br class=""></div><blockquote style="margin:0 0 0 40px;border:none;padding:0px" class=""><div class=""><font face="Courier New" class="">Postgis Plugin: ERROR: relation "numbered" does not exist
LINE 1: SELECT ST_SRID("way") AS srid FROM numbered WHERE "way" IS N...
^
in executeQuery Full sql was: 'SELECT ST_SRID("way") AS srid FROM numbered WHERE "way" IS NOT NULL LIMIT 1;'
encountered during parsing of layer 'places_admin_6' in Layer</font></div></blockquote><div class=""><br class=""></div><div class="">En loggant l’erreur dans PostgreSQL :</div><div class=""><br class=""></div><blockquote style="margin:0 0 0 40px;border:none;padding:0px" class=""><div class=""><div class=""><font face="Courier New" class="">2018-09-26 23:24:56.797 CEST [32589] STATEMENT: SELECT ST_SRID("way") AS srid FROM numbered WHERE "way" IS NOT NULL LIMIT 1;</font></div></div><div class=""><div class=""><font face="Courier New" class="">2018-09-26 23:24:56.798 CEST [32590] LOG: statement: SELECT ST_SRID("way") AS srid FROM numbered WHERE "way" IS NOT NULL LIMIT 1;</font></div></div><div class=""><div class=""><font face="Courier New" class="">2018-09-26 23:24:56.798 CEST [32590] ERROR: relation "numbered" does not exist at character 36</font></div></div></blockquote><div class=""><br class=""></div><div class="">Je n’arrive pas à cerner le problème. Bien sûr si on change le nom « numbered » par autre chose, le message d’erreur fera référence à ce nouveau nom.</div><div class="">J’ai pas testé directement sur un serveur de tuiles mais je vois pas pourquoi ça passerait.</div><div class="">Si quelqu’un a une piste… </div><div class=""><br class=""></div><div class="">Cdt, Maël evit osm-bzh</div><div class=""><br class=""></div><div class=""><br class=""></div><div class=""><br class=""></div><div class=""><br class=""></div><div class=""><br class=""></div><div class=""><br class=""></div></div></div></blockquote></div></div></blockquote></div></div></blockquote></div></div></div></blockquote></div></div></div></blockquote></div><br class=""></body></html>