[Geoserver-users] join

GeoServer GeoServer User
1

Hi,

Do you know if it is possible to express a layer as a SQL expression with Geoserver as in Mapserver like :

DATA " the_geom from (SELECT .... "

or have we to actually build the resulting table in the database ?

Laurent

2

laurent pierrre ha scritto:

Hi,

Do you know if it is possible to express a layer as a SQL expression with Geoserver as in Mapserver like :

DATA " the_geom from (SELECT .... "

or have we to actually build the resulting table in the database ?

It's not possible to do that in GeoServer, no, but you do not
have to build the table either. The GeoServer way is that you
create a view in your database that represents that query,
and then register the view in the spatial metadata tables
of your database (e.g., for postgis, in the "geometry_columns"
table) so that GeoServer knows what's the geometry column, the
SRS and the geometry type.

Hope this helps
Cheers
Andrea

3

Thanks Andrea,

And if I have several geometry columns in a table (or a view) ? Is there a way to tell geoserver to use a specific one ?

Regards

Laurent
Le 15 avr. 08 à 10:43, Andrea Aime a écrit :

laurent pierrre ha scritto:

Hi,

Do you know if it is possible to express a layer as a SQL expression
with Geoserver as in Mapserver like :

DATA " the_geom from (SELECT .... "

or have we to actually build the resulting table in the database ?

It's not possible to do that in GeoServer, no, but you do not
have to build the table either. The GeoServer way is that you
create a view in your database that represents that query,
and then register the view in the spatial metadata tables
of your database (e.g., for postgis, in the "geometry_columns"
table) so that GeoServer knows what's the geometry column, the
SRS and the geometry type.

Hope this helps
Cheers
Andrea

-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
Don't miss this year's exciting event. There's still time to save $100.
Use priority code J8TL2D2.
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

4

laurent pierrre ha scritto:

Thanks Andrea,

And if I have several geometry columns in a table (or a view) ? Is there a way to tell geoserver to use a specific one ?

First of all, you'll have to register them all in the metadata tables.
Besides that, GeoServer will use by default the first one it finds in the view columns, but with SLD you can tell GeoServer to use whatever
other column you like.

Cheers
Andrea

5

Hi,

i'am evaluation geoserver and run into the same Problems.
I've two tables in Database:
1. Table user_feature
containing userid and featureid
userid: about 500 different ids
featureid: about 250000 ids
Rows at all, about 4million (4.000.000)

2. Table feature
contains about 250000 features.
With id, name, some textattributes (about 300 Columns) and a geometry_column
which represent a point in EPSG:31466 Projection

So when a user logs into the system, we want to present just the features,
which are assigned to him by quering the userid_featureid table.

To do this we created a view in the database which joins the
userid_featureid table with the feature table.
We registered this view in the geometry_columns table.

Now we can use this view with the Geoserver.
So we created a new FeatureType, based on this View.

And now the Problems begins.
Its not possible to retrieve the Bounds/Extends fo the geometry, because
without a limitation to the user_id this view would have 4 millions rows.

Wen we request this Feature from a WMS Client, we send an OGC Filter for
this userid, but this is also very very slow. And if we start the geoserver,
he tries to retrieve feature Metadata from all features. Also from this view
which end in 100% CPU Usage of the Database Server for a long long time.

We defined much indexes for the Columns . Using this Table combination in
other applications runs acceptable fast.

Is there a Strategy, how to resolve this problem ?

What would be the best way, for maximum performance, to visualize that much
features based on the user which is currently logged in?

Hope someone can help me.

aaime wrote:

laurent pierrre ha scritto:

Thanks Andrea,

And if I have several geometry columns in a table (or a view) ? Is there
a way to tell geoserver to use a specific one ?

First of all, you'll have to register them all in the metadata tables.
Besides that, GeoServer will use by default the first one it finds in
the view columns, but with SLD you can tell GeoServer to use whatever
other column you like.

Cheers
Andrea

-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
Don't miss this year's exciting event. There's still time to save $100.
Use priority code J8TL2D2.
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

--
View this message in context: http://www.nabble.com/join-tp16697691p16741928.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

6

Alexander Bruch ha scritto:

Hi,

i'am evaluation geoserver and run into the same Problems.
I've two tables in Database:
1. Table user_feature containing userid and featureid
userid: about 500 different ids
featureid: about 250000 ids
Rows at all, about 4million (4.000.000)

2. Table feature
contains about 250000 features.
With id, name, some textattributes (about 300 Columns) and a geometry_column
which represent a point in EPSG:31466 Projection

So when a user logs into the system, we want to present just the features,
which are assigned to him by quering the userid_featureid table.

To do this we created a view in the database which joins the
userid_featureid table with the feature table.
We registered this view in the geometry_columns table.

Now we can use this view with the Geoserver.
So we created a new FeatureType, based on this View.

And now the Problems begins.
Its not possible to retrieve the Bounds/Extends fo the geometry, because
without a limitation to the user_id this view would have 4 millions rows.

I can extract the bounds for tables of 20 millions... it's just a matter
or waiting. Press "compute bbox", wait as needed, and finally submit
the results. GeoServer won't try to compute the native bbox any longer.

Cheers
Andrea