pgloader/test/census-places.load

LOAD ARCHIVE
   FROM http://www.census.gov/geo/maps-data/data/docs/gazetteer/places2k.zip
   INTO postgresql:///pgloader

   BEFORE LOAD DO
     $$ drop table if exists places; $$,
     $$ create table places
       (
          usps            char(2)  not null,
          fips            char(2)  not null,
          fips_code       char(5),
          "LocationName"  varchar(64)
       );
     $$

   LOAD FIXED
        FROM FILENAME MATCHING ~/places2k.txt/
             WITH ENCODING latin1
             (
                usps           from   0 for  2,
                fips           from   2 for  2,
                fips_code      from   4 for  5,
                "LocationName" from   9 for 64 [trim right whitespace],
                p              from  73 for  9,
                h              from  82 for  9,
                land           from  91 for 14,
                water          from 105 for 14,
                ldm            from 119 for 14,
                wtm            from 131 for 14,
                lat            from 143 for 10,
                long           from 153 for 11
             )
        INTO postgresql:///pgloader?places
             (
	        usps, fips, fips_code, "LocationName"
             );