I am trying to do a 'point outside polygon' query for GBR with ~ 4 million points. Most of these points are inside the boundary but a handful are falling way outside the boundary.
- The country shape boundary (A0 level) has been downloaded from http://www.diva-gis.org/gdata/
- Point data is a set of Latitude and Longitude
Below steps are done to pre-process point data -
- Add primary key GID (
ALTER TABLE point_gbr ADD COLUMN gid serial PRIMARY KEY)
- Add GEOM (
ALTER TABLE point_gbr ADD COLUMN the_geom geography(POINT,4326))
- Update Geom (
UPDATE point_gbr SET the_geom = ST_SetSRID(ST_MakePoint(longitude,latitude),4326))
- Create Spatial index (
CREATE INDEX pt_idx_geom ON point_gbr USING GIST(the_geom))
For Polygon data -
Uploaded the A0 shapefile using 'Postgis3 Shapefile and DBF loader' and set the SRID to 4326. While upload I also create an index on GEOM column.
I executed the below query to find the points outside the boundary only if they fall beyond 500m, but it is still running for 6+ hours. Not sure what am I doing wrong?
SELECT ST_Distance(a.the_geom::geography, b.geom::geography) as distance_metre, a.* FROM point_gbr a , polygon_gbr b WHERE a.gid NOT IN (SELECT a.gid FROM point_gbr a , polygon_gbr b WHERE ST_Within(a.the_geom::geometry, b.geom) ) AND ST_Distance(a.the_geom::geography, b.geom::geography) > '500'
The laptop has a RAM of 24 GB and 4 cores and is running PostgreSQL 11 with PostGIS 2.5.
Also, is there a better way to do this for similar complex countries like Russia, Mexico?