On Sep 27, 2012, at 10:12 AM, Martin Davis wrote: > Touches is a complex topological relationship (overly, perhaps?) which takes > a long time to compute on large geometries. It's also computing more > information than you need (since it sounds like you already know the lines > touch only at endpoints). So working with just the endpoint should be > faster, for sure. > > CoveredBy is more amenable to optimization, which is why it is faster. > And really fast when I get the SQL right - UPDATEs are different than SELECTs ;) I finally figured out that I shouldn't use a JOIN in an update, even a small region went on forever until I stopped it. Put the st_coveredby in the where and the whole table processed in a couple minutes.
I doing the update in stages, for sanity. Now working on the touching updates. Phwew. > On 9/26/2012 7:21 AM, William Kyngesburye wrote: >> I'm working on updating the tables instead. The idea was to save a lot of >> preprocessing time on the whole table, and morph the classifications when >> extracting small regions. >> >> distinct on + order by (to get the same priority of reclassification as the >> case statement) was really slow even for a small region. I imagine group by >> would be similarly slow, and as you say other attributes would have to be >> restored in another step. >> >> st_touches() appears to be the time consuming part. I'm trying >> st_distance() with start-end points on both sides (4 joins) to speed this >> up. st_coveredby() seems to go a lot faster than the touch join. >> >> On Sep 25, 2012, at 11:19 PM, Martin Davis wrote: >> >>> This is one of those cases that SQL seems to make unnecessarily difficult. >>> Writing this query in a procedural way would be trivial, so why is it so >>> hard to express in a declarative relational way? >>> >>> DISTINCT ON (a.id) is the classic non-standard Postgres way of handling >>> this. >>> >>> The other hack to get uniqueness of the driving table is to GROUP BY a.id. >>> You then need to use appropriate aggregation functions to maintain the >>> dependent columns from a. You might be able to use MAX on newclass. MAX >>> might work for the other invariate a columns too, but it might be faster to >>> use FIRST. This is not built into Postgres, but is available as an >>> extension: >>> >>> http://wiki.postgresql.org/wiki/First/last_%28aggregate%29 >>> >>> Or, you could just SELECT a.id, MAX(newclass) and then join back to a to >>> recover the dependent columns. >>> >>> No idea whether any of these ideas will be performant. Maybe someone else >>> has a more clever way of doing this. >>> >>> On 9/25/2012 10:48 AM, William Kyngesburye wrote: >>>> I'm trying to do a series of joins to classify some lines based on other >>>> lines they are touching in the same table and whether they are in polygons >>>> in another table, and lines are replicating. >>>> >>>> in a simplified form, it's something like (I imported with GDAL, so all my >>>> geometry fields are wkb_geometry): >>>> >>>> select a.wkb_geometry, a.id, a.name, a.class, >>>> case >>>> when a.class = 'a' and b.class is not NULL and (d1.newclass = 'b' or >>>> d2.newclass = 'b') then 'b' >>>> ... >>>> else a.class >>>> as newclass >>>> from source as a left outer join polys as b st_coveredby(a.wkb_geometry, >>>> b.wkb_geometry) >>>> left outer join source as c1 on st_touches(st_startpoint(a.wkb_geometry), >>>> c1.wkb_geometry) >>>> left outer join source as c2 on st_touches(st_endpoint(a.wkb_geometry), >>>> c2.wkb_geometry) >>>> >>>> All lines are replicated 4-9 times (maybe more that I didn't notice), each >>>> duplicate with different newclass values though some newclass values >>>> duplicated also (I suppose dependent on which join it came from). >>>> >>>> How can I stop the replication? Probably some SQL thing, the different >>>> join types still confuse me some. >>>> >>>> Adding a DISTINCT on the id to the select (and a sort to make that work) >>>> adds a LOT to the processing time processing time. >>>> >>>> ----- >>>> William Kyngesburye <kyngchaos*at*kyngchaos*dot*com> >>>> http://www.kyngchaos.com/ >>>> >>>> "Oh, look, I seem to have fallen down a deep, dark hole. Now what does >>>> that remind me of? Ah, yes - life." >>>> >>>> - Marvin >>>> >>>> >>>> _______________________________________________ >>>> postgis-users mailing list >>>> postgis-users@postgis.refractions.net >>>> http://postgis.refractions.net/mailman/listinfo/postgis-users >>>> >>>> >>>> ----- >>>> No virus found in this message. >>>> Checked by AVG - www.avg.com >>>> Version: 2012.0.2221 / Virus Database: 2441/5290 - Release Date: 09/24/12 >>>> >>>> >>> _______________________________________________ >>> postgis-users mailing list >>> postgis-users@postgis.refractions.net >>> http://postgis.refractions.net/mailman/listinfo/postgis-users >> ----- >> William Kyngesburye <kyngchaos*at*kyngchaos*dot*com> >> http://www.kyngchaos.com/ >> >> The equator is so long, it could encircle the earth completely once. >> >> >> >> ----- >> No virus found in this message. >> Checked by AVG - www.avg.com >> Version: 2012.0.2221 / Virus Database: 2441/5292 - Release Date: 09/25/12 >> >> >> > ----- William Kyngesburye <kyngchaos*at*kyngchaos*dot*com> http://www.kyngchaos.com/ "This is a question about the past, is it? ... How can I tell that the past isn't a fiction designed to account for the discrepancy between my immediate physical sensations and my state of mind?" - The Ruler of the Universe _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
