MS + PostGIS no rows detection?

[Charlton Purvis]

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