queryByPoint and multiple result rows

[Julie Knoll]

I would like to allow users to query a point on a map and return data values
for unemployment rates for 12 months of the selected year.


My code looks like this:


$sql = "SELECT uc.the_geom, uc.gid, at.value, at.date
            FROM geom_usa_counties AS uc,
                (SELECT fo.value AS value, fc.state_fips, fc.county_fips,
fo.period_start_date as date
                FROM fred_category_to_state_county_fips AS fc,
fred_series_in_category AS fsic, fred_observation AS fo
                WHERE fc.cat_id=fsic.cat_id
                    AND fsic.series_id=fo.series_id
                    AND fsic.series_id LIKE '%$_GET['series']'
                    AND fc.cat_id IN (SELECT cat_id FROM
fred_category_ancestor WHERE ancestor_id BETWEEN 195 AND 201)
                    AND fo.period_start_date BETWEEN '{$_GET['year']}-01-01'
AND '{$_GET['year']}-12-31'
                ) AS at
            WHERE uc.state_fips=at.state_fips
                AND uc.cnty_fips=at.county_fips";

$fred_layer = $map->getLayer(1);
$fred_layer->set("data" , "the_geom from ($sql) as foo using unique gid
using SRID=4269");

$fred_layer->queryByPoint($query_point_obj , MS_MULTIPLE , -1);
$num_results = $fred_layer->getNumResults();

$fred_layer->open();

for($x = 1 ; $x <= $num_results ; $x++)
{
    $result = $fred_layer->getResult($x);
    $shape = $fred_layer->getShape($result->tileindex,$result->shapeindex);
    if($shape)
    {
        echo $shape->getValue($fred_layer , 'date') . ': ' .
$shape->getValue($fred_layer , 'value') . '<br>'
    }
}

$fred_layer->close();


It correctly retrieves 12 results  But when it prints out the date and value
for each result they are exactly the same.

1990-01-01: 9.9
1990-01-01: 9.9
1990-01-01: 9.9
1990-01-01: 9.9
1990-01-01: 9.9
1990-01-01: 9.9
1990-01-01: 9.9
1990-01-01: 9.9
1990-01-01: 9.9
1990-01-01: 9.9
1990-01-01: 9.9
1990-01-01: 9.9



I took a look at the postgresql log, and the query being used is:



SELECT
gid::text,value::text,date::text,asbinary(force_collection(force_2d(the_geom)),'NDR')

FROM (
    SELECT uc.the_geom, uc.gid, at.value, at.date
    FROM geom_usa_counties AS uc,
        (SELECT fo.value AS value, fc.state_fips, fc.county_fips,
fo.period_start_date as date
        FROM fred_category_to_state_county_fips AS fc,
fred_series_in_category AS fsic, fred_observation AS fo
        WHERE fc.cat_id=fsic.cat_id
            AND fsic.series_id=fo.series_id
            AND fsic.series_id LIKE '%URN'
            AND fc.cat_id IN (SELECT cat_id FROM fred_category_ancestor
WHERE ancestor_id BETWEEN 195 AND 201)
            AND fo.period_start_date BETWEEN '1990-01-01' AND '1990-12-31'
        ) AS at
    WHERE uc.state_fips=at.state_fips
        AND uc.cnty_fips=at.county_fips
) as foo WHERE gid = 2362



Which run by itself in pgsql correctly returns the data values for 12
months.

 2362 | 9.900 | 1990-01-01 |
 2362 | 8.800 | 1990-02-01 |
 2362 | 7.900 | 1990-03-01 |
 2362 | 7.200 | 1990-04-01 |
 2362 | 6.700 | 1990-05-01 |
 2362 | 8.000 | 1990-06-01 |
 2362 | 7.000 | 1990-07-01 |
 2362 | 6.700 | 1990-08-01 |
 2362 | 7.100 | 1990-09-01 |
 2362 | 6.300 | 1990-10-01 |
 2362 | 7.400 | 1990-11-01 |
 2362 | 9.200 | 1990-12-01 |

Mapserver obviously sees 12 results, but it is returning the first result 12
times.  Does anyone know what's going on or have any suggestions?

Thanks!
Julie
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20060713/91212bf3/attachment.htm>