0

So I'm in the middle of learning PostGIS/PostgreSQL.

I've a polygon table ans I've a point table. The polygon layer contains a foreign key that points to another table. For the example the Foreign key points to the type of polygon. So TypeFK in the polygon table references TypeID in the type_tbl;

I can do a:

select type 
from polygon as a, type_tbl as b 
where a.typefk = b.typeid; 

Now I know the polygon is a type of tree.

I can do a:

select typefk 
from polygon 
join points on ST_Contains(polygon.geom, points.geom); 

I know what typefk they fall in.

I'm ultimately trying to return the type of polygon the points are located within.

So - gurus of PostGIS/SQL - how do I combine the two?

I've been sitting here too long without a break unfortunately and am just not getting it.

2

I think this is what you want. I might have the column and tables wrong and not sure what fields you want returned

SELECT t.type, point.gid As ptgid, polygon.gid As polygid
 FROM polygon INNER JOIN type_tbl As t ON polygon.typefk = t.typeid
INNER JOIN points ON ST_Contains(polygon.geom, points.geom);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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