1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
|
/*
* The data file comes from the US census website:
*
* http://www.census.gov/geo/maps-data/data/gazetteer2013.html
*
* We import it directly into pgloader git repository so that we have at
* least a CSV test where we read from a local file...
*/
LOAD CSV
FROM stdin
(
usps, -- United States Postal Service State Abbreviation
geoid, -- Geographic Identifier
aland, -- Land Area (square meters)
awater, -- Water Area (square meters)
aland_sqmi, -- SQMI Land Area (square miles)
awater_sqmi, -- SQMI Water Area (square miles)
intptlat, -- Latitude (decimal degrees)
intptlong -- Longitude (decimal degrees)
)
INTO postgresql:///pgloader?districts
(
usps, geoid, aland, awater, aland_sqmi, awater_sqmi,
location point using (format nil "(~a,~a)" intptlong intptlat)
)
WITH truncate,
skip header = 1,
batch rows = 200,
batch size = 1024 kB,
batch concurrency = 3,
fields terminated by '\t'
BEFORE LOAD DO
$$ drop table if exists districts; $$,
$$ create table districts (
usps text,
geoid text,
aland bigint,
awater bigint,
aland_sqmi double precision,
awater_sqmi double precision,
location point
);
$$;
|
