Greetings.
I am trying to make a query which builds a table that has a 'present' or a NULL
in each column of whether or not a point is within that polygon. I have a
polygon file of waterbodies and a point file of invasive species data. I want
to make a table that shows which invasive species are known to be present in
the waterbody. So far I have come up with the following:
SELECT DISTINCT
wi_ogaa_harvest_waterbodies.mwbc,
wi_ogaa_harvest_waterbodies.county,
wi_ogaa_harvest_waterbodies.lake,
(CASE WHEN invspp_occurrence_data.tsn_char = '81339' then 'present' else NULL
end) AS "Zebra_Mussel",
(CASE WHEN invspp_occurrence_data.tsn_char = '684624' then 'present' else NULL
end) AS "Spiny_Waterflea",
(CASE WHEN invspp_occurrence_data.genus = 'Myriophyllum' then 'present' else
NULL end) AS "Eurasian_Water_Milfoil",
(CASE WHEN invspp_occurrence_data.tsn_char = '39007' then 'present' else NULL
end) AS "Curly_Leaf_Pondweed",
(CASE WHEN invspp_occurrence_data.genus = 'Heterosporis' then 'present' else
NULL end) AS "Heterosporis"
FROM inland.wi_ogaa_harvest_waterbodies
JOIN invasive_species.invspp_occurrence_data ON ST_Contains
(wi_ogaa_harvest_waterbodies.the_geom, invspp_occurrence_data.the_geom)
ORDER BY county, lake
This seems to work correctly, but for each record (lake) that has a species
present a new line is formed. For example, if a lake had all five species
listed, the lake would be listed 6 times in the table (one line for each
species present and one line for all NULLs). If I use GROUP BY, it returns
errors unless I include all columns including "invspp_occurrence_data.tsn_char"
and "invspp_occurrence_data.genus" which puts me in the same spot I was without
using GROUP BY.
Does anyone know what I am doing wrong or how to fix? Any help would be
greatly appreciated.
Miigwech!
Dara
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
