[postgis-users] Improving Performance

[David Blasby]

Wood Brent wrote:
> The second type of query is against coastlines to identify points on/off land.
> The polygons in this case may have millions of vertices & the bounding box test
> is enormously faster.

Unfortunately, this is always going to be slow because you're going to 
have to compare each of your test points against each of the millions of 
edges in the polygon.

If you really need this to go quick, there is a way, but its a fair bit 
of work.

SELECT * FROM <points table>
WHERE the_geom && <polygon>
       AND PointInPolygon(prepareForPIP(<polygon>), the_geom);

Basically, you need to write two functions:

1. something that takes a postgis (or WKB) polygon and returns
    a structure thats makes it very easy to do Point-In-Polygon tests.
    Typically this builds a spatial index on the vertex-to-vertex edges
    that makes up the polygon.  The hard part is that the structure has
    to be in its 'serializable' form [This means (1) dont use true
    pointers - you have to use offsets and (2) the structure has to be
    continuous in memory].

2. something that takes the prepared structure and actually queries a
    a point.


I just did something like this for a java application.  It went from 
more than 20 minutes (which was my tolerance for waiting) to almost instant.

dave