Re: Another Informix programming snag....Need help, please :-)
In article <2uq50j$h49@max.cecer.army.mil> weller@zorro.cecer.army.mil (Bonnie Weller) writes:
>
>I am stumped as to how to progress with this problem. I have a set of launch
>points that get written into a sql script using awk. (see select.sql script
>below for example) The number of launch points can vary from 1 to 20. Each
>select statement represents a separate launch point. The selection represents
>the location and type of endangered species within a given radius of the launch
>site. I need to print out (preferably using ACE report) something like the
>report below. How can I group the select statements so that the selections from
>each launch site may be listed separately in the report ? Also, each launch
>point must be identified by its easting and northing UTM, if possible.
>
>select.sql
>-----------------------------
>SELECT *
>FROM sites WHERE
> ( ( ( ( easting - 368345 ) * ( easting - 368345 ) +
> ( northing - 3586176 ) * ( northing - 3586176 )
> ) < ( 5700 ) * ( 5700 )
> ) AND easting > 0 AND northing > 0
> );
>SELECT *
>FROM sites WHERE
> ( ( ( ( easting - 368306 ) * ( easting - 368306 ) +
> ( northing - 3586195 ) * ( northing - 3586195 )
> ) < ( 5700 ) * ( 5700 )
> ) AND easting > 0 AND northing > 0
> );
>SELECT *
>FROM sites WHERE
> ( ( ( ( easting - 370284 ) * ( easting - 370284 ) +
> ( northing - 3585720 ) * ( northing - 3585720 )
> ) < ( 5700 ) * ( 5700 )
> ) AND easting > 0 AND northing > 0
> );>---------------------
>
>report format (ideally)
>
>
> Endangered Species Within Given Radius of Launch Sites
>
>Launch Point Species Species Species Document Document Document
>UTM Name Easting Northing Name Type Date
>
>(368345,3586176) birda x1 y1 doca typea 12/12/93
> birdb x2 y2 docb typeb 12/12/92
> birdc x3 y3 docc typec 1/1/91
>
>(368306,3586195) birdd x4 y4 docd typea 3/3/93
> birdf x5 y5 docf typeb 5/5/92
> birdc x3 y3 docc typec 1/1/91
>
>(370284,3585720) birda x1 y1 doca typea 12/12/93
> birdg x6 y6 docg typeb 6/6/92
> birdc x3 y3 docc typec 1/1/91
>--------------------------------------------
>
>Please Note: Launch point UTM is NOT in Informix Database "sites". The UTM
>data has been taken from the Where statement in the sql script (these UTM's will
>vary each time the report is run). This could be substituted for launch 1,
>launch 2, launch 3 if necessary.
>
>I realize this may have to be done using something besides the ACE report, but
>I'd prefer using ACE if possible. I know a little awk and originally had each
>select statement output to a separate file, but I couldn't figure out how to
>read them all back into a single awk script with launch UTM data attached.
>Nothings ever easy. Sigh.
>
>
>Any suggestions would be welcome.
>
>Thanks in advance
>
>e-mail prefered but posting okay
>
>weller@zorro.cecer.army.mil
>
>Bonnie
I would tend to try something like this:
Three tables for:
(1) launch site co-ordinates
(2) endangered species co-ordinates (by the way, there seems to be
an assumption that we can locate a species at a single point).
(3) document info
Table launch_site:
l_site easting northing selected
------ ------- -------- --------
1 368345 3586176 Y
2 368306 3586195 N
3 ... ...
(The "selected" is to enable you to indicate which launch sites you want to
consider in this run. It means that you can put all the launch sites in the
table at the start and then turn them "on" and "off" via a perform screen
or something like that. It is easier than having to load and unload sets of
launch sites every time)
Table species_site:
sp_name easting northing doc_ref
------- ------- -------- -------
birda <x1> <y1> 1
birdb <x2> <y2> 3
birdc ...
Table doc:
doc_ref doc_name doc_type doc_date
------- -------- -------- --------
1 "owl rpt" typea 12/12/93
2 "whale doc" typeb 12/12/92
In fact, here is the script I used to set this up:
--script begins--
create table species_site ( sp_name char(15) not null,
easting integer not null,
northing integer not null,
doc_ref smallint ) ;
create table launch_site ( l_site_no smallint not null,
easting integer not null,
northing integer not null,
selected char(1) not null ) ;
create table doc ( doc_ref smallint not null,
doc_name char(20) not null,
doc_type char(10),
doc_date date ) ;
insert into launch_site values ( 1, 368345, 3586176, "Y" ) ;
insert into launch_site values ( 2, 368306, 3586195, "Y" ) ;
insert into launch_site values ( 3, 370284, 3585720, "Y" ) ;
insert into species_site values ( "owl", 368340, 3586170, 1 );
insert into species_site values ( "eagle", 368300, 3586190, 3 );
insert into species_site values ( "whale", 370280, 3585710, 6 );
insert into doc values ( 1, "owl document", "long", "12/12/93" ) ;
insert into doc values ( 3, "eagle report", "long", "12/12/92" ) ;
insert into doc values ( 6, "whale manual", "short", TODAY ) ;
--script ends--
Then I ran the following report against this DB - which I named "owl"
for no particular reason. Of course it has more than just owls in it:
database owl
end
output
left margin 0
right margin 0
report to "owl.out"
end
select launch_site.l_site_no,
launch_site.easting l_east,
launch_site.northing l_north,
species_site.sp_name[1,8],
species_site.easting s_east,
species_site.northing s_north,
doc.doc_ref,
doc.doc_name[1,13],
doc.doc_type[1,5],
doc.doc_date
from launch_site, species_site, doc
where ( species_site.easting - launch_site.easting ) *
( species_site.easting - launch_site.easting )
+
( species_site.northing - launch_site.northing ) *
( species_site.northing - launch_site.northing )
< ( 100 * 100 )
and sp