Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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 */

Clearly I am writing some foolish SQL. What is the right way to do this?

share|improve this question

3 Answers 3

up vote 5 down vote accepted

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

share|improve this answer
    
Wow Thomas! Thanks for the very thorough answer! –  BenjaminGolder Apr 3 '11 at 22:32

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

share|improve this answer
    
thank you @Sasa! –  BenjaminGolder Apr 1 '11 at 20:50
    
Many apologies, but I moved the answer to Thomas's below, because it is a bit more thorough. Even though you answered first, I felt Thomas's might be a better reference in the future if others have the same question. Thanks again Sasa. –  BenjaminGolder Apr 3 '11 at 22:34

Benjamin,

You might find our SQL Primer useful. We designed it to answer SQL newbie questions primarily though it does cover some advanced SQL constructs as well.

http://www.postgis.us/chapter_appendix_c

Chapter 1 might be useful too since it's a PostGIS spatial primer.

share|improve this answer
1  
+1. That is the free chapters. Buy the whole book, Benjamin, it is a well worth investment. if you want to learn about PostGIS and a lot more. –  Nicklas Avén Apr 1 '11 at 14:40
    
This isn't the precise answer to my question, but it answers my need to ask the question. Thanks @LR & @Nicklas. I will definitely look into this. –  BenjaminGolder Apr 1 '11 at 20:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.