Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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

share|improve this answer
    
Excellent! That worked perfectly. Thanks for your help. –  RyanDalton Dec 2 '10 at 4:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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