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?

1 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:



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.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.