1

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.

3

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
  • Thank you so much for your help but I am using Windows 7 (x64) based machine. I wonder whether I would be able to use this script in Windows environment? Nevertheless, much useful. – khajlk Aug 9 '16 at 7:49
  • I'm sorry not to be of any help here, haven't used Windows for yonks... – Michal Zimmermann Aug 9 '16 at 8:33
  • Your help is still appreciated! Cheers :) – khajlk Aug 9 '16 at 8:40
  • 1
    The other way would be to prepare dynamic query and run it using EXECUTE. – Michal Zimmermann Aug 9 '16 at 9:21
0

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 ',';
  • Thanks for your feedback. Isn't the case that adding 'Where' clause will change the results of select query? how could it break my_file.txt file into each years? We are still outputting everything to a single file of year 2003 now. – khajlk Aug 8 '16 at 20:14
  • @JibranKhan there are lots of tutorials about how to do a loop in a windows batch file, try one of those or implement the code into a stored procedure. – kttii Aug 9 '16 at 13:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.