Skip site navigation (1) Skip section navigation (2)

sql query on multiple contains

From: "Dara Olson" <dolson(at)glifwc(dot)org>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: sql query on multiple contains
Date: 2010-03-03 23:17:33
Message-ID: 598B9423482E4E2BB99C6FE4C0AC27A1@GISWKSTN2 (view raw or flat)
Thread:
Lists: pgsql-novice
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

pgsql-novice by date

Next:From: bill houseDate: 2010-03-04 04:53:25
Subject: Re: Function
Previous:From: Atif JungDate: 2010-03-03 15:45:59
Subject: libpq or ESQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group