Data Mining
I was inspired by an article I read in April-June 2006 ArcUser (yes James I still read ArcUser and ArcNews) about comparing ortho photos from multiple dates in Peirece County, Washington. I got to thinking that there has to be a way to mine Mapdex for date information. Basically image services indexed in Mapdex have five sources of information:
So here it goes...
We need to query each layer name by the individual words in each layer name. So if a layer name were, "Kansas Digital Ortho Photo 2004," we would need to look for "photo" and "2004." Finding layers that might contain imagery is pretty easy. I can look for keywords like...
The number part ("2004") is a bit trickier. I don't want to specifically look for every instance of a number in my query, I want to let the database do the work.
So I use a custom Oracle function (thanks AskTom) that turns a string with spaces into multiple rows.
Now for each word in a layer name I can check to see if it is a number...
Finally I can put all this together and find all layers that have imagery type names in the layer name AND a date.
Once I do that I have almost 3000 imagery datasets that can be identified by a time period.
So check the list out here (I apologize for the large download). I am trying to talk my friend Keith into finishing his really cool date swipe flash viewer so users can really compare imagery from one date to the next. Stay tuned...
This is just one way to do this. You could use regular expressions or use a text based data mining tool like Oracle's Intermedia.
Questions/Comments?
Jeremy
- Server DNS name,
- Map service name,
- Individual layer name,
- Layer geographic location,
- Individual field names within each layer.
So here it goes...
We need to query each layer name by the individual words in each layer name. So if a layer name were, "Kansas Digital Ortho Photo 2004," we would need to look for "photo" and "2004." Finding layers that might contain imagery is pretty easy. I can look for keywords like...
where (a.layer_name like '%photo%'
or a.layer_name like '%aerial%'
or a.layer_name like '%image%'
or a.layer_name like '%ortho%'
or a.layer_name like '%orto%'
or a.layer_name like '%doq%'
or a.layer_name like '%raster%'
or a.layer_name like '%naip%'
or a.layer_name like '%fsa%'
or a.layer_name like '%color%'
or a.layer_name like '%foto%'
or a.layer_name like '%modis%'
or a.layer_name like '%avhrr%'
or a.layer_name like '%aster%'
or a.layer_name like '%ndvi%'
or a.layer_name like '%spot%'
or a.layer_name like '%ikonos%'
or a.layer_name like '%landsat%')
or a.layer_name like '%aerial%'
or a.layer_name like '%image%'
or a.layer_name like '%ortho%'
or a.layer_name like '%orto%'
or a.layer_name like '%doq%'
or a.layer_name like '%raster%'
or a.layer_name like '%naip%'
or a.layer_name like '%fsa%'
or a.layer_name like '%color%'
or a.layer_name like '%foto%'
or a.layer_name like '%modis%'
or a.layer_name like '%avhrr%'
or a.layer_name like '%aster%'
or a.layer_name like '%ndvi%'
or a.layer_name like '%spot%'
or a.layer_name like '%ikonos%'
or a.layer_name like '%landsat%')
The number part ("2004") is a bit trickier. I don't want to specifically look for every instance of a number in my query, I want to let the database do the work.
So I use a custom Oracle function (thanks AskTom) that turns a string with spaces into multiple rows.
create or replace type myTable as table of varchar2(750);
/
create or replace function str2tbl( p_str in varchar2 )
return myTable
pipelined
as
l_str long default p_str || ' ';
l_n number;
begin
loop
l_n := instr( l_str, ' ' );
exit when (nvl(l_n,0) = 0);
pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );
l_str := ltrim( substr( l_str, l_n+1 ) );
end loop;
return;
end;
/
/
create or replace function str2tbl( p_str in varchar2 )
return myTable
pipelined
as
l_str long default p_str || ' ';
l_n number;
begin
loop
l_n := instr( l_str, ' ' );
exit when (nvl(l_n,0) = 0);
pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );
l_str := ltrim( substr( l_str, l_n+1 ) );
end loop;
return;
end;
/
Now for each word in a layer name I can check to see if it is a number...
CREATE OR REPLACE FUNCTION isnumber(p_val IN VARCHAR2) RETURN NUMBER IS
l_val NUMBER;
BEGIN
l_val := TO_NUMBER(p_val);
RETURN 1;
EXCEPTION WHEN VALUE_ERROR THEN
RETURN 0;
END;
l_val NUMBER;
BEGIN
l_val := TO_NUMBER(p_val);
RETURN 1;
EXCEPTION WHEN VALUE_ERROR THEN
RETURN 0;
END;
Finally I can put all this together and find all layers that have imagery type names in the layer name AND a date.
create table ORTHO_PHOTO_BY_DATE_T as (
SELECT a.layer_name LAYER_NAME,
a.server_name SERVER_NAME,
a.service_name SERVICE_NAME,
a.layer_id LAYER_ID,
to_number(t2.column_value) CNUM
FROM layerinfo a,
TABLE(str2tbl(a.layer_name)) t2
where (a.layer_name like '%photo%'
or a.layer_name like '%aerial%'
or a.layer_name like '%image%'
or a.layer_name like '%ortho%'
or a.layer_name like '%orto%'
or a.layer_name like '%doq%'
or a.layer_name like '%raster%'
or a.layer_name like '%naip%'
or a.layer_name like '%fsa%'
or a.layer_name like '%color%'
or a.layer_name like '%foto%'
or a.layer_name like '%modis%'
or a.layer_name like '%avhrr%'
or a.layer_name like '%aster%'
or a.layer_name like '%ndvi%'
or a.layer_name like '%spot%'
or a.layer_name like '%ikonos%'
or a.layer_name like '%landsat%')
and isnumber(t2.column_value) = 1
)
SELECT a.layer_name LAYER_NAME,
a.server_name SERVER_NAME,
a.service_name SERVICE_NAME,
a.layer_id LAYER_ID,
to_number(t2.column_value) CNUM
FROM layerinfo a,
TABLE(str2tbl(a.layer_name)) t2
where (a.layer_name like '%photo%'
or a.layer_name like '%aerial%'
or a.layer_name like '%image%'
or a.layer_name like '%ortho%'
or a.layer_name like '%orto%'
or a.layer_name like '%doq%'
or a.layer_name like '%raster%'
or a.layer_name like '%naip%'
or a.layer_name like '%fsa%'
or a.layer_name like '%color%'
or a.layer_name like '%foto%'
or a.layer_name like '%modis%'
or a.layer_name like '%avhrr%'
or a.layer_name like '%aster%'
or a.layer_name like '%ndvi%'
or a.layer_name like '%spot%'
or a.layer_name like '%ikonos%'
or a.layer_name like '%landsat%')
and isnumber(t2.column_value) = 1
)
Once I do that I have almost 3000 imagery datasets that can be identified by a time period.
So check the list out here (I apologize for the large download). I am trying to talk my friend Keith into finishing his really cool date swipe flash viewer so users can really compare imagery from one date to the next. Stay tuned...
This is just one way to do this. You could use regular expressions or use a text based data mining tool like Oracle's Intermedia.
Questions/Comments?
Jeremy
Posted by jbartley at 10:11 AM | Link | 0 comments
Subscription Options
You are not logged in, so your subscription status for this entry is unknown. You can login or register here.
No comments found.
Commenting has been disabled for this entry.