topic Re: Some polygons not displaying in PowerBI visual when using EsriJSON in Location field well in ArcGIS for Power BI Questions

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

artj — Tue, 26 Apr 2022 19:23:33 GMT

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:

artj_1-1651000422968.png

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.

artj_0-1651000343275.png

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)

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

Anonymous User — Tue, 26 Apr 2022 19:41:47 GMT

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.

AndrewStauffer_0-1651001858869.png

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

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

artj — Tue, 26 Apr 2022 20:00:15 GMT

@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

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

Anonymous User — Wed, 27 Apr 2022 14:10:29 GMT

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

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

MatthewPatterson2 — Wed, 18 Jan 2023 18:44:24 GMT

Hello @artj ,

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

Thanks!

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

AccGrow — Thu, 18 Apr 2024 10:33:01 GMT

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.

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

artj — Thu, 23 May 2024 12:51:38 GMT

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)

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

GulfRegion — Fri, 14 Mar 2025 12:09:24 GMT

Do you run the script on it's own or do you run it in Arc or PowerBI?