Power Platform
Power Platform
Organizer:
- JOSE Organized by
-
-
Python (Shapely) or DAX – calculate Area and perimeter of Polygons
-
Python (Shapely) or DAX – calculate Area and perimeter of Polygons
-
Hi Community!I’m trying to figure out if this is even possible in DAX :Ā calculating the area and perimeter ofĀ polygons in DAX .
Ā Here is my test data set:
Polygon Ā Order Ā Ā Ā X Ā Ā Y Armada Ā 0 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,707 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,920 Armada Ā 1 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,720 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,938 Armada Ā 2 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,738 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,938 Armada Ā 3 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,752 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,958 Armada Ā 4 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,772 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,945 Armada Ā 5 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,770 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,933 Armada Ā 6 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,753 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,933 Armada Ā 7 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,747 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,925 Armada Ā 8 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,726 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,921 Armada Ā 9 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,721 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,913 Armada Ā 10 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,707 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,920 Barbaras Ā 0 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,758 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,917 Barbaras Ā 1 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,747 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,925 Barbaras Ā 2 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,726 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,921 Barbaras Ā 3 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,721 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,913 Barbaras Ā 4 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,729 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,907 Barbaras Ā 5 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,736 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,907 Barbaras Ā 6 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,758 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,917 Coconuss Ā 0 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,769 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,922 Coconuss Ā 1 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,758 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,917 Coconuss Ā 2 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,747 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,925 Coconuss Ā 3 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,753 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,933 Coconuss Ā 4 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,770 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,933 Coconuss Ā 5 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,769 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,922 Descripta Ā 0 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,707 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,920 Descripta Ā 1 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,690 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,927 Descripta Ā 2 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,709 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,978 Descripta Ā 3 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,738 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,968 Descripta Ā 4 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,746 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,963 Descripta Ā 5 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,752 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,958 Descripta Ā 6 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,738 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,938 Descripta Ā 7 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,720 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,938 Descripta Ā 8 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,707 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 439,920 Eliptica Ā 0 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,769 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 440,209 Eliptica Ā 1 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,767 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 440,198 Eliptica Ā 2 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,753 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 440,177 Eliptica Ā 3 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,745 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 440,183 Eliptica Ā 4 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,753 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 440,195 Eliptica Ā 5 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,752 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 440,202 Eliptica Ā 6 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,762 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 440,217 Eliptica Ā 7 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 93,769 Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā 440,209 Ā
Ā
(Order is the vertice number and needs to be in that order)Ā
Ā
Or if this is not possible directly in DAX, whether anyone has the skills in doing this in Python during data load in Power Query. I’m thinking shapely ? I’ve tested the below code and it gives the same (very close at least) to what I get from my 3D modelling package
Ā
##Āfrom shapely.geometry import Polygon coords = ((93839.0588, 440544.24), (93856.1497, 440568.57), (93870.6033, 440589.233), (93862.4824, 440595.037), (93853.77, 440582.709), (93822.5886, 440537.878), (93859.032, 440512.267), (93841.9089, 440487.256), (93858.5466, 440475.645), (93866.9771, 440487.881), (93882.9585, 440476.785), (93877.4614, 440468.637), (93889.9265, 440459.975), (93902.9131, 440487.405), (93920.0571, 440511.777), (93916.2681, 440532.708), (93887.9194, 440552.97), (93879.3518, 440540.655), (93899.8176, 440526.171), (93887.7273, 440491.686), (93867.6099, 440505.955), (93876.1829, 440518.291), (93839.0588, 440544.24) ) polygon = Polygon(coords) polygon.area
##
Area =Ā 3925.58
Perimeter =Ā 566.96
Ā
And then using polygon.length and polygon.area to give area and perimeter, but I’m not sure how to get this to run through the complete table of polygons and export the list on unique polygons with their respective areas and perimeters through Power Query.I’m stuck trying to figure out how to get the data sucked in and referenced in the tuples, I guess. Huge newb at Python and here I am trying to do this.Ack!
Ā
Thanks heaps in advance for any pointers
Ā
Cheers
Ā
Manfred??
——————————
Manfred Wimberger——————————
-
Hi Manfred,
I was intrigued by your post. I think it would be difficult to do in DAX but Shapley seems to be exactly the tool for the job! The biggest problem, as you have pointed out is grouping the data and passing it to the Shapely function.Here’s the python code I used. I took the source data from a csv file – you will need to modify the code to use your path for the source file.
from shapely.geometry import Polygon import pandas as pd def processPolygon(Coord_list): ###Pass list of co-ordinates to Shapely Polygon function polygon=Polygon(Coord_list) perimeter=polygon.length area=polygon.area return area,perimeter #path for source data path="YOUR PATH/data2.csv" #create dataframe from source df=pd.read_csv(path) #create co-ordinate column in dataframe df["Co-ords"]=list(zip(df["X"],df["Y"])) #create dictionary of co-ordinate lists for each polygon poly_dict=df.groupby("Polygon")["Co-ords"].apply(lambda x:x.tolist()).to_dict() #create list for results results=[] #pass co-ordinates to fuction and return area and perimeter for poly,Coords in poly_dict.items(): area,perimeter=processPolygon(Coords) results.append([poly,area,perimeter]) #create new dataframe from results df_results=pd.DataFrame(results,columns=["Polygon","Area","Perimeter"]) ?
These are the results I got from Shapely – check if they match your expectations.
I have attached the pbix and csv files – you will need to modify the paths for the source.
Let me know how you get on,
Colin.——————————
colin mcilwain
Lead Process Specialist
——————————
——————————————- -
Hi Ā
This is perfect! So very happy you could help me! The results are within ~0.001% of what I was getting out of a process in Vulcan, which is awesome.
I originally posted this question in a another forum and just heard crickets back, so I’m happy the question intrigued you.
This is on a path to learning more about intergarting Python into Power BI and dealiong with polygon geometriesĀ Ā (Ā Shapes and geometry descriptionsĀ ) and getting coordinates or vertices converted from one system to another ( another topic altogether ) .
I am trying to describe the behavior of polygons and associated behaviours using the following measurements:
- Perimeter and Area to allow for calculating the Perimeter to Area ratio (solved thanks to you and Compactness = (perimeter^2) / (4*pi* area)Ā (solved thanks to you)
- Elongation using Minor* and Major Axes Length*Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā ( still to do)
- Area of MBR ( minimum bounding rectangle)Ā Ā (probably just major axis x minor axis)Ā Ā Ā Ā Ā Ā Ā Ā Ā (still to do)
- Convex Area* and Convex Perimeter* using Convex HullsĀ Ā (still to do)
- Convexity = ( convex perimeter/ perimeter)Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā ( can be calced from 1 and 4)
- Solidity ( Convex Area / Area)Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā Ā (can be calced from 1 and 4)
- RectangularityĀ (ratio of the polygon to the area of the minimum bounding rectangle). ( can be calced from 1 and 3)
Notes to 1:Ā I’m using a compactness equation which compares the area of a polygon to the area of a circle with the same perimeter. This shows how close a polygon is to a circle.
Notes to 3 and 4: An issue that I’m trying to resolve is how elongate a polygon is, along with the convex perimeter and convex area using thisĀ Convex HullĀ but I’m not sure how to get those exterior points extracted..
TheĀ Major and Minor Axes would be to calculate how elongate a polygon is.
I’m now trying to work out if it is possible to generate the major and minor axis of a polygonĀ Min Maj AxisĀ and someone said theĀ Ā Ā Minimum Bounding RectangleĀ , is required.
If you have done this before, any pointers would be super useful, especially if its possible to put into the same summary which you created before šĀ Ā
Thanks heaps again and looking forward to your thoughts.
Cheers
Manfred
?——————————
Manfred Wimberger——————————
——————————————- -
Hi Manfred,
I’m glad you found this helpful. I haven’t used Shapely before, so thanks for bringing it to my attention – I can see the value of integrating tools to perform complex functions with the ease of use of Power BI.Ā
You could easily add other Shapely functions into the definedĀ processPolygonĀ function. The MBR is given byĀ minimum_rotated_rectangleĀ and the convex hull byĀ convex_hull.Ādef processPolygon(Coord_list): ###Pass list of co-ordinates to Shapely Polygon function polygon=Polygon(Coord_list) perimeter=polygon.length area=polygon.area bounds=polygon.bounds bounding_rect=polygon.minimum_rotated_rectangle convex_hull=polygon.convex_hull return area,perimeter,bounds,bounding_rect,convex_hull
Might need to do a bit of DAX to extract the individual co-ordinates from these functions.
You could use the same method with the sci.py functions as well.
Colin.
——————————
colin mcilwain
Lead Process Specialist
——————————
——————————————-
-
-
0 Replies
Sorry, there were no replies found.
The discussion ‘Python (Shapely) or DAX – calculate Area and perimeter of Polygons’ is closed to new replies.
