Tuesday, August 23, 2005
Oracle Analytics to the rescue!
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:
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" >
where="PLSS.ACRES_40.TRS = WIMAS.POU_LANE.LOCATION"
<VALUEMAPRENDERER lookupfield="WIMAS.POU_LANE.TRACT" >
<EXACT value="Whole Tract" label="Whole Tract">
<SIMPLEPOLYGONSYMBOL fillcolor="0,128,0" boundary="false" />
<EXACT value="Partial Tract" label="Partial Tract">
<SIMPLEPOLYGONSYMBOL fillcolor="0,255,0" boundary="false" />
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))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.
over (partition by substr(location, 1, 10) ) a_160
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))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.
over (partition by substr(location, 1, 10)) tc_160,
Now we take the information from the analytical functions and perform our logic.
case when dc_640 = 1 and tc_640 = 16 then a_640
when dc_160 = 1 and tc_160 = 4 then a_160
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...
- If there is only 1 distinct value (either whole tract or partial tract) in the dc_640 column and there are a total of 16 rows (16 rows that have the same a_640 value) in the tc_640 column then the row value is equal to the 8 character section identifier (a_640)
- If there is only 1 distinct value (either whole tract or partial tract) in the dc_160 column and there are a total of 4 rows (4 rows that have the same a_160 value) in the tc_160 column then the row value is equal to the 10 character quarter-section identifier (a_160)
- If neither of the above are true then the location value is the full quarter-quarter-section.
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.
Sunday, August 21, 2005
SQL for Geographers
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...