[postgis-users] Allocating house numbers to a parcel table

[Stephen Woodbridge]

On 3/20/2012 8:39 AM, Sindile Bidla wrote:
> I have two tables (street and parcel) in my Postgis database and I would
> like to perform the following:
> 1. Select a particular street segment
> 2. Select all parcels to the left or right of 1
> 3. Update a field in parcels selected in 2 with numbers (odd numbers if
> to the left of 1 or even numbers if to the right of 1.
>
> This is to allow me to allocate house numbers to my parcel table.

select * from streets a, parcels b
  where st_dwithin(b.the_geom, st_buffer(a.the_geom, <buf_distance>)) = 0.0;

This will find the parcels on the right and left.

BUT, your assignment of odd/even numbers is arbitrary unless all the 
streets have been digitized in the direction such that right and left 
fit this schema.

ALSO you will need to sort out the corner cases where the buffer also 
picks up the parcels beyond the end of the street. so maybe looking at 
the amount of area of each parcel that is in the buffer area and picking 
the parcels with the most would work. This ALSO assumes that you streets 
start and end at parcel boundaries which might not be the case.

-Steve