MS + PostGIS no rows detection?

[Jesse Cleary]

Howdy Charlton -  I should have known my call for clever solutions would 
bring an answer from you.  This is indeed very clever - I got it working 
after some hassles with the syntax in my Data and Filter strings.

To serve this layer by WMS, this works the way I want:
 
DATA "the_geom from (select GeomFromText('POINT(-75.085 35.893)') as 
the_geom, 12 as seq) as sample USING UNIQUE seq USING SRID=-1"
FILTER "(select count(*) from table where (time = 
date_trunc('hour',timestamp without time zone '%timestamp%'))) < 1"

I have some concern about using the same static number (12) as 'seq' but 
since this should only ever return one or 0 rows it seems to work?

When I try to alter the FILTER parameter from my Chameleon application 
using MapScript things get a little weirder.  It mainly works, but 
occasionally spits out an error image and no map.  Not sure at this 
point if it is a Chameleon issue or Postgres/PostGIS balking at the 
constant setting and re-setting of the FILTER parameter. I'm outputting 
a whole new string with each user entry - I don't think I can get the 
existing FILTER string with MapScript 4.2.  No big deal to leave it be 
for now, the WMS fix is the main one I was after.

Thanks for your help!

Jesse



Charlton Purvis wrote:
> Hey, Jesse:
>
> Fun question.
>
> What if you left your original layer alone -- let it do its magic.
>
> And added another layer that was always on whose query string looked similar
> your original layer.  Except w/ a twist.
>
> Say we have this table:
>
> table = test; 
> columns = val, the_geom; 
> it has 2 rows:  where val = 1 and val = 2 & the_geoms = something
>
> Your original layer has a query string like (where the val = 1 is the
> dynamic part):
>
> * select the_geom from test where val = 1;
>
> * And that would give you a lovely picture.
>
> The additional "No data found" query would look like this:
>
> * select 'geom_here' where (select count(*) from test where val = 1) < 1;
>
> * That would give you NOTHING.  Perfect -- because you actually got data.
>
> Well, if you substitute val = 3 on each of those queries, you'd get the
> inverse.  The orig query gives you zilch while the 2nd gives you
> 'geom_here'.
>
> I think that is what you want.  Certainly not your only option but one that
> I think would work.
>
> Charlton
>
>   
>> -----Original Message-----
>> From: UMN MapServer Users List [mailto:MAPSERVER-USERS at LISTS.UMN.EDU] On
>> Behalf Of Jesse Cleary
>> Sent: Wednesday, January 25, 2006 11:45 AM
>> To: MAPSERVER-USERS at LISTS.UMN.EDU
>> Subject: [UMN_MAPSERVER-USERS] MS + PostGIS no rows detection?
>>
>> Hi folks
>>
>> I'm trying to determine a programmatic method to determine when a
>> PostGIS layer query in MapServer returns no rows/empty, beyond not
>> seeing the data on the map.  I am setting my FILTER string interactively
>> using MapScript - sometimes the user's choice does not return any rows
>> from the DB.  In that case, I would like to substitute a small "No Data
>> Available" label on the map instead of the data.
>>
>> I thought about implementing this through a separate label layer in my
>> mapfile, but "no rows" will not return any geometry to use for my "No
>> Data" labeling.  I have tried using inline FEATUREs to handle the label
>> geometry, querying the DB using the same filter, and setting my class
>> expression to detect empty values for any field ( '[field]' = '' ).
>> However,  this does not work - I think inline features override the
>> DATA, CONNECTION, or FILTER parameters and hence no DB querying takes
>> place at all.
>>
>> I am open to using PHP/MapScript if that could work better - I could
>> then rewrite the label layer DATA string to point to a static "No Data"
>> shapefile.  Or perhaps this requires a separate DB table with label-only
>> text and geometry, populated with only the empty time slices missing
>> from my main geom table?
>>
>>
>> Hoping there are some clever ideas that I have overlooked.  I'm just not
>> able to wrap my head around which option to investigate further.  Thanks
>> for any suggestions y'all might have!
>>
>>
>> Jesse Cleary
>>
>>
>> MapServer 4.2.5, PostgreSQL 8.0.5, PostGIS 1.0.4-2
>>
>>
>> --
>> Jesse Cleary
>> Department of Marine Sciences
>> UNC Chapel Hill
>>
>> 17-6 Venable Hall
>> jcleary at email.unc.edu
>> (919) 962-4323
>>     
>
>
>   

-- 
Jesse Cleary				
Department of Marine Sciences	
UNC Chapel Hill

17-6 Venable Hall
jcleary at email.unc.edu
(919) 962-4323