[Geoserver-devel] Please Help - GeoServer - PostGIS issue

Ganesh_Jothikumar · December 1, 2007, 8:01am

Hi All

I am using geoserver with PostGis. I have created a datasource for PostGIS and now an trying to create feature types using the GeoServer struts application.

I have two tables one which has a geometry column (the column is added to geometry_columns meta-table in PostGIS) and the other which doesn’t have. Now I have created a view thet joins these both tables and also selects the geometry column from teh first table. Now if I try to create a feature type for a view that is base on the first table only then there is no problem. But if I try to create a feature type for teh view then I get an error saying that the geometry column is not present in geometry_columns for the view. But the view is based on teh table andthe geomtry column is present in geometry_coulmns for the table. So idealy the view also needn’t have another row in geoetry_columns. Additionaly PostGIS doesn’t allow to add geometry columns into geometry_columns for view columns.

The SQL for the view based on only teh first table and which works without any problem is present below

CREATE VIEW DEVICELOCATIONVIEW AS SELECT DEVICENAME as “DeviceName”, DEVICEIPADDR as “IPAddress”, MODE as “Mode”, CONFIGURATIONFLAG as “Configured”,FLOORID,“GEOM_LOCATION” from DEVICE;

The SQL for the view which joins both tables and which is doesn’t work is present below

CREATE VIEW DEVICELOCATIONVIEW AS SELECT DEVICENAME as “DeviceName”, DEVICEIPADDR as “IPAddress”, MODE as “Mode”, CONFIGURATIONFLAG as “Configured”, floor.FLOORNAME as “FloorName”,“GEOM_LOCATION” from DEVICE dev,FLOOR floor where dev.FLOORID = floor.FLOORID;

When trying to create the feature type for the above view the following is the exception in GeoServer

40234047 [WARNING] org.geotools.data.jdbc.JDBC1DataStore - Error building attriute type. The column will be ignored
org.geotools.data.DataSourceException: no geometry found in the GEOMETRY_COLUMS table for devicelocationview of the postgis install. A row for GEOM_LOCATION is required for geotools to work correctly
at org.geotools.data.postgis.PostgisDataStore.getGeometryAttribute(PostisDataStore.java:1082)
at org.geotools.data.postgis.PostgisDataStore.buildAttributeType(PostgiDataStore.java:961)
at org.geotools.data.jdbc.JDBC1DataStore.buildSchema(JDBC1DataStore.jav:1202)
at org.geotools.data.postgis.PostgisDataStore.buildSchema(PostgisDataStre.java:657)
at org.geotools.data.jdbc.FeatureTypeHandler.getFeatureTypeInfo(FeatureypeHandler.java:197)
at org.geotools.data.jdbc.FeatureTypeHandler.getSchema(FeatureTypeHandlr.java:165)
at org.geotools.data.jdbc.JDBC1DataStore.getSchema(JDBC1DataStore.java:65)
at org.geotools.data.postgis.PostgisDataStore.getSchema(PostgisDataStor.java:1902)
at org.vfny.geoserver.action.data.TypesEditorAction.executeBBox(TypesEdtorAction.java:204)
at org.vfny.geoserver.action.data.TypesEditorAction.execute(TypesEditorction.java:121)
at org.vfny.geoserver.action.ConfigAction.execute(ConfigAction.java:102
at org.apache.struts.action.RequestProcessor.processActionPerform(RequetProcessor.java:431)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.jva:236)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:116)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:44)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServleHandler.java:1050)
at org.vfny.geoserver.filters.SetCharacterEncodingFilter.doFilter(SetChracterEncodingFilter.java:103)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServleHandler.java:1041)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:54)
at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:26)
at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:27)
at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHanlerCollection.java:149)
at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollectionjava:123)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:41)
at org.mortbay.jetty.Server.handle(Server.java:269)
at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:40)
at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnectin.java:701)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:617)
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:199)
at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:339)
at org.mortbay.jetty.nio.HttpChannelEndPoint.run(HttpChannelEndPoint.jaa:270)
at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPoo.java:475)
40234047 [SEVERE] org.geotools.data.jdbc.JDBC1DataStore - Error Performing SQL query: SELECT FROM “public”.“devicelocationview” WHERE TRUE
org.postgresql.util.PSQLException: ERROR: syntax error at or near “FROM”
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryEecutorImpl.java:1548)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutoImpl.java:1316)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.jva:191)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Staement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstracJdbc2Statement.java:337)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdb2Statement.java:236)
at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccesorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHanler.invoke(AbstractJdbc23PooledConnection.java:471)
at $Proxy1.executeQuery(Unknown Source)
at org.geotools.data.jdbc.JDBC1DataStore.executeQuery(JDBC1DataStore.jaa:926)
at org.geotools.data.jdbc.JDBC1DataStore.getFeatureReader(JDBC1DataStor.java:669)
at org.geotools.data.DefaultFeatureResults.boundsReader(DefaultFeatureRsults.java:177)
at org.geotools.data.DefaultFeatureResults.getBounds(DefaultFeatureResuts.java:220)
at org.geoserver.feature.FeatureSourceUtils.getBoundingBoxEnvelope(FeatreSourceUtils.java:40)
at org.vfny.geoserver.util.DataStoreUtils.getBoundingBoxEnvelope(DataStreUtils.java:253)
at org.vfny.geoserver.action.data.TypesEditorAction.executeBBox(TypesEdtorAction.java:211)
at org.vfny.geoserver.action.data.TypesEditorAction.execute(TypesEditorction.java:121)
at org.vfny.geoserver.action.ConfigAction.execute(ConfigAction.java:102
at org.apache.struts.action.RequestProcessor.processActionPerform(RequetProcessor.java:431)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.jva:236)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:116)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:44)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServleHandler.java:1050)
at org.vfny.geoserver.filters.SetCharacterEncodingFilter.doFilter(SetChracterEncodingFilter.java:103)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServleHandler.java:1041)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:54)
at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:26)
at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:27)
at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHanlerCollection.java:149)
at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollectionjava:123)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:41)
at org.mortbay.jetty.Server.handle(Server.java:269)
at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:40)
at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnectin.java:701)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:617)
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:199)
at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:339)
at org.mortbay.jetty.nio.HttpChannelEndPoint.run(HttpChannelEndPoint.jaa:270)
at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPoo.java:475)

Can somebody help us out of this issue. It is really stopping us from proceeding

Thanks
ganesh

Save all your chat conversations. Find them online.

Andrea_Aime3 · December 1, 2007, 9:10am

Ganesh Jothikumar ha scritto:

Hi All
I am using geoserver with PostGis. I have created a datasource for PostGIS and now an trying to create feature types using the GeoServer struts application.

Nice. Why are you making usage questions on the devel list? The devel
list is for development related topics, if you want to make questions
on how to use geoserver, please use the users list instead. Every developer is on both mailing lists too, so here you won't reach more people.

I have two tables one which has a geometry column (the column is added to geometry_columns meta-table in PostGIS) and the other which doesn't have. Now I have created a view thet joins these both tables and also selects the geometry column from teh first table. Now if I try to create a feature type for a view that is base on the first table only then there is no problem. But if I try to create a feature type for teh view then I get an error saying that the geometry column is not present in geometry_columns for the view. But the view is based on teh table andthe geomtry column is present in geometry_coulmns for the table. So idealy the view also needn't have another row in geoetry_columns.

Either you know an easy way to do this that I don't know, or you've
never written a parser. The only way I can think of to do what you're
asking is to grab the sql that defines the view and parse it. Not
too easy, very likely to get wrong with complex queries. If you have
a patch for the postgis datastore that works for every conceivable
sql query defining a view please share it with us.

Additionaly PostGIS doesn't allow to add geometry columns into geometry_columns for view columns.

No, you can do it by a manual insert into geometry_columns. That's
what everybody else is doing.
Hope this helps.
Cheers
Andrea