PostGIS, st_within, and a join

Question

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.

Answer 1

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);