[postgis-users] update / closest polygon

[Markus Schaber]

Hi, Paul,

Paul Ramsey wrote:

> select a.id,a.distance from a, b where a && expand(b,<arbitrary 
> distance>) and b.id = <id for one geometry> order by distance limit 1;
> 
> As long as <arbitrary distance> always hits something, you always get 
> an answer, and pretty fast too.  Obviously, it is not a general 
> solution :)

But be shure to make the arbitrary distance large enough. It was also
suggested to increase the arbitrary distance in a loop until you hit
something, but this does not always work as expected:

 . .________________
 . /               .
 ./                .
 |                 .
 | A               .
 |                 .
 |                 .  |
 |                 .  |
 |                 .  | B
 |              X  .  |
 |                 .  |
 | . . . . . . . . .  |

                |----| <arbitrary distance>

In this example, the LineString B clearly is nearer to point X than
LineString B, but for the given distance, A is selected as nearest. (The
BBox is symbolized via . . . )


> For your mass update, you would wrap the SQL above in something 
> procedural to iterate through each record in b.  Again, note the 
> ugliness of the solution.  With a real nearest-neighbor index, it  could
> be one piece of SQL, and not involve a magic number distance  filter.

How much work would it be to create "nearest neighbour" code?

And for "real" nearest neightbour, not just bbox nearest neighbour?

I ask this questions, because "nearest" is no common relational
operation, and therefore the


-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org