I am having great difficulty with the simple st_intersect command when applied to two datasets containing points, derived from the same source. I am using osgeo4w gdal 2.1.3 at command line.
My aim is simply to return in the output the point geometry features from sqlite table A where they intersect with sqlite table B (also point geometry). There are no other geometries present in the datasets. Whilst the command seems to work ok for most of the data (providing the expected output), for a small percentage of the data the st_intersect will not identify points which clearly do intersect upon visual scrutiny - i.e. after zooming to 1:1! These points do not appear in the output. I am puzzled. Might there be a precision issue here re. point geometry location, or is there some other reason for the omission of points? What method will achieve the desired result?
ogr2ogr -unsetFid -nlt POINT -f SQLite -dsco SPATIALITE=YES outfile.sqlite infile.sqlite -nln outtable -dialect sqlite -sql "SELECT A.* FROM 'intable1' A, 'intable2' B WHERE ST_Intersects(A.geometry, B.geometry)"