Geocoding from Different Database

The datasets used by pgGeocoder's geocoder and reverse_geocoder functions are quite hefty and will require more than several gigabytes of resources, especially when historical data are also installed. This is the reason why pgGeocoder is customarily installed only on a single database and all geocoding and reverse_geocoding processes are done on that database.

There are instances though that there are needs to use pgGeocoder on data found in an another Database, and moving the data into the Database containing pgGeocoder is not practical. This Wiki will try to show how it is possible to geocode data found in a table located in statistics database and call the geocoder installed on a separate addresses2020 database using PostgreSQL's dblink function.

Using DBLINK

dblink is a PostgreSQL function that makes it possible to execute a SQL query from a different Database. This package is normally included already in recent PostgreSQL versions, and can easily be installed into a database by a CREATE EXTENSION call:

statistics=# create extension dblink;

Calling geocoder located in another Database

Here, the geocoder function located in the address2020 database will be called to geocode addresses found in the table taddr:

select address from taddr;

      address       
--------------------
 函館市栄町７－３
 函館市大町４－４
 室蘭市幸町７番６号
 函館市時任町８－５
 函館市柳町９番５号
 江東区冬木5-2
 板橋区大門7-5
 江東区石島４－４
 江東区千田6-1
 板橋区仲町１－４
 池田市栄町３番３号
 交野市松塚１番１号
 守口市淀江町３－５
 守口市祝町９－１８
 池田市栄町７－１３
 名護市城１－１－３
 那覇市辻２－４－１
 那覇市東町２６－１
 那覇市西３－４－１
 那覇市山下町3-1
(20 rows)

And from here, dblink function will be used to call the geocoder function and geocode the addresses seen above. The dbname=addresses2020 parameter instructs dblink to connect to the addreses2020 database and execute the SQL geocoder query.

select gc.code,gc.x,gc.y,a.address,gc.output_address from taddr a,
  dblink('dbname=addresses2020',
   format('select code,x,y,address from geocoder(''%s'')',a.address)) 
   as gc(code int,x float,y float,output_address text);

The resulting output of the query will be:

 code |       x       |      y       |      address       |     output_address      
------+---------------+--------------+--------------------+-------------------------
    5 | 140.723849549 | 41.764847246 | 函館市栄町７－３   | 北海道函館市栄町7-3
    5 | 140.708756321 | 41.768591869 | 函館市大町４－４   | 北海道函館市大町4-4
    5 | 140.970960237 | 42.315100519 | 室蘭市幸町７番６号 | 北海道室蘭市幸町7-6
    5 | 140.751891535 | 41.785361264 | 函館市時任町８－５ | 北海道函館市時任町8-5
    5 | 140.760768095 | 41.792977704 | 函館市柳町９番５号 | 北海道函館市柳町9-5
    5 | 139.802139069 | 35.673290533 | 江東区冬木5-2      | 東京都江東区冬木5-2
    5 |   139.6489284 |  35.78332249 | 板橋区大門7-5      | 東京都板橋区大門7-5
    5 | 139.811179767 | 35.680283239 | 江東区石島４－４   | 東京都江東区石島4-4
    5 | 139.815582756 | 35.680816736 | 江東区千田6-1      | 東京都江東区千田6-1
    5 |   139.6995335 |  35.75216472 | 板橋区仲町１－４   | 東京都板橋区仲町1-4
    5 | 135.424813079 | 34.822802778 | 池田市栄町３番３号 | 大阪府池田市栄町3-3
    5 | 135.670882514 | 34.792646098 | 交野市松塚１番１号 | 大阪府交野市松塚1-1
    5 | 135.567628291 | 34.748239101 | 守口市淀江町３－５ | 大阪府守口市淀江町3-5
    5 | 135.559816044 | 34.732592095 | 守口市祝町９－１８ | 大阪府守口市祝町9-18
    5 | 135.425545386 | 34.823655372 | 池田市栄町７－１３ | 大阪府池田市栄町7-13
    5 |   127.9821749 | 26.591162107 | 名護市城１－１－３ | 沖縄県名護市城一丁目1-3
    5 | 127.672455839 | 26.218052182 | 那覇市辻２－４－１ | 沖縄県那覇市辻二丁目4-1
    5 | 127.673542041 | 26.213388049 | 那覇市東町２６－１ | 沖縄県那覇市東町26-1
    5 | 127.667601796 | 26.214933424 | 那覇市西３－４－１ | 沖縄県那覇市西三丁目4-1
    5 |  127.66841311 | 26.203934604 | 那覇市山下町3-1    | 沖縄県那覇市山下町3-1
(20 rows)

Calling reverse_geocoder located in another Database

Using the same process, a reverse_geocoder query can also be accomplished using the dblink function.

select * from dblink('dbname=addresses2020',
  format('select address,todofuken,shikuchoson,ooaza from 
   reverse_geocoder(%L,%L);',139.811179767,35.680283239)) 
   as gc(address text,todofuken text,shikuchoson text,ooaza text);

And the result of the query will be:

       address       | todofuken | shikuchoson | ooaza 
---------------------+-----------+-------------+-------
 東京都江東区石島4-4     | 東京都      | 江東区       | 石島
(1 row)

Note

There will be a performance degradation when calling a SQL query from a remote database. If heavy geocoding or reverse_geocoding performance is of the utmost importance, it will be well to consider moving the necessary data into the same database where pgGeocoder has been installed.