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?