Saving select query results (year wise) from PostgreSQL/PostGIS to text files

Question

I have two spatially enabled tables 'bar' and 'foo' in my PostgreSQL 9.5 (x64) database. Table 'bar' contains 87 rows and 'foo' contains 16,060 rows. For each address location (foo.gid), following query selects (260 rows returned in 100ms execution time) nearest streets (foo.geom) within 50 meters as well as interpolates a point to that street. Then, query results are exported to the desired path using postgres COPY() command.

Query:

Copy (
      Select
           foo.gid As addr_ID, 
           bar.geom As streets,
           St_LineInterpolatePoint
               (ST_LineMerge(bar.geom),
                  St_LineLocatePoint
                     (St_LineMerge(bar.geom),foo.geom)
           ) As interpolated_point
      FROM foo
      Left JOIN bar ON ST_DWithin(foo.geom, bar.geom, 50)
      ORDER BY  
      foo.gid, St_Distance(foo.geom, bar.geom)
     )      
To '~path/my_file.txt' WITH DELIMITER ',';

Table 'foo' also contains two columns 'start_time' and 'end_time' (data type 'timestamp without time zone') depicting an interval of varying years like this:

Start_time              End_time
2003-06-07 00:00:00     2004-09-30 23:59:59
2004-02-03 00:00:00     2005-03-10 23:59:59

I would like to break my above select query results into years wise so that I would have a text file of each year with unique records (query results).

Expected output:

my_file_2003.txt
my_file_2004.txt
my_file_2005.txt

Can someone guide me to achieve my expected output?

EDIT:

I am using PostgreSQL version 9.5 with PostGIS version 2.2 on Windows 7 Enterprise (x64) based machine. Thus, I intend to go for Windows' specific solution.

Answer 1

I would go with a simple bash loop. Even the years array could be defined as a result fo SQL query.

years=( 2001 2002 2003 )

for y in "${years[@]}"
do
    echo "Copy (
    Select
       foo.gid As addr_ID,
       bar.geom As streets,
       St_LineInterpolatePoint
           (ST_LineMerge(bar.geom),
              St_LineLocatePoint
                 (St_LineMerge(bar.geom),foo.geom)
       ) As interpolated_point
    FROM foo
    Left JOIN bar ON ST_DWithin(foo.geom, bar.geom, 50)
    WHERE Extract(Year FROM foo.Start_time)=${y}
    ORDER BY
    foo.gid, St_Distance(foo.geom, bar.geom)
 )
To '~path/my_file_${y}.txt' WITH DELIMITER ',';" | psql -h host -d your_db
done

Answer 2

You can use the extract function to get the year from foo.Start_time:

Copy (
      Select
           foo.gid As addr_ID,
           bar.geom As streets,
           St_LineInterpolatePoint
               (ST_LineMerge(bar.geom),
                  St_LineLocatePoint
                     (St_LineMerge(bar.geom),foo.geom)
           ) As interpolated_point
      FROM foo
      Left JOIN bar ON ST_DWithin(foo.geom, bar.geom, 50)
      WHERE Extract(Year FROM foo.Start_time)=2003
      ORDER BY
      foo.gid, St_Distance(foo.geom, bar.geom)
     )
To '~path/my_file_2003.txt' WITH DELIMITER ',';