[GIS] Querying points inside and outside of polygons

Question

In PostGIS I have two tables:

Table that contains polygons:

table a.provinces

gid | geom       |   name    |
------------------------------
1   | 010604 ... | Champagne |

Table that contains points:

table a.stations

gid | geom     | province_short |
---------------------------------
1   | 0132 ... | ch             |

They are not connected with foreign key!

I would like to get:

1. Only points that are inside of the polygon
2. Only points that are outside of the polygon

SRID: 3857

Something like this:

Can it be done just like this or I have to modify database?

Answer 1

-- get all the points inside polygons
SELECT poi.*
FROM polygons pol
JOIN points poi ON (ST_Within(poi.geom, pol.geom));


-- get all the points not in polygon
-- if polygons overlap it might be better to go for NOT EXISTS clause
SELECT poi.*
FROM polygons pol
LEFT JOIN points poi ON (ST_Within(poi.geom, pol.geom))
WHERE pol.gid IS NULL;