Forums PostgreSQL.fr

ramsestom

Membre

Réaliser une requete avec une fonction personalisée

Bonjour a tous
 
    Je débute avec Postgresql et j'aurai besoin de réaliser une requête avec une clause WHERE  utilisant une fonction personnalisée.
J'ai dans ma base une table "zones" définie avec les colonnes suivantes:

zones {
    id: integer
    boxTopLat: double
    boxBotLat: double
    boxLeftLng: double
    boxRightLng: double
    center: point
    radius: double
}

et j'aurais voulu faire une requete dessus du type SELECT * FROM zones WHERE Overlap(queryzone)

ou overlap(zone) est une fonction permettant de tester si deux zones s'overlappent et à laquelle on passe une zone query qu'elle va comparer aux zones enregistrées dans ma table "zones".

Basiquement ce que je cherche a faire est de pouvoir chercher le plus rapidement possible si deux aires géographiques circulaires (définie par un centre et un rayon) s'overlappent et appliquer ce filtre sur mes requêtes. Je ne peut pas utiliser les opérateurs géométriques déja définis dans Postgresql (car je travaille sur des zones géographiques qui peuvent êtres définies a l'échelle mondiale et je ne peux donc pas utiliser de projection globale) et Postgis ne défini malheureusement pas de géométrie de type cercle (il faut les approximer avec des polygones et regarder si deux polygones s'overlappent est relativement coûteux en CPU). L'idée est donc de définir pour chacune de mes zones circulaires une boundingBox rectangulaire et de rechercher très rapidement si les boundingBox de deux zones s'overlappent. Si oui, je peut alors calculer la distance entre les deux centres de ces zones (plus coûteux que de chercher un overlap entre deux rectangles) et regarder si elle est inférieure à la somme des deux rayons de ces zones (auquel cas ces zones s'overlappent bien)
Le pseudo code de la fonction Overlap est donc du type:

Overlap(queryZone, targetZone)
{
    if ( (queryZone.boxLeftLng < targetZone.boxLeftLng && queryZone.boxLeftLng < targetZone.boxRightLng && queryZone.boxRightLng < targetZone.boxLeftLng && queryZone.boxRightLng < targetZone.boxRightLng)
         || (queryZone.boxLeftLng > targetZone.boxLeftLng && queryZone.boxLeftLng > targetZone.boxRightLng && queryZone.boxRightLng > targetZone.boxLeftLng && queryZone.boxRightLng > targetZone.boxRightLng) )
    {
         return false;
    }

     if ( (queryZone.boxTopLat < targetZone.boxTopLat && queryZone.boxTopLat < targetZone.boxBotLat && queryZone.boxBotLat < targetZone.boxTopLat && queryZone.boxBotLat < targetZone.boxBotLat)
         || (queryZone.boxTopLat > targetZone.boxTopLat && queryZone.boxTopLat > targetZone.boxBotLat && queryZone.boxBotLat > targetZone.boxTopLat && queryZone.boxBotLat > targetZone.boxBotLat) )
    {
         return false;
    }

   
    //les boundingbox s'overlappent
   
   if (distance(queryZone.center, targetZone.center)<(queryZone.radius+targetZone.radius)) {  //les deux zones circulaires s'overlappent
        return true;
   }
   //sinon c'est que les deux zones circulaires ne s'overlappent pas
    return false; 

}

malheureusement je ne sait pas comment définir ce type de fonction custom à injecter dans une requete.

J'ai regardé un peu la doc, entre les "query language functions", les "procedural language functions" et les "C-language functions" mais ça ne semble pas exactement fait pour ce genre de cas. Donc si quelqu'un pouvait m'aider et m'orienter dans la bonne direction ça serait super sympa...

Merci d'avance.

Dernière modification par ramsestom (31/05/2017 00:50:53)

Marc Cousin

Membre

Re : Réaliser une requete avec une fonction personalisée

Le mieux, c'est probablement de commencer en PLPgSQL. Le langage est simple d'accès, et devrait te permettre d'écrire ta fonction.

https://www.postgresql.org/docs/9.6/static/plpgsql.htm

L'écrire d'emblée en C, c'est vraiment beaucoup de travail.

---

Marc.

rjuju

Administrateur

Re : Réaliser une requete avec une fonction personalisée

Bonjour,

Tout d'abord je ne suis pas spécialiste de postgis, mais je pense que cela vaudrait la peine de tester les performances dans votre cas.  En effet, le calcul exact d'intersection entre deux polygones n'est effectué que sur les enregistrements pour lesquels il n'y avait pas d'intersection des bounding box respectives, et cette première vérification est bien évidemment très rapide, qui plus est peut être effectuée via un parcours d'index.  Dans votre cas, pour bénéficier d'un parcours d'index il va probablement falloir ruser et définir un index fonctionnel, ce qui va complexifier la mise en place ainsi que les requêtes.

Sinon, si vous restez sur votre solution je vous conseillerais de définir un type custom contenant la définition de votre géométrie (concrètement tous les champs sauf id), et de l'utiliser à la fois dans votre table et votre fonction.  Quelque chose comme :

CREATE TYPE circle_geom AS (boxTopLat float8, boxBotLat float8...);

CREATE TABLE zones (
    id integer,
    geom circle_geom
);

Vous pouvez ensuite utiliser ce type dans la définition de votre fonction, et en créer un dynamiquement, avec une syntaxe comme :

'(1.1, 1.1...)'::circle_geom

Pour les détails de syntaxe sur la création de la fonction : http://docs.postgresql.fr/9.6/plpgsql.html et plus précisément http://docs.postgresql.fr/9.6/plpgsql-declarations.html

---

Julien.
https://rjuju.github.io/

ramsestom

Membre

Re : Réaliser une requete avec une fonction personalisée

Merci pour vos réponses. Je pensais effectivement définir un type custom pour ma géographie de type "Circle" comme conseillé par rjuju. J'ai regardé du coté de postgis et effectivement il y a d'abord une recherche des chevauchements sur les boundingbox des deux géométries mais ces boudingbox sont définies par 4 points (avec chacun un x et y) alors que moi je sais que mes boundingbox seront forcément rectangulaires, donc je n'ai que 4 variables a stoquer ( minX, minY, maxX, maxY) et ma recherche de chevauchement des boundingbox peut etre plus rapide et l'index pour chaque borne des ces bounding box prendre moins de place en mémoire que si j'utilisais postgis. Qui plus est, le calcul de la distance entre les centres de mes deux cercles est aussi nettement plus rapide que de calculer l'intersection entre des polygones.  Bref, je ne pense pas utiliser postgis car je cherche à avoir la meilleure performance possible pour ce cas précis.
Ce qui m’amène à la seconde question: est-ce qu'une fonction écrite en C sera significativement plus rapide qu'en PLPgSQL (ici il n'y a pas de calcul réellement complexe, que des comparaisons <, > et un calcul de distance qui fait intervenir une racine carrée et des multiplication, additions mais qui ne sera effectuée que sur le sous-set d'objets ayant des boundingbox qui se chevauchent)? Si oui, autant partir directement sur la création de la fonction en C plutôt que d'avoir a effectuer le travail 2 fois...

Dernière modification par ramsestom (31/05/2017 17:43:37)

ramsestom

Membre

Re : Réaliser une requete avec une fonction personalisée

Bon. Pour l'instant j'ai choisit l'option simple de définir un type custom avec une boundingbox rectangulaire créée en tant que POLYGON potgis et avec un centre de type postgis POINT et un rayon de type double. De cette façon je pourrais utiliser les fonctions ST_OVERLAP et ST_Distance déja implémentées dans postgis et dévinir ma propre fonction PLPgSQL circle_overlap faisant appel a ces deux fonctions sur mes objets circle custom ne devrait pas etre très compliqué. Je prendrais du temps pour refaire tout ça en natif avec une fonction C si réellement j'ai des soucis de performances...
Par contre j'ai une question, j'ai pour l'instant defini mon type custom de cette façon:

CREATE TYPE circle_geo AS (
       box geography(POLYGON,4326),
       center geography(POINT,4326),
       radius float8
);

mais j'ai vu que CREATE TYPE pouvait prendre une input_function et une output_function. L'idéal serait donc que je ne fournisse en entrée que center et radius et que box soit inféré automatiquement dans la fonction input_function (et soit eventuellement retiré dans output_function). Comment ferait-on cela?

Dernière modification par ramsestom (31/05/2017 21:12:53)

rjuju

Administrateur

Re : Réaliser une requete avec une fonction personalisée

Les boundings box d'objets 2d sont forcément rectangulaires, et donc bien définies par 2 points. Cf https://github.com/postgis/postgis/blob … .h#L41-L52

Par contre, si vous cherchez à afficher la bouding box via postgis, par exemple avec st_astext(st_envelope(geometry)), vous aurez une geometry représentant la bouding box, donc 5 points (les géométries sont forcément fermées).  Cela ne change pas la représentation interne ni l'espace utilisé etc.

La fonction en C sera beaucoup plus rapide, mais plus complexe à écrire aussi, et potentiellement plus dangereuse (un segfault est vite arrivé).

Pour votre autre question, avec postgis pour l'utilisateur tout est défini comme un type geometry.  Vous pouvez y stocker ce que vous voulez (des points, des lignes, des polygones, des polygones 3d...), à l'aide des fonctions st_make* par exemple.

Si vous voulez vraiment partir sur la solution hardue, vous pouvez vous inspirer de ce support de conférence qui devrait répondre à beaucoup de questions : https://wiki.postgresql.org/images/1/11 … d_type.pdf

À voir ensuite comment pouvoir faire cohabiter votre type en suplément des objets postgis.

---

Julien.
https://rjuju.github.io/

ramsestom

Membre

Re : Réaliser une requete avec une fonction personalisée

Bon. Pour l'instant j'ai choisit l'option simple de définir un type custom avec une boundingbox rectangulaire créée en tant que POLYGON potgis et avec un centre de type postgis POINT et un rayon de type double. De cette façon je pourrais utiliser les fonctions ST_OVERLAP et ST_Distance déja implémentées dans postgis et dévinir ma propre fonction PLPgSQL circle_overlap faisant appel a ces deux fonctions sur mes objets circle custom ne devrait pas etre très compliqué. Je prendrais du temps pour refaire tout ça en natif avec une fonction C si réellement j'ai des soucis de performances...
Par contre j'ai une question, j'ai pour l'instant defini mon type custom de cette façon:

CREATE TYPE circle_geo AS (
       box geography(POLYGON,4326), 
       center geography(POINT,4326), 
       radius float8
);

mais j'ai vu que CREATE TYPE pouvait prendre une input_function et une output_function. L'idéal serait donc que je ne fournisse en entrée que center et radius et que box soit inféré automatiquement dans la fonction input_function (et soit eventuellement retiré dans output_function). Comment ferait-on cela?

Basiquement, j'aimerais que ma fonction input_function prennent en entrée un représentation au format (x,y),r (comme le type 'circle' de postgresql geometry) et applique la transformation suivante pour creer l'objet:

radius = r;
center = ST_MakePoint(x,y);
pdist = sqrt(2*radius*radius);
box = ST_MakePolygon(ST_MakeLine(ARRAY[
				ST_Project(center, pdist, radians(45.0)),
				ST_Project(center, pdist, radians(135.0)),
				ST_Project(center, pdist, radians(-135.0)),
				ST_Project(center, pdist, radians(-45.0)),
				ST_Project(center, pdist, radians(45.0))
	]))

mais je n'ai aucune idée de la syntaxe à appliquer à cette fonction.

Dernière modification par ramsestom (31/05/2017 22:33:54)

ramsestom

Membre

Re : Réaliser une requete avec une fonction personalisée

Bon en fait j'avais mal lu la doc sur CREATE TYPE. Comme je créé un type composite, je ne peut pas utiliser des fonction d'input/output. J'ai simplement a créer une fonction plpgsql qui créera une instance de ce type a partir des parametres fournis en argument.
Du coup ma fonction plpgsql devrait ressembler à ça:

CREATE FUNCTION circle_geo_create(lon float8, lat float8, radius float8)
RETURNS circle_geo AS $$
    DECLARE
		pdist float8;
                center geography(POINT,4326);  //est-ce que ça ça marche comme déclaration en plpgsql?
                box geography(POLYGON,4326);
                ret circle_geo;
    BEGIN
		pdist = sqrt(2*radius*radius);
		center = ST_MakePoint(lon,lat);
		box = ST_MakePolygon(ST_MakeLine(ARRAY[
                ST_Project(center, pdist, radians(45.0)),
                ST_Project(center, pdist, radians(135.0)),
                ST_Project(center, pdist, radians(-135.0)),
                ST_Project(center, pdist, radians(-45.0)),
                ST_Project(center, pdist, radians(45.0))
		]));
			
                //Problème ici, comment creer mon objet composite de type circle_geo auquel je passe box,center et radius avant de le retourner?
		ret = ROW(box, center, radius);

		RETURN(ret);
    END;
$$ LANGUAGE plpgsql;

j'ai juste besoin d'un coup de main pour savoir comment créer un type custom dans une fonction plpgsql

Dernière modification par ramsestom (01/06/2017 00:07:04)