PostgreSQL database doesn't seem to work with GeoServer

Question

I've downloaded a project from internet, that is supposed to let me draw some polygons, points and so on on the map, then save it on the PostgreSQL database. You can also upload KML files to show already drawn points,polygons, etc - that doesn't work as well.

The project is using PostGis + GeoServer.

The problem is, I don't know how to enable database in it to save the coordinates.

So far I did: 1)Install PostgreSQL 2)Install PostGis 3)Install GeoServer 4)Install WAMP 5)Create database called 'parking' 6) In the 'parking' I've run SQL queries like this :

-- After creating database
CREATE EXTENSION postgis;

-- CREATE SEQUENCE FOR TABLE parking_spaces
CREATE SEQUENCE public.sq_parking_spaces
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- TABLE parking_spaces
CREATE TABLE public.parking_spaces
(
  id integer NOT NULL DEFAULT nextval('sq_parking_spaces'::regclass),
  name character varying(80),
  paid boolean,
  spaces integer,
  geometry geometry(Polygon,3857),
  CONSTRAINT parking_spaces_pkey PRIMARY KEY (id)
)

-- CREATE SEQUENCE FOR TABLE parking_meters
CREATE SEQUENCE public.sq_parking_meters
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

-- TABLE parking_meter
CREATE TABLE public.parking_meters
(
  id integer NOT NULL DEFAULT nextval('sq_parking_meters'::regclass),
  name character varying(80),
  geometry geometry(Point,3857),
  CONSTRAINT parking_meters_pkey PRIMARY KEY (id)
)

What should be my next goal? How do I check the tables, using PgAdmin?

EDIT:

The question is how to properly connect PostgreSQL database to GeoServer? And how to give GeoServer full write access to layers?

Are you able to run a PHP code yet? Please check http://www.homeandlearn.co.uk/php/php1p3.html and http://stackoverflow.com/questions/14621181/integration-of-postgresql-on-wamp — Dhruv Saxena, Jan 16 '17 at 20:39
Hello, yes I'm able to do that. I access it via localhost/GEO (its the folder in /www) and I can already draw on the map, but after I reload it, everything is gone, and I think that the coordinates were not being saved into database. — odinas1212, Jan 16 '17 at 20:46
Ok, thanks for confirming. You mentioned that the code was downloaded from somewhere. Has it been configured according to the credentials of your DB user's access? It's hard to guess but, if not already configured, then it could be in the "config" folder or a file of the similar name in your project? — Dhruv Saxena, Jan 16 '17 at 20:55
Hmm, I can't seem to find database credentials anywhere in the code. The project has "database" folder with database.sql file, "php" folder with kml.php file ( I think that it is used for uploading KML files ), "css" folder with .css files, and "js" folder with jquery,openlayer and main script ( in that main script I couldn't find any refference to database, but the code is complicated and I might have missed it somehow). Do you want any of these files uploaded? — odinas1212, Jan 16 '17 at 21:06
Do you find any configuration instructions on the source you downloaded the code from? Is there a README document with the code package? Your application needs to know where it resides and what credentials to use to be able to access the DB. Also, please do try inserting the data manually in the tables and run `SELECT` queries to make sure your DB is actually working? Are you able to use phpPgAdmin? If not, please see this answer from the second link in the first comment: http://stackoverflow.com/a/18804253/2298301 — Dhruv Saxena, Jan 16 '17 at 21:23
I think I started to understand how it should work. Firstly I draw polygons/points etc, then I use upload function and select kml.php file. This generates another (edited) .kml files. There are database credentials in it. I will update my question and include that kml.php file, so you can see for yourself. I'm updating my question now so you can see it. — odinas1212, Jan 16 '17 at 21:36
Thanks for the additional info. It seems like "kml.php" is called in background via AJAX? Or, if that is not happening, then one of the form submit event must transfer your code to kml.php file - in the foreground. If it's called via AJAX, you can verify the results by checking the NETWORK tab in your browser's console and see if there's any error diagnosis there. If it is invoked in the foreground, then the errors should be spotted easily. Except, if there's an error with `exec()`... — Dhruv Saxena, Jan 16 '17 at 22:14
I'd suggest that you step debug the code by putting a few `echo` / `die` statements in between to ensure that the execution is falling through as you'd expect; find out what is contained within `$name` variable; see if that file exists in your upload folder; and run the command contained within `exec()` directly at the Command Line by navigating to **FWTools2.4.7\bin\ folder**. If it works well at the command line, then you could possibly be certain that the problem is with the code. Also please have a look at this to try and debug `exec()` using PHP: http://stackoverflow.com/q/11875820. — Dhruv Saxena, Jan 16 '17 at 22:17
To be fair, I didn't understand almost anything that you've said... If you have time, maybe you could come here https://tlk.io/help112 - it is a simple web chat APP, where we can chat in real time, no need to register, only enter your username. After we find the solution, I will post it here. — odinas1212, Jan 16 '17 at 22:26
Sorry, I guess I overlooked the last line of the code: `header()`. So, it must most likely be getting executed in the foreground. Just comment out that line and you should be able to "stay" on **kml.php** once taken there by some other program (i.e. after form submit). Please do continue to debug then to narrow down on where the problem is. — Dhruv Saxena, Jan 16 '17 at 22:32

score 1 · Accepted Answer · edited Apr 13 '17 at 12:33

In continuation to the links shared above, here are the generic steps to ensure that the configuration works well:

Make sure that WAMP is installed successfully and is working.
PostgreSQL is installed successfully and that you're able to run the queries using an admin interface.
Create a new user (who doesn't necessarily have to be a superuser): https://www.postgresql.org/docs/9.1/static/app-createuser.html
GRANTpermissions for SELECT, INSERT, UPDATE and DELETE for this new user on your database: https://www.postgresql.org/docs/9.0/static/sql-grant.html
In the context of this particular problem, add Service Level Security for the application: http://docs.geoserver.org/stable/en/user/security/service.html
Ensure that you have the write access for the Layers. In loose terms, there will have to be one layer in Geoservices per table in the DB: http://docs.geoserver.org/stable/en/user/security/layer.html
Finally, when attempting to invoke WFS calls to the Services, the parameters in your jQuery must be set as described at the following link: https://gis.stackexchange.com/questions/21251/how-to-initialize-a-wfs-layer

Hope that helps.

score 1 · Answer 2 · answered Jan 17 '17 at 08:42

1

From your commands above it doesn't appear as if you have added the geometry column to the geometry_columns table - use the AddGeometryColumn statement to do this.

The next thing to try is to work through the GeoServer tutorial on PostGIS.

answered Jan 17 '17 at 08:42

Ian Turton

10,018
1
28
47

PostgreSQL database doesn't seem to work with GeoServer

2 Answers2