[Geoserver-devel] Where are PostGIS Queries built?

Ivo_Brodien · December 15, 2008, 3:54pm

Hello,

is it right, that i I would want to make any changes to how the data from PostGIS is queried, I would have to make in GeoTools?

The thing is, that I would like to query a function with custom parameters which returns a ResultSet. GeoServer only sees GEO-enabled views & tables and I cannot set any parameters.

I would like a view with something similar to this but having x,y,tx and ty dynamically substituted.

---------
CREATE OR REPLACE VIEW myview AS
SELECT planet_osm_roads.name, planet_osm_roads.way, AsSVG(simplify(st_affine(planet_osm_roads.way,x,0,0,y,tx,ty),2),1) AS assvg
FROM planet_osm_roads;
-----------

Searching the web now for a while I figured out, that it is not possible to pass variables to a view. So i figured I would intercept and modify the SELECT query...although it is ugly, I know.

Thanks a lot
Ivo

Justin_Deoliveira · December 15, 2008, 4:01pm

Hi Ivo,

You are correct that GeoServer uses GeoTools for all access to PostGIS. In particular code living in the postgis module:

http://svn.osgeo.org/geotools/trunk/modules/plugin/postgis/

For a while we have been talking about adding support for on the fly queries as feature types... not sure if this falls into your use case. How exactly are x,y,tx,ty generated?

-Justin

Ivo Brodien wrote:

Hello,

is it right, that i I would want to make any changes to how the data from PostGIS is queried, I would have to make in GeoTools?

The thing is, that I would like to query a function with custom parameters which returns a ResultSet. GeoServer only sees GEO-enabled views & tables and I cannot set any parameters.

I would like a view with something similar to this but having x,y,tx and ty dynamically substituted.

---------
CREATE OR REPLACE VIEW myview AS
SELECT planet_osm_roads.name, planet_osm_roads.way, AsSVG(simplify(st_affine(planet_osm_roads.way,x,0,0,y,tx,ty),2),1) AS assvg
FROM planet_osm_roads;
-----------

Searching the web now for a while I figured out, that it is not possible to pass variables to a view. So i figured I would intercept and modify the SELECT query...although it is ugly, I know.

Thanks a lot
Ivo

------------------------------------------------------------------------------
SF.Net email is Sponsored by MIX09, March 18-20, 2009 in Las Vegas, Nevada.
The future of the web can't happen without you. Join us at MIX09 to help
pave the way to the Next Web now. Learn more and register at
http://ad.doubleclick.net/clk;208669438;13503038;i?http://2009.visitmix.com/
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

--
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.

Ivo_Brodien · December 15, 2008, 4:29pm

Hi Justin,

i am always amized how quickly you guys answer…thanks.

On 15.12.2008, at 17:01, Justin Deoliveira wrote:

SELECT planet_osm_roads.name, planet_osm_roads.way, AsSVG(simplify(st_affine(planet_osm_roads.way,x,0,0,y,tx,ty),2),1) AS assvg

For a while we have been talking about adding support for on the fly queries as feature types… not sure if this falls into your use case.

Kind of. It is on-the-fly since the output of column “assvg” is computed on the fly by postgis passing parameters for calculations. How far has gone the talking?

How exactly are x,y,tx,ty generated?

They are computed like this: it is simply a affine transformation (scale and translate) from the lat/lon coords to output screen coords:

double x = screen.getWidth() / mapExtent.getWidth();
double y = screen.getHeight() / mapExtent.getHeight();

double tx = -mapExtent.getMinX() * scaleX;

double ty = (mapExtent.getMinY() * scaleY) + paintArea.getHeight();

The view will also have some WHERE (either hard coded or as CQL) clause for filtering by type of the features. I want to set up a view(layer) for every zoom level so that only the features for that zoom level get returned by the database. Or is that a bad approach?

I mean, all the filtering and calculations could be done in my SVG Renderer in Geoserver but I think it would be much faster within the database.

cheers
ivo

Justin_Deoliveira · December 15, 2008, 5:38pm

Ivo Brodien wrote:

Hi Justin,

i am always amized how quickly you guys answer...thanks.

no problem

On 15.12.2008, at 17:01, Justin Deoliveira wrote:

SELECT planet_osm_roads.name, planet_osm_roads.way, AsSVG(simplify(st_affine(planet_osm_roads.way,x,0,0,y,tx,ty),2),1) AS assvg

For a while we have been talking about adding support for on the fly queries as feature types... not sure if this falls into your use case.

Kind of. It is on-the-fly since the output of column "assvg" is computed on the fly by postgis passing parameters for calculations. How far has gone the talking?

We sort of have a design for it, but nothing implemented as of yet. Its a pretty big change.

How exactly are x,y,tx,ty generated?

They are computed like this: it is simply a affine transformation (scale and translate) from the lat/lon coords to output screen coords:

double x = screen.getWidth() / mapExtent.getWidth();
double y = screen.getHeight() / mapExtent.getHeight();

double tx = -mapExtent.getMinX() * scaleX;

double ty = (mapExtent.getMinY() * scaleY) + paintArea.getHeight();

The view will also have some WHERE (either hard coded or as CQL) clause for filtering by type of the features. I want to set up a view(layer) for every zoom level so that only the features for that zoom level get returned by the database. Or is that a bad approach?

I think its a good approach for good performance. The only other alternative is to try and use SLD to do the scale dependence but that won't be nearly as efficient.

I mean, all the filtering and calculations could be done in my SVG Renderer in Geoserver but I think it would be much faster within the database.

Agreed... but getting GeoServer to serve that out will be tricky...

cheers
ivo

--
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.

Ivo_Brodien · December 16, 2008, 2:48pm

I mean, all the filtering and calculations could be done in my SVG Renderer in Geoserver but I think it would be much faster within the database.

Agreed… but getting GeoServer to serve that out will be tricky…

I am afraid you are right. I saw that Andrea is one of the geotools postig classes devoloper. Maybe he has an idea?

am I right that until

Query bboxQuery = new DefaultQuery(schema.getTypeName(), bboxFilter);
Query definitionQuery = layer.getQuery();
DefaultQuery finalQuery = new DefaultQuery(DataUtilities.mixQueries(definitionQuery, bboxQuery, “svgEncoder”));
finalQuery.setHints(definitionQuery.getHints());
finalQuery.setSortBy(definitionQuery.getSortBy());
finalQuery.setStartIndex(definitionQuery.getStartIndex());

featureReader = fSource.getFeatures(finalQuery).features();

until the last line is executed in my SVGRenderer or in any other Renderer the database is not queried?

Wouldn’t it be useful (i am asking in general) if a PostGIS would somehow also promote functions that have a geometry column, just like

having a geometry_column table you would have a geometry_funcion table which also includes the desired paramters…if any? Just an idea from a total newbie to all this…

Thanks
ivo

Andrea_Aime3 · December 19, 2008, 10:44am

Ivo Brodien ha scritto:

I mean, all the filtering and calculations could be done in my SVG Renderer in Geoserver but I think it would be much faster within the database.

Agreed... but getting GeoServer to serve that out will be tricky...

>

I am afraid you are right. I saw that Andrea is one of the geotools postig classes devoloper. Maybe he has an idea?

am I right that until

Query bboxQuery = new DefaultQuery(schema.getTypeName(), bboxFilter);
Query definitionQuery = layer.getQuery();
DefaultQuery finalQuery = new DefaultQuery(DataUtilities.mixQueries(definitionQuery, bboxQuery, "svgEncoder"));
finalQuery.setHints(definitionQuery.getHints());
finalQuery.setSortBy(definitionQuery.getSortBy());
finalQuery.setStartIndex(definitionQuery.getStartIndex());

featureReader = fSource.getFeatures(finalQuery).features();

until the last line is executed in my SVGRenderer or in any other Renderer the database is not queried?

Yes, you're indeed right, the data access code works in pull mode,
the database is accessed only the last moment, it's the features()
call that actually triggers the query on the db.

Wouldn't it be useful (i am asking in general) if a PostGIS would somehow also promote functions that have a geometry column, just like having a geometry_column table you would have a geometry_funcion table which also includes the desired paramters...if any? Just an idea from a total newbie to all this...

The crux of the problem is passing parameters down to SQL, function
or not. And that is not trivial, as we don't have any place
to put those parameters in the standard Query API, to start with.
We should probably create a subclass of Query, ParametricQuery,
that has the ability to pass down params.
And then also have a way to state a FeatureType is parametric,
in that querying it can require specify parameters (or allows
specify params, and uses default values when not).
And finally have a way to configure the params for such a
feature type on creation, which could be done with a table
like the one you suggest, or in a ton other ways...

So I guess it's doable, but it require a non trivial amount of
changes in core classes. Something that we can think about
for jdbc-ng...
Ivo, maybe you can prototype out a solution for your specific
use case (which as I understand works on top of the old jdbc
classes, and it cannot be elsewhere, since there is no
jdbc-ng version of the postgis datastore)
and then we can work into merging the solutions
or the ideas that come out of it into the new jdbc classes,
so that everybody can get, in due time, benefits from
this work?

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.