[postgis-users] ST_Intersection very slow

[Mark Wynter]

John, I've got a variation in mind that works solely using lc_class32 - should take same time as what I sent through earlier. I'm guessing 2 minutes!!!  A different SQL statement in the data prep stage - it is now becoming very clear to me as to how to tile lc_class32 in the purest sense. Which means universal application for other use cases. let me test version 2 before sharing.

Sent from my iPhone

> On 26 Feb 2015, at 4:19 am, John Abraham <jea at hbaspecto.com> wrote:
> 
> Wow guys.  131 seconds is a lot faster than 10 days.
> 
> Remi, if you can work out a fast intersection method that uses points and lines as a preprocessor to dealing with the polygons, I think it would be a great addition to PostGIS.  ST_Intersection in PostGIS is often quite a bit slower than the implementation in Geomedia, Mapinfo, and (I hear) ArcGIS, so any functionality that results in speed improvements would be great.
> 
> Mark, I can't wait to figure out why your system was fast!  I was following your (preliminary) tutorial and gridding the data was progressing very slowly.  
> 
> I have a provincial boundary file but there seems to be much ambiguity in GIS representations of the provincial boundary, so I won't send you the one I have.  I can try to assemble one from other sources.
> 
> --
> John Abraham
> jea at hbaspecto.com
> 403-232-1060
> 
>> On Feb 25, 2015, at 6:28 AM, Mark Wynter <mark at dimensionaledge.com> wrote:
>> 
>> Hi John
>> 
>> I’ve just crunched your whole dataset.  The process takes 131 seconds for the vector tiling (using a 16 CPU machine).  Plus another 170 seconds for data prep at the start including making the poly's valid.
>> For a 2 CPU machine, it will take circa 15 minutes, or double that using a single CPU.
>> 
>> Only one small issue outstanding - and that relates to clipping the regular grid prior to tiling.  For clipping I used the union of the abmiw2wlcv_48tiles as supplied with the data - the problem is the abmiw2wlcv_48tiles are rough and ready, which produces voids. The voids using my method unfortunately get the same feature class as lc_class = 32.  You’ll see this clearly on second screenshot.
>> The way around this is to clip the regular grid using a high-res shapefile of the Alberta state boundary prior to the tile crunching the lancover_polygons_2010 table.  This is easily done - I just didn’t have the state boundary data.
>> 
>> I need to get some sleep. John, Remi,  I will share with you the code tomorrow.  For others, I’ll be posting a tutorial that steps through the methods touched upon in this thread..
>> 
>> John, the only difference between my tutorial and its application to your land cover data was a few tweaks to the data prep stage. Otherwise the same code pattern (no modification at all needed to the worker_function).  It was great to test the code with your data.
>> 
>> Speak tomorrow.
>> Mark
>> 
>> 
>> 
>> 
>> <Screen Shot 2015-02-25 at 11.29.15 pm.png>
>> 
>> 
>> <Screen Shot 2015-02-25 at 11.39.04 pm.png>
>> 
>> 
>> <Screen Shot 2015-02-25 at 11.41.41 pm.png>
>