[Geoserver-users] how to pass user parameters to PostGIS queries?

Tyler_Erickson1 · April 29, 2008, 6:43pm

Is there any way to pass user parameters through GeoServer that can be used
in a PostGIS query? I am trying to figure out how to retrieve summary data
(calculated by PostGIS) based on some user supplied parameters.

As a concrete example, I'll refer to some PostGIS datasets described on the
following page:
http://geoserver.org/display/GEOSDOC/Tropical+Storm+Tracking+with+PostGIS-GeoServer-GoogleEarth
Consider two datasets: storm tracks (line geometry) and US state boundaries
(polygon geometry). Is there a way to construct a request that returns the
number of storm tracks that intersect state boundaries within a user
specified time?

Within PostGIS, I can construct a prepared statement (see below) that
creates the summary data using two parameters (i.e. start time and end
time). Is there any way to pass these parameters from GeoServer, and
retrieve back the results of the prepared statement? Or is there a better
way to go about doing this?

- Tyler

-- DEALLOCATE p_storm_count;
PREPARE p_storm_count (timestamp, timestamp) AS
  SELECT
    s.state_name,
    count(t.storm_num) as storm_count
  FROM
    v_storm_track as t,
    states as s
  WHERE
    storm_start BETWEEN $1 and $2 AND
    ST_Intersects(t.the_route, s.the_geom)
  GROUP BY
    s.state_name
  ORDER BY
    s.state_name;

EXECUTE p_storm_count('1/1/1995', '10/1/1998');
EXECUTE p_storm_count('1/1/1995', '10/1/2003');
EXECUTE p_storm_count('1/1/2000', '10/1/2005');
--
View this message in context: http://www.nabble.com/how-to-pass-user-parameters-to-PostGIS-queries--tp16967374p16967374.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

Alexander_Petkov · April 29, 2008, 7:01pm

On Tue, Apr 29, 2008 at 12:43 PM, Tyler Erickson <tyler.erickson@anonymised.com> wrote:

Is there any way to pass user parameters through GeoServer that can be used
in a PostGIS query? I am trying to figure out how to retrieve summary data
(calculated by PostGIS) based on some user supplied parameters.

With a WFS request, one can use the OGC filters to specify criteria.
There are examples if you follow the "Demo/Sample Requests" link on
your Geoserver installation.

With a WMS request, I am not sure--although I use the filter parameter
that Andrea implemented:

http://geoserver.org/display/GEOSDOC/WMS+vendor+parameters

Is this still a valid way to use filters with WMS requests in the 1.6
geoserver releases?

Thanks,

Alex

Tyler_Erickson1 · April 29, 2008, 7:25pm

Alex,

To my best understanding, the OGC and CQL filters can only be used to filter records that are returned from a datastore, and cannot be used to supply parameters to SQL aggregate functions (such as COUNT or SUM) within a PostGIS datastore. So while they could be used to return all records between a given start_time and end_time, it is not clear to me how they could be used to return a count of records between a given start_time and end_time.

Tyler

Alexander Petkov wrote:

···

-- 
Tyler A. Erickson, Ph.D.
Research Scientist
Michigan Tech Research Institute
3600 Green Court, Suite 100
Ann Arbor, MI 48105
[tyler.erickson@anonymised.com](mailto:tyler.erickson@anonymised.com)
[www.mtri.org](http://www.mtri.org)
[www.michiganview.org](http://www.michiganview.org)

Alexander_Petkov · April 29, 2008, 7:38pm

On Tue, Apr 29, 2008 at 1:25 PM, Tyler Erickson <tyler.erickson@anonymised.com> wrote:

Alex,

To my best understanding, the OGC and CQL filters can only be used to
filter records that are returned from a datastore, and cannot be used to
supply parameters to SQL aggregate functions (such as COUNT or SUM) within a
PostGIS datastore. So while they could be used to return all records
between a given start_time and end_time, it is not clear to me how they
could be used to return a count of records between a given start_time and
end_time.

- Tyler

Ah, I see... In this case, if your goal is to get the results
displayed on a web page, the first thing that comes to mind is to make
an Ajax call.

Tyler_Erickson1 · April 29, 2008, 7:58pm

One potential client app could be a web page, in which case it seems like the records could be aggregated by the client (using ajax). But I would also like to be able to use Google Earth as a client and be able to specify the start_time and end_time in the URL that is sent to GeoServer from a Google Earth network link.

I’m kind of skeptical about using the client application to aggregate the data, given the size of the datasets we work with (> million records) will likely overwhelm the web browser or Google Earth. I am hoping there is a way to let the database perform the aggregation and reduce the dataset to a more reasonable size, since that is what it is good at doing…

Tyler

Alexander Petkov wrote:

···

-- 
Tyler A. Erickson, Ph.D.
Research Scientist
Michigan Tech Research Institute
3600 Green Court, Suite 100
Ann Arbor, MI 48105
[tyler.erickson@anonymised.com](mailto:tyler.erickson@anonymised.com)
[www.mtri.org](http://www.mtri.org)
[www.michiganview.org](http://www.michiganview.org)

pere_roca · April 30, 2008, 9:04am

hi,
what about passing parameters via WMS to dynamically construct a SLD?
you pass timestamps that are used by postGIS, execute the query, get the
results (count) and insert them in the newly created SLD.

This SLD can then be called from the <Url> tag in the KML (which can also
refer to start_time and end_time in the URL).

Maybe this post helps you
(http://www.nabble.com/Changing-dynamically-SLD-using-php-td12781786.html#a12781786)

hope it helps,
Pere Roca

visit EDIT project!
http://edit.csic.es/edit_geo/prototype/edit_project.html

Tyler Erickson wrote:

Is there any way to pass user parameters through GeoServer that can be
used in a PostGIS query? I am trying to figure out how to retrieve
summary data (calculated by PostGIS) based on some user supplied
parameters.

As a concrete example, I'll refer to some PostGIS datasets described on
the following page:
http://geoserver.org/display/GEOSDOC/Tropical+Storm+Tracking+with+PostGIS-GeoServer-GoogleEarth
Consider two datasets: storm tracks (line geometry) and US state
boundaries (polygon geometry). Is there a way to construct a request that
returns the number of storm tracks that intersect state boundaries within
a user specified time?

Within PostGIS, I can construct a prepared statement (see below) that
creates the summary data using two parameters (i.e. start time and end
time). Is there any way to pass these parameters from GeoServer, and
retrieve back the results of the prepared statement? Or is there a better
way to go about doing this?

- Tyler

-- DEALLOCATE p_storm_count;
PREPARE p_storm_count (timestamp, timestamp) AS
  SELECT
    s.state_name,
    count(t.storm_num) as storm_count
  FROM
    v_storm_track as t,
    states as s
  WHERE
    storm_start BETWEEN $1 and $2 AND
    ST_Intersects(t.the_route, s.the_geom)
  GROUP BY
    s.state_name
  ORDER BY
    s.state_name;

EXECUTE p_storm_count('1/1/1995', '10/1/1998');
EXECUTE p_storm_count('1/1/1995', '10/1/2003');
EXECUTE p_storm_count('1/1/2000', '10/1/2005');

--
View this message in context: http://www.nabble.com/how-to-pass-user-parameters-to-PostGIS-queries--tp16967374p16978504.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

Tyler_Erickson1 · April 30, 2008, 1:17pm

Pere,

That sounds like a promising (and creative) way to incorporate the
user-supplied parameters.
Would you be willing to share your states_filter.php script so that I could
use it as a starting point?

- Tyler

pere roca wrote:

hi,
what about passing parameters via WMS to dynamically construct a SLD?
you pass timestamps that are used by postGIS, execute the query, get the
results (count) and insert them in the newly created SLD.

This SLD can then be called from the <Url> tag in the KML (which can also
refer to start_time and end_time in the URL).

Maybe this post helps you
(http://www.nabble.com/Changing-dynamically-SLD-using-php-td12781786.html#a12781786)

hope it helps,
Pere Roca

visit EDIT project!
http://edit.csic.es/edit_geo/prototype/edit_project.html

--
View this message in context: http://www.nabble.com/how-to-pass-user-parameters-to-PostGIS-queries--tp16967374p16982338.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

pere_roca · April 30, 2008, 1:54pm

Tyler,

below there is a very simple php that constructs a SLD getting the passed
parameters
(http://edit.csic.es/geoserver/wms?bbox=-127.61950064999999,23.7351786,-64.08177035,50.592523400000005&format=application/openlayers&request=GetMap&width=800&height=317&srs=EPSG:4326&sld=http://edit.csic.es/gmap/states_filter.php?state=Florida)
; states parameters can be changed.

For your purpose you need more complex php stuff that can slow the GetMap
response. Maybe you find some interesting code in the SVN of the project
I'm working for: http://dev.e-taxonomy.eu/trac/browser/trunk/geo/mapviewer/
(check lines 127 and more of data_upload.php to retrieve and construct a SLD
using php and XSLT); you can see the webapplication in
http://edit.csic.es/edit_geo/prototype/edit.html

cheers,
(make me know if it fits for you, I also would be interested to do
something similar on the future)

Pere Roca

states_filter.php
<?php
header("Content-type: text/xml");
echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
?>

<NamedLayer>
<Name>topp:states</Name>

<UserStyle>
    
  <Name>population</Name>
  <Title>Population in the United States</Title>
  <Abstract>A sample filter that filters the United States into three
            categories of population, drawn in different colors</Abstract>
    <FeatureTypeStyle>
      <Rule>
        
        <ogc:Filter xmlns:gml="http://www.opengis.net/gml">
         <ogc:And>
            <ogc:PropertyIsLike wildCard="*" singleChar="?" escape="\">

<ogc:PropertyName>STATE_NAME</ogc:PropertyName>
                                <ogc:Literal><?php echo($_GET['state']);
?></ogc:Literal>
             </ogc:PropertyIsLike>

         </ogc:And>
        </ogc:Filter>
        <PolygonSymbolizer>
           <Fill>
              
              <CssParameter name="fill">#FF4D8F</CssParameter>
           </Fill>
        </PolygonSymbolizer>
      </Rule>

</FeatureTypeStyle>
</UserStyle>
</NamedLayer>
</StyledLayerDescriptor>

Tyler Erickson wrote:

Pere,

That sounds like a promising (and creative) way to incorporate the
user-supplied parameters.
Would you be willing to share your states_filter.php script so that I
could use it as a starting point?

- Tyler

pere roca wrote:

hi,
what about passing parameters via WMS to dynamically construct a SLD?
you pass timestamps that are used by postGIS, execute the query, get the
results (count) and insert them in the newly created SLD.

This SLD can then be called from the <Url> tag in the KML (which can also
refer to start_time and end_time in the URL).

Maybe this post helps you
(http://www.nabble.com/Changing-dynamically-SLD-using-php-td12781786.html#a12781786)

hope it helps,
Pere Roca

visit EDIT project!
http://edit.csic.es/edit_geo/prototype/edit_project.html

--
View this message in context: http://www.nabble.com/how-to-pass-user-parameters-to-PostGIS-queries--tp16967374p16983047.html
Sent from the GeoServer - User mailing list archive at Nabble.com.