Cannot configure Postgres PostGIS database with GeoServer

Question

I made a table in a Postgres database named image_id and a geometry column. Here's what the circuit looks like:

CREATE TABLE images ( "name" character varying(256) NOT NULL, image_id integer NOT NULL, srid integer NOT NULL DEFAULT 4326, images_geom geometry, CONSTRAINT images_pkey PRIMARY KEY (name), CONSTRAINT enforce_dims_images_geom CHECK (st_ndims(images_geom) = 2), CONSTRAINT enforce_srid_images_geom CHECK (st_srid(images_geom) = 4326) ) WITH ( OIDS=FALSE ); ALTER TABLE images OWNER TO postgres; ALTER TABLE images ALTER COLUMN images_geom SET STATISTICS 4326; 

I used this SQL statement to insert a test record into a table:

 insert into images (name, image_id, srid, images_geom) values ('image1.png', 1, 4326, ST_GeomFromText('point(21.12312 -125.01010)', 4326)); 

Then in GeoServer I created a new PostGIS store and connected it to the database that I created in Postgres. When I try to create a new layer and connect it to a new repository, I created its errors and displays this error message:

 java.lang.RuntimeException: Error occurred while building the resources for the configuration page at org.geoserver.web.data.layer.NewLayerPage.buildLayerInfo(NewLayerPage.java:202) at org.geoserver.web.data.layer.NewLayerPage$3.onClick(NewLayerPage.java:173) at org.geoserver.web.wicket.SimpleAjaxLink$1.onClick(SimpleAjaxLink.java:44) at org.apache.wicket.ajax.markup.html.AjaxLink$1.onEvent(AjaxLink.java:65) at org.apache.wicket.ajax.AjaxEventBehavior.respond(AjaxEventBehavior.java:163) at org.apache.wicket.ajax.AbstractDefaultAjaxBehavior.onRequest(AbstractDefaultAjaxBehavior.java:297) at org.apache.wicket.request.target.component.listener.BehaviorRequestTarget.processEvents(BehaviorRequestTarget.java:104) at org.apache.wicket.request.AbstractRequestCycleProcessor.processEvents(AbstractRequestCycleProcessor.java:91) at org.apache.wicket.RequestCycle.processEventsAndRespond(RequestCycle.java:1239) at org.apache.wicket.RequestCycle.step(RequestCycle.java:1316) at org.apache.wicket.RequestCycle.steps(RequestCycle.java:1418) at org.apache.wicket.RequestCycle.request(RequestCycle.java:532) at org.apache.wicket.protocol.http.WicketFilter.doGet(WicketFilter.java:356) at org.apache.wicket.protocol.http.WicketServlet.doGet(WicketServlet.java:124) at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at org.springframework.web.servlet.mvc.ServletWrappingController.handleRequestInternal(ServletWrappingController.java:158) at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153) at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:809) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:501) at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1093) at org.geoserver.filters.ThreadLocalsCleanupFilter.doFilter(ThreadLocalsCleanupFilter.java:23) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084) at org.vfny.geoserver.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:265) at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107) at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275) at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:124) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275) at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:125) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275) at org.acegisecurity.ui.rememberme.RememberMeProcessingFilter.doFilter(RememberMeProcessingFilter.java:142) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275) at org.acegisecurity.wrapper.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:81) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275) at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:271) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275) at org.acegisecurity.ui.logout.LogoutFilter.doFilter(LogoutFilter.java:110) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275) at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:249) at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275) at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:149) at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084) at org.geoserver.filters.LoggingFilter.doFilter(LoggingFilter.java:73) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084) at org.geoserver.filters.ReverseProxyFilter.doFilter(ReverseProxyFilter.java:183) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084) at org.geoserver.filters.GZIPFilter.doFilter(GZIPFilter.java:41) at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084) at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360) at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216) at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181) at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:726) at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405) at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:206) at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114) at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152) at org.mortbay.jetty.Server.handle(Server.java:324) at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505) at org.mortbay.jetty.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:828) at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:514) at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211) at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380) at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395) at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:450) Caused by: java.io.IOException at org.geotools.jdbc.JDBCFeatureSource.buildFeatureType(JDBCFeatureSource.java:340) at org.geotools.jdbc.JDBCDataStore.createFeatureSource(JDBCDataStore.java:712) at org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:383) at org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:348) at org.geotools.data.store.ContentDataStore.getFeatureSource(ContentDataStore.java:647) at org.geoserver.catalog.CatalogBuilder.buildFeatureType(CatalogBuilder.java:324) at org.geoserver.web.data.layer.NewLayerPage.buildLayerInfo(NewLayerPage.java:198) ... 73 more Caused by: org.postgresql.util.PSQLException: ERROR: relation "geometry_columns" does not exist Position: 18 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:252) at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208) at org.geotools.data.postgis.PostGISDialect.getMapping(PostGISDialect.java:225) at org.geotools.jdbc.JDBCFeatureSource.buildFeatureType(JDBCFeatureSource.java:258) ... 79 more 

Am I doing something wrong in my setup steps?

Answer 1

Check if there is a table named geometry_columns .

If not, PostGIS installation may not be successful.

Oh, and if you have several databases on your host, make sure you create your table in a database where you have access to your PostGIS materials.

Answer 2

I had similar problems when adding PostGIS to an existing PostgreSQL table. It turns out that the owner of the geometry_columns and space_ref_sys tables was postgres instead of my username for existing postgresql tables (that is, user1).

I registered as "postgres" and executed "ALTER TABLE tablename OWNER TO user1; et voila - problem solved.

Answer 3

I did not use the installer, but you need to include geometry types for each database that you want to use with PostGIS. Instructions are here . Summary: enable plpgsql in the database, then transfer these two files to some variations of these two paths:

/opt/local/share/postgresql84/contrib/postgis-2.0/postgis.sql

then:

/opt/local/share/postgresql84/contrib/postgis-1.5/spatial_ref_sys.sql

These sql files will create 2-3 tables depending on the version of PostGIS, including geometry_columns . That is: psql -d my_geo_db -f /opt/whatever/postgis.sql , the same with spatial_ref_sys.sql . For some reason, it is never easy to find postgis.sql or spatial_ref_sys.sql . There is no shame in using a spotlight.

It is wise to configure the template to avoid repeating these steps with later databases, see here . It may also be that the installer created such a database, try \l from the psql prompt and see if there is anything called template_postgis . If so, you can create new databases (as custom postgres) createdb -T template_postgis -O your_username new_postgis_db_name

Answer 4

Make sure you use the following SQL statement:

 CREATE EXTENSION postgis; 

This will create the following views in the yout database:

 geography_columns geometry_columns raster_columns raster_overviews