How to find points within a polygon in PostGIS

Question

I have a series of lat and long coordinates I've loaded into a spacial table (Nov01) in PostGIS, and I've created a geometry column in the table. I also have a shapefile (greatermanchester) I've loaded in with the POSTGIS 2.0 shapefile loader. I've double checked that they both have the same SRID of 4326, and then I ran the code

select a.*, b.*
from "Nov01" a
join greatermanchester b
on ST_Intersects(a.the_geom, b.geom)
limit 10

However it doesn't return any results. My points table looks like:

What might I have gotten wrong?

EDIT: There definitely should be points within the polygon. I loaded the original csv and shapefile into QGIS and got this:

I also ran explain analyze queries with these results:

EXPLAIN ANALYZE VERBOSE select count(*) 
from "Nov01" a 
join greatermanchester b 
on ST_Intersects(a.the_geom, b.geom)

result:

Answer 1

You should use the ST_within function and not the ST_intersect function. Here the documentation. Here the sql code. You want only the points within greatermanchester

select a.*
from "Nov01" as a
join greatermanchester as b
on ST_WITHIN(a.the_geom, b.geom)