CREATE DATABASE DB_GEO_TESTS; GO USE DB_GEO_TESTS; GO CREATE SCHEMA S_GEO; GO CREATE TABLE S_GEO.T_GEOTEST_GOT (GOT_ID INT IDENTITY PRIMARY KEY, GOT_GEO GEOMETRY, GOT_DESCRIPTION VARCHAR(256)); GO TRUNCATE TABLE S_GEO.T_GEOTEST_GOT; GO -- jeu de données pour le test INSERT INTO S_GEO.T_GEOTEST_GOT VALUES ('POINT (1.1 2.2)', 'Point seul'), ('Point (1.1 2.2)', 'Point seul en minuscule'), ('POINT(1.1 2.2)', 'Point seul sans espace'), ('POINT (1.1 2.2)', 'Point seul avec espace'), (' POINT ( 1.1 2.2 ) ', 'Point seul avec beaucoup d''espace'), ('POINT (1.1 2.2 3.0)', 'Point avec Z (élévation)'), ('POINT (1.1 2.2 NULL 3.1415927)', 'Point avec M (mesure)'), ('POINT (1.1 2.2 3.0 3.1415927)', 'Point avec Z (élévation) et M (mesure)'), ('MULTIPOINT ((3.3 4.4), (5.5 6.6))', 'Multipoint'), ('MULTIPOINT ((3.3 4.4 NULL), (5.5 6.6))', 'Multipoint avec Z null'), ('MULTIPOINT ((3.3 4.4 7), (5.5 6.6 8))', 'Multipoint avec Z'), ('MULTIPOINT ((3.3 4.4), (5.5 6.6), (7.7 8.8))', 'Multipoint avec 3 points'), ('LINESTRING (1.1 2.2, 3.3 4.4)', 'Ligne simple'), ('LINESTRING (1.1 2.2, 3.3 4.4, 5.5 6.6)', 'Ligne avec 3 points'), ('MULTILINESTRING ((1.1 2.2, 3.3 4.4), (4.4 3.3, 2.2 1.1))', 'Ligne composée'), ('POLYGON ((10 10, 40 10, 40 40, 10 10, 10 10))', 'Polygone simple'), ('POLYGON ((10 10, 10 40, 40 40, 40 10, 10 10))', 'Polygone simple sens inverse'), ('POLYGON ((10 10, 40 10, 40 40, 10 40, 10 10), (15 15, 15 34, 34 34, 34 15, 15 15))', 'Polygone avec lac'), ('POLYGON ((10 10, 10 40, 40 40, 40 10, 10 10), (15 15, 15 34, 34 34, 34 15, 15 15))', 'Polygone avec lac sens inverse'), ('POLYGON ((10 10, 40 10, 40 40, 10 40, 10 10), (50 50, 50 60, 60 60, 60 50, 50 50))', 'Polygone avec île (non valide)'), ('MULTIPOLYGON (((20 10, 40 10, 40 40, 20 40, 20 10)), ((50 30, 60 30, 40 60, 30 60, 50 30)))', 'Multipolygone'), ('MULTIPOLYGON (((20 10, 20 40, 40 40, 40 10, 20 10)), ((50 30, 50 40, 60 40, 60 30, 50 30)))', 'Multipolygone sens inverse'); GO CREATE OR ALTER FUNCTION S_GEO.F_GEOMETRY_AS_GEOJSON (@geo geometry) RETURNS NVARCHAR(max) WITH RETURNS NULL ON NULL INPUT AS /****************************************************************************** * MODULE : GEO JSON * * NATURE : FONCTION scalaire * * OBJECT : S_GEO.F_GEOMETRY_AS_GEOJSON * * OUPUT : Texte UNICODE représentant un GeoJSON * * CREATE : 2025-10-19 * * AUTHOR : Frédéric Brouard (SQLpro) - SARL SQL SPOT - TOULON * * VERSION : 1 * * VALID : 2012 * ******************************************************************************* * Frédéric BROUARD - alias SQLpro - SARL SQL SPOT - SQLpro@sqlspot.com * * Architecte de données : expertise, audit, conseil, formation, modélisation * * tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL * * blog: http://blog.developpez.com/sqlpro site: http://sqlpro.developpez.com * * expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 * ******************************************************************************* * PURPOSE : transforme une géometrie SQL OGC en texte GeoJSON * ******************************************************************************* * INPUTS : @geo de type geometry * ******************************************************************************* * EXAMPLE : DECLARE @G geometry = geometry::Parse('POINT (1.1 2.2 3.0)'); * * SELECT dbo.F_GEOMETRY_AS_GEOJSON(@G); * ******************************************************************************* * NOTES : Attention GeoJSON est limité aux objets ayant des coordonnées XZ(Z).* * GeoJSON n'acceptant pas l'attribut M (mesure), la fonction rejette * * les géométries avec une mesure (M) et renvoie NULL. * * La fonction rejette aussi les géométries invalides et renvoie NULL. * * Les géométries courbes, types CIRCULARSTRING, COMPOUNDCURVE, * * CURVEPOLYGON et la collection géometrique (GEOMETRYCOLLECTION) ne * * sont pas supportées. * * Pour les types Polygon et par extension pour le type MultiPolygon, * * GeoJSON impose un sens ordinal des points suivant l'anneau : pour * * l'anneau extérieur, tourner dans le sens inverses des aiguilles * * d'une montre, alterner ensuite pour les anneaux imbriqués dans * * l'ordre d'imbrication. Cette règle n'ayant pas de sens dans la * * définition des objets OGC, elle n'est pas prise en compte dans la * * présente fonction. Les données renvoyées pour le GeoJSON ne sont * * donc pas ordonnées suivant cette règle. Elle n'est d'ailleurs pas * * obligatoire, pour des raisons de rétrocompatibilité selon la RFC * * 7946. * * SOURCES : * * RFC 7946 : https://datatracker.ietf.org/doc/html/rfc7946 * * GeoJSON spécifications : https://stevage.github.io/geojson-spec/ * * Outil de tests de validation : https://geojsonlint.com/ * ******************************************************************************* * IMPROVE : à faire ultérieurement * * 1) reprendre l'ordre des données des anneaux des polygones OGC * * pour les conformer au sens de rotation de la règle GeoJSON * * 2) autoriser la création de la GEOMETRYCOLLECTION * * 3) utiliser un type ASCII pour renvoyer le GeoJSON * ******************************************************************************* * BUGFIX : * ******************************************************************************/ BEGIN -- validation de la géométrie IF @geo.HasM = 1 OR @geo.STIsValid() = 0 OR @geo.STGeometryType() IN ('CircularString','CompoundCurve','CurvePolygon','GeometryCollection') RETURN NULL; -- variables de travail DECLARE @GEOTYPE NVARCHAR(32) = @geo.STGeometryType(); -- récupération du nuage de points DECLARE @GEOSTR NVARCHAR(max) = RTRIM(REPLACE(@geo.ToString() COLLATE Latin1_General_CI_AI, @geo.STGeometryType(), '')); -- création de l'entête au format JSON DECLARE @RETVAL NVARCHAR(max) = '{"type": "' + @GEOTYPE + '","coordinates": '; -- traitement générique SET @GEOSTR = LTRIM( TRANSLATE( REPLACE(@GEOSTR, '), (', '],[' ), '()', '[]' ) ); -- traitement spécifique pour le type LINESTRING IF @GEOTYPE = N'LineString' SET @GEOSTR = N'[' + REPLACE(@GEOSTR, N', ', N'],[') + N']'; -- traitement spécifique pour géométries à 2 niveaux MULTILINESTRING, POLYGON IF @GEOTYPE IN (N'MultiLineString', N'Polygon') SET @GEOSTR = N'[' + REPLACE( REPLACE( REPLACE(@GEOSTR, N'],[', N']],[[' ), N', ', N'],[' ), ' ', ',' ) + ']'; -- traitement spécifique pour la géométrie à 3 niveaux du MULTIPOLYGON IF @GEOTYPE = N'MultiPolygon' SET @GEOSTR = N'[' + REPLACE( REPLACE( REPLACE( REPLACE(@GEOSTR, ')), ((', ']]],[[[' ),N'],[', N']],[[' ), N', ', N'],[' ), ' ', ',' ) + ']'; -- valeur de retour RETURN @RETVAL + @GEOSTR +'}'; END GO -- tests SELECT GOT_ID, GOT_DESCRIPTION, GOT_GEO.ToString() AS GEO_TEXTE, S_GEO.F_GEOMETRY_AS_GEOJSON(GOT_GEO) AS GEO_JSON FROM S_GEO.T_GEOTEST_GOT GO