Postgres SELECT with multiple select as

Question

I'm trying to use the output from two SELECT statements in a PostGIS function.

What is the correct syntax for doing this? I'm getting a syntax error at or near the second SELECT statement.

SELECT ST_Split(tracks, roads)
FROM
(
    SELECT * FROM (SELECT ST_Buffer(road_geom,50) FROM table1 WHERE a = '' AND b = '') as roads,
    SELECT * FROM (SELECT the_geom FROM table2 WHERE c = '' AND d = '') as tracks

)

Error output:

 ERROR:  syntax error at or near "SELECT"
 LINE 5:  SELECT * FROM (SELECT the_geom FROM table2...
     ^
********** Error **********
ERROR: syntax error at or near "SELECT"
SQL state: 42601
Character: 178

Thanks!

bma · Accepted Answer · 2013-10-25 16:54:18Z

6

Demonstrating one method of using a CTE:

WITH    roads as (SELECT ST_Buffer(road_geom,50) as road FROM table1 WHERE a = '' AND b = ''),
        tracks as (SELECT the_geom as track FROM table2 WHERE c = '' AND d = '')
SELECT ST_Split( (select track from tracks), (select road from roads) );

Docs at http://www.postgresql.org/docs/current/static/queries-with.html

answered Oct 25, 2013 at 16:54

bma

9,8362 gold badges35 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Matt Over a year ago

Thanks for your quick and accurate reply.

bma Over a year ago

One thing to note is that the SELECTs from the "roads" and "tracks" virtual tables (the clauses in the WITH block) -- if they could ever return more than one row the query will fail, so you should then add LIMIT 1 to both of them (in the WITH clause I mean).

Matt Over a year ago

Yeah - I ended up wrapping the split in a loop. Thanks again for your help!

Collectives™ on Stack Overflow

Postgres SELECT with multiple select as

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related