Power BI Location using ESRI Shape field

955
1
03-07-2018 11:35 AM
TylerJesse
New Contributor

I am trying to make heat maps for our CMMS (computerized maintenance management system. E.g. Which assets are needing repairs in what area the most. 

 

I have connected Power BI to our sde SQL database. 

 

Problem:

There are no latitude or longitude coordinates in the sde tables. The GIS guy here at work says ESRI finds the location using the "Shape" field which looks like "POINT (2306636.8429999985 14740070.512000002)." From what I can tell the "Shape" field is a reference to some underlying ERSI data which knows how to relate and display the data. SO,  when I drag this field to the "Location" box in Power BI, some points show up, but are spread across the planet. Apparently Power BI does not know how to deal with "Shape" information.

 

Is there a way I can get the Shape data in a format Power BI would understand? 

 

Any help would be appreciated!

example of data:

0 Kudos
1 Reply
RobertKrisher
Esri Regular Contributor

Good news / bad news on this one.  The bad news is that (as you know) POWER BI only supports lat/long.  The good news is that if you can convince your GIS guy to add columns to your table that have those values you can pull them in via sql.  Unfortunately Sql server doesn't have a way to do this on the fly via a query (see thread here: https://community.esri.com/thread/122469 ).  But your GIS guy should be able to whip up a python script that could recalculate these values periodicaly.

One additional note, it looks like you're hitting whats know in SDE as the 'base table', you should talk to your GIS guy to make sure that you're querying the 'versioned views' for the table, that way you're always getting the most up to date version of the data.

0 Kudos