Hi, Thanks for you suggestions. I have gist indexes on both tables for columns geom, and for the centroid column in table g1.
Best, Andreas 2014-02-17 13:15 GMT+01:00 <[email protected]>: > > Hi, > > to speed up, "CREATE INDEX name_gist ON table_name USING gist(geom);" is > your friend! > > If you go for something like this "st_intersect(st_centroid(a.geom) > ,.b.geom) " , remember there will be no Index on st_centroid(a.geom) = SLOW! > Better way, in my opinin, create a second centroid table with an index. > > Good Luck > Simon > > > > *Gesendet:* Montag, 17. Februar 2014 um 12:38 Uhr > *Von:* "Andreas Forø Tollefsen" <[email protected]> > *An:* "PostGIS Users Discussion" <[email protected]> > *Betreff:* Re: [postgis-users] Point, Polygon - Does not intersects > Thank you Hugues, > > Your suggestion worked. I still have some problems understanding why my > query did not work, but I will give it some thought. > > Andreas > > > 2014-02-17 12:09 GMT+01:00 Hugues François <[email protected]>: >> >> Hello, >> >> Maybe the issue is the number of polygon which not intersect between your >> tables : this query will return you all g1 polygon any times it does not >> intersect one polygon in your g2 table (I don't know if I'm clear enought, >> but a g1 polygon where g2 exists will also be returned since it does not >> intersect others g2 polygon) >> >> I think you could try >> >> SELECT name, geom INTO gadm2_level1_union_selected >> FROM >> (SELECT g1.*, g2.gid AS g2_gid FROM gadm2_level1_union g1 >> LEFT JOIN gadm2_level2_union g2 ON ST_Intersects(g1.centroid, g2.geom)) >> foo >> WHERE g2_gid is null >> >> Hugues. >> >> >> ------------------------------ >> *From:* [email protected] [mailto: >> [email protected]] *On Behalf Of *Andreas Forø >> Tollefsen >> *Sent:* Monday, February 17, 2014 11:49 AM >> *To:* PostGIS Users Discussion >> *Subject:* [postgis-users] Point, Polygon - Does not intersects >> >> >> Hi, >> >> I am running the current setup: >> "POSTGIS="2.0.2 r10789" GEOS="3.4.0dev-CAPI-1.8.0" PROJ="Rel. 4.7.1, 23 >> September 2009" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8" (core >> procs from "2.0.1 r9979" need upgrade) RASTER (raster procs from "2.0.1 >> r9979" need upgrade)" >> >> I have two tables with geometries. Both include polygons representing >> administrative units. One at the first level and one at the second level. >> I want a table that includes all the second level polygons where >> available, and where not available I want the first level polygons. >> >> To find the level 1 polygons for filling the gaps where level 2 is N/A, I >> thought about finding where the centroid of level one polygons does not >> intersects with the polygons for level 2. >> >> Hence, I tried: >> >> SELECT g1.name, g1.geom INTO gadm2_level1_union_selected FROM >> gadm2_level1_union g1, gadm2_level2_union g2 WHERE >> ST_Intersects(g1.centroid, g2.geom)=FALSE; >> >> I also tried the ST_Disjoint(g1.centroid, g2.geom). >> >> My problem is that this is running for ages without finishing. This >> should be such a simple query, but I have used half of the day running it. >> If I take the two geometries into PostGIS and run a spatial query to find >> the points from level 1 not intersecting with polygons of level 2, it takes >> 15 seconds. >> >> What could be the issue with my query? >> >> Thanks! >> Andreas >> >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > _______________________________________________ postgis-users mailing > list [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
