Trying to improve the TIGER Geocoder query for QGIS

In an earlier post I showed how you can use the Fast SQL Layers plugin (available from the QGIS Plugin Repository) to execute a TIGER Geocoder query.  I’ve simplified the query slightly, instead of using a UUID for the unique ID field you can use a window query and the row_number() function instead.

SELECT ROW_NUMBER() OVER (ORDER BY g.rating) 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('5200 Duesner Rd, Evansville, IN 47712') As g;

While simpler, it doesn’t quite work as intended.  If you run the query in Pgadmin or psql it works just fine.  However, running it in Fast SQL Layers the window doesn’t work, the ID for each feature is zero.  This causes a crash when you try to open the attribute table.  The identify tool and exporting to shapefile both work, but as mentioned the ID field will show 0 for all records returned.

  1. No trackbacks yet.

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: