Using the TIGER Geocoder from QGIS

Edit:

Not long after I posted this I found an easier method, not sure why I didn’t update this post.  Anyway rather than mess around with UUIDs, just use Postgres’ window function.  In this case the row_number function will generate the unique integers that QGIS needs to display your dynamic layer.  Replace the uuid_generate_v4() with:

SELECT row_number() OVER () As id,

Easy peasy.

 

I’ve been trying to use the Fast SQL Layers plugin with QGIS to map results from TIGER Geocoder queries.  The query below works, it will place point(s) but trying to open the attribute table crashes QGIS.  The identify tool works, but you will get an error stating the cursor has been lost.  I think it has something to do with the UUIDs, but I’m not positive.    Anyway, the query is after the jump.

select uuid_generate_v4() as id, 
 g.rating, ST_X(g.geomout) As lon, ST_Y(g.geomout) As lat, 
 (addy).address As stno, (addy).streetname As street, 
 (addy).streettypeabbrev As styp, (addy).location As city, (addy).stateabbrev As st,(addy).zip, g.geomout As the_geom
 FROM geocode('123 Main St, Anytown, IA, 99999') As g

For the query to work you need to install the Postgres module uuid-ossp.  If you installed Postgres from the Arch repositories it is not available by default, but there is an entry for it in AUR under postgresql-uuid-ossp.  Once it is installed you’ll need to create the extension in any databases you’ll want to use it in.  If you’re using Postgres 9.1 or greater simply use:

CREATE EXTENSION uuid-ossp;

I’m still looking for a method that works better than this, if you have any suggestions let me know.

Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: