Constructing spatial queries that include data from non-spatial tables in PostGIS

Question

I've got a database with the following tables

CREATE TABLE survey (
id_s integer primary key,
eventdate timestamp without time zone,
locality character varying(100)
geom geometry(Point,25830))

CREATE TABLE provinces (
id_p integer primary key,
name character varying(100)
geom geometry(Polygon,25830))

A simple query would be

SELECT locality, eventdate
FROM provinces p, survey s
WHERE st_contains (p.geom,s.geom) and name = 'myprovince'

But, in order to save space (and, hopefully, speed queries), I re-structured the database as follows:

The problem now is that I can't figure out how to construct that simple query. What woud be the syntax?

Answer 1

This is one of the key abilities of relational databases. Just join all three tables together on the attribute ID key and the spatial relationship:

SELECT l.name_loc, s.eventdate
FROM survey s
JOIN location l ON l.id_loc = s.id_loc
JOIN provinces p ON ST_Contains( p.geom, l.geom ) 
WHERE p.name = 'myprovince';