Stewart’s

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.

library(tidyverse)
library(RPostgreSQL)
library(sf)

con <- DBI::dbConnect(RPostgres::Postgres(), dbname='gis', host='localhost', 
                      port=5432, user='postgres', password='xxxxx')

# function that uses PostGIS query to return the closest
# point in the SAM database
reverseGeocode <- function(geom) {
  # convert sf geom object to text
  geom <- st_as_text(geom)
  
  # query
  sql <- str_c("
SELECT 
  *,
  \"Shape\" <-> 'SRID=26918;", geom, "'::geometry AS dist
FROM
  nys_sam_points
ORDER BY
  dist
LIMIT 1;")
  
  query <- dbGetQuery(con, sql)
  
  str_c(query$addresslabel, 
        ', ',
        query$citytownname,
        ', NY ',
        query$zipcode
        ) %>% return
}

# obtain centroids for each county
ny.county.centroids <- tigris::county_subdivisions('ny') %>% 
  st_centroid()

# get addresses of properties by reverse gecoding
ny.county.centroids %>%
  st_transform(26918) %>%   # use same projection 
                            # as your postgis database
  mutate(
    # obtain address using purr and map_chr using the 
    # reverseGeocode Function defined above
    Address = map_chr(geometry, reverseGeocode)
  )

March 8, 2023 Morning

|

Good morning! What day is it? Well the as signs on the Staten Island Expressway say, Upgrade Maintain Speed. 🛣️ That and as you approach Verrazano spelled with one Z Bridge, Warning Sudden Stops. Don’t say the Triborough Authority didn’t warn you what is ahead with this week. ⚠

Mostly cloudy and 26 degrees in the Delmar for the morning walk. ☁ There is a northwest breeze at 14 mph. 🍃. The current wind chill is 15. There are 2 inches of snow on the ground. ☃ Continued cold and gray. ️Things will start to thaw out at around 10 am. 🌡️

I was up and walking 🚶 at 6:05 this morning in preparation for next week when the morning is an hour earlier 🕒 based on the changing times of the clock. Today is a session day, so I need to iron my shirts 🧑🏻‍💼, shower 🚿 and catch the express downtown. If I get a minimum of 10,000 steps in today and tomorrow then it will be 50 days in a row, though most days I’ve far exceeded that number, averaging closer to 15k.

Today will be mostly cloudy 🌥, with a high of 38 degrees at 2pm. Four degrees below normal, which is similar to a typical day around February 24th. Apparently the weather man forgot what month it Northwest wind around 14 mph. A year ago, we had mostly sunny skies in the morning, remaining cloudy in the afternoon. The high last year was 36 degrees. The record high of 68 was set in 2012. 6.5 inches of snow fell back in 2013.❄

I figure it will be cold until it ain’t anymore. 🌬️ Probably go from 30 degrees to 90 degrees in like a day. Temperatures in New York State usually follow a logistic regression with the low tail cold and the high tail mad hot. 😰 I like wearing my vest two days a year.

Solar noon 🌞 is at 12:07 pm with sun having an altitude of 42.4° from the due south horizon (-28.4° vs. 6/21). A six foot person will cast a 6.6 foot shadow today compared to 2.2 feet on the first day of summer. The golden hour 🏅 starts at 5:16 pm with the sun in the west-southwest (258°). 📸

The sunset is in the west (264°) with the sun dropping below the horizon at 5:53 pm after setting for 2 minutes and 55 seconds with dusk around 6:21 pm, which is one minute and 12 seconds later than yesterday. 🌇 The best time to look at the stars is after 6:54 pm. At sunset, look for mostly cloudy skies ☁ and temperatures around 37 degrees. There will be a northwest breeze at 13 mph. Tomorrow will have 11 hours and 39 minutes of daytime, an increase of 2 minutes and 54 seconds over today.

Tonight will be mostly cloudy 🌥, with a low of 28 degrees at 4am. Four degrees above normal, which is similar to a typical night around March 21st. Northwest wind 10 to 14 mph. In 2022, we had cloudy skies in the evening, which became mostly clear by the early hours of the morning. It got down to 27 degrees. The record low of -14 occurred back in 1943.

In 1965, Thirty-five hundred United States Marines are the first American land combat forces committed during the Vietnam War. 🪖 But the war would be ramping up for several years in advance and it would stick around to the mid 1970s. It would be the nation’s longest war until the 21st century but the politicians say those wars don’t count as they were under paid volunteer government employees binded by their contracts and their colored skin and or poverty growing up.

Right now, a split verdict on the weekend. 😕 Saturday, cloudy, with a high near 37. Sunday, partly sunny, with a high near 40. Typical average high for the weekend is 43 degrees. I do want to get out of town one of these weekends but probably not this weekend. But maybe Five Rivers for Saturday.

Friendly Strangers in black sedans will note that, next Wednesday is Ides of March ✝ when the sun will be setting at 7:02 pm with dusk at 7:29 pm (Daylight Savings Time). On that day in 2022, we had cold weather, but mostly sunny and temperatures between 30 and 11 degrees. Typically, the high temperature is 45 degrees. We hit a record high of 72 back in 2019.

Go Down

Hollyhock Hollow Sanctuary

|

Today was the first time I had explored the Hollyhock Preserve. It was kind of interesting to explore, even if it was a bit of a chilly morning.