<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div>Hello,</div><div><br></div><div>I have a question regarding the <span class="Apple-style-span" style="font-family: Monaco; font-size: 11px; ">st_difference operation:</span></div><div><span class="Apple-style-span" style="font-family: Monaco; font-size: 11px; "><br></span></div><div><font class="Apple-style-span" face="Monaco"><span class="Apple-style-span" style="font-size: 11px;">I want to identify the non overlapping area of a given polygon, that might intersect with arbitrary other polygons.</span></font></div><div><font class="Apple-style-span" face="Monaco"><span class="Apple-style-span" style="font-size: 11px;">I realized, that st_difference operation works only with two parameters. The API says not to use it with geometry collections.</span></font></div><div><font class="Apple-style-span" face="Monaco"><span class="Apple-style-span" style="font-size: 11px;"><br></span></font></div><div><font class="Apple-style-span" face="Monaco"><span class="Apple-style-span" style="font-size: 11px;">In order to realize this, I needed to do combine st_intersection with st_difference in recursive manner as shown below with the following output:</span></font></div><div><font class="Apple-style-span" face="Monaco"><span class="Apple-style-span" style="font-size: 11px;"><br></span></font></div><div><font class="Apple-style-span" face="Monaco"><span class="Apple-style-span" style="font-size: 11px;"><br></span></font></div><div><font class="Apple-style-span" face="Monaco"><span class="Apple-style-span" style="font-size: 11px;"><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; ">st_intersection(</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; "> st_intersection(</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; "> st_difference(</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; "> st_buffer(st_pointfromtext('POINT(680386.8879388 5152170.83093339),82344),9000.0),</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; "> st_buffer(st_pointfromtext('POINT(688402.843016069 5157054.21005474),82344),3000.0)</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; "> ),</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; "> st_difference(</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; "> st_buffer(st_pointfromtext('POINT(680386.8879388 5152170.83093339),82344),9000.0),</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; "> st_buffer(st_pointfromtext('POINT(674802.141078014 5161560.18713918),82344),5860.0)</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; "> )</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; "> ),st_difference(</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; "> st_buffer(st_pointfromtext('POINT(680386.8879388 5152170.83093339),82344),9000.0),</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; "> st_buffer(st_pointfromtext('POINT(680910.532411225 5142908.46168964),82344),5000.0)</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; "> )</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; "> )</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; ">)</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; ">)</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; ">my question now is: Is there a simpler way?</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; ">cheers Markus</div></span></font></div><div><font class="Apple-style-span" face="Monaco"><span class="Apple-style-span" style="font-size: 11px; "><br></span></font></div><div><font class="Apple-style-span" face="Monaco"><span class="Apple-style-span" style="font-size: 11px;"><br></span></font></div><div><font class="Apple-style-span" face="Monaco"><span class="Apple-style-span" style="font-size: 11px;"><br></span></font></div><div><br></div><br><div apple-content-edited="true">
<div>-- <br>Ph D. Student Markus Innerebner<br><br>DIS Research Group - Faculty of Computer Science<br>Free University Bozen-Bolzano<br><br>Dominikanerplatz 3 - Room 211<br>I - 39100 Bozen<br>Phone: +39-0471-016143<br>Mobile: +39-333-9392929<br><br><br>gpg --fingerprint<br>-------------------------------------<br>pub 1024D/588F6308 2007-01-09<br> Key fingerprint = 6948 947E CBD2 89FD E773 E863 914F EB1B 588F 6308<br>sub 2048g/BF4877D0 2007-01-09<br></div>
</div>
<br><div><div>On Sep 11, 2012, at 1:01 PM, Thomas Klemmer wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite">Hi hugues, <br><br>net_geom has a spatial index, the postam is just using the primary key on this tabel since I am not useing any spatial filter to get the LINESTING out of the table (just "where ogs_fid = 2" which is the primary key).<br>
<br>The cruzial part is the seq scen on the large point table which should not be used since the ST_DWithin is a spatial filter thus the spatial index should be used.<br><br>TK<br><br><div class="gmail_quote">2012/9/11 Francois Hugues <span dir="ltr"><<a href="mailto:hugues.francois@irstea.fr" target="_blank">hugues.francois@irstea.fr</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><u></u>
<div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span>I think your query does not use the index on net_geom
because it is not a spatial index (not a gist one) on the geometry, but a btree
index on the id.</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>You should make the index (create index net_geom_gist
on net_geom using gist(geom) ) and try again your query</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>Hugues.</span></font></div>
<div><font color="#0000ff" face="Arial"></font> </div>
<div align="left"><font face="Arial"></font> </div><br>
<div dir="ltr" align="left" lang="fr">
<hr>
<font face="Tahoma"><b>De :</b>
<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>
[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>] <b>De la part de</b>
Thomas Klemmer<br><b>Envoyé :</b> mardi 11 septembre 2012
11:09<br><b>À :</b>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br><b>Objet :</b> [postgis-users]
ST_DWithin is not using SpatialIndex with Subquery<br></font><br></div><div><div class="h5">
<div></div>Hi folks <br><br>first of here some system
informations:<br><br>Server: Ubuntu 12.04 LTS, 16GB RAM 1TB 4x SSD HW
Raid<br><br>"PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit"<br>"POSTGIS="2.0.0 r9605"
GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23 September 2009" GDAL="GDAL 1.9.1,
released 2012/05/15" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY
RASTER"<br><br>Database:<br><br>TableName:
PointTbl_v1<br>Index:
idx_pdb_v1<br>NumRows:
4.09806e+08<br>TableSize: 280 GB<br>IndexSize:
21 GB [Gist
on(point_pos)]<br><br>GeometryCo:
point_pos<br>Type:
POINT<br>Dimensions: 2<br>SRID:
4326<br><br>/-----------------------------------------------<br><br>TableName:
PointTbl_v5<br>Index:
idx_pdb_v5<br>NumRows:
4.16218e+07<br>TableSize: 19 GB<br>IndexSize:
2344 MB [Gist on(point_pos)]<br>Primarykey:
false<br><br>GeometryCo: point_pos<br>Type:
POINT<br>Dimensions:
2<br>SRID:
4326<br><br>/-----------------------------------------------<br><br>TableName:
NetTbl<br>Index:
idx_net<br>NumRows:
270615<br>TableSize: 195 MB<br>IndexSize:
17 MB<br>Primarykey:
NetTbl_pk<br><br><br>GeometryCo:
net_geom<br>Type:
LINESTRING<br>Dimensions: 2<br>SRID:
4326<br><br>Basically I'm
trying to gather all points from PointTbl_v5 / PointTbl_v1 that are close or on
a LineString in NetTbl;<br>Here ist the Query I'm trying to
run:<br><br>SELECT ST_AsBinary(point_pos) AS point_pos, oid, ..., type
<br>FROM PointTbl_v5 <br>WHERE ST_DWithin(point_pos,(SELECT net_geom from
NetTbl where ogc_fid = 500) ,5e-05);<br><br>This query takes 319005 msec to
return which is very slow due to the a seq scan done on PoinDB_v5<br><br>EXPLAIN
ANALYSE<br><br>"Seq Scan on PointTbl_v5
(cost=10000000008.36..10013364820.01 rows=13873927 width=202) (actual
time=199926.978..318895.494 rows=5 loops=1)"<br>" Filter:
st_dwithin(point_pos, $0, 5e-05::double precision)"<br>" InitPlan 1
(returns $0)"<br>" -> Index Scan using NetTbl_pk on
NetTbl (cost=0.00..8.36 rows=1 width=847) (actual time=2.069..2.075 rows=1
loops=1)"<br>" Index Cond:
(ogc_fid = 2)"<br>"Total runtime: 318895.583 ms"<br><br>The query:
<br><br>SELECT ST_AsText(net_geom) from NetTbl where ogc_fid = 2<br><br>returns
in 16 ms;<br><br>If I instert the Geometry by Hand into the first query like
this:<br><br>SELECT ST_AsBinary(point_pos) AS point_pos, oid, country,
federalstate, district, town <br>FROM PointTbl_v5 <br>WHERE
ST_DWithin(point_pos, ST_GeomFromText(
'LINESTRING(....)',4326),5e-05);<br><br>This query return in 63ms on the small
table and 766ms on the bigger table.<br><br>EXPLAIN ANALYSE<br><br>"Index Scan
using idx_pdb_v5 on PointTbl_v5 (cost=0.00..147.61 rows=1 width=202)
(actual time=0.047..1.050 rows=23 loops=1)"<br>" Index Cond: (point_pos
&& '0103.....A40'::geometry)"<br>" Filter:
(('0102.....4A40'::geometry && st_expand(point_pos, 5e-05::double
precision)) AND _st_dwithin(point_pos, '01020.....A40'::geometry, 5e-05::double
precision))"<br>"Total runtime: 1.080 ms"<br><br>Does anybody have a clue why
the first query with the subquery for the LineString Geometry withing ST_DWithin
not using the Spatial index?<br><br>seqscan_enabled is turned
off...<br><br>cheers Thomas<br></div></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br>
_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>http://postgis.refractions.net/mailman/listinfo/postgis-users<br></blockquote></div><br></body></html>