<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.6001.19412"></HEAD>
<BODY>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=525594513-17022014>My english won't help me to explain myself but I'll try
! A picture will :)</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=525594513-17022014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=525594513-17022014>If I'm looking for polygon which don't intersect poly
A, It will return a line each polygon it does not intersect,
i.e.</SPAN></FONT><FONT color=#0000ff size=2 face=Arial><SPAN
class=525594513-17022014>for B, D, E and F.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=525594513-17022014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=525594513-17022014>In your case it your query won't return you only the
lack of polygon but also all g1 polygons which doesn't intersect each g2
ones.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=525594513-17022014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=525594513-17022014>Is it clearer ?</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=525594513-17022014></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=525594513-17022014>Hugues.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff size=2 face=Arial></FONT> </DIV>
<DIV align=left><FONT size=2 face=Arial></FONT> </DIV><BR>
<DIV dir=ltr lang=fr class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> postgis-users-bounces@lists.osgeo.org
[mailto:postgis-users-bounces@lists.osgeo.org] <B>On Behalf Of </B>Andreas Forø
Tollefsen<BR><B>Sent:</B> Monday, February 17, 2014 12:38 PM<BR><B>To:</B>
PostGIS Users Discussion<BR><B>Subject:</B> Re: [postgis-users] Point, Polygon -
Does not intersects<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr>Thank you Hugues,
<DIV><BR></DIV>
<DIV>Your suggestion worked. I still have some problems understanding why my
query did not work, but I will give it some thought.</DIV>
<DIV><BR></DIV>
<DIV>Andreas</DIV>
<DIV><BR></DIV></DIV>
<DIV class=gmail_extra><BR><BR>
<DIV class=gmail_quote>2014-02-17 12:09 GMT+01:00 Hugues François <SPAN
dir=ltr><<A href="mailto:hugues.francois@irstea.fr"
target=_blank>hugues.francois@irstea.fr</A>></SPAN>:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote><U></U>
<DIV>
<DIV dir=ltr align=left><FONT color=#0000ff
face=Arial><SPAN>Hello,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff
face=Arial><SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff face=Arial><SPAN>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)</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff
face=Arial><SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff face=Arial><SPAN>I think you could
try </SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff
face=Arial><SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff><SPAN><FONT color=#000000>SELECT
</FONT><FONT color=#000000>name, geom INTO
gadm2_level1_union_selected</FONT></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff><SPAN><FONT color=#000000>FROM
</FONT></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff><SPAN><FONT color=#000000>(SELECT
g1.*, g2.gid AS g2_gid FROM gadm2_level1_union
g1</FONT></SPAN></FONT></DIV>
<DIV dir=ltr align=left><SPAN>LEFT JOIN gadm2_level2_union g2 ON
ST_Intersects(g1.centroid, g2.geom)) foo</SPAN></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff><SPAN></SPAN></FONT><FONT
color=#0000ff><SPAN><FONT color=#000000>WHERE g2_gid is
null</FONT></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#000000><SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff
face=Arial><SPAN>Hugues.</SPAN></FONT></DIV>
<DIV align=left><FONT face=Arial></FONT> </DIV><BR>
<DIV dir=ltr lang=fr align=left>
<HR>
<FONT face=Tahoma><B>From:</B> <A
href="mailto:postgis-users-bounces@lists.osgeo.org"
target=_blank>postgis-users-bounces@lists.osgeo.org</A> [mailto:<A
href="mailto:postgis-users-bounces@lists.osgeo.org"
target=_blank>postgis-users-bounces@lists.osgeo.org</A>] <B>On Behalf Of
</B>Andreas Forø Tollefsen<BR><B>Sent:</B> Monday, February 17, 2014 11:49
AM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> [postgis-users]
Point, Polygon - Does not intersects<BR></FONT><BR></DIV>
<DIV>
<DIV class=h5>
<DIV></DIV>
<DIV dir=ltr>Hi,
<DIV><BR></DIV>
<DIV>I am running the current setup:</DIV>
<DIV> "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)"</DIV>
<DIV><BR></DIV>
<DIV>I have two tables with geometries. Both include polygons representing
administrative units. One at the first level and one at the second
level.</DIV>
<DIV>I want a table that includes all the second level polygons where
available, and where not available I want the first level polygons.</DIV>
<DIV><BR></DIV>
<DIV>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.</DIV>
<DIV><BR></DIV>
<DIV>Hence, I tried:</DIV>
<DIV>
<DIV><BR></DIV>
<DIV>SELECT <A href="http://g1.name" target=_blank>g1.name</A>, g1.geom INTO
gadm2_level1_union_selected FROM gadm2_level1_union g1, gadm2_level2_union g2
WHERE ST_Intersects(g1.centroid, g2.geom)=FALSE;</DIV>
<DIV><FONT color=#0000ff face=Arial></FONT><BR></DIV>
<DIV>I also tried the ST_Disjoint(g1.centroid, g2.geom).</DIV>
<DIV><BR></DIV>
<DIV>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.</DIV>
<DIV><BR></DIV>
<DIV>What could be the issue with my query?</DIV>
<DIV><BR></DIV>
<DIV>Thanks!</DIV>
<DIV>Andreas</DIV>
<DIV><BR></DIV></DIV></DIV></DIV></DIV></DIV><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A
href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</A><BR><A
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users"
target=_blank>http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</A><BR></BLOCKQUOTE></DIV><BR></DIV></BODY></HTML>