I have two tables, both have lat/lon columns within same country (UK). Tables sizes are roughly 80M and 50M.
Along with the lat/lon columns I have created a geo-indexes for both tables in this way:
SELECT AddGeometryColumn('my_table_50/80', 'geom', 4326, 'POINT', 2); UPDATE my_table SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326); CREATE INDEX my_table_geom_idx ON my_table USING gist(geom);
To find the nearest points from the table with 80M respect to 50M within 0.1 miles I run something like:
SELECT A.latitude, A.longitude, B.latitude, B.longitude, FROM my_table_50 AS A, my_table_80 AS B where ST_Distance(A.geom, B.geom) < 0.1609 -- 1 mile / 10 ORDER BY ST_Distance(A.geom, B.geom) ASC LIMIT 1;
The query is very slow to run (pretty much is cartesian 50M X 80M).
Is there a way to speed it up?
Also, is it really useful to use "postgis geo indexing" for a problem like this? Using "pythagorean theorem" can be enough (like in the chosen answer here https://stackoverflow.com/questions/1664799/calculating-distance-between-two-points-using-pythagorean-theorem), as I am expecting distances way far shortest than earth radius or it may lead to some error?