I've got a database with the following tables

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:

enter image description here

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


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

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.