[postgis-users] update / closest polygon
schabi at logix-tt.com
Mon Mar 6 07:27:12 PST 2006
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
More information about the postgis-users