[OSM-talk-fr] kosmtik + requête PostGIS exploitant une relation = ERROR
Maël REBOUX
osm at breizhpositive.bzh
Dim 28 Oct 20:30:12 UTC 2018
Bon.
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.
Et ça donne la grosse requête avec x sous-requêtes ci-dessous.
Ça fonctionne très bien sur un dump Bretagne (administrative) et sous kosmtik.
\o/
Mais quand je teste avec kosmtik sur le serveur qui est branché sur la base OSM monde,
j’obtiens un méchant « Postgis Plugin: ERREUR: syntaxe en entrée invalide pour l'entier : « » in getAsyncResult »
Je ne sais pas encore pourquoi…
-- admin_places
-- on part de la table planet_osm_point qui contient la géométrie
-- et on fait une jointure sur
-- la jointure de la requête sans doublons avec la requête avec doublons MAIS admin_level
SELECT
p.osm_id, p.way, COALESCE(p.tags -> 'name:br'::text,'???') as name, p.place as type,
sub_admin.admin_level, sub_admin.admin_name
FROM planet_osm_point AS p
JOIN
(
SELECT sub_unique.admin_centre_id, sub_unique.admin_level, sub_duplicate.admin_name
FROM
(
-- table sans les niveaux dupliqués : on garde le plus élevé
SELECT
sub1.admin_centre_id, MAX(sub1.admin_level) AS admin_level
FROM
(
-- table avec tous les niveaux administratifs cumulés
-- préfectures
SELECT
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,
'4' as admin_level
FROM planet_osm_rels WHERE tags::text ~ 'admin_level,6'
UNION
-- sous-préfectures
SELECT
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,
'3' as admin_level
FROM planet_osm_rels WHERE tags::text ~ 'admin_level,7'
UNION
-- chefs-lieux de canton
SELECT
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,
'2' as admin_level
FROM planet_osm_rels WHERE tags::text ~ 'political_division,canton'
UNION
-- communes
SELECT
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,
'1' as admin_level
FROM planet_osm_rels WHERE tags::text ~ 'admin_level,8'
) AS sub1
GROUP BY sub1.admin_centre_id
) AS sub_unique
LEFT JOIN
(
SELECT
sub2.admin_centre_id, sub2.admin_level, sub2.admin_name
FROM
(
-- table avec tous les niveaux administratifs cumulés
-- préfectures
SELECT
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,
'4' as admin_level,
'préfecture' as admin_name
FROM planet_osm_rels WHERE tags::text ~ 'admin_level,6'
UNION
-- sous-préfectures
SELECT
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,
'3' as admin_level,
'sous-préfecture' as admin_name
FROM planet_osm_rels WHERE tags::text ~ 'admin_level,7'
UNION
-- chefs-lieux de canton
SELECT
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,
'2' as admin_level,
'chef-lieu de canton' as admin_name
FROM planet_osm_rels WHERE tags::text ~ 'political_division,canton'
UNION
-- communes
SELECT
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,
'1' as admin_level,
'commune' as admin_name
FROM planet_osm_rels WHERE tags::text ~ 'admin_level,8'
) AS sub2
) AS sub_duplicate
-- le critère de jointure
ON sub_unique.admin_centre_id = sub_duplicate.admin_centre_id AND sub_unique.admin_level = sub_duplicate.admin_level
) AS sub_admin
ON p.osm_id = sub_admin.admin_centre_id
> Le 8 oct. 2018 à 22:10, Maël REBOUX <osm at breizhpositive.bzh> a écrit :
>
> Je n’ai toujours pas trouver de solutions malgré différents essais.
> J’aimerai éviter de créer une table en dur compliquée à maintenir à jour.
>
>
>> Le 28 sept. 2018 à 08:09, Maël REBOUX <osm at breizhpositive.bzh <mailto:osm at breizhpositive.bzh>> a écrit :
>>
>> je vois l’idée mais c’est pas encore ça.
>>
>> …
>> "table": "(
>> SELECT DISTINCT way, COALESCE(tags -> 'name:br'::text) as name
>> FROM planet_osm_point
>> JOIN (
>> WITH numbered AS(
>> SELECT row_number() OVER() AS row, entry
>> FROM(
>> SELECT unnest(members) AS entry
>> FROM planet_osm_rels
>> WHERE ARRAY['boundary','administrative']<@tags AND ARRAY['admin_level','6']<@tags) AS mylist)
>> SELECT ltrim(a.entry,'n')::bigint AS osm_id
>> FROM numbered AS a JOIN numbered AS b
>> ON a.row = b.row-1 AND b.entry = 'admin_centre'
>> ) x
>> USING(osm_id)
>> ) AS data",
>> "geometry_table":"numbered",
>> "key_field": "",
>> "geometry_field": "way",
>> "asynchronous_request": "true",
>> "max_async_connection": "4",
>> "simplify_geometries": "true",
>> "extent_cache": "auto",
>> "extent": "-1363990,3994624,1824475,9411676"
>> },
>> …
>>
>> donne une erreur différente :
>>
>> 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;'
>>
>> logique : la géométrie n’est pas dans « numbered » (c’est pour récupérer le admin_level dans la table des relations)
>> mais dans planet_osm_point
>>
>> donc on essaie :
>>
>> "table": "(
>> SELECT DISTINCT way, COALESCE(tags -> 'name:br'::text) as name
>> FROM planet_osm_point
>> JOIN (
>> WITH numbered AS(
>> SELECT row_number() OVER() AS row, entry
>> FROM(
>> SELECT unnest(members) AS entry
>> FROM planet_osm_rels
>> WHERE ARRAY['boundary','administrative']<@tags AND ARRAY['admin_level','8']<@tags) AS mylist)
>> SELECT ltrim(a.entry,'n')::bigint AS osm_id
>> FROM numbered AS a JOIN numbered AS b
>> ON a.row = b.row-1 AND b.entry = 'admin_centre'
>> ) x
>> USING(osm_id)
>> ) AS data",
>> "geometry_table":"planet_osm_point",
>> "key_field": "",
>> "geometry_field": "way",
>> "asynchronous_request": "true",
>> "max_async_connection": "4",
>> "simplify_geometries": "true",
>> "extent_cache": "auto",
>> "extent": "-1363990,3994624,1824475,9411676"
>> },
>>
>> et là ça passe : plus d’erreur MAIS je récupère pas de géométrie non plus :(
>>
>>> Le 27 sept. 2018 à 09:31, Christian Quest <cquest at openstreetmap.fr <mailto:cquest at openstreetmap.fr>> a écrit :
>>>
>>> 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".
>>>
>>> Essaye en ajoutant un paramètre "geometry_table": "data" voire aussi "geometry_field": "way"
>>>
>>> Pour l'ensemble des paramètres qu'on peut passer, voir: https://github.com/mapnik/mapnik/wiki/PostGIS <https://github.com/mapnik/mapnik/wiki/PostGIS>
>>>
>>> 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...
>>>
>>>
>>> Le jeu. 27 sept. 2018 à 07:45, Maël REBOUX <osm at breizhpositive.bzh <mailto:osm at breizhpositive.bzh>> a écrit :
>>> J’ai oublié de mettre la déclaration de la couche dans le projet :
>>>
>>>
>>> {
>>> "id": "places_admin_6",
>>> "name": "places_admin_6",
>>> "class": "",
>>> "Datasource": {
>>> "type": "postgis",
>>> "host": "db.openstreetmap.local",
>>> "user": "osm",
>>> "password": "osm",
>>> "dbname": "osm",
>>> "table": "(
>>> SELECT DISTINCT way, COALESCE(tags -> 'name:br'::text) as name
>>> FROM planet_osm_point
>>> JOIN (
>>> WITH c AS(
>>> SELECT row_number() OVER() AS row, entry
>>> FROM(
>>> SELECT unnest(members) AS entry
>>> FROM planet_osm_rels
>>> WHERE ARRAY['boundary','administrative']<@tags AND ARRAY['admin_level','6']<@tags) AS mylist)
>>> SELECT ltrim(a.entry,'n')::bigint AS osm_id
>>> FROM c AS a JOIN c AS b
>>> ON a.row = b.row-1 AND b.entry = 'admin_centre'
>>> ) x
>>> USING(osm_id)
>>> ) AS data",
>>> "key_field": "",
>>> "geometry_field": "way",
>>> "asynchronous_request": "true",
>>> "max_async_connection": "4",
>>> "simplify_geometries": "true",
>>> "extent_cache": "auto",
>>> "extent": "-1363990,3994624,1824475,9411676"
>>> },
>>> "geometry": "point",
>>> "srs-name": "3857",
>>> "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",
>>> "extent": [ -10, 34, 20, 70 ],
>>> "advanced": {}
>>> }
>>>
>>>
>>>> Le 27 sept. 2018 à 00:10, Maël REBOUX <osm at breizhpositive.bzh <mailto:osm at breizhpositive.bzh>> a écrit :
>>>>
>>>> Bonjour tous,
>>>>
>>>> Je cherche à faire apparaître sur une carte en ligne les préfectures et leur nom en breton.
>>>>
>>>> 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).
>>>>
>>>> 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) :
>>>>
>>>> SELECT DISTINCT way, COALESCE(tags -> 'name:br'::text) as name
>>>> FROM planet_osm_point
>>>> JOIN (
>>>> WITH numbered AS(
>>>> SELECT row_number() OVER() AS row, entry
>>>> FROM(
>>>> SELECT unnest(members) AS entry
>>>> FROM planet_osm_rels
>>>> WHERE ARRAY['boundary','administrative']<@tags AND ARRAY['admin_level','6']<@tags) AS mylist)
>>>> SELECT ltrim(a.entry,'n')::bigint AS osm_id
>>>> FROM numbered AS a JOIN numbered AS b
>>>> ON a.row = b.row-1 AND b.entry = 'admin_centre'
>>>> ) x
>>>> USING(osm_id);
>>>>
>>>> source : https://dba.stackexchange.com/questions/104943/osm2pgsql-select-relation-member-by-role <https://dba.stackexchange.com/questions/104943/osm2pgsql-select-relation-member-by-role>
>>>>
>>>>
>>>> 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 :
>>>>
>>>> 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
>>>>
>>>> En loggant l’erreur dans PostgreSQL :
>>>>
>>>> 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;
>>>> 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;
>>>> 2018-09-26 23:24:56.798 CEST [32590] ERROR: relation "numbered" does not exist at character 36
>>>>
>>>> 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.
>>>> J’ai pas testé directement sur un serveur de tuiles mais je vois pas pourquoi ça passerait.
>>>> Si quelqu’un a une piste…
>>>>
>>>> Cdt, Maël evit osm-bzh
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
-------------- section suivante --------------
Une pièce jointe HTML a été nettoyée...
URL: <http://lists.openstreetmap.org/pipermail/talk-fr/attachments/20181028/74477e3d/attachment.htm>
Plus d'informations sur la liste de diffusion Talk-fr