Getting geometry from multiple tables using PostGIS?

Question

I am very new to PostGIS, PostgreSQL, and SQL in general.

I have 44 tables in my PostGIS database, and each one represents a different layer of vector data. Each was loaded from a separate shapefile, and each has a column that describes the geometry for that layer, called wkb_geometry

I want to select a specific polygon on one layer, and then retrieve ALL the geometry from a subset of layers that overlap that polygon's bounding box. I'm not feeling picky about the order coming out, but it would be helpful if it was organized by the tables each group of geometry came from.

Here's a sample of my SQL statement:

SELECT
    ST_AsEWKT(wkb_geometry) /* Some of this data has z values, hence the EWKT */
FROM
    table2, table3, table4, table5
WHERE 
    wkb_geometry &&
        (
        SELECT
            wkb_geometry
        FROM
            table1
        WHERE
            ogc_fid = 25
        );

which returns an error:

column reference "wkb_geometry" is ambiguous
/* note that every table that I am selecting from has a "wkb_geometry" column */

What is the right way to do this?

Answer 1

first of all you get this error message, because you did not specify from which table you want to select the geometry column (and since they all have they same name, postgres is getting confused). That's why you get the error message

column reference "wkb_geometry" is ambiguous

If you have the same column name in multiple tables always add the table name in front of the column name: eg. table1.wkb_geometry

For your query: If I understand you right, you want to find the objects in different layers that intersect a specific object in one specific layer.

Start looking at two tables at a time in the beginning to keep it simple:

Table1 is the table with the specific object, table2 the table with the other objects

SELECT
ST_AsEWKT(table2.wkb_geometry)
FROM
table1, table2
WHERE
(table2.wkb_geometry && table1.wkb_geometry)
AND
table1.ogc_fid = 25

Now, if you want to add additional objects from other tables, you need UNION ALL, as Sasa already mentioned. Column names don't need to be the same, but number of columns and data types!

SELECT
ST_AsEWKT(table2.wkb_geometry)
FROM
table1, table2
WHERE
(table2.wkb_geometry && table1.wkb_geometry)
AND
table1.ogc_fid = 25

UNION ALL

SELECT
ST_AsEWKT(table3.wkb_geometry)
FROM
table1, table3
WHERE
(table3.wkb_geometry && table1.wkb_geometry)
AND
table1.ogc_fid = 25

UNION ALL

etc...

You might get problems, opening the query in a viewer, since there is no unique ID. A simple way to solve this is to save the result in as a table with an id column.

have fun

Answer 2

I don't have an SQL client in front of me so this may not be 100% accurate, but you'll want something along the lines of:

SELECT ST_AsEWKT(wkb_geometry) FROM table2 WHERE ...
UNION ALL
SELECT ST_AsEWKT(wkb_geometry) FROM table3 WHERE ...

and so on. Your problem is that the sql query doesn't know what table (table2/3/4/5) you are referring to when you specify SELECT ST_AsEWKT(wkb_geometry), thus the ambiguous reference. You can add an ORDER BY to the results too if you want them ordered