I'm trying to do a spatial join on a PostGIS table that has a bunch of hexes and each hex has an ID. I have a separate table withs points in lats and longs and I want to know which hex each point belong in. For instance (points are made up)
id lat long 1 -83.1 24.5 2 55.9 -90.2 3 16.7 23.4to
id lat long hexid 1 -83.1 24.5 5 2 55.9 -90.2 87 3 16.7 23.4 23The problem I have is that I have alot of points (over 100 million) and doing the spatial join is taking DAYS. I have broken the query up into special geographic regions and running them concurrently, but is there anything I can do to my query that will improve my performance? My query is below:
CREATE TABLE newtable AS select id, lat::double precision, long::double precisionALTER TABLE newtable ADD Primary key (id);SELECT AddGeometryColumn ('public','newtable','geom',4326,'POINT',2);
أكثر...
id lat long 1 -83.1 24.5 2 55.9 -90.2 3 16.7 23.4to
id lat long hexid 1 -83.1 24.5 5 2 55.9 -90.2 87 3 16.7 23.4 23The problem I have is that I have alot of points (over 100 million) and doing the spatial join is taking DAYS. I have broken the query up into special geographic regions and running them concurrently, but is there anything I can do to my query that will improve my performance? My query is below:
CREATE TABLE newtable AS select id, lat::double precision, long::double precisionALTER TABLE newtable ADD Primary key (id);SELECT AddGeometryColumn ('public','newtable','geom',4326,'POINT',2);
أكثر...