12 Replies Latest reply on Mar 3, 2013 8:53 PM by patfmnd

    Add list of Markers in google map from apex

    961972
      Hi,

      I would like to display markers on the google map from Apex.

      knowing that I have a table "client" in database oracle :

      code_client | adresse_client | laltitude | longitude | order
      ---------------------------------------------------------------------------------------
      00001 | Adresse1 | 30.45985 | -9.660478333 | 1
      00002 | Adresse2 | 30.45674333|-9.659188333 | 2
      00002 | Adresse2 | 30.50261333|-9.660558333 | 3
      .
      .
      .

      ---------------------------------------------------------------------------------------------

      I create Page in Apex and Region ..... in Body of this region I have this javascript code :


      <!DOCTYPE html>
      <html>
      <head>
      <meta http-equiv="content-type" content="text/html; charset=UTF-8" />
      <title>Google Maps Multiple Markers</title>
      <script src="http://maps.google.com/maps/api/js?sensor=false"
      type="text/javascript"></script>
      </head>
      <body>
      <div id="map" style="width: 500px; height: 400px;"></div>

      <script type="text/javascript">

      var locations = [
      ['Bondi Beach', -33.890542, 151.274856, 4],
      ['Coogee Beach', -33.923036, 151.259052, 5],
      ['Cronulla Beach', -34.028249, 151.157507, 3],
      ['Manly Beach', -33.80010128657071, 151.28747820854187, 2],
      ['Maroubra Beach', -33.950198, 151.259302, 1]
      ];

      var map = new google.maps.Map(document.getElementById('map'), {
      zoom: 10,
      center: new google.maps.LatLng(-33.92, 151.25),
      mapTypeId: google.maps.MapTypeId.ROADMAP
      });

      var infowindow = new google.maps.InfoWindow();

      var marker, i;

      for (i = 0; i < locations.length; i++) { 
      marker = new google.maps.Marker({
      position: new google.maps.LatLng(locations[1], locations[i][2]),
      map: map
      });

      google.maps.event.addListener(marker, 'click', (function(marker, i) {
      return function() {
      infowindow.setContent(locations[i][0]);
      infowindow.open(map, marker);
      }
      })(marker, i));
      }
      </script>
      </body>
      </html>

      -----------------------------

      this is a static data in this example but

      I want to Replace this array :

      var locations = [
      ['Bondi Beach', -33.890542, 151.274856, 4],
      ['Coogee Beach', -33.923036, 151.259052, 5],
      ['Cronulla Beach', -34.028249, 151.157507, 3],
      ['Manly Beach', -33.80010128657071, 151.28747820854187, 2],
      ['Maroubra Beach', -33.950198, 151.259302, 1]
      ];

      by my table "client" :

      does anyone 'can help me display markers by coordinates "laltitude" and "longitude" and display the infowindow "code_client" and "order" by clicking on the marker!???

      thanks
        • 1. Re: Add list of Markers in google map from apex
          VC
          Is this static data example working correctly on your page?

          If your answer is Yes then you just have to print your dynamic data using PL/SQL

          You can add a PL/SQL dynamic content region above the map region to print the data from your table in the required format
          //open
          htp.p('var locations = [');
          
          //here loop through your table to print the data in ['Bondi Beach', -33.890542, 151.274856, 4], format
          
          //close
          htp.p(']');
          In the past we have done similar using KML, in this case you have to generate your dynamic content into a .kml file

          These links might help
          http://display-kml.appspot.com
          http://www.kksou.com/php-gtk2/Joomla-Google-Maps-Samples/Use-KML-file-to-place-multiple-markers-on-Google-Maps.php
          • 3. Re: Add list of Markers in google map from apex
            961972
            @orhiee

            I followed your steps

            1)I created a procedure "show_points" :

            ******
            create or replace
            procedure show_points is

            cursor c_klt is
            select
            laltitude||','||longitude geoloc,
            code_client AS ID,
            type_client AS TYPE
            from client
            order by code_client;

            l_t number(3) := 0;

            begin
            htp.print('
            <script type="text/javascript">
            function initialize() {

            var center_map = new google.maps.LatLng(40.390227,49.872385);
            var mapOptions = {center: center_map,zoom: 10,mapTypeId: google.maps.MapTypeId.ROADMAP};
            var map = new google.maps.Map(document.getElementById("map_canvas"), mapOptions);
            var marker = new google.maps.Marker(
            {position: center_map,map: map,animation: google.maps.Animation.DROP,title:"center of map!"});
            ');
            for r_klt in c_klt
            loop
            htp.print('
            var geocoder = new google.maps.LatLng('||r_klt.geoloc||''||');

            var marker = new google.maps.Marker(
            {position: geocoder,map: map,animation:google.maps.Animation.DROP,title:"Client ID: '||r_klt.ID||''||' Client type: '||r_klt.TYPE||''||'"});
            ');

            l_t := l_t + 1;
            end loop;
            htp.print('
            }
            </script>
            ');

            htp.print('
            <body onload="initialize()" style="">
            </body>
            ');
            end show_points;

            ***************

            2) I created a page "page 1" with this 2 codes:

            *****HTML Header and Body Attribute****

            <script type="text/javascript"
            src="http://maps.googleapis.com/maps/api/js?key=&API_KEY&sensor=true">
            </script>

            *****Page HTML Body Attribute*********

            onload="initialize()"

            **********************************

            3) I created a region "Zone_Map" with PL/SQL :

            ******* (Source)**********

            begin
            show_points();
            end;

            *********

            4) in my project I configure substitutions with:

            *****
            Substitution String : API_KEY
            Substitution Value : ABQIAAAA9wWHEGNCfZW2F9-qCEHgohQ72da4xWLg1N8Ce02Xdu0VxpZsuRQSY7XJzvVLWb1SH9-ZtUeYmXh5_Q
            ***************


            The Result is an Empty Page :'( !!!!! ???

            can you help me more Please !
            • 5. Re: Add list of Markers in google map from apex
              963323
              Hi,

              i checked your code on my apex :)

              first of all your api key works :) but that shouldnt be puvlic info you shoud change it :)

              your sql seem right

              replace the lower part of your sql in your procedure and all should be fine :)
              *******************
              l_t number(3) := 0;

              begin
              htp.print('
              <script type="text/javascript">
              function initialize() {
              var center_map = new google.maps.LatLng(40.390227,49.872385);
              var mapOptions = {center: center_map,zoom: 10,mapTypeId: google.maps.MapTypeId.ROADMAP};
              var map = new google.maps.Map(document.getElementById("map_canvas"), mapOptions);


              var marker = new google.maps.Marker(
              {position: center_map,map: map,animation: google.maps.Animation.DROP,title:"center of map!"});

              ');
              for r_klt in c_klt
              loop
              htp.print('
              var geocoder = new google.maps.LatLng('||r_klt.geoloc||''||');

              var marker = new google.maps.Marker(
              {position: geocoder,map: map,animation:google.maps.Animation.DROP,title:"Client ID: '||r_klt.ID||''||' Client type: '||r_klt.TYPE||''||'"});


              ');


              l_t := l_t + 1;
              end loop;
              htp.print('
              }
              </script>
              ');


              htp.print('
              <body onload="initialize()" style="font-family: Arial;border: 0 none;">
              <div id="map_canvas" style="width: 1000px; height: 600px"></div>
              </body>
              ');
              end;
              ************************************************
              please confirm, that your sql query works :)

              yours,

              Edited by: orhiee on Oct 10, 2012 5:56 AM
              • 6. Re: Add list of Markers in google map from apex
                961972
                1) i replaced the code

                2) query in sql developer gives results

                3) i generate new API_KEY for (google map api 3)

                API_KEY = AIzaSyAkfoei4VuHwwj6F9ZjyhparzOeZ-YkCAQ


                but page is always empty :-( :'( !!!!


                I changed a browser ! it give me an error :

                "Google has disabled use of the Maps API for this application. The key provided is not a valid Google API key or is not an authorized key for version 3 of the Google Maps Javascript API on this site. If you are the owner of this application, please visit the following link for information about obtaining a valid key: # https://developers.google.com/maps/documentation/javascript/tutorial Obtaining_Key"
                • 7. Re: Add list of Markers in google map from apex
                  fatehcis-JavaNet
                  Hi,
                  Did you have a look at the Google map plugins available for APEX:

                  http://www.apex-plugin.com/oracle-apex-plugins/item-plugin/extension-warp11-gmaps-item_109.html
                  http://www.apex-plugin.com/oracle-apex-plugins/item-plugin/warp11-gmaps-item_87.html

                  Regards,
                  Fateh
                  • 8. Re: Add list of Markers in google map from apex
                    961972
                    can you could just export an application example apex with this plugin google map used a table ??

                    because i can not understand steps in this tutorials :-(

                    mail: moutambo@gmail.com

                    thanks
                    • 9. Re: Add list of Markers in google map from apex
                      961972
                      I found part of my solution :)

                      I used the plugin: Botoka's Google Map

                      http://www.apex-plugin.com/oracle-apex-plugins/region-plugin/botoka%27s-google-map_18.html

                      in Region attributes you just add your Query in SQL Statement and Markers displayed in Map :)

                      But the problem is it works with a Standard Request example:

                      " select code_client, laltitude, longitude from client
                      Where vendor = 'JOE'
                      laltitude and is not null
                      and longitude is not null "

                      but if i want to replace it with

                      " select code_client, laltitude, longitude from client
                      Where vendor =: Map_Selecteur_Vendeur
                      laltitude and is not null
                      and longitude is not null "

                      (Map_Selecteur_Vendeur: it's a item Select List in the page)

                      it's gives me an error :-(

                      and I would put 4 Select List and Each one with a color that is displayed in the same map! is that it is possible ?? :-(

                      geniuses help me :)
                      • 10. Re: Add list of Markers in google map from apex
                        fatehcis-JavaNet
                        Hi,

                        I tried this
                        http://www.apex-plugin.com/oracle-apex-plugins/item-plugin/extension-warp11-gmaps-item_109.html

                        and it is working fine
                        Please, review all values at "Settings" section are filled and have proper data type filling. Any lacking info at that section produces green screen. 
                        
                        For instance, try:
                        
                        StreetViewControl: YES
                        navigationControl: Yes
                        maptye: ROADMAP
                        maptypecontrol: YES
                        maptypeControl_position: TOP_RIGHT
                        mapTypeControl_style: DEFAULT
                        address: P1_ITEM (one of your items with session value which contains a name of a location)
                        Pin Icon: (this is the only possible blank)
                        Zoom: 10
                        
                        Ensure as well you have the following SQL structure under Lists Of values:
                        
                        select address, --For instance 52 street
                        name, -- Test
                        description, -- Description
                        'false', --In order to close infobox
                        icon_image, -- URL of image icon
                        latlong -- For instance 0
                        from your_table
                        • 11. Re: Add list of Markers in google map from apex
                          961972
                          @Fateh

                          Yes Great :)

                          Thanks

                          But


                          can i make 4 SELECT List with different colors markers at the same map ??

                          i WANT show 4 sellers with customers for example

                          Select List 1 - if I chose - "seller 1" - customers in map with" black" markers
                          Select List 2 - if I chose - "seller 2" - customers in map with "Red" markers
                          Select List 3 - if I chose -"seller 3" - customers in map with "yellow" markers
                          Select List 4 - if I chose - "seller 4" - customers in map with "white" markers

                          Thanks for help
                          • 12. UPDATE: Add list of Markers in google map from apex
                            patfmnd
                            Hi, Fateh,
                            I have tried that plugin that you refer to and have not been able to get it to work. Can you post a working example on apex.oracle.com that shows how the page needs to be set up--allowing a user to see the page structure? I think my problem is that I cannot figure out how to get the items defined before the map. If you don't want to post a demo, can you detail exactly how the page items and map plugin have to be 'assembled' on a page?

                            There used to be a plug -in 'Botoka map' which I had gotten to work a year ago but it is no longer on the plug in site.

                            Thanks,
                            Pat

                            UPDATE -- I finally got the map and markers to work. I was able to get it to work on apex.oracle.com!! Just copied my data and imported my application and it worked right away. So there must be some type of firewall/security or proxy problem with where we have our application hosted. Need to talk to our hosting company to get issue resolved. Thank you Oraclel for apex.oracle.com. So perhaps some of the other problems users have been having is related to the type of issue I had.

                            PM

                            Edited by: patfmnd on Mar 3, 2013 12:49 PM