[postgis-users] st_difference on more than two geometries
Markus Innerebner
markus.innerebner at inf.unibz.it
Tue Sep 11 07:29:07 PDT 2012
Hello,
I have a question regarding the st_difference operation:
I want to identify the non overlapping area of a given polygon, that might intersect with arbitrary other polygons.
I realized, that st_difference operation works only with two parameters. The API says not to use it with geometry collections.
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:
st_intersection(
st_intersection(
st_difference(
st_buffer(st_pointfromtext('POINT(680386.8879388 5152170.83093339),82344),9000.0),
st_buffer(st_pointfromtext('POINT(688402.843016069 5157054.21005474),82344),3000.0)
),
st_difference(
st_buffer(st_pointfromtext('POINT(680386.8879388 5152170.83093339),82344),9000.0),
st_buffer(st_pointfromtext('POINT(674802.141078014 5161560.18713918),82344),5860.0)
)
),st_difference(
st_buffer(st_pointfromtext('POINT(680386.8879388 5152170.83093339),82344),9000.0),
st_buffer(st_pointfromtext('POINT(680910.532411225 5142908.46168964),82344),5000.0)
)
)
)
)
my question now is: Is there a simpler way?
cheers Markus
--
Ph D. Student Markus Innerebner
DIS Research Group - Faculty of Computer Science
Free University Bozen-Bolzano
Dominikanerplatz 3 - Room 211
I - 39100 Bozen
Phone: +39-0471-016143
Mobile: +39-333-9392929
gpg --fingerprint
-------------------------------------
pub 1024D/588F6308 2007-01-09
Key fingerprint = 6948 947E CBD2 89FD E773 E863 914F EB1B 588F 6308
sub 2048g/BF4877D0 2007-01-09
On Sep 11, 2012, at 1:01 PM, Thomas Klemmer wrote:
> Hi hugues,
>
> 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).
>
> 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.
>
> TK
>
> 2012/9/11 Francois Hugues <hugues.francois at irstea.fr>
> 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.
>
> You should make the index (create index net_geom_gist on net_geom using gist(geom) ) and try again your query
>
> Hugues.
>
>
>
> De : postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] De la part de Thomas Klemmer
> Envoyé : mardi 11 septembre 2012 11:09
> À : postgis-users at postgis.refractions.net
> Objet : [postgis-users] ST_DWithin is not using SpatialIndex with Subquery
>
> Hi folks
>
> first of here some system informations:
>
> Server: Ubuntu 12.04 LTS, 16GB RAM 1TB 4x SSD HW Raid
>
> "PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit"
> "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"
>
> Database:
>
> TableName: PointTbl_v1
> Index: idx_pdb_v1
> NumRows: 4.09806e+08
> TableSize: 280 GB
> IndexSize: 21 GB [Gist on(point_pos)]
>
> GeometryCo: point_pos
> Type: POINT
> Dimensions: 2
> SRID: 4326
>
> /-----------------------------------------------
>
> TableName: PointTbl_v5
> Index: idx_pdb_v5
> NumRows: 4.16218e+07
> TableSize: 19 GB
> IndexSize: 2344 MB [Gist on(point_pos)]
> Primarykey: false
>
> GeometryCo: point_pos
> Type: POINT
> Dimensions: 2
> SRID: 4326
>
> /-----------------------------------------------
>
> TableName: NetTbl
> Index: idx_net
> NumRows: 270615
> TableSize: 195 MB
> IndexSize: 17 MB
> Primarykey: NetTbl_pk
>
>
> GeometryCo: net_geom
> Type: LINESTRING
> Dimensions: 2
> SRID: 4326
>
> Basically I'm trying to gather all points from PointTbl_v5 / PointTbl_v1 that are close or on a LineString in NetTbl;
> Here ist the Query I'm trying to run:
>
> SELECT ST_AsBinary(point_pos) AS point_pos, oid, ..., type
> FROM PointTbl_v5
> WHERE ST_DWithin(point_pos,(SELECT net_geom from NetTbl where ogc_fid = 500) ,5e-05);
>
> This query takes 319005 msec to return which is very slow due to the a seq scan done on PoinDB_v5
>
> EXPLAIN ANALYSE
>
> "Seq Scan on PointTbl_v5 (cost=10000000008.36..10013364820.01 rows=13873927 width=202) (actual time=199926.978..318895.494 rows=5 loops=1)"
> " Filter: st_dwithin(point_pos, $0, 5e-05::double precision)"
> " InitPlan 1 (returns $0)"
> " -> 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)"
> " Index Cond: (ogc_fid = 2)"
> "Total runtime: 318895.583 ms"
>
> The query:
>
> SELECT ST_AsText(net_geom) from NetTbl where ogc_fid = 2
>
> returns in 16 ms;
>
> If I instert the Geometry by Hand into the first query like this:
>
> SELECT ST_AsBinary(point_pos) AS point_pos, oid, country, federalstate, district, town
> FROM PointTbl_v5
> WHERE ST_DWithin(point_pos, ST_GeomFromText( 'LINESTRING(....)',4326),5e-05);
>
> This query return in 63ms on the small table and 766ms on the bigger table.
>
> EXPLAIN ANALYSE
>
> "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)"
> " Index Cond: (point_pos && '0103.....A40'::geometry)"
> " Filter: (('0102.....4A40'::geometry && st_expand(point_pos, 5e-05::double precision)) AND _st_dwithin(point_pos, '01020.....A40'::geometry, 5e-05::double precision))"
> "Total runtime: 1.080 ms"
>
> Does anybody have a clue why the first query with the subquery for the LineString Geometry withing ST_DWithin not using the Spatial index?
>
> seqscan_enabled is turned off...
>
> cheers Thomas
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120911/ef82a3cd/attachment.html>
More information about the postgis-users
mailing list