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 ( 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).
my_file_2003.txt my_file_2004.txt my_file_2005.txt
Can someone guide me to achieve my expected output?
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.