locked
Point Inside a polygon RRS feed

  • Question

  • Good Morning,
    I thank you for your help in solving the problem in question ...
    I started now to study power bi and for the beginning I decided to take some old work solutions in excel and translate them into power bi in order to learn and train solutions.
    The attached files present a problem with this portability, since part of the solution was made in VBA and among my researches it has no function or functions directly for the case.
    The files are on the share: https://drive.google.com/drive/folders/1fGaetFdrXK5TKQVoiR5yHznW-aSQQTc5?usp=sharing
    In this folder you have 2 files:
    1. "Mapa - Distritos Administrativos.kml" - this is a standard file (xml format) of google earth that contains polygons that are not the neighborhoods of the city, but another division;
    a) In the file, between the <coordinates> and </coordinates> keys contains groups of 3 coordinates referring to the vertices of the polygon, for example: -46.65471793567885, -23.43065206873295.0 which are -46.65471793567885 (Longitude), -23.43065206873295 (Latitude) and 0 (height in relation to the ground), for calculations only Longitude and latitude are used.
    2. "Ponto no mapa (Pedido de Ajuda).xlsb" - excel file that has the solution that I want to bring to power bi, since it has a considerable processing time due to its matrix structures, I think the best solution be it working in powerquery (M language) and so only in the update process will be slow and the use of power bi by the user will not be harmed; but feel free to present another solution.
    a) There are 2 spreadsheets in the file:
    a.1) "Poligonos" that contains a table with 5 columns: Name of the polygon (agglomeration of neighborhoods of the city), Region - regarding the location of the polygon (North, South, East and etc ...), the grouped coordinates and the last 2 which are the separation of Longitude and Latitude of each vertex.
    a.2) "Pontos" containing 3 tables with the application of the solution: the first that goes from columns A to E that contains the points to be tested; the second that goes from column F to CW (only the first line shows the application of the formula and the others were pasted as values ​​to save processing time) and the last 2 are the expected result for each point they are in the columns CX and CY presenting the polygon name and the Region.
    a.2.i) The solution uses a function in VBA that tests each vertex point angle versus the angle and returns true if the point is contained in the polygon.

    Thanks
    Monday, September 14, 2020 1:31 PM

Answers

  • Hi there. It might be better if you broke the problem down into pieces and posted questions about the parts you're having trouble with. Posting a large, complete solution in one technology and asking how to port it to another seems like too large a task for most of the folks here to tackle on your behalf.

    Can you clarify what exactly you need help with?

    Ehren

    Monday, September 21, 2020 11:04 PM

All replies

  • Hi there. It might be better if you broke the problem down into pieces and posted questions about the parts you're having trouble with. Posting a large, complete solution in one technology and asking how to port it to another seems like too large a task for most of the folks here to tackle on your behalf.

    Can you clarify what exactly you need help with?

    Ehren

    Monday, September 21, 2020 11:04 PM
  • Thanks for you time and answer, I'll rewrite the question breaking it in parts...
    Tuesday, September 22, 2020 3:00 PM