Spatial join ST_Within

Question

I have two tables

Barnet
0   pk_uid  INTEGER 0   NULL    1
1   name    TEXT    1   NULL    0
2   gss_code    TEXT    1   NULL    0
3   district    TEXT    1   NULL    0
4   lagsscode   TEXT    1   NULL    0
5   hectares    DOUBLE  1   NULL    0
6   nonld_area  DOUBLE  1   NULL    0
7   geometry    GEOMETRY    0   NULL    0

and

uprns
0   PK_UID  INTEGER 0   NULL    1
1   UPRN    INTEGER 0   NULL    0
2   X_COORDINATE    DOUBLE  0   NULL    0
3   Y_COORDINATE    DOUBLE  0   NULL    0
4   LATITUDE    DOUBLE  0   NULL    0
5   LONGITUDE   DOUBLE  0   NULL    0
6   GeometryLatLon  POINT   0   NULL    0

Barnet is a collection of polygons (exported as a geoJSON from QGIS and imported using spatialite_gui) while uprns is a collection of points.

I want to find all the points in uprns that are contained is a specific polygon from Barnet.

I have tried

select count(Barnet.name) 
from Barnet 
CROSS JOIN uprns 
where (Barnet.name == "High Barnet" 
   AND ST_Within(uprns.GeometryLatLon, Barnet.geometry));

and

select count(Barnet.name) 
from Barnet 
INNER JOIN uprns 
ON ST_Within(uprns.GeometryLatLon, Barnet.geometry) 
where Barnet.name == "High Barnet";

and both return 0 (I know it is not the case)

I checked in QGIS and the geometries are there. From Geopandas I can see the list of polygons in Barnet.

A simpler query such

select count(Barnet.name) 
from Barnet 
CROSS JOIN uprns  
where (ST_Within(uprns.GeometryLatLon, ST_PolygonFromText('POLYGON ((-0.1468197 51.6165088, -0.1467007 51.6163413, -0.1465113 51.6163933, -0.1465534 51.6164525, -0.1466056 51.6164381, -0.1466497 51.6165002, -0.146579 51.6165196, -0.1466118 51.6165658, -0.1468197 51.6165088))')));

works fine.

I would like to share the database, but it is 7GB!

Answer 1

How about an inner join on intersects and a where clause?

select point.ogc_fid, 
       point.geometry as pointgeom, 
       poly.ogc_fid, 
       poly.geometry as polygeom

from ok_bs_riks point
join ok_my_riks poly

on st_intersects(point.geometry, poly.geometry)
where poly.ogc_fid = 21613 --a specific polygon