12 Replies Latest reply: Mar 3, 2013 2:53 PM by patfmnd RSS

    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
          • 2. Re: Add list of Markers in google map from apex
            963323
            i had a similer project my post link

            have a look :)

            google maps, multiple markers/polyline, cordinates from db
            • 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
                    Mindmap
                    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
                          Mindmap
                          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