Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-341

Use Google Maps API with EXASOL

    XMLWordPrintable

    Details

    • Type: How To
    • Status: Published
    • Affects Version/s: EXASolution 5.0, EXASOL 6.0.0, Exasol 6.1.0
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Solution:
      Hide

      See https://www.youtube.com/watch?v=C3eyqTw3HsQ for a video demonstration of this process

      1. First sign up for a developer account at Google https://developers.google.com/
      2. Now create a Google developer project, activate the APIs you need, and most importantly, generate an API key - this will need to be used when you run your script Slide5.JPG Slide6.JPG
      3. You can now import the UDF libraries in EXAOperation in the usual way - see attached for the Python libraries Slide7.JPG

      Here are some simple examples of how you can use the Google Map data within EXASOL.

      You should replace the API key given here with you own key

      Geocoding

      Slide9.JPG

       CREATE or replace PYTHON SET SCRIPT google_geocode(placename varchar(200)) emits (lat double, lng double) AS
      import googlemaps
      def run(ctx):
      	gmaps = googlemaps.Client(key='AIzaSyCUyZBkLKMvFCpDaxm-4kxuvMURGuiQt4Y')
      	geocode_result = gmaps.geocode(ctx.placename)
      	for this_place in geocode_result:
      		lat=this_place["geometry"]["location"]["lat"]
      		lng=this_place["geometry"]["location"]["lng"]
      		ctx.emit(lat,lng)
      /
      
      select google_geocode('10 Downing Street, London, UK') from dual;

      Reverse Geocoding

      Slide10.JPG

      CREATE or replace PYTHON SET SCRIPT google_reverse_geocode(lat double, lng double) emits (placename varchar(20000)) AS
      import googlemaps
      def run(ctx):
      	gmaps = googlemaps.Client(key='AIzaSyCUyZBkLKMvFCpDaxm-4kxuvMURGuiQt4Y')
      	reverse_geocode_result = gmaps.reverse_geocode((ctx.lat, ctx.lng))
      	for this_result in reverse_geocode_result:
      		if str(this_result["geometry"]["location_type"])=='ROOFTOP':
      			ctx.emit(str(this_result["formatted_address"]))
      /
      
      select google_reverse_geocode(51.5034066, -0.1275923) from dual;

      Nearest Bakery to EXASOL Head Office

      Slide8.JPG

      CREATE OR REPLACE PYTHON SET SCRIPT google_nearest(address_search varchar(2000), category_search varchar(2000)) 
      EMITS (placename varchar(20000), geolocation varchar(200)) AS
      import googleplaces
      from googleplaces import GooglePlaces
      
      def run(ctx):
      	google_places = GooglePlaces('AIzaSyCUyZBkLKMvFCpDaxm-4kxuvMURGuiQt4Y')
      	query_result = google_places.nearby_search(
      	   location=ctx.address_search, keyword=ctx.category_search, rankby='distance')
      
      	for this_place in query_result.places:
      		ctx.emit(this_place.name, str(this_place.geo_location))
      
      /
      
      select google_nearest('Neumeyerstrasse, Nuremberg, Germany', 'Bakery') from dual;

      Driving distance and time between two points

      Slide11.JPG

      CREATE or replace PYTHON SET SCRIPT google_summary_directions(from_place varchar(20000), to_place varchar(20000)) 
      emits (distance_metres double, duration_seconds double) AS
      import googlemaps
      def run(ctx):
      	gmaps = googlemaps.Client(key='AIzaSyCUyZBkLKMvFCpDaxm-4kxuvMURGuiQt4Y')
      	query_result = gmaps.directions(ctx.from_place, ctx.to_place, mode='driving')
      	for this_result in query_result:
      		for legs in this_result["legs"]:
      			distance_metres=legs["distance"]["value"]
      			duration_seconds=legs["duration"]["value"]
      			ctx.emit(distance_metres, duration_seconds)
      /
      
      
      select google_summary_directions('Buckingham Palace, Westminster, London', '10 Downing Street, London') from dual;

      Detailed Satellite Navigation between two points

      Slide12.JPG

      CREATE or replace PYTHON SET SCRIPT google_detail_directions(from_place varchar(20000), to_place varchar(20000)) 
      emits (html_instructions varchar(2000), distance varchar(100), duration varchar(100)) AS
      import googlemaps
      def run(ctx):
      	gmaps = googlemaps.Client(key='AIzaSyCUyZBkLKMvFCpDaxm-4kxuvMURGuiQt4Y')
      	query_result = gmaps.directions(ctx.from_place, ctx.to_place, mode='driving')
      	for this_result in query_result:
      		for legs in this_result["legs"]:
      			steps=legs["steps"]
      			for this_step in steps:
      				turn_description=this_step["html_instructions"]
      				turn_distance=this_step["distance"]["text"]
      				turn_duration=this_step["duration"]["text"]
      				ctx.emit(turn_description, turn_distance, turn_duration)
      /
      
      select google_detail_directions('10 Downing Street, London', 'Houses of Parliament, London') from dual;
      Show
      See https://www.youtube.com/watch?v=C3eyqTw3HsQ for a video demonstration of this process First sign up for a developer account at Google https://developers.google.com/ Now create a Google developer project, activate the APIs you need, and most importantly, generate an API key - this will need to be used when you run your script Slide5.JPG Slide6.JPG You can now import the UDF libraries in EXAOperation in the usual way - see attached for the Python libraries Slide7.JPG Here are some simple examples of how you can use the Google Map data within EXASOL. You should replace the API key given here with you own key Geocoding Slide9.JPG CREATE or replace PYTHON SET SCRIPT google_geocode(placename varchar(200)) emits (lat double , lng double ) AS import googlemaps def run(ctx): gmaps = googlemaps.Client(key= 'AIzaSyCUyZBkLKMvFCpDaxm-4kxuvMURGuiQt4Y' ) geocode_result = gmaps.geocode(ctx.placename) for this_place in geocode_result: lat=this_place[ "geometry" ][ "location" ][ "lat" ] lng=this_place[ "geometry" ][ "location" ][ "lng" ] ctx.emit(lat,lng) / select google_geocode( '10 Downing Street, London, UK' ) from dual; Reverse Geocoding Slide10.JPG CREATE or replace PYTHON SET SCRIPT google_reverse_geocode(lat double , lng double ) emits (placename varchar(20000)) AS import googlemaps def run(ctx): gmaps = googlemaps.Client(key= 'AIzaSyCUyZBkLKMvFCpDaxm-4kxuvMURGuiQt4Y' ) reverse_geocode_result = gmaps.reverse_geocode((ctx.lat, ctx.lng)) for this_result in reverse_geocode_result: if str(this_result[ "geometry" ][ "location_type" ])== 'ROOFTOP' : ctx.emit(str(this_result[ "formatted_address" ])) / select google_reverse_geocode(51.5034066, -0.1275923) from dual; Nearest Bakery to EXASOL Head Office Slide8.JPG CREATE OR REPLACE PYTHON SET SCRIPT google_nearest(address_search varchar(2000), category_search varchar(2000)) EMITS (placename varchar(20000), geolocation varchar(200)) AS import googleplaces from googleplaces import GooglePlaces def run(ctx): google_places = GooglePlaces( 'AIzaSyCUyZBkLKMvFCpDaxm-4kxuvMURGuiQt4Y' ) query_result = google_places.nearby_search( location=ctx.address_search, keyword=ctx.category_search, rankby= 'distance' ) for this_place in query_result.places: ctx.emit(this_place.name, str(this_place.geo_location)) / select google_nearest( 'Neumeyerstrasse, Nuremberg, Germany' , 'Bakery' ) from dual; Driving distance and time between two points Slide11.JPG CREATE or replace PYTHON SET SCRIPT google_summary_directions(from_place varchar(20000), to_place varchar(20000)) emits (distance_metres double , duration_seconds double ) AS import googlemaps def run(ctx): gmaps = googlemaps.Client(key= 'AIzaSyCUyZBkLKMvFCpDaxm-4kxuvMURGuiQt4Y' ) query_result = gmaps.directions(ctx.from_place, ctx.to_place, mode= 'driving' ) for this_result in query_result: for legs in this_result[ "legs" ]: distance_metres=legs[ "distance" ][ "value" ] duration_seconds=legs[ "duration" ][ "value" ] ctx.emit(distance_metres, duration_seconds) / select google_summary_directions( 'Buckingham Palace, Westminster, London' , '10 Downing Street, London' ) from dual; Detailed Satellite Navigation between two points Slide12.JPG CREATE or replace PYTHON SET SCRIPT google_detail_directions(from_place varchar(20000), to_place varchar(20000)) emits (html_instructions varchar(2000), distance varchar(100), duration varchar(100)) AS import googlemaps def run(ctx): gmaps = googlemaps.Client(key= 'AIzaSyCUyZBkLKMvFCpDaxm-4kxuvMURGuiQt4Y' ) query_result = gmaps.directions(ctx.from_place, ctx.to_place, mode= 'driving' ) for this_result in query_result: for legs in this_result[ "legs" ]: steps=legs[ "steps" ] for this_step in steps: turn_description=this_step[ "html_instructions" ] turn_distance=this_step[ "distance" ][ "text" ] turn_duration=this_step[ "duration" ][ "text" ] ctx.emit(turn_description, turn_distance, turn_duration) / select google_detail_directions( '10 Downing Street, London' , 'Houses of Parliament, London' ) from dual;
    • Category 1:
      UDFs and In-Database Analytics
    • Category 2:
      ETL / Data Integration

      Attachments

        Issue Links

        1. Slide10.JPG
          Slide10.JPG
          98 kB
        2. Slide11.JPG
          Slide11.JPG
          106 kB
        3. Slide12.JPG
          Slide12.JPG
          139 kB
        4. Slide5.JPG
          Slide5.JPG
          73 kB
        5. Slide6.JPG
          Slide6.JPG
          94 kB
        6. Slide7.JPG
          Slide7.JPG
          97 kB
        7. Slide8.JPG
          Slide8.JPG
          170 kB
        8. Slide9.JPG
          Slide9.JPG
          93 kB

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated: