HubL Reference

Table of contents
Close

Let's Build A Page with a Map, using HubDB

One of the exciting things that HubDB enables on the COS is using location to allow visitors to find just what they want. HubDB natively supports locations as a field type and has a number of features that making building location-based pages easy and fun.

In this tutorial, I’ll show you how to create a page with a map on HubSpot’s COS. We will use HubDB. You can reference more detailed HubDB documentation here.

You’ll need:

  • A HubSpot account with the $300 Website Add-On
    • Portals with Website Starter or without the Website Add-On do not support HubDB
    • You can check if your portal has the $300 Website Add-On by signing in here.
  • Approximately 1 hours
  • Some prior knowledge of HubSpot's COS, HTML and CSS will be needed to customize your page

What's a Location?

When you create a column on a HubDB table with the "Location" type, you're designating this column as a place to store coordinates that point to a location on the Earth.

A location is often represented as coordinates (a pair of decimal numbers) such as 42.36, -71.11. The first number is the latitude, or the degrees North or South of the equator. The second number is the longitude, or the degrees from the Prime Meridian. The Prime Meridian is an imaginary line starting at the North Pole, running through Greenwich, England, France, Spain, Algeria, Ghana and ending at the South Pole. Longitudes east of that line are positive, west are negative. At 180 degrees, the positive and negative degree values meet.

1. Find the Visitor's Location

With the Global Positioning System (GPS), it's much easier for us to determine the location of a visitor to your site. Every modern mobile phone device has a GPS chip that can receive GPS signals. The device can also use nearby Wi-Fi networks or mobile network towers to determine its location. Laptop and desktop computers typically use Wi-Fi to determine their location.

Within a page loaded in web browser, you can use JavaScript to request the device's location.


<script> navigator.geolocation.getCurrentPosition(function(position) { console.log("I'm at " + position.coords.latitude + ", " + position.coords.longitude); }); </script>

This will first ask the visitor if it's OK for the page to get the visitor's location, and then report the coordinates. It will log something like this to the console:


I'm at 42.370354299999995, -71.0765916

Great! Now we have the visitor's location.

 

2. Build a Delicious Database

Now that we know the visitor's location, we can help them find things around them. In this example, we'll build a database of lunch spots.

Create a new table in HubDB and add two columns: 'location' (type: Location) and for just a little more info, 'cuisine' (type: Text).

It should look like this:

You can build your own database of local eateries or you can import some data for lunch spots near the HubSpot HQ in Cambridge, Massachusettes -- here's a file you can use: lunch-spots.csv. Upload the file to HubDB, and map the columns as shown.

Once you finish the import, you should have a delicious list of eateries that looks like this: 

Publish your table, make a note of the ID of the table (the last number in the URL) and you're ready to start using your data.

3. Create A Simple Listing Page

The first thing we can do with this data is create a simple list using HubL and the HubDB functions.

Create a new template in Design Manager using the Code Editor. You can name it something like "lunchspots.html".

Add this code just after <body> tag, using the table ID you noted above.


{% set table_id = YOUR_TABLE_ID %} <table> {% for row in hubdb_table_rows(table_id) %} <tr> <td>{{ row.name }}</td> <td>{{ row.cuisine }}</td> </tr> {% endfor %} </table>

Click over to the preview of the template and you should see a simple list of restaurants and their cuisine. If nothing appears, double check that your table ID is correct and that the table is published.

4. Filter to a Single Listing

Your listing page can also double as a detail page with a little logic. Let's link the restaurant name to a page specific to that restaurant. Make sure to leave in the set table_id... line here and in all other code samples on this page.


<table> {% for row in hubdb_table_rows(table_id) %} <tr> <td><a href="?{{ request.query }}&row_id={{ row.hs_id }}">{{ row.name }}</a></td> <td>{{ row.cuisine }}</td> </tr> {% endfor %} </table>

Each row will now be linked back to the same page with a query string parameter in row_id which is the unique id of this row. Note the use of request.query to make sure we preserve any existing query string parameters on the current URL.

Now we'll add the details:


{% if request.query_dict['row_id'] %} {% set row = hubdb_table_row(table_id, request.query_dict['row_id']) %} <h1>{{ row.name }}</h1> <h3>{{ row.cuisine }}</h3> <p>{{ row.location['lat'] }}, {{ row.location['lon'] }}</p> {% else %} <table> {% for row in hubdb_table_rows(table_id) %} <tr> <td><a href="?{{ request.query }}&row_id={{ row.hs_id }}">{{ row.name }}</a></td> <td>{{ row.cuisine }}</td> </tr> {% endfor %} </table> {% endif %}

The if statement ({% if request.query_dict['row_id'] %}) determines whether to show the details or skip down to the else block to show the listing. Our details page is a little bare, but it works. It will look something like this:

5. How Far From Your Visitor?

Now let's ask for the visitor's location, so we can show them how far away each restaurant is. Change your code to the following.


{% if request.query_dict['row_id'] %} {% set row = hubdb_table_row(table_id, request.query_dict['row_id']) %} <h1>{{ row.name }}</h1> <h3>{{ row.cuisine }}</h3> <p>{{ row.location['lat'] }}, {{ row.location['lon'] }}</p> {% elif request.query_dict['lat'] %} <table> {% for row in hubdb_table_rows(table_id) %} <tr> <td><a href="?{{ request.query }}&row_id={{ row.hs_id }}">{{ row.name }}</a></td> <td>{{ row.cuisine }}</td> <td>{{ row.location|geo_distance(request.query_dict['lat'], request.query_dict['lon'], "mi")|round(3) }} mi away</td> </tr> {% endfor %} </table> {% else %} Please allow us to read your location so we can show you the closest lunch spots. <script> navigator.geolocation.getCurrentPosition(function(position) { window.location = window.location.href + "&lat=" + position.coords.latitude + "&lon=" + position.coords.longitude; }); </script> {% endif %}

This code introduces an if statement. In the default case, it asks the visitor to share their location. If they accept, it redirects the page with lat and lon query parameters. The second time the page loads, it shows the list, now with a 3rd column that is the calculated distance from the provided location.

Since row.location just returns the restaurant's coordinates, we use the geo_distance filter to calculate the distance. It takes the visitor's latitude, longitude and the units of the distance. The units default to meters ("M"), but also accept kilometers ("KM"), miles ("MI") and feet ("FT"). Lastly, we round the number a bit to make it more readable.

6. Sort the List

This list is OK, but especially if the list got much bigger, it would be nice to show the closest restaurants first. You can make this work by adding sorting options to the hubdb_table_rows function. Change this section as follows:


... {% elif request.query_dict['lat'] %} {% set params = "orderBy=geo_distance(location," ~ request.query_dict['lat'] ~ "," ~ request.query_dict['lon'] ~ ")" %} <table> {% for row in hubdb_table_rows(table_id, params) %} <tr> ...

Now the list will be sorted by distance between the location column and the users' coordinates. The key is building the geo_distance ordering function which ends up looking like orderBy=geo_distance(42.37,-71.076). If we wanted to sort the list in reverse order, with the restaurants the furthest away first, we'd prepend a - (minus sign) to geo_distance.

7. Filter by Distance

Again, this code will work fine for a small list of locations, but what if you had hundreds, or thousands? It would be huge. Luckily, we can filter the list by distance as well. We'll use our friend geo_distance, this time as a query filter, to find all restaurants less than 1 mile away.


... {% elif request.query_dict['lat'] %} {% set params = "orderBy=geo_distance(location," ~ request.query_dict['lat'] ~ "," ~ request.query_dict['lon'] ~ ")" %} {% set params = "geo_distance(location," ~ request.query_dict['lat'] ~ "," ~ request.query_dict['lon'] ~ ",mi)__lt=1.0&" ~ params %} <table> {% for row in hubdb_table_rows(table_id, params) %} <tr> ...

This constructs another parameter to the the hubdb query which looks like geo_distance(location,42.37,-71.076,mi)__lt=1.0

The geo_distance query filter takes 4 arguments. First is the location column you want to filter by. Conveniently we named ours "location". The next 3 parameters should look familiar. They're the latitude, longitude and distance units. After the geo_distance operator, we specify the filter operator, "less than" or "lt". Finally, after the equal sign is the value to compare with the result of the function with. 

8. Map it Out

While we've made this great list, it would be better if it was more visual, say on a map. The Google Maps APIs make this really simple. Replace the block of code inside the second if condition (elif request.query_dict['lat']) with this:


<style> #map { width: 100%; height: 1200px; } </style> <div id="map" height="500"></div> <script> var map; function initMap() { map = new google.maps.Map(document.getElementById('map'), { center: {lat: 42.3665171, lng: -71.0820328}, zoom: 16 }); var marker_origin = new google.maps.Marker({ map: map, title: "you are here", icon: { scaledSize: {width:50, height:50}, url: "http://maps.google.com/mapfiles/kml/paddle/blu-blank.png"}, position: {lat: {{ request.query_dict['lat'] }}, lng: {{ request.query_dict['lon'] }}} }); {% for row in hubdb_table_rows(table_id) %} var marker_{{ row.hs_id }} = new google.maps.Marker({ map: map, position: {lat: {{ row.location["lat"] }}, lng: {{ row.location["lon"] }}}, title: '{{ row.name }}', icon: { scaledSize: {width:40, height:40}, url: "http://maps.google.com/mapfiles/kml/shapes/dining.png"} }); marker_{{ row.hs_id }}.addListener('click', function() { new google.maps.InfoWindow({ content: '<div> {{ row.name }} is {{ row.location|geo_distance(request.query_dict['lat'], request.query_dict['lon'], "mi")|round(3) }} miles away</div>' }).open(map, marker_{{ row.hs_id }}); }); {% endfor %} } </script> <script src="https://maps.googleapis.com/maps/api/js?key=YOUR_GOOGLE_API_KEY&callback=initMap" async defer></script>

Substitute in your table ID and your Google API key and you should now see an interactive map in place of the listing. Your map should look like this (with your blue origin marker wherever you are).

9. Over to You

That's it! You've built a page which uses a visitor's location to find a local place to eat. The key is the geo_distance function which can used to filter, order and calculate the distance. This simple page could be improved by adding a toggle between the listing and map view, a map and more info on the details page, and perhaps a link to each restaurant's website.

But what if the visitor's browser cannot determine their location or they want to look for places in a different area? You could allow them to type in a location and find the coordinates for it using the the Google Maps Geocoding API.

Eat up!