Suspect that attaching large amounts of code is a breach of etiquette.
So apologies in advance.
Needed to write a contains(polygon,point) function that gave a level of
accuracy greater than the current 'poly@>point' operator. The new
function works just fine. While at it, thought it would be nice to add
in all the 'box op point' and 'point op box' routines that are not
currently defined.
The code in contains.c implements these functions. In contains.sql you
can find how these functions are defined, tided to operators, and then
placed in an operator class. (Permission granted to copy any code in
here you like, it's all trivial)
The perl script test1 tests all the operators. It compares the output
of "box op point" with "box op box(point,point)". It is called with one
parameter, the name of the database to use. It creates a table called
points within that database. It also creates two files in the current
directory a.out and b.out. tkdiff is called to show the differences.
Running this shows that all the operators work fine.
The perl script test2 tests 1000 points against 1000 boxes. It is
called with one
parameter, the name of the database to use. It creates two tables
called
points and boxes within that database. It also creates two files in the
current
directory a.out and b.out. tkdiff is called to show the differences.
Running this shows that the indexing works fine. (Explain says it uses
the index)
Problem:
The following code works in production with 20,000,000 points against
10,000 polygons.
SELECT
W.geocode,
F.state_code,
F.area_code,
F.area_name
FROM
work as W,
boundary as B,
features as F
WHERE
B.boundbox @> box(W.geocode,W.geocode)
AND contains(B.boundary,W.geocode)
AND B.boundout = 'T'
AND (B.feature_id) NOT IN (
SELECT feature_id
FROM boundary
WHERE boundbox @> box(W.geocode,W.geocode)
AND contains(boundary,W.geocode)
AND boundout = 'F' )
AND B.feature_id = F.feature_id;
However the following does not work. It returns an empty set. Note that
all that has changed is that "box @> box(point,point)" has been
changed to "box @> point". From test1 we know that the operators
work, and from test2 we know the indexing works. Confused and
bewildered at this point.
SELECT
W.geocode,
F.state_code,
F.area_code,
F.area_name
FROM
work as W,
boundary as B,
features as F
WHERE
B.boundbox @> W.geocode
AND contains(B.boundary,W.geocode)
AND B.boundout = 'T'
AND (B.feature_id) NOT IN (
SELECT feature_id
FROM boundary
WHERE boundbox @> W.geocode
AND contains(boundary,W.geocode)
AND boundout = 'F' )
AND B.feature_id = F.feature_id;