[GIS] PostGIS, st_within, and a join

attribute-joinspostgissql

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.

Best Answer

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