CODEWITHSUNDEEP
sqlsql-serverjsonsqlgeography
arcee123
arcee123

Reputation: 211

Pulling SQL Server GEOGRAPHY to POLYGONS

I have this query

SELECT 
    ' {"location":"'+ G.ZCTA5CE10 +', ' + INTPTLON10 + ', ' + INTPTLAT10 + '",'+
               '"polygon":' +
                replace(replace(replace(replace(replace(replace(replace(G.geom.ToString(), 'POLYGON ((', '[{"lng":'), '))', '}]'), ', ', '},{"lng":'), ' ', ',"lat":'), ')', ''), '(', ''), 'MULTI', '') +
                '}'
FROM REF_ZIP_GEOG G WITH(INDEX([geog_sidx]))
WHERE G.geom.STDistance(geography::STPointFromText('POINT(-81.3225 32.113)', 4326))<= 40234;  --40.234 KM ~ 25 Miles

The intent of this query is to "Stringify" the GEOGRAPHY polygon into google maps. POLYGONS are working great. However, if the POLYGON is a MULTI-POLYGON, I am having issues working the JSON. The result is a Google error of a bad JSON.

Has anyone worked with MULTI POLYGONS and could you recommend anything to alter my SQL statement to work this correctly?

Thanks.

Upvotes: 0

Views: 140

Answers (1)

Ronald Swaine
Ronald Swaine

Reputation: 33

Though you'll need to translate into your replaces...

Ignore "MULTIPOLYGON"s and split on ")), ((" and handle each as you would a polygon.

But, the easiest and fastest method I've used to accomplishing this (in JS mostly):

get the geography.STAsText() from sql into some functional language
foreach multi
    replace "MULTIPOLYGON" with "" (nothing)
    split on ")), (("
    foreach poly
        replace "POLYGON" with ""
        split on "), ("
        foreach coordset
            replace "[((]" and "[))]" with ""
            split on ","
            foreach point
                trim and split on " "
                lat = coord[1]
                lng = coord[0]

Upvotes: 1

Related Questions