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
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 */
Clearly I am writing some foolish SQL. What is the right way to do this?