[postgis-users] Merging census block data in postgresql

[Tsering W. Shawa]

I have to run a spatial join between millions of addresses and the census block boundaries covering whole United States. I can do this function in ArcGIS Pro by merging all the census blocks of each state to one File geodatabase and then run a spatial join tool. Many of you might know that the census block boundaries for the whole United States is over 11 GB and therefore too large for a shapefile.

I am learning PostGIS and wanted to see how fast spatial join function works in PostGIS compare to ArcGIS Pro or QGIS. I have already imported all the census block boundaries in postgresql and plan to import geocoded addresses also in the postgresql database.

My question is...what sql function should I use to merge them together? Do I need to create a new database or table that has all the census block boundaries merged together? Any spatial index I need to use to run it faster?

Any workflow or suggestion or example of sql will be appreciated.

Many thanks in advance.

-Tsering
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200511/86158f73/attachment.html>