Posts by angomera

  • Looking everywhere for VBA to do reverse geocoding, not just for address

    Re: Looking everywhere for VBA to do reverse geocoding, not just for address



    Trebor76 the solution doesnt work for me. Can you be more precise? Can you send me a new file I can download?

  • Looking everywhere for VBA to do reverse geocoding, not just for address

    Re: Looking everywhere for VBA to do reverse geocoding, not just for address



    The part I understood is you attached the file with corrections. I download the file you posted here, run the macro and it didnt populate.


    Am I missing something?

  • Looking everywhere for VBA to do reverse geocoding, not just for address

    Re: Looking everywhere for VBA to do reverse geocoding, not just for address



    Trebor76 I created a new VBA module with the new code you post, I executed the macro and I get a popup that says: "Done". But no data gets populated on the workbook. I appreciate a lot your effort.


    Thanks a lot. :)

  • Looking everywhere for VBA to do reverse geocoding, not just for address

    Re: Looking everywhere for VBA to do reverse geocoding, not just for address



    Its amazing what you have done, thanks for taking the time to help.


    I do have a problem. I downloaded your file XML Extract and I go to the macros list and run the macro "ReverseGeocoding" and then I get an error popup windows that says: "Excel stoped working" and then excel closes. I checked out the reference to Microsoft XML, v6.0 and its correct.


    I also tried opening a new excel file, I opened the vba window, copied your code as a new module, added the reference to Microsoft XML, v6.0, added latitude and longitude in row two and run the macro. Now I get an error that says: "Compilation error, the type defined by the user has not been defined." Then I added a reference to Microsoft XMl, v3.0 and the error disapeared when running the macro. But nothing happenes when running it, info doesn't get populated.


    I am really new to macros so I might be making a very little mistake. I feel so frustrated because after what you have done I feel like one step away from making it.


    BTW what do you mean by "run the macro while on that tab" ?


    Appreciate any help.
    Thanks.

  • Looking everywhere for VBA to do reverse geocoding, not just for address

    Re: Looking everywhere for VBA to do reverse geocoding, not just for address


    Quote from Trebor76;784869

    So from that xml file what are the neighborhood and sublocality names you need?


    The User Defined Function (UDF) bryce mentions in thread 6 is using that URL so that is part of the solution I would imagine. This thread might also help.


    Trebor76, in the next xml: https://maps.googleapis.com/ma…TKgfJAeQSlETDadjoQHsXBF6Y


    You can find neigborhood and sublocality_level_1 marked in green here


    <address_component>
    <long_name>Emaus</long_name>
    <short_name>Emaus</short_name>
    <type>neighborhood</type>
    <type>political</type>
    </address_component>

    <address_component>
    <long_name>Chapinero</long_name>
    <short_name>Chapinero</short_name>
    <type>political</type>
    <type>sublocality</type>
    <type>sublocality_level_1</type>
    </address_component>


    But in the link you shared which can be really helpfull I found that sublocality_level can go through one to 5. "sublocality indicates a first-order civil entity below a locality. For some locations may receive one of the additional types: sublocality_level_1 through to sublocality_level_5. Each sublocality level is a civil entity. Larger numbers indicate a smaller geographic area."


    So I know now my need is: Neigborhood, locality, sublocality_level_1, sublocality_level_2, sublocality_level_3, sublocality_level_4 and sublocality_level_5, administrative_area_level_1, administrative_area_level_2, administrative_area_level_3, administrative_area_level_4 and administrative_area_level_5


    In the next code you will see an example of sublocality_level_1, locality, administrative_area_level_3 and administrative_area_level_1 From a Hotel in Mexico City: https://maps.googleapis.com/ma…TKgfJAeQSlETDadjoQHsXBF6Y


    <address_component>
    <long_name>Portales Norte</long_name>
    <short_name>Portales Nte</short_name>
    <type>political</type>
    <type>sublocality</type>
    <type>sublocality_level_1</type>
    </address_component>

    <address_component>
    <long_name>Ciudad de México</long_name>
    <short_name>México D.F.</short_name>
    <type>locality</type>
    <type>political</type>
    </address_component>

    <address_component>
    <long_name>Benito Juárez</long_name>
    <short_name>Benito Juárez</short_name>
    <type>administrative_area_level_3</type>
    <type>political</type>
    </address_component>

    <address_component>
    <long_name>Ciudad de México</long_name>
    <short_name>CDMX</short_name>
    <type>administrative_area_level_1</type>
    <type>political</type>
    </address_component>


    So conslusion:

    1. What I needed before got bigger, sorry for that but your link really helped :)
    2. Not all cities shows the same data so the macro must be good to ignore missing information.
    3. Google takes up to 2,500 free requests per day which is fine by me.


    Check out this nice url: https://developers.google.com/…cript/geocoding?hl=es-419


    Appreciate a lot your help.


  • Looking everywhere for VBA to do reverse geocoding, not just for address

    Re: Looking everywhere for VBA to do reverse geocoding, not just for address


    Quote from bryce;784865

    Robert just means how would you find the data you want through google maps. I.E. how would you take a set or coordinates and have google maps return a neighborhood etc.


    I think the answer is here...just need ot figure out how to get it written in VBA


    http://maps.googleapis.com/map…24,-73.961452&sensor=true


    Apparently google maps api only allows to post one lafitude and one longitude into the url for getting the json file or the xml file.


    I thing the process of the macro would be to take the latitude in column A1 and the longitude in column B1, place it in the google maps url just like this: https://maps.googleapis.com/ma…TKgfJAeQSlETDadjoQHsXBF6Y
    Then into that file look for the values neigborhood and sublocality.


    And repeat de proces for a2, b2; a3, b3, etc

  • Looking everywhere for VBA to do reverse geocoding, not just for address

    Re: Looking everywhere for VBA to do reverse geocoding, not just for address


    Sorry for not cross linking, im very new.
    I don't understand your question.
    Lets say I have 100 spots in thr world and its correspondant latitude and longitude in a database of mine. On the excel workbook I will imput the latitude in column 1A, the longitude in column B1; and column C1 will have a macro that gives me the neigborhood, and in Column D1 a kacro that gives me the sublocality. Thats it. I need the macros.

  • Looking everywhere for VBA to do reverse geocoding, not just for address

    Re: Looking everywhere for VBA to do reverse geocoding, not just for address


    Quote from bryce;784858

    Another quick search yeilds this


    https://usefulgyaan.wordpress.…/07/05/reverse-geocoding/


    for the 500 coordinates you can use a loop. If you posy that book I will help to the extent that I can. I have never used the API but it seems interesting


    Check this out: http://oco-carbon.com/coding/google-maps-and-excel-download/


    Please download the file, its a workbook with macros enabled. In that file you can see a very good example of a macro that allready does reverse geocoding. The address field is the result of the macro. The thing is I don't need the address cos I allready have it.


    Now, what I need is one macro in one column that calls neigborhood; another macro in another column that calls sublocality. And that is it.


    Thing is I dont know macros. If I would know, I would edit the macro that calls the address in that file and edit the part that calls the address and change it to call neigborhood and sublocality.


    Can you do that?

  • Looking everywhere for VBA to do reverse geocoding, not just for address

    Re: Looking everywhere for VBA to do reverse geocoding, not just for address


    Quote from bryce;784854

    You are going to have to read up on the API.


    https://developers.google.com/…ng/intro#ReverseGeocoding


    The article I posted should get you most of the way there.


    I have read that page 10 times. By doing it I can get the info im talking about by a url with parameters. The thing is I need to get the info for 500 different coordinates. I don't see any information on that page that tells you how to write a macro for getting address, neigborhood, locality, sublocality, etc for a given latitude and longitude. I think you don't understand what I need. Please read above to understand.

  • Looking everywhere for VBA to do reverse geocoding, not just for address

    Re: Looking everywhere for VBA to do reverse geocoding, not just for address


    Quote from bryce;784839

    This guys did just that.


    http://www.myengineeringworld.…using-vba-google-api.html


    That link is a solution for geocoding. Getting latitud and longitude from a given address.


    What I need as explained in the title and post is Reverse Geocoding, wich is getting the address from a given latitude and longitude. But not only that. I need to go a bit further and get: neighborhood, sublocality, sublocality_level_1 and locality.


    But thanks anyway.

  • Can someone write a macro for me to do reverse geocoding with my needs?

    Hello,


    I have been looking everywhere for a macro that can help me do reverse geocoding: https://developers.google.com/…mentation/geocoding/intro Reverse Geocoding basically consists of getting the address of a place after providing latitude and longitude.



    I need to get the next data after providing latitud and longitud:

    • neighborhood
    • sublocality
    • sublocality_level_1
    • locality
    • Maybe another here ( I have to check)


    You can see a better example here: https://maps.googleapis.com/ma…TKgfJAeQSlETDadjoQHsXBF6Y



    What I need is a macro that gets all this components for every latitude and longitude I provide.



    Can someone write this for me?

  • Looking everywhere for VBA to do reverse geocoding, not just for address

    Hello,


    I have been looking everywhere for a macro that can help me do reverse geocoding: https://developers.google.com/…mentation/geocoding/intro Reverse Geocoding basically consists of getting the address of a place after providing latitude and longitude.



    I need to get the next data after providing latitud and longitud:



    • neighborhood
    • sublocality
    • sublocality_level_1
    • locality



    You can see a better example here: https://maps.googleapis.com/ma…TKgfJAeQSlETDadjoQHsXBF6Y



    What I need is a macro that gets all this components for every latitude and longitude I provide.



    Can someone help me with this?