Getting geometry from multiple tables using PostGIS?

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


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

Tags:

Sql

Postgis