-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathStep2_ExportCsv.sql
More file actions
27 lines (26 loc) · 7.71 KB
/
Copy pathStep2_ExportCsv.sql
File metadata and controls
27 lines (26 loc) · 7.71 KB
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
--This code (step 2) exports batches of RPS centroid data from the PostGres database into csv files with geography represented as text. Run via three sub-steps:
--1. In Command Prompt, navigate to directory where the sql file is saved
--2. Connect to psql using: psql postgre postgres (database is named postgres and password is postgres); enter postgres at password prompt
--3. Run sql using: \i filename.sql
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 0 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch1.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 250000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch2.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 500000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch3.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 750000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch4.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 1000000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch5.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 1250000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch6.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 1500000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch7.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 1750000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch8.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 2000000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch9.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 2250000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch10.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 2500000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch11.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 2750000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch12.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 3000000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch13.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 3250000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch14.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 3500000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch15.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 3750000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch16.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 4000000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch17.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 4250000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch18.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 4500000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch19.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 4750000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch20.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 5000000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch21.csv WITH DELIMITER '|' CSV HEADER;
\copy (select *, st_astext(st_geogfromtext(st_astext(st_transform(geom,4326)))) as geog_wkt from parcels.rpscentroids order by id offset 5250000 rows fetch first 250000 rows only) to C:\Users\shannon.thol\Documents\ArcGIS\Projects\ParcelAggregation\NonadjacentAggregation\dev\BigQuery\rpscentroids_batch22.csv WITH DELIMITER '|' CSV HEADER;