This is the sort of thing that seems really easy in hindsight, but it took me a little while to get right.
There are lots of things that I don’t like about PL/SQL, but I often prefer to do spatial operations at the table level inside the database, rather than using ArcPy or ArcObjects; I think that it’s easier to reuse queries like this, the code tends to be more readable and concise, and performance is almost always significantly better.
In this case, we had a set of polygons (“PPAs”) that needed to be assigned a value based on the county they fell in, as well as being uniquely numbered within that county.
select /*+ ordered use_nl (t,c) use_nl (t,p) */ objectid, state, county_name county, region, row_number() over (partition by region, state, county_name order by region, state, county_name, sdo_geom.sdo_centroid(p.shape, 0.005).sdo_point.x, sdo_geom.sdo_centroid(p.shape, 0.005).sdo_point.y) as rnum from ( select p.objectid, p.traps, c.state, c.county_name, p.region, sdo_geom.sdo_centroid(p.shape, 0.005) as shape from table(sdo_join('PPAS','SHAPE','COUNTIES','SHAPE')) t, ppas p, counties c where t.rowid1 = p.rowid and t.rowid2 = c.rowid and c.state = p.state and sdo_geom.relate(c.shape, 'anyinteract', sdo_geom.sdo_centroid(p.shape, 0.005), 0.005) = 'TRUE' ) p order by state, county_name, rnum
Ordering by the centroid X and Y values in the partition clause is important; without that, the polygons don’t get numbered in a deterministic way. In this case, the polygons were generated daily by a decision support system, and without ordering the blocks spatially, the numbers within each county would be swapped around each day.comments powered by Disqus