FE tool - investigate if a postgres view will allow control over rendering joined layers in GeoServer

[Ben Wyss]

Small issues needs to be resolved: if we are doing all these joins between layers that are referencing the geometry from the trace layer, then we will not have any control over rendering the layer... like the line type and color etc.

A posible solution to this could be to create a postgres view for the fault section and fault layer. The view should include all atributes of the fault_section layer, and also include the geometry column of the trace layer.

A proof of concept need to be done to see if this will resolve the issue.

[Ben Wyss (bmwyss)]

I emailed the GeoServer mailing list about this:

A SQL view would probably be the easiest way to accomplish this. I mean a view created using Postgres' "CREATE VIEW" command, not a "SQL View" as documented in http://docs.geoserver.org/stable/en/user/data/sqlview.html . The SQL view within GeoServer supports the same use case but is really intended for more dynamic usage, and takes a little care to use securely.

If you go the Postgres route, make sure to add an entry for your view to the geometry_columns table. The Populate_Geometry_Columns() management function in recent Postgis versions should help with that: http://postgis.refractions.net/documentation/manual-1.5/Populate_Geometry_Columns.html

--
David Winslow
OpenGeo - http://opengeo.org/

On Wed, Oct 12, 2011 at 4:52 AM, Ben <email address hidden> wrote:

I have a PostGIS database that has two tables. The first table contains geometry, and the second table is connected to the first with a many to many relationship. I would like to expose the second table to GeoServer, and I am wondering what the best way to do this is? I was thinking of creating a view that contains the geometry of table one, and the attributes of table two. Is this the best solution or are there other ways to do this?
Thanks in advance, Ben
------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure contains a
definitive record of customers, application performance, security
threats, fraudulent activity and more. Splunk takes this data and makes
sense of it. Business sense. IT sense. Common sense.
http://p.sf.net/sfu/splunk-d2d-oct
_______________________________________________
Geoserver-users mailing list
<email address hidden>
https://lists.sourceforge.net/lists/listinfo/geoserver-users

So it seems we are on the right track

[Ben Wyss (bmwyss)]

I have successfully added a postgres sql view to the geoserver-dev DB. I think was able to register this layer with GeoServer and it behaves exactly as we need.

I will now add a bug for the additional views that are needed in the tool