tahar.sql

-- create an index for your tables, this will create a rtree

CREATE INDEX idx_ny_fires ON NYC_FireAccidents USING GIST(geom);
CREATE INDEX idx_ny_building ON mappluto_13v2 USING GIST(wkb_geometry);

-- check if one single association is fast

EXPLAIN ANALYZE SELECT f.unique_key, b.ogc_fid
FROM  NYC_fireaccidents f, mappluto_13v2 b 
WHERE ST_Distance_Sphere( ST_Transform(f.geom,4269), ST_Transform(b.wkb_geometry,4269)) < 50 
ORDER BY f.geom <-> b.wkb_geometry LIMIT 1;

-- then try to create the whole table

INSERT into association_building_fire(f.unique_key, b.ogc_fid)                                            
SELECT f.unique_key, b.ogc_fid
FROM  NYC_fireaccidents f, mappluto_13v2 b 
WHERE ST_Distance_Sphere( ST_Transform(f.geom,4269), ST_Transform(b.wkb_geometry,4269)) < 50 
ORDER BY f.geom <-> b.wkb_geometry LIMIT 1;