Fast Reverse Geocoding Using R and a Local PostGIS Database

Most states like New York have Street Address Management system where you can download every address in the state as a Shapefile then upload it to a locally running PostGIS database.

view source
print?
01library(tidyverse)
02library(RPostgreSQL)
03library(sf)
04 
05con <- DBI::dbConnect(RPostgres::Postgres(), dbname='gis', host='localhost',
06                      port=5432, user='postgres', password='xxxxx')
07 
08# function that uses PostGIS query to return the closest
09# point in the SAM database
10reverseGeocode <- function(geom) {
11  # convert sf geom object to text
12  geom <- st_as_text(geom)
13   
14  # query
15  sql <- str_c("
16SELECT
17  *,
18  \"Shape\" <-> 'SRID=26918;", geom, "'::geometry AS dist
19FROM
20  nys_sam_points
21ORDER BY
22  dist
23LIMIT 1;")
24   
25  query <- dbGetQuery(con, sql)
26   
27  str_c(query$addresslabel,
28        ', ',
29        query$citytownname,
30        ', NY ',
31        query$zipcode
32        ) %>% return
33}
34 
35# obtain centroids for each county
36ny.county.centroids <- tigris::county_subdivisions('ny') %>%
37  st_centroid()
38 
39# get addresses of properties by reverse gecoding
40ny.county.centroids %>%
41  st_transform(26918) %>%   # use same projection
42                            # as your postgis database
43  mutate(
44    # obtain address using purr and map_chr using the
45    # reverseGeocode Function defined above
46    Address = map_chr(geometry, reverseGeocode)
47  )

Leave a Reply

Your email address will not be published. Required fields are marked *