Fonctions spatiales et norme SQL

Bonjour à tous,

J'ai décidé de reprendre le projet opensource doctrine-spatial. Doctrine, un abstracteur de base de données, permet de faire le lien entre les objets PHP et la base de données. Doctrine spatial est une extension pour intégrer les types géométriques, géographiques et les fonctions spatiales. Le projet est dormant depuis 2017. Je le reprends. Je remarque dans le code quelques écueils que je voudrais éviter.

Par exemple, la fonction CONTAINS permet de déterminer si un objet en contient un autre. En MySQL 5.7, elle se nommait CONTAINS, en MySQL 8.0, elle se nomme ST_CONTAINS, en PostgreSQL, elle se nomme ST_CONTAINS et en MariaDB, l'algorithme est différent et ne renvoie pas le même résultat qu'en MySQL 5.7. (Je crois que les arguments sont inversés, mais je n'ai pas vérifié).

Bref, l'objectif d'un abstracteur de données est de rendre l'application compatible quelque soit le moteur de base de données derrière. Actuellement, le développeur se retrouve à créer des requêtes DQL (Doctrine Query Language), mais il doit adapter le nom des fonctions en fonction du SGBD. Je trouve cela incohérent avec l'objectif de doctrine : faire abstraction du SGBD.

La solution que j'ai choisie pour choisir la syntaxe de la fonction est la suivante :

• Je privilégie la norme SQL,
• À défaut, je suis la syntaxe de PostgreSQL puis de Microsoft SQL Server (j'ai bon espoir que quelqu'un me propose un "pull request" un jour)
• Voire je privilégie ce que décrit l'OGC (Open Geospatial Consortium) (travail fastidieux)

Derrière ce vœu pieu, je suis confronté à un problème, je ne trouve rien dans la norme SQL définissant les fonctions spatiales.

Existe-t-il une norme, une extension de la norme, définissant les fonctions spatiales ? L'objectif est de sélectionner un nom standardisé et un ordre des arguments standardisé également.

Comme j'ai beaucoup travaillé sur le spatial (voir les articles que j'ai écrit à ce sujet), je te confirme différents points :
1) la norme SQL est un sous ensemble du standard OGC. Tu peut donc te caler sur le standard OGC, car tôt ou tard, les fonctions SQL normalisées seront celles de la norme. Cependant, les SGBDR étant ensembliste, certaines fonctions ne peuvent s'appliquer tandis que d'autres sont spécifiques aux algorithmes spatiaux apppliquées à un monde ensembliste (par exemple agrégation spatiale)
2) PostgreSQL suit la norme de très près, mais d'un peu trop. En effet, la norme ce sont des fonctions dont le nom commence par ST_. Mais PostgreSQL y a ajouté beaucoup de fonctions en ST_ qui n'ont rien à voir avec la norme. Si certaines nouvelles fonctions normalisées voient le jour dans le futur, il est possible qu'elles utilisent un nom déjà pris par PostgreSQL conduisant a une différence fonctionnelle, ce qui est problématique. Par exemple PostgreSQL propose la fonction ST_MakeValid qui n'existe pas dans le standard OGC ni dans la norme…. Mais il est probable qu'elle soit réalisée un de ces jour… Et pour information, elle ne donne pas le même résultat que la méthode MakeValid (sans ST_) de MS SQL Server…
3) SQL Server utilisant des méthodes appliquées aux types GEO (du fait de la programmation objet, car utilisation de la machine .net de manière sous-jacente pour les algorithme GEO) les noms sont légèrement différents car en notation objet pointée par une méthode et non pas en application de fonctions. De ce fait pour bien distinguer la chose MS a décidé d'omettre le caractère underscore qui suit le préfixe ST...
4) le spatial dans MySQL / MariaDB est fortement bugué en sus d'être très imprécis. En gros il est inexploitable !

A +

PS : mes articles :
https://blog.developpez.com/sqlpro/p...on_geographiqu
https://blog.developpez.com/sqlpro/p..._geographiqu_2

Merci beaucoup Frédéric,

Je vais me baser sur ce document : http://portal.opengeospatial.org/fil...ifact_id=25354 et notamment le chapitre 7.2.8.2
Je te rejoins sur le fait que c'est inexploitable en MySQL. Les fonctions Contains et ST_Contains retournent des résultats différents et certaines fonctions ne retournent pas le même résultat sous MariaDB, qui est pourtant un Fork. Bref... Je le maintiens, car je ne vais pas supprimer ce qui existe, mais ce n'est vraiment pas mon objectif principal. Je change les tests réalisés par mon prédecesseur pour me limiter au fait que la fonction n'explose pas, qu'elle accepte les bons arguments (dans le bon ordre sic), mais je ne compare plus les résultats. (MySQL5.7 et MySQL8, ne retourne pas le même résultat pour la surface d'un point après être passé dans la fonction st_buffer. ST_AREA(ST_BUFFER(POINT(0 0),3)))

Ce que tu dis sur le respect de la norme va me poser un souci je pense. Du coup, voilà ce que je pense faire. Je préfixe le mot-clé du langage DQL par ST si et seulement si la fonction est déclarée dans les documents de l'OGC sinon je la préfixe par SP_ (pour spécific) afin d'éviter tout conflit avec une fonction ou un mot clé SQL existant (Je pense à la méthode UNION par exemple).

• Pour Area, j'aurai donc ST_AREA en DQL, que j'utilise la fonction SQL Area(MySQL5.7), ST_Area(MySQL8 et PostGreSQL)
• Pour MakeValid, j'aurai donc SP_AREA en DQL que même si derrière j'appelle la fonction SQL ST_MakeValid de PostGreSQL.

Il n'y aura pas de problème de rétrocompatibilité, puisque :

1. Je changerai le numéro de version pour bien stipuler que je n'assure pas toute la rétrocompatibilité
2. Les développeurs utilisant la librairie (dans Symfony notamment) peuvent changer le mot clé DQL par celui de leur choix pour leur application via un fichier de configuration.

Je note comme résolu, j'ai mes réponses :)

Merci beaucoup pour les liens, je vais lire de suite !

ça me parait jouable….. !

Pour l'anecdote voici une bug particulièrement croustillant sur MySQmerde :
https://blog.developpez.com/sqlpro/p...oudre_aux_yeux

Cherche GeomFromtext….

A +

Bonjour Frédéric,

Oui j'avance dans le développement et je me retrouve confronté à de nombreux problèmes dans les tests.
Par exemple, la fonction ST_Buffer accepte bien le bon nombre d'arguments, mais en MySQL, le troisème argument ne peut accepter qu'un type, le retour de la fonction ST_Buffer_Strategy. Une invention MySQL qui me donne l'impression qu'ils ont choisi une solution de facilité.

En revanche, MySQL reste toujours incapable de couper une ligne en deux.

Code:

---

1 2 3

SET @ligneA = ST_GeomFromText('LINESTRING(0 0, 10 10)'); SET @ligneB = ST_GeomFromText('LINESTRING(10 0, 0 10)'); SELECT ST_AsTEXT(ST_DIFFERENCE(@ligneA, @ligneB)) AS DIFFERENCE, VERSION() as VERSION;

---

DIFFERENCE	VERSION
LINESTRING(0 0,10 10)	8.0.19

Alors que le résultat attendu est une ligne coupée, cad une multiligne composée de deux lignes qui se séparent en (5 5)

Code:

---

1 2 3 4

SELECT ST_AsText(ST_DIFFERENCE( ST_GeomFromText('LINESTRING(0 0, 10 10)'), ST_GeomFromText('LINESTRING(10 0, 0 10)') )), VERSION();

---

DIFFERENCE	VERSION
MULTILINESTRING((0 0,5 5),(5 5,10 10))	"PostgreSQL 9.6.6, compiled by Visual C++ build 1800, 64-bit"

La création des tests unitaires en devient infernale.

Est-ce qu'il existe une traduction anglosaxone de ton billet ? Je voudrais en faire mention dans le README de la librairie sous l'avertissement où je déconseille avec vigueur d'utiliser mysql comme mariadb.

Deuxième souci, je galère sans exemplaire de la norme ISO/IEC 13249-3:2016. (Et je n'ai pas 300€ pour l'acheter, je suis en train de la négocier avec mon employeur, mais ce n'est pas gagné). Je galère justement concernant ce que tu disais sur Postgis. PostGis préfixe par ST toutes les fonctions, y compris celle qui sont propres à PostgreSQL.
Par exemple, la fonction ST_ConvexHull reçoit une collection comme paramètre, je voulais utiliser ST_COLLECT pour rassembler des géométries et faire des tests, mais je ne trouve pas de trace de cette fonction dans le géostandard.

Y aurait-il une documentation, une publication du standard autre que celle que j'utilise ? https://www.ogc.org/standards/sfs

Citation:

---

Envoyé par Alexandre T

Est-ce qu'il existe une traduction anglosaxone de ton billet ? Je voudrais en faire mention dans le README de la librairie sous l'avertissement où je déconseille avec vigueur d'utiliser mysql comme mariadb.

---

Hélas non, mais un coup de google translate et quelques corrections….

Citation:

---

Deuxième souci, je galère sans exemplaire de la norme ISO/IEC 13249-3:2016. (Et je n'ai pas 300€ pour l'acheter, je suis en train de la négocier avec mon employeur, mais ce n'est pas gagné). Je galère justement concernant ce que tu disais sur Postgis. PostGis préfixe par ST toutes les fonctions, y compris celle qui sont propres à PostgreSQL.
Par exemple, la fonction ST_ConvexHull reçoit une collection comme paramètre, je voulais utiliser ST_COLLECT pour rassembler des géométries et faire des tests, mais je ne trouve pas de trace de cette fonction dans le géostandard.

Y aurait-il une documentation, une publication du standard autre que celle que j'utilise ? https://www.ogc.org/standards/sfs

---

Tu peut partir sur le standard OGC....

A +

Salut,
Regardez du côté de ST_SymDifference pour le MULTILINESTRING , ça semble bien fonctionner avec MariaDB

Frédéric, j'ai récupéré la norme de 2009, c'est toujours cela (d'autant que le standard OGS que j'utilise date de 2007)
Je vais envisager la traduction de ton billet.

J'ai lancé la requête que tu as construite dans ton blog pour vérifier comment la fonction avait été corrigée dans MySQL8 et que de surprises !

Alors premier point, la requête dans MySQL8 doit-être exécuter avec les préfixes ST_ La requête retourne enfin le bon résultat.
J'ai lancé exactement la même requête que la tienne dans MySQL5.7, dernière version et je constate la même erreur que toi. Le bug n'est pas corrigé malgré une mise à jour récente.
Mais quand je lance la requête avec les préfixes ST_ dans MySQL5.7 et l'erreur est corrigée. Je ne comprends pas que la fonction "Contains" n'ait pas été corrigée. Hallucinant !


Willy_k, je ne comprends le sens de ta remarque. En tout cas, concernant MariaDb, je ne compte pas m'investir dans le développement des variantes pour ce SGBD. Je m'occupe de l'abstraction de MySQL uniquement parce que dans la première version de CrEOF/doctrine-spatial intégrait cette abstraction pour MySQL et que je souhaite assurer une certaine portabilité, mais je t'avoue que je m'en passerai bien vu son codage chaotique.

MariaDB ne fait pas partie des futurs SGBD que j'intégrerai dans cet abstracteur. Je vais finir ce que je fais, j'ai déjà très bien avancé. J'espère rajouter Microsoft SQL Server, dès que j'aurai compris comment paramétrer Appveyor pour y déployer MSSQL Server. ( Ce serait un bon article pour Developpez.net, au passage ! ) (Je note dans un coin)

Hors sujet : Petit bug détecté dans PostgreSQL comme dans MySQL sous Windows :

Code:

---

SELECT ST_AsText(ST_Buffer(ST_GeometryFromText('POINT(0 0)'), 4, 'quad_segs=4'))

---

Le résultat sous Linux retourne :

Citation:

---

POLYGON((4 0,3.69551813004515 -1.53073372946036,2.82842712474619 -2.82842712474619,1.53073372946036 -3.69551813004515,0 -4,-1.53073372946035 -3.69551813004515,-2.82842712474618 -2.8284271247462,-3.69551813004514 -1.53073372946037,-4 0,-3.69551813004515 1.53073372946035,-2.8284271247462 2.82842712474618,-1.53073372946037 3.69551813004514,0 4,1.53073372946034 3.69551813004515,2.82842712474617 2.82842712474621,3.69551813004514 1.53073372946038,4 0))

---

Le résultat sous Windows a quelques soucis d'arrondis :

Citation:

---

POLYGON((4 0,3.69551813004515 -1.53073372946036,2.82842712474619 -2.82842712474619,1.53073372946036 -3.69551813004515,6.46217020866535e-015 -4,-1.53073372946035 -3.69551813004515,-2.82842712474618 -2.8284271247462,-3.69551813004514 -1.53073372946037,-4 -1.29243404173307e-014,-3.69551813004515 1.53073372946035,-2.8284271247462 2.82842712474618,-1.53073372946037 3.69551813004514,-1.84983322062959e-014 4,1.53073372946034 3.69551813004515,2.82842712474617 2.82842712474621,3.69551813004514 1.53073372946038,4 0))

---

Je suppose que cela vient d'une librairie C.

Sous quelle version est tu sous MS SQL Server ?
Avec 2017 :

Code:

---

1 2 3 4 5 6 7 8 9

SELECT CAST('POINT(0 0)' AS GEOMETRY).STBuffer(4).STAsText() POLYGON ((0 -3.9999999999999605, 0.19627069730967128 -3.9951818248206505, 0.39206856131824086 -3.9807389066887491, 0.58692189782144477 -3.9567060398590863, 0.78036128806451011 -3.9231411216128853, 0.971920719613052 -3.88012501277814, 1.1611387090178453 -3.8277613429288, ...

---

A +

J'avais écrit MySql, pas MSSQL ;)

Je teste les fonctions spatiales sous PostGreSQL et Mysql (malheureusement pour ce dernier), mais je travaille sous Windows. Je vérifie mes tests sur Travis qui lance tout sous Linux. Et entre Windows et Linux, le polygone retourné n'est pas le même après le 14ème chiffre après la virgule.

Ha désolé j'ai été un peu vite.... Ce serait intéressant de faire cette même comparaisons avec SQL Server Windows et Linux !

A +