Example: Street Address Geocoding

In this example we street address geocode a table of street addresses using the Google geocoder.   This example is also shown, using the Google geocoder, in the Street Address Geocoding using SQL YouTube video  in the Manifold Sales YouTube channel.


Google's web-based geocoding service allows geocoding a limited number of addresses per day for free.  A paid version of the service allows geocoding as many as desired.  In this example we will only geocode ten addresses so we can easily use the free service.    


We begin by launching File - Create - New Data Source to create a new data source in our project.




We would like to use a shorter name for this data source so we enter google_gcode as the Name.   In the Type box we choose Web Server: geocodeserver and then as a subtype we choose the Google Geocoder.    Press Create Data Source.





The google_gcode data source is created in the project pane.   While we are at it we also create a data source for the Google Streets imageserver and then we create a map and drop the Google streets layer into it.  We will use this map later on to display the locations of our geocoded records.


The project also contains a restaurants table that we will geocode.  The restaurants table contains the addresses for a selection of In-N-Out restaurants located in California, Utah and Texas.  



Clicking open the restaurants table we see that it contains an mfd_id index field, a number field that provides the official number of the restaurant in In-N-Out's database and an address field.


To geocode the restaurant table we create a query which we call Geocode Restaurants.  



The query text is


SELECT [mfd_id], [number], [address],

GeomMakePoint(GeocodeAddress([google_gcode],[address])) AS [Geom]

   INTO [geocoded restaurants]

   FROM [restaurants];


To deconstruct the above query, we are creating a new table by using SELECT ... INTO to populate the new table using fields from the restaurants table.  The most important line is


GeomMakePoint(GeocodeAddress([google_gcode],[address])) AS [Geom]


where we use the GeocodeAddress function to geocode the address field  using the google_gcode geocoding data source.   The float64x2 value returned by that function is converted by the GeomMakePoint function into a Manifold geom data type which is saved into the new table using the name Geom.


To run the query we press the ! button.




The Results tab reports 10 results.    




We can double-click open the new geocoded restaurants table the query created and see that it does indeed contain a list of restaurants with the number, address and a Geom field that contains a point geom for each.


Using the procedures discussed in the Example: Add a Spatial Index to a Table and  Example: Create a Drawing from a Geocoded Table topics we create a spatial index on the Geom field for the table, and then we create a drawing for that table,  launching Assign Initial Coordinate System with the coordinate system picker button  to assign Latitude / Longitude as the initial coordinate system.


We can then drop the new drawing into the map to see where the geocoded points are located.


In the illustration above we have used Style to color the points green for better visibility.   As expected, the points are located in California, Utah and Texas.


We zoom into the points in Texas for a closer look.



The point for the In-n-Out restaurant on Stacy Road in Allen, Texas, appears above, the third record in our geocoded table.


Variations in Geocoded Data Type

The above is a typical procedure for geocoding a table in Manifold, where we create Manifold geom fields so the geometry is immediately usable in Manifold.   If desired, we can also create different data types for the result of geocoding, either by later converting the Manifold geom field using the Transform panel or by writing a different query initially.




SELECT [mfd_id], [number], [address],

GeocodeAddress([google_gcode],[address]) AS [longlat]

   INTO [longlat restaurants]

   FROM [restaurants];



For example, the above simply stores the float64x2 vector value created by the GeocodeAddress function without converting it into a geom.   We run the query by pressing the ! button.



As before, we get ten results.


We double-click open the resulting longlat restaurants table.



The longlat field contains a float64x2 vector, which we can see by hovering the mouse over the column header to see the data type reported in a tooltip.



We named the field longlat to remind us that coordinates are reported in X,Y order, that is longitude first and then latitude.  


Suppose we would like to geocode a table to create separate longitude and latitude fields for use by some other application?  That is easy to do:




SELECT [mfd_id], [number], [address],

VectorValue(GeocodeAddress([google_gcode],[address]), 1) AS [lat],

VectorValue(GeocodeAddress([google_gcode],[address]), 0) AS [lon]

   INTO [latlon restaurants]

   FROM [restaurants];


Using procedures similar to those in the Example: Create a Geocoded Table from a Drawing topic, The above query uses the VectorValue function to extract the latitude and the longitude values from the results of GeocodeAddress.    This isn't the most efficient way to use a geocoder since we end up calling the geocoder twice for each address, but it does make what is going on completely obvious.


We run the query by pressing the ! button.



Again, there are 10 results and a new table is created.



We double-click the table to open it and to see the above, a table with a latitude and a longitude for each record.   tech_ravi_sm.png


Caution:  The query above that runs the GeocodAddress function twice, once to then extract a latitude and once to then extract a longitude is not efficient since in both cases we are getting a latitude and longitude.  It is presented as written to show the idea in a very simple way.  It would be smarter to simply save the latitude and longitude values received from running it once and then extracting from there.  Running the GeocodAddress function twice in short succession can also anger Google, which might return a NULL for the latitude or the longitude to show its displeasure at asking the same thing twice.  We can get around all that by creating a geom and then using the procedure in the Example: Create a Geocoded Table from a Drawing topic to generate separate Latitude and Longitude fields from the geom.  


Using the Manifold Geocoding Database

The Manifold Geocoding Database (GCDB) is a legacy database originally created from US Census Bureau data years ago to provide a free US address geocoding data source for Manifold GIS users.  It is a free download on the Manifold web site.   The data is now old and out of date so it doesn't make sense to use the GCDB if we can use a free web geocoding server.  However, the GCDB is totally free, it is local and requires no Internet link, and it can be useful if we simply want to run some addresses through a geocoder to generate a group of geocoded addresses for testing or other such purposes without needing to worry about geocoding each and every address.


The GCDB consists of individual .dat data files for each US state plus a states.dat file that covers all states.   Manifold has a geocoding dataport that allows Manifold to use the GCDB as a geocoding data source.


To create a data source for GDCB we launch the New Data Source dialog.




We enter a short name GCDB and choose File: datgdcb as the Type.   We click the [...] Browse button to specify the location of the GDCB data files we will use.





We browse to the location where the GCDB files are stored on our machine and click on the states.dat file and then press Open.  If we will be geocoding addresses only within one US state we could achieve faster performance by choosing only that state's .dat file.   In this case we will be geocoding files from three states so we choose the states.dat file to have all US states included.





We press Create Data Source and that creates a new data source called GCDB in the project.


Next we open the Command Window and write a query that uses that data source to geocode our table of In-n-Out restaurant addresses.



SELECT [mfd_id], [number], [address],

GeomMakePoint(GeocodeAddress([GCDB],[address])) AS [Geom]

   INTO [gcdb restaurants]

   FROM [restaurants];


The query is similar to the ones used earlier in this topic.   We use the GCDB data source as the geocoding data source.


Running the query produces a new gcdb restaurants table.  We can add a spatial index to that table and then create a drawing to display the Geom in that new table, launching Assign Initial Coordinate System with the coordinate system picker button  to assign Latitude / Longitude as the initial coordinate system.


We then drag and drop that new drawing into the map to see how the points geocoded by GCDB line up with those geocoded by Google.




In the illustration above the GCDB points have been styled as yellow squares.     Zoomed into the region near the In-n-Out restaurant in Allen, Texas, we see a difference between the location geocoded for the same address, a typical situation when using different geocoders.




The above is a photo of the actual restaurant, from the Spatial Data topic.



NULLs - When a geocoding server returns a NULL for an address that usually means one of several  things:



See Also



Data Types


Web Servers and Image Servers


Street Address Geocoding


Adding an Index to a Table - A basic topic on adding an index using the built-in capabilities of the mfd_id field.


File - Create - New Data Source


Command Window


Assign Initial Coordinate System


Style: Drawings


Example: Add a Spatial Index to a Table - A typical use of an index is to provide a spatial index on a geom field in a table, so the geom data can be visualized in a drawing. This topic provides the step by step procedure for adding a spatial index.


Example: Create a Geocoded Table from a Drawing - A partner example to this topic.  A geocoded table has records with a latitude and longitude for each record.   This example starts with a table for a drawing of points where the geom field in the table contains geometry information for each point.   We extract the Y and X locations for each point  from the geom field to create latitude and longitude fields in the table for each record.


Example: Create a Drawing from a Geocoded Table - A partner example this topic.  A geocoded table has records with a latitude and longitude for each record.   This example starts with a table containing a list of cities with a latitude and longitude field for the location of each city.   We create a geom from the latitude and longitude fields using a template in the Transform panel and then we create a drawing that shows the cities as points.  This example shows all the infrastructure steps involved.


Example: Create a Drawing from Read-Only Geocoded CSV File - A detailed example using a mix of dialogs and SQL to create a drawing that shows data from an external, read-only geocoded CSV file that is linked into the project.