Spatial Datatype (geometry) to GeoJSON

Question

I want to convert geom (geometry) datatype to GeoJSON. How could I do that?

For example, the geometry in WKT:

    POLYGON((455216.346127297 4288433.28426224,455203.386722146 4288427.76317716,455207.791765017 4288417.51116228,455220.784166744 4288423.30230044,455216.346127297 4288433.28426224))

To the following GeoJSON:

{ "type": "Polygon",
    "coordinates": [
      [ [100.0, 0.0], [101.0, 0.0], [101.0, 1.0], [100.0, 1.0], [100.0, 0.0] ],
      [ [100.2, 0.2], [100.8, 0.2], [100.8, 0.8], [100.2, 0.8], [100.2, 0.2] ]
    ]
}

Answer 1

I had this same need. We have a large database containing tables with GEOMETRY columns in SQL Server. I felt that it would be more desirable to be able to get a single string object as a result from a stored proceedure that would contain the GeoJson. I wrote a function that takes a geometry instance as an object and returns a GeoJson string.

CREATE FUNCTION [dbo].[geomToGeoJSON] (@geom GEOMETRY)
RETURNS VARCHAR(MAX)
AS
BEGIN
-- Declare the return variable here
    DECLARE @geoJSON VARCHAR(MAX)


    DECLARE @Ngeom GEOMETRY
    DECLARE @ptCounter INT
    DECLARE @numPt INT
    DECLARE @ringCounter INT
    DECLARE @numRing INT
    DECLARE @gCounter INT
    DECLARE @numGeom INT
    DECLARE @handled BIT = 0
    DECLARE @extRing GEOMETRY
    DECLARE @intRing GEOMETRY

    -- fix bad geometries and enforce ring orientation
    SET @geom = @geom.STUnion(@geom.STPointN(1)).MakeValid()

    -- Point ----------------------------
    IF (@geom.STGeometryType() = 'Point')
    BEGIN
        SET @geoJSON = '{ "type": "Point", "coordinates": [' + LTRIM(RTRIM(STR(@geom.STX, 38, 8))) + ', ' + LTRIM(RTRIM(STR(@geom.STY, 38, 8))) + '] }'
        SET @handled = 1
    END


    -- MultiPoint ---------------------------------------------
    IF (
        @handled = 0
        AND @geom.STGeometryType() = 'MultiPoint'
        )
    BEGIN
        SET @gCounter = 1
        SET @numGeom = @geom.STNumGeometries()

        SET @geoJSON = '{ "type": "MultiPoint", "coordinates": ['

        WHILE @gCounter <= @numGeom
        BEGIN
            SET @geoJSON += '[' + LTRIM(RTRIM(STR(@geom.STGeometryN(@gCounter).STX, 38, 8))) + ', ' + LTRIM(RTRIM(STR(@geom.STGeometryN(@gCounter).STY, 38, 8))) + '], '
            SET @gCounter += 1
        END

        SET @geoJSON = LEFT(@geoJSON, LEN(@geoJSON) - 1) + '] }'
        SET @handled = 1
    END




    -- LineString ---------------------------------------------
    IF (
        @handled = 0
        AND @geom.STGeometryType() = 'LineString'
        )
    BEGIN
        SET @ptCounter = 1
        SET @numPt = @geom.STNumPoints()

        SET @geoJSON = '{ "type": "LineString", "coordinates": ['

        WHILE @ptCounter <= @numPt
        BEGIN
            SET @geoJSON += '[' + LTRIM(RTRIM(STR(@geom.STPointN(@ptCounter).STX, 38, 8))) + ', ' + LTRIM(RTRIM(STR(@geom.STPointN(@ptCounter).STY, 38, 8))) + '], '
            SET @ptCounter += 1
        END

        SET @geoJSON = LEFT(@geoJSON, LEN(@geoJSON) - 1) + ' ] }'
        SET @handled = 1
    END




    -- MultiLineString ---------------------------------------------
    IF (
        @handled = 0
        AND @geom.STGeometryType() = 'MultiLineString'
        )
    BEGIN
        SET @gCounter = 1
        SET @numGeom = @geom.STNumGeometries()

        SET @geoJSON = '{ "type": "MultiLineString", "coordinates": ['

        WHILE @gCounter <= @numGeom
        BEGIN
            SET @Ngeom = @geom.STGeometryN(@gCounter)
            SET @geoJSON += '['
            SELECT
                @ptCounter = 1
                ,@numPt = @Ngeom.STNumPoints()

            WHILE @ptCounter <= @numPt
            BEGIN
                SET @geoJSON += '[' + LTRIM(RTRIM(STR(@Ngeom.STPointN(@ptCounter).STX, 38, 8))) + ', ' + LTRIM(RTRIM(STR(@Ngeom.STPointN(@ptCounter).STY, 38, 8))) + '], '
                SET @ptCounter += 1
            END

            SET @geoJSON = LEFT(@geoJSON, LEN(@geoJSON) - 1) + '],'

            SET @gCounter += 1
        END

        SET @geoJSON = LEFT(@geoJSON, LEN(@geoJSON) - 1) + '] }'
        SET @handled = 1
    END




    -- Polygon ---------------------------------------------
    IF (
        @handled = 0
        AND @geom.STGeometryType() = 'Polygon'
        )
    BEGIN
        SET @extRing = @geom.STExteriorRing()

        SET @geoJSON = '{ "type": "Polygon", "coordinates": [['

        SELECT
            @ptCounter = 1
            ,@numPt = @extRing.STNumPoints()

        WHILE @ptCounter <= @numPt
        BEGIN
            SET @geoJSON += '[' + LTRIM(RTRIM(STR(@extRing.STPointN(@ptCounter).STX, 38, 8))) + ', ' + LTRIM(RTRIM(STR(@extRing.STPointN(@ptCounter).STY, 38, 8))) + '], '
            SET @ptCounter += 1
        END

        SET @geoJSON = LEFT(@geoJSON, LEN(@geoJSON) - 1) + ']'

        SET @ringCounter = 1
        SET @numRing = @geom.STNumInteriorRing()

        WHILE @ringCounter <= @numRing
        BEGIN
            SET @geoJSON += ',['

            SET @intRing = @geom.STInteriorRingN(@ringCounter)
            -- set the ring orientation so that they are consistent
            SET @intRing = @intRing.STUnion(@intRing.STPointN(1)).MakeValid()

            SELECT
                @ptCounter = @intRing.STNumPoints()

            WHILE @ptCounter > 0
            BEGIN
                SET @geoJSON += '[' + LTRIM(RTRIM(STR(@intRing.STPointN(@ptCounter).STX, 38, 8))) + ', ' + LTRIM(RTRIM(STR(@intRing.STPointN(@ptCounter).STY, 38, 8))) + '], '
                SET @ptCounter -= 1
            END

            SET @geoJSON = LEFT(@geoJSON, LEN(@geoJSON) - 1) + ']'

            SET @ringCounter += 1
        END

        SET @geoJSON = LEFT(@geoJSON, LEN(@geoJSON) - 1) + ']] }'
        SET @handled = 1
    END




    -- MultiPolygon ---------------------------------------------
    IF (
        @handled = 0
        AND @geom.STGeometryType() = 'MultiPolygon'
        )
    BEGIN
        SELECT
            @gCounter = 1
            ,@numGeom = @geom.STNumGeometries()

        SET @geoJSON = '{ "type": "MultiPolygon", "coordinates": ['

        WHILE @gCounter <= @numGeom
        BEGIN
            SET @Ngeom = @geom.STGeometryN(@gCounter)

            SET @extRing = @Ngeom.STExteriorRing()

            SET @geoJSON += '[['

            SELECT
                @ptCounter = 1
                ,@numPt = @extRing.STNumPoints()

            -- add the exterior ring points to the json
            WHILE @ptCounter <= @numPt
            BEGIN
                SET @geoJSON += '[' + LTRIM(RTRIM(STR(@extRing.STPointN(@ptCounter).STX, 38, 8))) + ', ' + LTRIM(RTRIM(STR(@extRing.STPointN(@ptCounter).STY, 38, 8))) + '], '
                SET @ptCounter += 1
            END

            SET @geoJSON = LEFT(@geoJSON, LEN(@geoJSON) - 1) + ']'

            SET @ringCounter = 1
            SET @numRing = @Ngeom.STNumInteriorRing()

            -- add any internal ring points to the json
            WHILE @ringCounter <= @numRing
            BEGIN
                SET @geoJSON += ',['

                SET @intRing = @Ngeom.STInteriorRingN(@ringCounter)
                -- make sure the ring orientation is the same every time
                SET @intRing = @intRing.STUnion(@intRing.STPointN(1)).MakeValid()

                SELECT
                    @ptCounter = @intRing.STNumPoints()

                WHILE @ptCounter > 0
                BEGIN
                    SET @geoJSON += '[' + LTRIM(RTRIM(STR(@intRing.STPointN(@ptCounter).STX, 38, 8))) + ', ' + LTRIM(RTRIM(STR(@intRing.STPointN(@ptCounter).STY, 38, 8))) + '], '
                    SET @ptCounter -= 1
                END

                SET @geoJSON = LEFT(@geoJSON, LEN(@geoJSON) - 1) + ']'

                SET @ringCounter += 1
            END



            SET @geoJSON += '],'
            SET @gCounter += 1
        END

        SET @geoJSON = LEFT(@geoJSON, LEN(@geoJSON) - 1) + '] }'
        SET @handled = 1
    END






    IF (@handled = 0)
    BEGIN
        SET @geoJSON = '{"type": "' + @geom.STGeometryType() + '", "coordinates": []}'
    END




    RETURN @geoJSON



END

Then I can either just select an individual GeoJSON object like this:

    SELECT dbo.geomToGeoJSON(GEOMCOLNAME) FROM DB.gis.PARCEL WHERE PARCEL = 'R1525750900'

and get a result that looks like

    {
        "type": "Polygon",
        "coordinates": [
            [
                [-116.27593761, 43.62939598],
                [-116.27558219, 43.62939633],
                [-116.27558253, 43.62955520],
                [-116.27582493, 43.62955445],
                [-116.27582534, 43.62963010],
                [-116.27593893, 43.62962975],
                [-116.27593761, 43.62939598]
            ]
        ]
    }

Or I can package an entire set of objects into a FeatureCollection like this:

        DECLARE @GeoJSON VARCHAR(MAX)
        SET @GeoJSON = '{"type": "FeatureCollection", "features": ['

        SELECT
            @GeoJSON += '{"type": "Feature", "geometry": ' + sde_apps.dbo.geomToGeoJSON(SHAPE) + ', "properties": { "Parcel": "' + PARCEL + '"}},'
        FROM
            db.gis.PARCEL
        WHERE
            SUBNM LIKE @subnm


        SET @GeoJSON = LEFT(@GeoJSON, LEN(@GeoJSON) - 1) + ']}'

        SELECT
            @GeoJSON

Query performance depends on the complexity and number of geometries, but I typically get a result in ~2 tenths of a second.

I have validated by using example geometries from MSDN and then entering the resulting GeoJSON into http://geojsonlint.com/. I know this is a year old but I still have a need and I suspect anyone without a mapserver could generate their own simple mapserver using something like this to draw layers on Bing Maps, etc.