0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
arcee123
  • 101
  • 9
  • 41
  • 118
  • It would help if you had an example of the SQL multipolygon and what you expected the output to look like. – Ben Thul Jun 25 '16 at 21:55

1 Answers1

1

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]
Ronald Swaine
  • 33
  • 4