Dale posted a request to see Line Density recreated in SQL. Please take a look at the ESRI help topic for line density.
I will warn you, the SQL for this is a total mess – I think I can simplify it, but I only spent about 15 minutes writing the query, and it’s finals week and I am tired 🙂
So, I have the code below, but first let me outline the steps I need to follow – we assume a raster called rastersurface, and a vector layer called lines
1. Take a raster (here we call it rastersurface)
2. For each pixel, we have to obtain a point (this is where we issue the NewPoint function)
3. For each point, we have to buffer it (in this case, I am using the buffer function with 1000m)
4. For each of the buffered points, I am performing a clipintersect with the lines.
5. The clipintersect of the lines and the buffers are for each base pixel – so, we sum the area of each line, and group it by the unique centerX and centerY or the base pixel.
6. We wrap that entire thing inside an UPDATE query and update the [Height (I)] feature for each pixel. To update the correct pixel value, we have to join the table where the centerX and centerY of the pixel equals the centerX and centerY of the points from which the buffer and summation of the areas are computed.
Like I said, I am too tired at the end of the semester to clean this one up better, and I hope the above steps help you understand the logic. You may want to print it out and use a colored pencil to figure out which parts of the SQL are described in the list. Personally, I think I can probably get rid of one of the sub-select queries (we have 3 sub-selects), but for now I am having a tough time focusing. Perhaps Dale can take a shot at reducing the query.
UPDATE (SELECT [Center X (I)], [Center Y (I)], cx, cy, sumlen, [Height (I)] FROM (SELECT sum(Length(g)*pop)/1000 AS sumlen, cx, cy FROM (SELECT lines.pop, [rastersurface].[Center X (I)] AS cx, [rastersurface].[Center Y (I)] AS cy,[Height (I)] AS Hgt, clipintersect(lines.id,buffer(AssignCoordSys(NewPoint([rastersurface].[X (I)],[rastersurface].[Y (I)]),CoordSys(“rastersurface” AS Component)),100,”m”)) AS g FROM [rastersurface], lines ) GROUP BY cx, cy ),[rastersurface] WHERE cx = [Center X (I)] AND cy = [Center Y (I)] ) SET [Height (I)] = sumlen
The result looks like this:
Comments