Tuesday, August 23, 2005

Oracle Analytics to the rescue!

Hi All-

This is a great example of taking a traditional database setup that is not intimately integrated with GIS and "spatially enabling" it. I have been working on a web-based application that will map out areas in Kansas where (spatial) irrigators are authorized to irrigate. Here is a partial example of what I have from the database for Lane county, Kansas:

location net_acres
17S28W22NENW 40
17S28W22NESE 40
17S28W22NESW 40
17S28W22NWNE 40
17S28W22NWNW 40
17S28W22NWSE 40
17S28W22NWSW 40
17S28W22SENE 40
17S28W22SENW 40
17S28W22SESE 40
17S28W22SESW 40
17S28W22SWNE 40
17S28W22SWNW 40
17S28W22SWSE 30
17S28W22SWSW 40
17S28W23NENE 40
17S28W23NENW 40
17S28W23NESE 40
17S28W23NESW 40
17S28W23NWNE 40
17S28W23NWNW 40
17S28W23NWSE 40
17S28W23NWSW 40
17S28W23SENE 40
17S28W23SENW 40
17S28W23SESE 40
17S28W23SESW 40
17S28W23SWNE 40
17S28W23SWNW 40
17S28W23SWSE 40
17S28W23SWSW 40

The "location" is the legal description (PLSS) of the area (down to 40 acre tracts; Township, Range, Section, Quarter, Quarter). The "net_acres" is the total amount that is authorized for that quarter-quarter section of land (up to 40 acres). The final map needs to display whether or not the quarter-quarter section is authorized for the full amount (40 acres) or whether it is only authorized for part of the quarter-quarter section (< 40).

To do this we need to join the legal description table with a spatial feature class (SDE layer) of the legal land parcels. We can do a straight join to a spatial layer of 40 acres polygons (PLSS Q1, Q2) in ESRI´s ArcIMS fairly easily.


<LAYERDEF id="acres_640" visible="true" >
<SPATIALQUERY searchorder="attributefirst"
where="PLSS.ACRES_40.TRS = WIMAS.POU_LANE.LOCATION"
jointables="WIMAS.POU_LANE"/>
<VALUEMAPRENDERER lookupfield="WIMAS.POU_LANE.TRACT" >
<EXACT value="Whole Tract" label="Whole Tract">
<SIMPLEPOLYGONSYMBOL fillcolor="0,128,0" boundary="false" />
</EXACT>
<EXACT value="Partial Tract" label="Partial Tract">
<SIMPLEPOLYGONSYMBOL fillcolor="0,255,0" boundary="false" />
</EXACT>
</VALUEMAPRENDERER>
</LAYERDEF>

This would be efficient if we did not have very large dataset, but some counties in Kansas can have more than 20,000 40-acre tracts! An attribute table join to a spatial table like this would not be efficient enough for the web.

What can we do? Well if you look at table 1, you should see that there is a lot of repetition. If four quarter-quarter sections have the same value then why not join the attribute to the quarter section SDE layer (160 acres; four 40-acre tracts)? To do this we need use SQL to aggregate our results. This is where Oracle´s analytical functions come in very handy! Analytical functions are extremely powerful. I am not going to do them justice here, but if you want more information go to AskTom or to a great introduction to Analytical functions by AMIS in the Netherlands. A key thing to remember about analytical functions is that they are done at the end of the sql statement, just before the order by clause. So you are not limiting your results on the first pass with the use of analytical functions, but are doing inline analysis on the rows you have selected based on your criteria.

Here is an example:



So the POU_LANE is a view that contains the data from the first table. What we want to do is use the "partition by" feature to aggreagte data within the current selection set.

first_value(substr(location, 1, 10))
over (partition by substr(location, 1, 10) ) a_160
This part of the query groups (partition by) the first 10 characters of the location string (this would be the 160 acre polygons) and sets all rows of each group equal to the first value (first_value) that is recorded for each group. The difference between this and a traditional group by is that we can do this partition for each column in a recordset. This methodology can be very powerful if you think about.

count(distinct sign(net_acres - 40))
over (partition by substr(location, 1, 10)) dc_160,


This part of the query does the same partition as above, except that it computes a count for the distinct irrigation tracts--whole tract (sign of 0) or partial tract (sign of -1).

count(sign(net_acres - 40))
over (partition by substr(location, 1, 10)) tc_160,
Here I am looking for a count of the number of 40 acre quarter-quarter sections in the 160 acre quarter sections. Again I am partitioning the records by the location (first 10 characters) within an individual column.

Now we take the information from the analytical functions and perform our logic.


select distinct

case when dc_640 = 1 and tc_640 = 16 then a_640
when dc_160 = 1 and tc_160 = 4 then a_160
else location
end location


Here is the logic part of the query. I am using the case function in Oracle. Case allows you to perform if/then/else statements on a per row basis on your query results. So...
Slap a distinct on the row and we have reduced our mapping component from 32 40-acre polygons to 4 40-acre, 3 160-acre, and 1 640-acre polygon. This doesn't sound like much, but this example has only looked at 2 640-acre sections. For large irrigation counties in Kansas like Finney, I can reduce over 11,000 40-acre tracts to 3200 multi-sectional tracts.

Finally we take the query above and put it into a view and, in ArcIMS, perform a join between the SDE spatial feature class layer and each legal grouping (640 acres, 160 acres, & 40 acres).




Finally after all that we have a map....

I hope this was useful. If you have any questions, please feel free to post a comment. Oracle Analytical functions are a bit complicated at first, but once you understand them they can be extremely powerful and useful.

Cheers,

Jeremy

Sunday, August 21, 2005

SQL for Geographers

Hi All-

This blog is dedicated to the integration between GIS and enterprise level relational databases. I am a Geographer by trade with a strong interest in relational databases. I have been in the GIS field for 10+ years and the database field for 7. The amount of data waiting to be spatially enabled within enterprise databases is extraordinary. I hope to show in this blog working examples of database-GIS interaction. Many examples that I will show are implemented within Oracle 9.2 and ESRI's SDE 8.3. Hopefully most of the SQL algorithms will transfer to your implementation. Stay tuned for more...

Jeremy

This page is powered by Blogger. Isn't yours?