Fonction de conversion spatiale OGC vers GeoJSON pour SQL Server

Avec l’arrivée de SQL Server 2025 et son type JSON — ce n’est plus un simple « texte », mais bien un type de données dont la syntaxe est validée à la saisie — il m’est apparu, lors d’une mission à Rabat (Maroc), au service cartographique du Royaume, qu’il était nécessaire de développer une petite fonction Transact-SQL de transformation des données au format OGC Well Known Text (WKT) en GeoJSON, format de plus en plus utilisé.

En voici les spécifications et le code…

Les trois grandes différences entre le format géométrique spatial OGC et le format GeoJSON sont que :

les coordonnées des points sont séparées les unes des autres par un espace en OGC alors que c’est par une virgule en GeoJSON
les composantes des objets sont séparées par des parenthèses en OGC, alors que c’est avec des crochets en GeoJSON
l’entête d’un objet spatial est constituée d’un seul mot en OGC (POINT, MULTIPOLYGON par exemple) alors que GeoJSON nécessite deux attributs et leurs valeurs.

En sus, il faut :

délimiter les noms des attributs avec des guillemets ( » ), ainsi que les valeurs lorsqu’elles sont littérales
délimiter le texte JSON avec des accolades ( { … } ).

Pour être puriste du langage, il aurait fallu concevoir une fonction récursive (ce qui est parfaitement possible avec SQL Server en Transact-SQL), mais j’ai préféré travailler sur une fonction itérative dont le coût en exécution est moindre.

Le principe en est simple :

1 – Transformation de l’entête

En OGC, l’entête spécifie le type de géométrie à l’aide d’un seul mot comme POINT, MULTIPOINT, LINESTRING, POLYGON… à la suite de laquelle la spécification des coordonnées figurent entre parenthèses.

En GeoJSON, l’entête est constitué de deux attributs :

type : qui spécifie le type de géométrie dans une typographie en CamelCase;
coordinates : qui introduit la définition géométrique de l’objet spatial.

Et pour introduire la valeur associée à un attribut, JSON utilise le caractère deux-points.

À titre d’exemple :

"type": "Point","coordinates":
"type": "MultiPoint","coordinates":
"type": "LineString","coordinates"
"type": "MultiLineString","coordinates":
"type": "Polygon","coordinates":
"type": "MultiPolygon","coordinates":

"type": "Point","coordinates":

"type": "MultiPoint","coordinates":

"type": "LineString","coordinates"

"type": "MultiLineString","coordinates":

"type": "Polygon","coordinates":

"type": "MultiPolygon","coordinates":

2 – Transformation des coordonnées

Les transformations suivantes doivent ensuite être effectuées, dans un ordre précis, dans le texte spécifiant les coordonnées :

les parenthèses sont remplacées par des crochets : ( => [, ) => ]
aux virgules on ajoute des crochets avant et après, ouvrant devant, fermant derrière en éliminant l’espace situé derrière la parenthèse : « , » => « ],[«
Les espaces sont remplacés par des virgules

Certaines transformation (imbrication des délimiteurs) devraient être effectuées de manière récursive. Mais il est possible de s’en passer en effectuant dans un certain ordre quelques transformations comme « )), (( » => « ]]],[[[« .

3 – Limitations

Il y a néanmoins quelques limitations, tant du côté du type OGC que du format GeoJSON… Voici comment nous les avons contournées :

3.1 – Géométrie invalide

Dans le cas ou la géométrie OGC est invalide, certains opérations sont impossible. La fonction renvoie NULL dans ce cas.

3.2 – Spécification de la mesure M

Le standard OGC permet une quatrième information pour tout point, en sus des coordonnées X, Y, Z, la mesure notée M. GeoJSON n’accepte pas la mesure M. Dans le cas ou une géométrie aurait valuée au moins en un point une mesure M, la géométrie est rejeté et la fonction renvoie NULL

3.3 – Objets non supportés

GeoJSON ne supporte pas les objets courbes de l’OGC, et donc pas ceux de SQL Server comme ;

Dans le cas ou une géométrie SQL serait de l’un de ces types, elle sera ignorée et la fonction renvoie NULL.

3.4 – Le cas de la GEOMETRYCOLLECTION

Bien que la spécification du format JSON permettent de créer des instances comportant une collection d’objets géométrique de différents types, sa complexité d’implémentation comme sa faible utilisation en pratique, nous a conduit a ignorer ce type.

Dans un tel cas, la fonction renvoie NULL.

3.5 – Polygone et sens de saisie des points

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 le sens ordinal des points pour les anneaux imbriqués dans l’ordre d’imbrication du plus externe au plus interne.

Ces règles sont écrites dans la RFC 7946 :
A linear ring MUST follow the right-hand rule with respect to the area it bounds, i.e., exterior rings are counterclockwise, and holes are clockwise.

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. Elle n’est d’ailleurs pas obligatoire, pour des raisons de rétrocompatibilité selon la RFC 7946 :
Note: the [GJ2008] specification did not discuss linear ring winding order. For backwards compatibility, parsers SHOULD NOT reject Polygons that do not follow the right-hand rule.

4 – Sources des spécifications

Pour le standard GeoJSON (publié par l’IETF), la RFC 7946 est reproduite ici : https://datatracker.ietf.org/doc/html/rfc7946
Pour les spécifications du GeoJSON une note explicative est disponible ici : https://stevage.github.io/geojson-spec/

5 – Validations

Nous avons utilisé GeoJSON Lint qui permet de valider les format GeoJSON : https://geojsonlint.com/

Notez que ce dernier indique comme invalide les géométries de polygones dont le sens ordinal des points n’est pas celui formalisé au § 3.5.

6 – Jeu de test

Pour ce faire nous avons créé une base MS SQL Server de nom DB_GEO_TEST et une table de nom dbo.T_GEOTEST_GOT composée des colonnes GOT_ID (IDENTITY PK), GOT_GEO (GEOMETRY) et GOT_DESCRIPTION (VARCHAR(256)). Nous y avons mis les objets suivants :

INSERT INTO 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');

INSERT INTO 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');

7 – Résultats

Nous voyons dans ce résultats que certaines transformation des types SQL GEOMETRY en GeoJSON, n’apparaissent pas (marqueur NULL), ceci à cause de la mesure M qui n’existe pas en GeoJSON ou de géométries invalides.

{"type": "Point","coordinates": [1.1 2.2]}
{"type": "Point","coordinates": [1.1 2.2]}
{"type": "Point","coordinates": [1.1 2.2]}
{"type": "Point","coordinates": [1.1 2.2]}
{"type": "Point","coordinates": [1.1 2.2]}
{"type": "Point","coordinates": [1.1 2.2 3]}
NULL
NULL
{"type": "MultiPoint","coordinates": [[3.3 4.4],[5.5 6.6]]}
{"type": "MultiPoint","coordinates": [[3.3 4.4],[5.5 6.6]]}
{"type": "MultiPoint","coordinates": [[3.3 4.4 7],[5.5 6.6 8]]}
{"type": "MultiPoint","coordinates": [[3.3 4.4],[5.5 6.6],[7.7 8.8]]}
{"type": "LineString","coordinates": [[1.1 2.2],[3.3 4.4]]}
{"type": "LineString","coordinates": [[1.1 2.2],[3.3 4.4],[5.5 6.6]]}
{"type": "MultiLineString","coordinates": [[[1.1,2.2],[3.3,4.4]],[[4.4,3.3],[2.2,1.1]]]}
{"type": "Polygon","coordinates": [[[10,10],[40,10],[40,40],[10,10],[10,10]]]}
{"type": "Polygon","coordinates": [[[10,10],[10,40],[40,40],[40,10],[10,10]]]}
{"type": "Polygon","coordinates": [[[10,10],[40,10],[40,40],[10,40],[10,10]],[[15,15],[15,34],[34,34],[34,15],[15,15]]]}
{"type": "Polygon","coordinates": [[[10,10],[10,40],[40,40],[40,10],[10,10]],[[15,15],[15,34],[34,34],[34,15],[15,15]]]}
NULL
{"type": "MultiPolygon","coordinates": [[[[20,10],[40,10],[40,40],[20,40],[20,10]]],[[[50,30],[60,30],[40,60],[30,60],[50,30]]]]}
{"type": "MultiPolygon","coordinates": [[[[20,10],[20,40],[40,40],[40,10],[20,10]]],[[[50,30],[50,40],[60,40],[60,30],[50,30]]]]}

{"type": "Point","coordinates": [1.1 2.2]}

{"type": "Point","coordinates": [1.1 2.2 3]}

NULL

{"type": "MultiPoint","coordinates": [[3.3 4.4],[5.5 6.6]]}

{"type": "MultiPoint","coordinates": [[3.3 4.4 7],[5.5 6.6 8]]}

{"type": "MultiPoint","coordinates": [[3.3 4.4],[5.5 6.6],[7.7 8.8]]}

{"type": "LineString","coordinates": [[1.1 2.2],[3.3 4.4]]}

{"type": "LineString","coordinates": [[1.1 2.2],[3.3 4.4],[5.5 6.6]]}

{"type": "MultiLineString","coordinates": [[[1.1,2.2],[3.3,4.4]],[[4.4,3.3],[2.2,1.1]]]}

{"type": "Polygon","coordinates": [[[10,10],[40,10],[40,40],[10,10],[10,10]]]}

{"type": "Polygon","coordinates": [[[10,10],[10,40],[40,40],[40,10],[10,10]]]}

{"type": "Polygon","coordinates": [[[10,10],[40,10],[40,40],[10,40],[10,10]],[[15,15],[15,34],[34,34],[34,15],[15,15]]]}

{"type": "Polygon","coordinates": [[[10,10],[10,40],[40,40],[40,10],[10,10]],[[15,15],[15,34],[34,34],[34,15],[15,15]]]}

NULL

{"type": "MultiPolygon","coordinates": [[[[20,10],[40,10],[40,40],[20,40],[20,10]]],[[[50,30],[60,30],[40,60],[30,60],[50,30]]]]}

{"type": "MultiPolygon","coordinates": [[[[20,10],[20,40],[40,40],[40,10],[20,10]]],[[[50,30],[50,40],[60,40],[60,30],[50,30]]]]}

8 – Code de la fonction

La fonction est créé dans un schéma SQL de nom S_GEO. Ce schéma SQL doit être créé avant de pouvoir créer la fonction F_GEOMETRY_AS_GEOJSON. Pour cela vous pouvez lancer la ligne de commande SQL suivante :

CREATE SCHEMA S_GEO;

1	CREATE SCHEMA S_GEO;

Le code de notre fonction est le suivant :

CREATE OR ALTER FUNCTION S_GEO.F_GEOMETRY_AS_GEOJSON (@geo geometry)
   RETURNS NVARCHAR(max) 
   WITH RETURNS NULL ON NULL INPUT
AS
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

CREATE OR ALTER FUNCTION S_GEO.F_GEOMETRY_AS_GEOJSON (@geo geometry)

RETURNS NVARCHAR(max)

WITH RETURNS NULL ON NULL INPUT

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(@GEOSTR, ')), ((', ']]],[[['

),N'],[', N']],[['

), N', ', N'],['

), ' ', ','

) + ']';

-- valeur de retour

RETURN @RETVAL + @GEOSTR +'}';

END

Le code de la fonction F_GEOMETRY_AS_GEOJSON et le jeu de test

Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL * Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com * * le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ * * Expert Microsoft SQL Server, MVP (Most valuable Professional) depuis 14 ans * * Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation * * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *