Importing GNIS into PostgreSQL - II

This is a continuation of my previous post. To make GNIS useable with spatial queries you need to add geometry or geography column to the table and create a spatial index.  Without the index traversing of a 2-million record table is very slow.  This is how to do it assuming that the data from GNIS tables has been imported as described in the previous post:

Importing GNIS into PostgreSQL

The script below will load GNIS National File into PostgreSQL. You can download National File from here (about 280MB uncompressed, main web site). Place the uncompressed file in some directory and run the following script from psql:

The GNIS file has over two million records so it will take some time to load into PostgreSQL. I hope I guessed column types right.

P.S.: GNIS is the "Geographic Names Information System", a product from USGS.

Google has come up with a new service with some neat features. Now you can shorten your URL if you go to http://goo.gl/. So, for example, to get to this blog you go to http://goo.gl/Vqml or to go to my main web site as http://goo.gl/AXx5

It even generates a 2D bar code that you can read with your cell phone camera:

 

More Contemporary Art

Contemporary Art from Space

Pet Automation

Really odd US map

(this is a result of a typo in the rendering code)