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.