[postgis-users] Query using st_contains for points within polygons

[Mike Toews]

Hi Dara,

You are close by using "GROUP BY". This needs to be turned into an
aggregate query for your needs. It is possibly easier and more
informative to show counts (rather than "present"). Here is my
rewrite, which may or may not work:

SELECT DISTINCT
wi_ogaa_harvest_waterbodies.mwbc,
wi_ogaa_harvest_waterbodies.county,
wi_ogaa_harvest_waterbodies.lake,
nullif(sum(CASE WHEN invspp_occurrence_data.tsn_char = '81339' then 1
end), 0) AS "Zebra_Mussel",
nullif(sum(CASE WHEN invspp_occurrence_data.tsn_char = '684624' then 1
end), 0) AS "Spiny_Waterflea",
nullif(sum(CASE WHEN invspp_occurrence_data.genus = 'Myriophyllum'
then 1 end), 0) AS "Eurasian_Water_Milfoil",
nullif(sum(CASE WHEN invspp_occurrence_data.tsn_char = '39007' then 1
end), 0) AS "Curly_Leaf_Pondweed",
nullif(sum(CASE WHEN invspp_occurrence_data.genus = 'Heterosporis'
then 1 end), 0) 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)
GROUP BY wi_ogaa_harvest_waterbodies.mwbc,
wi_ogaa_harvest_waterbodies.county, wi_ogaa_harvest_waterbodies.lake
ORDER BY county, lake;

The nullif() function suppresses showing zeros if there are not any
present, otherwise the numbers speak for themselves. If you do want to
show "present" or null, you could easily edit it to another CASE
statement.

-Mike

On 8 March 2010 06:45, Dara Olson <dolson at glifwc.org> wrote:
> 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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>