Tuesday, November 01, 2005

The Power of the Spatial to Attribute Join-Part 1

This is the start of a series of posts related to the mapping and analysis of complex multi-dimensional datasets. Traditionally it has been difficult to map multiple dimensional datasets in a GIS. In this series we are going to look at several database driven GIS projects related to multi-dimensional mapping.

The first project relates to the mapping and analysis of Kansas Oil and Gas production data.

Oil and Gas Production Through Time
A dynamic web page that allows a user to map production data by section through time. For each product (oil or gas), you can map production by year, range of years (average or cumulative), or the percent change from one year to another. This is a good example of time-series gis within an enterprise database system.
From... KGS Energy Resources Interactive Maps


The image to the right contains a table schematic of the main lease table in our database. This table identifies how much oil and gas was produced from a designated lease. As you can see we have:
While this is useful information we need to structure it a bit more to make useful real-time mapping visualization. Because of the complex nature of lease information, we cannot currently match leases to particular wells in Kansas. We can aggregate the spatial location of leases up to public land survey boundaries (PLSS). Once we do this aggregation we have a much cleaner table to work with.


Where "RECNMBR" is the unique id for a particular 640-acre section in Kansas.

From this table can construct queries that identify the amount of production for a particular product for a particular year or for the sum over a range of years.

In the query above we are asking the database to create a view that gives us the sum of gas production between from 1990 to 2000. Within this view we are using the Group By function to group the recnmbr column into a distinct number. Each distinct recnmbr will have multiple production values. First we eliminate all the rows that do not produce gas (where product = 'G') and then we eliminate all the rows where the year is not between 1990 and 2000. We are then left with rows that have produced gas between 1990 and 2000. Take RECNMBR 2555, it has produced gas 10 out of the 12 possible years in that range. We need to summarize these 10 values for this unique RECNMBR. We can perform functions like: count, average, min, max, and sum. In our case we are going to sum the values that fit our criteria.

Now we have reduced that multi-dimensional table to one value per spatial polygon (RECNMBR). We can now join that view to a spatial dataset in either ArcMap or ArcIMS. Now we can easily produce a map like this...


Let's take it a step further and say we want to produce a map of the percent change in gas production between the years 1990 and 2000. To do that we need to formulate a bit more complicated query.

CREATE OR REPLACE VIEW OIL_GAS_WEB_PROD163922 AS
(select a.recnmbr as RECNMBR,
decode(sign(avg(b.x1)-avg(c.x2)),
1, ROUND((-100 * (avg(b.x1) - avg(c.x2))/avg(b.x1)),0),
-1,ROUND((-100 * (avg(b.x1) - avg(c.x2))/avg(b.x1)),0),
0) as PERCENTCHANGE
from ACRES_640_PRODUCTION a,
(select recnmbr, PRODUCTION x1
from ACRES_640_PRODUCTION
where year = 1990
and product = 'G') b,
(select recnmbr, PRODUCTION x2
from ACRES_640_PRODUCTION
where year = 2000
and product = 'G') c
where a.recnmbr = b.recnmbr
and a.recnmbr = c.recnmbr
and a.year in (1990,2000)
and a.product = 'G'
group by a.recnmbr
union
select distinct b.recnmbr RECNMBR,
19901 as PERCENTCHANGE
from (select recnmbr,
PRODUCTION x1
from ACRES_640_PRODUCTION
where year = 1990
and product = 'G') b
left outer join
(select recnmbr, PRODUCTION x2
from ACRES_640_PRODUCTION
where year = 2000
and product = 'G') c
on b.recnmbr = c.recnmbr
where b.x1 is not null
and c.x2 is null
union
select distinct c.recnmbr RECNMBR,
20001 as PERCENTCHANGE
from (select recnmbr,
PRODUCTION x1
from ACRES_640_PRODUCTION
where year = 1990
and product = 'G') b
right outer join
(select recnmbr,
PRODUCTION x2
from ACRES_640_PRODUCTION
where year = 2000
and product = 'G') c
on c.recnmbr = b.recnmbr
where c.x2 is not null
and b.x1 is null )

Don't be frightened! The query should be broken up into three parts. The first part will compute the percent change between year 1990 and year 2000 for all the sections where we have gas production in 1990 and in 2000 (and a.year in (1990,2000)). Parts 2 and 3 of the union are specifically for sections that produce in one year, but not the other. The final map looks like...


Areas in red are places where gas production increased from 1990 to 2000. Areas in blue show a precent decreases. Areas in green did not produce in 1990. Areas in orange stopped producing by 2000. Email me if you have any questions.

Next time we will look at sea-surface temeratures through time. I know you can't wait!

Comments: Post a Comment



<< Home

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