Tuesday, June 12, 2007

Performance and Contains()

The reason I was thinking about performance improvements — and how billing by CPU usage provides vendors with no incentive to work on them — is because we have been thinking about a particular PostGIS use case recently.

Suppose you have a very large candidate table of smallish things, 10s of millions of them, and you want to find all of the smallish things that are contained by a largish polygon.

The spatial index will be very useful for quickly winnowing down the 10s of millions of things to the 10s of thousands of things that might fall inside the polygon. However, after that, you're left testing each of the smallish things individually for containment. And a majority of the smallish things will be unambiguously inside the large polygon, not even close to the edge, so a great deal of computation will be wasted. The same issue adheres to Intersects() and inversely to Within().

The trick, clearly, is to provide some kind of short circuit, so that the "easy" cases can be trivially dealt with and only the boundary cases need a full test.

A nice approach for generally convex polygons would be a "maximum inscribed rectangle" (MIR) — any small thing whose MBR fits in the MIR is definitely contained. However, then you have to calculate a MIR, which is itself costly.

A variation on the MIR approach is just to superimpose the polygon on a grid and find all those squares that are fully contained in the polygon. Any smallish feature whose MBR is fully contained by "inside grid squares" is itself fully contained.

What it looks like we'll do first is to speed up the general calculation of containment, by caching a topologized version of the large polygon. The topologized version will have an index on all the edge segments, for fast testing if a given candidate crosses the boundary, and an index for fast point-in-polygon testing. The idea is first you see if the candidate crosses the boundary, if it does not then it must be either fully inside or fully outside, so then use a point-in-polygon test on one of the end points to see if it is in or out.

All in all, it is a lot of complexity for what seems like a very common hard-to-index case: test a large number of candidates for containment.

1 comment:

Peter Batty said...

Paul, while I'm sure it's not enough to make you entertain the thought of a Microsoft solution :), the way Ed Katibah explained how they are doing the spatial indexing in SQL Server, I think it should do a pretty good job on this (if I understood it correctly, their grid index includes variable size squares which are contained completely within a polygon, as well as those which contain it, so they should be able to handle the non-boundary cases very efficiently). More at my blog.

About Me

My Photo
Victoria, British Columbia, Canada


Blog Archive


bc (40) it (31) postgis (23) video (12) enterprise IT (11) icm (11) gis (9) sprint (9) open source (8) osgeo (8) enterprise (7) cio (6) foippa (6) management (6) spatial it (6) foi (5) foss4g (5) outsourcing (5) politics (5) mapserver (4) bcesis (3) boundless (3) opengeo (3) oracle (3) rant (3) COTS (2) architecture (2) deloitte (2) esri (2) hp (2) idm (2) javascript (2) natural resources (2) ogc (2) open data (2) openstudent (2) postgresql (2) technology (2) vendor (2) web (2) 1.4.0 (1) HR (1) access to information (1) accounting (1) agile (1) aspen (1) bcpoli (1) benchmark (1) buffer (1) build vs buy (1) business (1) business process (1) c (1) career (1) cartodb (1) cathedral (1) client (1) cloud (1) code (1) common sense (1) consulting (1) contracting (1) core review (1) crm (1) crockofshit (1) cunit (1) custom (1) data science (1) data warehouse (1) design (1) development (1) digital (1) email (1) environment (1) essentials (1) evil (1) exadata (1) fcuk (1) fgdb (1) fme (1) foocamp (1) foss4g2007 (1) ftp (1) gdal (1) gds (1) geocortex (1) geometry (1) geoserver (1) geotiff (1) google (1) google earth (1) government (1) grass (1) hadoop (1) iaas (1) icio (1) imagery (1) industry (1) innovation (1) integrated case management (1) introversion (1) iso (1) isss (1) isvalid (1) jpeg (1) jts (1) lawyers (1) mapping (1) mcfd (1) microsoft (1) money (1) mysql (1) new it (1) nosql (1) nrs transformation (1) opengis (1) openlayers (1) oss (1) paas (1) pirates (1) policy (1) portal (1) proprietary software (1) public accounts (1) qgis (1) r (1) rdbms (1) recursion (1) redistribution (1) regression (1) rfc (1) right to information (1) saas (1) salesforce (1) sardonic (1) seibel (1) sermon (1) server (1) siebel (1) snark (1) spatial (1) standards (1) statistics (1) svr (1) taxi (1) tempest (1) texas (1) tired (1) transit (1) twitter (1) uber (1) udig (1) uk (1) uk gds (1) verbal culture (1) victoria (1) waterfall (1) wfs (1) where (1) with recursive (1) wkb (1)