12 Replies Latest reply on Feb 23, 2017 12:07 AM by jwellsnh

    Plotting Lat/Long Coordinates stored in an SQL table on Google maps in an APEX region

    PPlatt

      APEX 4.2.6 ! NOT APEX 5

      I have a table with saved lat/long coordinates for landmarks such as powerpoles.  I want to plot multiple coordinates on a google map via the lat/long coordinates NOT via addresses as most landmarks do not have postal addresses. I need a solution to run an sql query, returning the lat/long coords and then plotting them on Google Maps.  I have Geocoding and plotting for mulltiple addresses working but I now need it for cases where there are no addresses, (except postcode city and country of course), once again my client is on APEX 4.2 NOT Apex 5.

      thanks in advance

      PaulP

        • 1. Re: Plotting Lat/Long Coordinates stored in an SQL table on Google maps in an APEX region
          jwellsnh

          Hi Paul,

           

          It's been quite a while since I experimented with Google maps and am almost positive I constructed this application and page via 4.2.  Unfortunately I no longer have access to 4.2 but here is a demo of 4.2 code running untouched in 5.1.  The report allows you to select a facility and then map its location with marker via Google.  If this meets your needs, I can share the code but my knowledge of the subject is pretty stale.

           

          https://apex.oracle.com/pls/apex/f?p=18762:LOGIN::::::

           

          Username and Password are both demo.

           

          Select Map/Google from the drop-down menu.

           

          Jeff

          • 2. Re: Plotting Lat/Long Coordinates stored in an SQL table on Google maps in an APEX region
            Carsten Czarski-Oracle

            Hi PaulP,

             

            just to mention it: it's not Google Maps, but APEX 4.2.6 contains a Packaged App "Sample Geolocation Showcase" which contains some ready-to-use region plug-ins for that purpose. All you have to do is to provide a SQL query and you are ready to go. This app is, of course, also available in 5.0 and 5.1.

             

            Perhaps that helps

             

            Best regards

             

            -Carsten

            • 3. Re: Plotting Lat/Long Coordinates stored in an SQL table on Google maps in an APEX region
              PPlatt

              Thanks Jeff but I need to plot multiple lat/longs on the same map.  I've got what you suggested up and running but it's multiple marker rendering that I'm having a problem with.

              regards

              Paul P

              • 4. Re: Plotting Lat/Long Coordinates stored in an SQL table on Google maps in an APEX region
                PPlatt

                Thanks Carsten for your reply but I need to use Google maps as the country Haiti is not supported/available with Oracle maps.??!. strange but true. Yes the Geolocation showcase would have been perfect for my needs except for this one small detail.  If you pass any Lat/Longs in the code for Haiti, e.g. Port-au-Prince you get a blank map but the strange thing is that if you pass Lat/Longs for the Dominican Republic with is right next door to Haiti on the same island it renders the map correctly.  Not sure what Oracle/Nokia maps have against Haiti??

                regards

                PaulP

                • 5. Re: Plotting Lat/Long Coordinates stored in an SQL table on Google maps in an APEX region
                  Carsten Czarski-Oracle

                  Hi Paul,

                   

                  in the Region Attributes of each Oracle Maps Plug-In region, you can change the "Map Tile Layer" from "Oracle eLocation" to either Microsoft Bing Maps or OpenStreetMap - perhaps that helps. Google is, unfortunately, not available out-of-the-box - that would require Javascript coding ...

                   

                  Best regards

                   

                  -Carsten

                  • 6. Re: Plotting Lat/Long Coordinates stored in an SQL table on Google maps in an APEX region
                    jwellsnh

                    Paul,

                     

                    Here is code from another map that plots multiple map points/markers and also draws directions.

                     

                    Jeff

                     

                    jQuery(function($) {
                        // Asynchronously Load the map API 
                        var script = document.createElement('script');
                        script.src = "https://maps.googleapis.com/maps/api/js?sensor=false&callback=initialize";
                        document.body.appendChild(script);
                    });
                    function initialize() {
                        var map;
                        var bounds = new google.maps.LatLngBounds();
                        var mapOptions = {
                            mapTypeId: 'roadmap'
                        };
                        // Display a map on the page
                        map = new google.maps.Map(document.getElementById("map_canvas"), mapOptions);
                        map.setTilt(45);
                        // Multiple Markers
                        var markers = [
                            ['Flat Rock', 43.649099, -71.327217, '#APP_IMAGES#FlatRock.png'],
                            ['19-Mile Bay', 43.647613, -71.278181, '#APP_IMAGES#red_MarkerA.png'],
                            ['Little Bear Island', 43.641296, -71.323596, '#APP_IMAGES#red_MarkerB.png'],
                            ['Boat Launch', 43.662679, -71.348981, '#APP_IMAGES#slipway.png']
                        ];
                        // Info Window Content
                        var infoWindowContent = [
                            ['<div class="info_content">' +
                                '<h3>Flat Rock</h3>' +
                                '<p>Flat Rock of Wellswood is located on East Point of Long Island, Lake Winnipesaukee.</p>' +
                                '</div>'
                            ],
                            ['<div class="info_content">' +
                                '<h3>19-Mile Bay</h3>' +
                                '<p>19-Mile Bay docks and store.  A great place to fill up your boat gas tank and cool off with some fresh ice cream!</p>' +
                                '</div>'
                            ],
                            ['<div class="info_content">' +
                                '<h3>Little Bear Island</h3>' +
                                '<p>Off its western and southern shores are great fishing spots for salmon in the early spring.</p>' +
                                '</div>'
                            ]
                            ['<div class="info_content">' +
                                '<h3>Boat Launch</h3>' +
                                '<p>Boat launch</p>' +
                                '</div>'
                            ]
                        ];
                        // Display multiple markers on a map
                        var infoWindow = new google.maps.InfoWindow(),
                            marker, i;
                        // Loop through our array of markers & place each one on the map  
                        for (i = 0; i < markers.length; i++) {
                            var position = new google.maps.LatLng(markers[i][1], markers[i][2]);
                            bounds.extend(position);
                            marker = new google.maps.Marker({
                                position: position,
                                map: map,
                                title: markers[i][0],
                                icon: markers[i][3]
                            });
                            // Allow each marker to have an info window    
                            google.maps.event.addListener(marker, 'click', (function(marker, i) {
                                return function() {
                                    infoWindow.setContent(infoWindowContent[i][0]);
                                    infoWindow.open(map, marker);
                                }
                            })(marker, i));
                            // Automatically center the map fitting all markers on the screen
                            map.fitBounds(bounds);
                        }
                        // init directions service
                        var dirService = new google.maps.DirectionsService();
                        var dirRenderer = new google.maps.DirectionsRenderer({
                            suppressMarkers: true
                        });
                        dirRenderer.setMap(map);
                        // highlight a street
                        var request = {
                            origin: "43.667002, -71.347597",
                            destination: "43.649181, -71.327512",
                            travelMode: google.maps.TravelMode.DRIVING
                        };
                        dirService.route(request, function(result, status) {
                            if (status == google.maps.DirectionsStatus.OK) {
                                dirRenderer.setDirections(result);
                            }
                        });
                        // Override our map zoom level once our fitBounds function runs (Make sure it only runs once)
                        var boundsListener = google.maps.event.addListener((map), 'bounds_changed', function(event) {
                            this.setZoom(13);
                            google.maps.event.removeListener(boundsListener);
                        });
                    }
                    
                    • 7. Re: Plotting Lat/Long Coordinates stored in an SQL table on Google maps in an APEX region
                      PPlatt

                      I seem to have a working solution now but the only problem is that I can only plot up to 35 markers.  Is there a limit? and is there a workaround?

                      thanks in advance

                      PaulP

                      • 8. Re: Plotting Lat/Long Coordinates stored in an SQL table on Google maps in an APEX region
                        jwellsnh

                        Wish I had an answer for you but without seeing your code I would suggest taking your issue to a Google Map forum.  35 seems to be an odd limitation.

                         

                        Jeff

                        • 9. Re: Plotting Lat/Long Coordinates stored in an SQL table on Google maps in an APEX region
                          PPlatt

                          OK I worked it out! I can now print multiple markers on google maps using lats and longs from the database.  The issue about the limit of markers came down to having degrees and radians as some of the lats/long rather than decimals in the database.  When I updated the data into the correct format all markers plotted!  Here is a nice and easy solution to reading lats and longs from a DB table and then plotting them as markers on google maps.

                          NOTE: you will need your own API Key from google (available from https://console.developers.google.com ) and of course you need to change the cursor to your own columns and table.

                          Put the following code in an APEX region of type "PL/SQL Dynamic Content".  That's all you have to do.

                          Declare
                          latCentre varchar2(250);
                          lngCentre varchar2(250);
                          Cursor geocur is
                             Select ELIT_UID As l_id,ELIT_CODE||'-'||ELIT_NAME  As l_desc, 
                                    ELIT_LATITUDE As l_lat,
                                    ELIT_LONGITUDE As l_lng
                             From ELIT_TOWER 
                             Order By ELIT_UID;
                          Begin
                          htp.print('<div id="googleMap" style="width:100%;height:400px;"></div>');
                          htp.print('<script>');
                          htp.print('function myMap() {');
                          htp.print('var mapCanvas = document.getElementById("googleMap");');
                          --My map is centered on the lat/long of Honiara, in the Solomons
                          htp.print('var myCenter = new google.maps.LatLng(-9.4456,159.9729);');
                          htp.print('var mapProp= {');
                          htp.print('    center: myCenter');
                          htp.print('    ,zoom:8');
                          htp.print('    };');
                          htp.print('var map=new google.maps.Map(mapCanvas,mapProp);');
                          htp.print('var markerBounds = new google.maps.LatLngBounds();');
                          
                          --loop for multiple markers
                          For georec in geocur Loop
                            htp.print(' var myLatLng = {
                                          lng: ' || georec.l_lng || ',
                                          lat: ' || georec.l_lat || '
                                          }');
                            htp.print('var markerProp = { ');
                            htp.print('    position: myLatLng ');
                            htp.print('    ,title:  "' || georec.l_desc || '" ');
                            htp.print('    };');
                            htp.print('var marker = new google.maps.Marker(markerProp);');
                            htp.print('marker.setMap(map);');
                            htp.print('markerBounds.extend(myLatLng);');
                          End Loop;
                            htp.print('map.fitBounds(markerBounds);');
                          htp.print('}'); --close function myMap
                          htp.print('</script>');
                          --Add you own Google API key below
                          htp.print('<script src="https://maps.googleapis.com/maps/api/js?key=ADD_YOUR_API_KEY_HERE&callback=myMap"></script>');
                          End;
                          

                           

                          • 10. Re: Plotting Lat/Long Coordinates stored in an SQL table on Google maps in an APEX region
                            jwellsnh

                            So in other words, were some of your markers on top of one another and would not render or were not visible due to the zoom level?

                             

                            Jeff

                            • 11. Re: Plotting Lat/Long Coordinates stored in an SQL table on Google maps in an APEX region
                              PPlatt

                              Hi Jeff,

                              Two rows of data were in the format

                              160°41'34.50"E08°45'12.30"S

                              rather than

                              160.692917-8.753417

                              When it came across these values to plot as markers on the map, the loop aborted and only showed markers up to that point. i.e. 35

                              PaulP

                              • 12. Re: Plotting Lat/Long Coordinates stored in an SQL table on Google maps in an APEX region
                                jwellsnh

                                Great news on your end Paul, I plan to experiment with your htp.print method.  I did some research the other night and saw that there is a concept of clustering markers within Google maps.  Essentially a marker cluster allows one to click on it then zoom and launch the map displaying all of the markers associated  Not sure it's applicable to your situation but thought readers might find it worth while to investigate.

                                 

                                Jeff