<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>Try something like this.</div>
<div> </div>
<div> </div>
<div>
<div>CREATE TABLE interlines(geom geometry,id numeric) WITH (OIDS=FALSE); ALTER TABLE interlines OWNER TO postgres;</div>
<div> </div>
<div>CREATE OR REPLACE FUNCTION interprob() RETURNS SETOF your_polygon_table AS<br/>
$BODY$<br/>
DECLARE<br/>
r your_polygon_table%rowtype;<br/>
BEGIN<br/>
FOR r IN SELECT * FROM your_polygon_table <br/>
LOOP<br/>
insert into interlines(geom,id) SELECT a.geom,r.id from your_line_table a where st_intersects(a.geom,r.geom);<br/>
RETURN NEXT r; <br/>
END LOOP;<br/>
RETURN;<br/>
END<br/>
$BODY$<br/>
LANGUAGE 'plpgsql' ;</div>
<div> </div>
<div>select * from interprob();</div>
</div>
<div> </div>
<div>Greetings</div>
<div>Simon</div>
<div>
<div name="quote" style="margin:10px 5px 5px 10px; padding: 10px 0 10px 10px; border-left:2px solid #C3D9E5; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style="margin:0 0 10px 0;"><b>Gesendet:</b> Samstag, 06. Juli 2013 um 19:19 Uhr<br/>
<b>Von:</b> "Stephen Woodbridge" <woodbri@swoodbridge.com><br/>
<b>An:</b> postgis-users@lists.osgeo.org<br/>
<b>Betreff:</b> Re: [postgis-users] Tricks to find polygon/line intersection faster</div>
<div name="quoted-content">The standard way of dealing this this is to chop you really large<br/>
polygons into tiles. Or if the multipolygons can be split into multiple<br/>
individual polygons you might get better performance.<br/>
<br/>
google: postgis tiling large polygons<br/>
<br/>
if you need the distance that the line intersects the multiple tiles or<br/>
multiple split multipolygons you will need to sum() and group on the<br/>
original id of the split object.<br/>
<br/>
-Steve<br/>
<br/>
On 7/6/2013 1:10 PM, Evan Martin wrote:<br/>
> It's not really "many large things vs many large things". Most lines are<br/>
> < 100 km long (but there are some over 1000 km). Here's a percentile<br/>
> chart: <a href="https://imageshack.us/a/img16/940/w5s.png" target="_blank">https://imageshack.us/a/img16/940/w5s.png</a><br/>
><br/>
> Most of the polygons are also quite small and simple, but there are a<br/>
> few really large complex ones. From my testing it looks like a few of<br/>
> the "worst" polygons (multi-polygons, actually) take all the time, so<br/>
> that 25,000 count was a bit misleading. 96% of them have < 100 points,<br/>
> but the worst one has > 23,000. I couldn't get the area, because<br/>
> ST_Area(geog) is returning some ridiculously high numbers, but it would<br/>
> be millions of sq km.<br/>
><br/>
> On 06.07.2013 5:48, Paul Ramsey wrote:<br/>
>> Without seeing your data it's quite hard to say. Many large things vs<br/>
>> many large things yields a problem where indexes and so on don't have<br/>
>> a lot of leverage on the problem.<br/>
>><br/>
>> On Tue, Jul 2, 2013 at 6:39 AM, Evan Martin<br/>
>> <postgresql@realityexists.net> wrote:<br/>
>>> Hi,<br/>
>>><br/>
>>> I have tables of ~25,000 polygons and ~80,000 lines and I want to<br/>
>>> find which<br/>
>>> lines intersect which polygons using PostGIS 2.1. Both are<br/>
>>> geographies and<br/>
>>> can span the dateline. Doing this the simple way using<br/>
>>> ST_Intersects(geog,<br/>
>>> geog) takes about 3 hours on my machine and I'd to see if there's a<br/>
>>> way to<br/>
>>> speed this up.<br/>
>>><br/>
>>> I already have indexes on the geography columns and one of them is being<br/>
>>> used (the one on the lines). Each line only has 2 points, but the<br/>
>>> polygons<br/>
>>> have anywhere from 4 to 20,000 points and some of them are very<br/>
>>> large. It<br/>
>>> would be OK to miss some of the smaller intersections (ie. where the two<br/>
>>> only just barely intersect), but I wouldn't want the query to return<br/>
>>> false<br/>
>>> positives. In fact, ideally, I'd like to find only the lines that<br/>
>>> "substantially" intersect a polygon, eg. at least x km or x% of the<br/>
>>> line is<br/>
>>> in the polygon, but finding any intersections at all would be a start.<br/>
>>><br/>
>>> One trick I tried is ST_SimplifyPreserveTopology. I used that to create<br/>
>>> simplified version of the polygons (at least those that don't span the<br/>
>>> dateline) and check those first, then if they intersect then check<br/>
>>> the real<br/>
>>> polygons. This seems to work, but the performance gains are marginal<br/>
>>> compared to the simple approach.<br/>
>>><br/>
>>> Is there another trick I can use to do this faster? I know<br/>
>>> ST_Intersects()<br/>
>>> internally calls ST_Distance(), which calculates the distance to a<br/>
>>> fraction<br/>
>>> of a metre. I don't need that kind of precision, so surely there's some<br/>
>>> "shorcut" to be found?<br/>
>>><br/>
>>> Thanks,<br/>
>>><br/>
>>> Evan<br/>
>>><br/>
>>> _______________________________________________<br/>
>>> postgis-users mailing list<br/>
>>> postgis-users@lists.osgeo.org<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/>
>>><br/>
>> _______________________________________________<br/>
>> postgis-users mailing list<br/>
>> postgis-users@lists.osgeo.org<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/>
><br/>
> _______________________________________________<br/>
> postgis-users mailing list<br/>
> postgis-users@lists.osgeo.org<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/>
<br/>
_______________________________________________<br/>
postgis-users mailing list<br/>
postgis-users@lists.osgeo.org<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></div>
</div>
</div></div></body></html>