PostGIS Queries using FILTER

[Kyle Wilcox]

Seems to be a bug in Mapserver.  Thanks to Armin of pmapper for helping
with the problem.

To get it to work correctly, I just added the filter line into the query
directly:

 DATA "the_geom from (SELECT * from forecast left join grid on
grid.gid=forecast.grid_id where time=TIMESTAMP'2007-11-06 00:00:00') as
data USING UNIQUE gid USING SRID=4326"

Kyle Wilcox wrote:
> I just added that layer name into the mail code to avoid confusion, they
> are named by their timestamp's index  For example:
> 
> First layer:
> LAYER
> 	NAME 'One'
> 	FILTER "time=TIMESTAMP'2007-11-06 00:00:00'"
> 	METADATA 'DESCRIPTION' '2007-11-06 00:00:00' END
> 	...
> END
> 
> Second Layer:
> LAYER
> 	NAME 'Two'
> 	FILTER "time=TIMESTAMP'2007-11-06 03:00:00'"
> 	METADATA 'DESCRIPTION' '2007-11-06 00:00:00' END
> 	...
> END
> 
> Those are the only differences between layers.
> 
> 
> Could it be that the queryByPoint() function is querying on the specific
> point and applying the filter, which fails or returns all timesteps
> associated with that point, because the 'time' column is in a separate
> table than the geometry column?  I assumed the queryByPoint() function
> would join my grid and data tables as specified in DATA.  The grid
> geometry (POINTS) are in a separate table than the data (values I want
> returned from a query).
> 
> The query code is coming from the pmapper framework.  I'll set up a
> small test case using Mapserver 4.10 and a self made application to see
> if the same thing happens.
> 
> Thanks for the response!
> 
> 
> 
> Steve Lime wrote:
>> Attribute queries use filters themselves so any predefined filter is set aside temporarily. That said, for other
>> types of purely spatial queries the FILTER should remain intact. I just stepped through the point query code
>> and couldn't any place where it was mucked with.
>>
>> What does the code that does that's doing the query look like? I notice in your sample layer that the layer 
>> name is 'NAME' which makes me wonder if somehow you're always querying the same layer. Not to say that
>> there isn't something else going on as you suggest but I'll start with the obvious.
>>
>> Steve
>>
>>>>> On 11/6/2007 at 4:00 PM, in message <4730E3F5.2080000 at Noaa.gov>, Kyle Wilcox
>> <Kyle.Wilcox at NOAA.GOV> wrote:
>>> Did the usual searching without finding anything relevant.
>>>
>>> The application I am referencing is at:
>>> http://155.206.18.162/mapping/map/map.phtml 
>>>
>>> I have time series data in a PostGIS enabled database (current and wind
>>> fields from model output).  I can display the point data without
>>> problems (try any of the current or wind layers).
>>>
>>> The problem is that when querying a point layer, the data that is always
>>> extracted from the first timestamp rather than the timestamp associated
>>> with the layer I am trying to query (specified as a FILTER).  I understand
>>> what is happening.. the query by point functionality is querying the
>>> database at the specific POINT and displaying the first row that returns
>>> (the first timestamp).  If I delete the first timestamp data, the second
>>> timestep data is displayed and so on.  The only thing that changes
>>> between layers is the FILTER.
>>>
>>> Does the queryByPoint() function utilize the FILTER definition from the
>>> mapfile?  It doesn't seem to be doing so.
>>>
>>> (Mapserver 5.0 and PHP/Mapscript)
>>>
>>>
>>>  Here is a sample layer definition:
>>>
>>>      LAYER
>>>                  TYPE POINT
>>>                  CONNECTIONTYPE postgis
>>>                  NAME 'NAME'
>>>                  CONNECTION 'host=HOST user=USER dbname=DATABASE'
>>>                  DATA "the_geom from (SELECT * from forecast left join
>>>  grid on grid.gid=forecast.grid_id) as data USING UNIQUE gid USING
>>>  SRID=4326"
>>>                  FILTER "time=TIMESTAMP'2007-11-06 00:00:00'"
>>>                  METADATA
>>>                          'DESCRIPTION' '2007-11-06 00:00:00'
>>>                          'RESULT_FIELDS'
>>>  'wind_speed,wind_from_direction,rotation'
>>>                          'RESULT_HEADERS' 'Wind Speed (m/s), Wind
>>>  Direction (from TN), Rotation of Image (from TN)'
>>>                          'LAYER_ENCODING' 'UTF-8'
>>>                  END
>>>                  STATUS OFF
>>>                  PROJECTION
>>>                          'init=epsg:4326'
>>>                  END
>>>                  TOLERANCE 10
>>>                  TOLERANCEUNITS pixels
>>>                  LABELMAXSCALE 60000
>>>                  TEMPLATE void
>>>          CLASS
>>>                      NAME ">= 0.95 m/s"
>>>                      EXPRESSION ([wind_speed] >= 0.95)
>>>                      STYLE
>>>                              SYMBOL "arrow2"
>>>                                ANGLE [wind_from_direction]
>>>                              COLOR 118 0 0
>>>                              OUTLINECOLOR 0 0 0
>>>                              SIZE 10
>>>                      END
>>>                      TEXT ([wind_speed] m/s - [wind_from_direction] TN)
>>>                      LABEL
>>>                              COLOR 118 0 0
>>>                              OUTLINECOLOR 0 0 0
>>>                              POSITION AUTO
>>>                              SIZE small
>>>                              PARTIALS TRUE
>>>                      END
>>>              END
>>>          ...
>>>      END
>>>
>>> -- 
>>>
>>>  Kyle Wilcox
>>>  NOAA Chesapeake Bay Office
>>>  410 Severn Avenue
>>>  Suite 107A
>>>  Annapolis, MD 21403
>>>  office: (410) 295-3151
>>>  Kyle.Wilcox at noaa.gov 
>>>
>>>  "It is from the wellspring of our despair and the places
>>>   that we are broken that we come to repair the world."
>>> 						- Murray Waas
> 

-- 

 Kyle Wilcox
 NOAA Chesapeake Bay Office
 410 Severn Avenue
 Suite 107A
 Annapolis, MD 21403
 office: (410) 295-3151
 Kyle.Wilcox at noaa.gov

 "It is from the wellspring of our despair and the places
  that we are broken that we come to repair the world."
						- Murray Waas