1

I am new to the world of PostgreSQL. I am using PostgreSQL 9.5. I have installed PostGIS 2.2.2 plugin and along with that came Tiger, Tiger_data schemas. But the Tiger_data schema does not have any tables (not sure if it is expected). I also installed postgs_tiger_geocoder along with fuzzystrmatch. When I am trying to insert an address using geocode(), no rows are inserted. Below are the few queries I tried.

SELECT * INTO temp FROM geocode('250 WASHINGTON ST, BOSTON, MA 02108');
Select * INTO TEMP4 from geocode('343 S 500 E, Salt Lake City, UT 84102');
1

Your syntax is wrong. But in the documentation is exactly the example given you need. Just took an example for streetname from the first basic example in the documentation an put it together to an insert statement. Did not test it. It is also important how your table is structured and that you have the appropriate columns as it is needed for the correct input. In the following you need a column street for example.

INSERT INTO temp(street)
    SELECT (addy).streetname
    FROM geocode('250 WASHINGTON ST, BOSTON MA 02108')

Otherwise check the docu:

http://postgis.net/docs/Geocode.html

2
  • I tried it but did not work. I believe that the problem is not with syntax but something else. I say this because I just tried to run a select query in the documentation to test te working of geocode() function but it did not give me any results. Are there any known issues by just installing the postgis extension through which we get the TIGER schema? Because I believe geocode() uses tiger schema to fetch the results. Please suggest. Appreciate help! – Abhi Jul 20 '16 at 12:20
  • But did it give an error message or just no results? No results might be due to lacking data. – Matte Jul 20 '16 at 13:21
0

Because you said "Tiger_data schema does not have any tables"...

After you install the extension there is still a lot of work to be done.

1. Update the loader platform for your setup

This command will show you the defaults:

SELECT * from tiger.loader_platform;

I recommend you create a new record with a unique OS value so you can retain the defaults.

Please note that you will need to expand the size of your row height because carriage returns are stored... see the difference here: enter image description here

You may want to update the "Max characters per column" to something higher (mine is at 999999 right now). It can be found in pgAdmin > File> Options... Query Tool.

Otherwise you will see a continuation symbol: enter image description here

2. run the loader script which create script files for the data that you want to load.

SELECT loader_generate_nation_script('windows');

and

SELECT loader_generate_script(ARRAY['MA','RI'], 'windows') AS result;

3. execute the scripts that actually load the data.

4. run install missing indexes which will help with the performance of the geocoder.

SELECT install_missing_indexes();

These links will help too:

http://postgis.net/docs/Extras.html

http://dracodoc.github.io/2015/11/17/Geocoding/

Installing TIGER geocoder

It took me several days (might have been a couple weeks) to load every US State. The more states you load, the slower the geocoding and the more space it takes up.

2
  • Ah!I understand. I missed the big part and I don't have enough time to do that now. Is there a way for me to get the geometric details of a single location so that we can just demonstrate how the proximity from the nearest train stations is determined by postgis, postgres, QGIS? We already got the train station related shape file and added that to QGIS. – Abhi Jul 21 '16 at 1:40
  • @Abhi you'll need to open another question for a new answer. but basically yes, you can explore the us bureau ftp site for the shapefile of interest. ftp2.census.gov/geo/tiger/TIGER2015 also, please mark an answer as accepted if it answered your question. – kttii Jul 21 '16 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.