Some polygons not displaying in PowerBI visual when using EsriJSON in Location field well

[artj]

This issue was observed when attempting to plot census blocks using EsriJSON polygon coordinates in the Location field well. Block number (GEOID20) 510411009382007 is not visible on the map in the below screenshot:

In ArcGIS Pro, the polygon displays correctly, however, this screenshot was taken while using the original Census shapefile before export to esrijson format. This leads me to believe that the issue is likely related to incorrect/invalid geometry formatting in the shape field. Also worth noting that this particular polygon does appear to have an "island" within it.

I have attached the full csv file (non-sensitive data) being used in PowerBI for this example. Columns included are GEOID20 and shape.

PowerBI Version: 2.104.702.0 64-bit (April 2022)

[Anonymous User · Accepted solution]

Hello @artj 

It looks like the polygon is too detailed and exceeds the Microsoft/Power BI 32,767 character limit for a single value. I can tell by looking at the CSV and seeing where the geometry bleeds into another row. Scanning the CSV, I do see multiple features that need adjusted.

There a a few options here:

1. Simplify the specific polygon in ArcGIS Pro, prior to publishing and pulling it through Power Automate.

2. Change the precision of the data to have less places after the decimal. This will affect the shape, but can help keep you under 32,767 for some polygons.

3. Simplify all the polygons you are using. This bulk approach will change all polys, which can be quicker but does affect all records.

Hope that helps,

Andrew

[artj]

@Anonymous User Thank you for the swift response! This is exactly what I needed to know - I was not aware of that character limit. Option 1 will be my likely course of action.

P.S., the custom polygon/line feature has been long awaited and I am stoked to see it's initial implementation. Now only if there was a quick-ish way to parse the geometry into the required format (using python at the moment).. I would bet that it's somewhere on the radar 😎

Best,

Art

[Anonymous User]

Hi Art!

You can leverage the ArcGIS connector in Power Automate to pull data from ArcGIS Online Hosted Feature Services into a CSV that can be loaded directly into Power BI. When stored in OneDrive, Power BI will automatically update when the CSV is updated.

No support for Enterprise yet, but we are looking at it.

Cheers,

Andrew

[MatthewPatterson2]

Hello @artj ,

How did you generate that CSV file from the features?  Specifically formatting the shape field to work with ArcGIS for PowerBI?

Thanks!

[AccGrow]

HI Art, 

Can you please explain how you created the CSV file? My polygons aren't displaying on the ArcGIS map in Power BI, even though I used the exact syntax you provided.  

[artj]

AccGrow,

I ended up writing a python script that utilizes the esrijson library to convert a csv file containing a WKT geometry to esriJSON format. Regarding your issue, it's possible that it could be due to the maximum character length that is allowed by Power BI (32,766). I included a function to handle this limitation by iteratively simplifying the geometry until the esrijson string is below the maximum character length. Hope this helps!

class PowerBIGeometryConverter:

    def __init__(

        self,

        key_col: str,

        wkt_col: str,

        delim="|",

        input_srid=2284,

        output_srid=4326

    😞

        self.key_col = key_col

        self.wkt_col = wkt_col

        self.delim = delim

        self.input_srid = f"EPSG:{input_srid}"

        self.output_srid = F"EPSG:{output_srid}"

        self.max_length = 32767

        self.tolerance = .00001

    def parse_esri_polygon(self, coordinates) -> str:

        ejson = esrijson.from_shape(coordinates)

        dumps = esrijson.dumps(ejson)

        return dumps

    def create_geodataframe(self, dframe: pd.DataFrame) -> gpd.GeoDataFrame:

        df = dframe.copy()

        df["geometry"] = df[self.wkt_col].astype(str).apply(lambda x: wkt.loads(x))

        df = df.drop(columns=[self.wkt_col])

        gdf = gpd.GeoDataFrame(df[[self.key_col, "geometry"]], geometry="geometry")

        gdf = gdf.set_crs(self.input_srid)

        gdf = gdf.to_crs(self.output_srid)

        return gdf

    def simplify_geos(self, gdframe: gpd.GeoDataFrame) -> gpd.GeoDataFrame:

        gdf = gdframe.copy()

        gdf["esrijson"] = gdf["geometry"].apply(lambda x: self.parse_esri_polygon(x)).astype(str)

        gdf["length"] = gdf["esrijson"].apply(lambda x: len(x))

        mask = gdf["length"] >= self.max_length

        n_geos_out_of_range = len(gdf.loc[mask])

        print("found", n_geos_out_of_range, "geometries out of range", self.tolerance)

        while n_geos_out_of_range > 0:

            print("simplifying", n_geos_out_of_range, "geometries at tolerance", self.tolerance)

            gdf.loc[mask, "geometry"] = gdf.loc[mask, "geometry"].simplify(tolerance=self.tolerance)

            gdf.loc[mask, "esrijson"] = gdf.loc[mask, "geometry"].apply(lambda x: self.parse_esri_polygon(x)).astype(str)

            gdf.loc[mask, "length"] = gdf.loc[mask, "esrijson"].apply(lambda x: len(x))

            mask = gdf["length"] >= self.max_length

            n_geos_out_of_range = len(gdf.loc[mask])

            min_length = gdf["length"].min()

            max_length = gdf["length"].max()

            print(n_geos_out_of_range, "geometries out of range at tolerance", self.tolerance)

            print("min length=", min_length, "max length=", max_length)

            self.tolerance += .00001

        return gdf

    def convert_pbi_dataframe(self, gdf: gpd.GeoDataFrame) -> pd.DataFrame:

        data = gdf[[self.key_col, "esrijson"]].copy()

        data.rename(columns={"esrijson": "shape"}, inplace=True)

        df = pd.DataFrame(data)

        return df

    def run(self, df: pd.DataFrame) -> pd.DataFrame:

        print("creating geodataframe...")

        gdf = self.create_geodataframe(df)

        print(f"simplifying geometries to minimum character length {self.max_length}...")

        gdf_simplified = self.simplify_geos(gdf)

        print("parsing geometry to esrijson...")

        df_out = self.convert_pbi_dataframe(gdf_simplified)

        return df_out

converter = PowerBIGeometryConverter(

    key_col=key_col,

    wkt_col=wkt_col

)

df = pd.read_csv(source_connection_string, sep=delimiter)

df_processed = converter.run(df)

df_processed.to_csv(sink_connection_string, index=False, sep=delimiter)