Postgres/postgis cursors

Question

I have a road network with StreetName and min/max address ranges for each segment (ie- StreetName = 'Main St', Min = 100, Max = 199). I also have individual address points with similar information (ie- StreetName = 'Main St', HouseNumber = 115).

I would like to assign the featureid (gid) of road segments to each address point that has matching StreetName and falls within the min/max range of the road segment. In ArcGIS I would probably write a searchcursor for the roads and that would look cycle through each road segment, get the gid, name, min, & max values, and then do a query statement to find all address points with HouseNumbers that fall within the given segment range (ie- select AddressPoints where StreetName = 'Main St' and min >=100 and max <=199), calculate the gid of the road segment into a field for the selected points in the Address Point layer, then move on to the next road segment and repeat.

1) How would I write a similar cursor in postgres/postgis or 2) Is there a more efficient way of doing this?

Thanks.

Answer 1

As I understand your question there is no need for cursors. This is a very common and simple task to do with simple sql. If we call your two tables roadsegments and adresspoints with fields like this:

roadsegments
gid
the_geom
min_address
max_address

adresspoints
streetname
housenumber

Then you can first create the column for the roadid in the addresspoints table like this:

ALTER TABLE adresspoints ADD COLUMN roadid integer;

Then you can update that field like this:

UPDATE adresspoints SET roadid = roadsegmants.gid FROM 
roadsegments WHERE roadsegments.streetname=adresspoints.streetnames
AND addresspoints.housenumber >= roadsegments.min_address
AND addresspoints.housenumber <= roadsegments.max_address;

If it is ver huge tables it might be more efficient to recreate addresspoints table like this:

CREATE TABLE new_addresspoints as
SELECT a.*, b.gid as roadid  
FROM addresspoints a inner join roadsegments b
on a.streetname=b.streetnames
AND a.housenumber >= b.min_address
AND a.housenumber <= b.max_addres;

To make things run fast you should also put indexes on streetname in both tables and min_address, max_address and housenumber.

HTH
Nicklas