0

(Several days ago a user on gis stackexchange suggest that I should use OpenGeo (I was using geojson before). I succeed at what I was doing at that time, but now I have a different problem.)

I was using MySQL, but for easier work with GeoServer I decidec to migrate to PostgreSQL. I spend a few days to migrate a PHP project.

I have a table events with fields: id(integer), name (varchar), ... , and coords(point). I am trying to display some point/icon/whatever on layer, where the event is taking place. I went through different tutorials on OpenGeo, GeoServer, Google ... For now I manage to create a SQL layer with SQL statement: SELECT id, coords FROM realestate WHERE coords IS NOT NULL.

But when I go to LayerPreview and click OpenLayers->Go, the browser download file (with wms filename) with content

?xml version="1.0" encoding="UTF-8" standalone="no"?>!DOCTYPE ServiceExceptionReport SYSTEM "http://localhost.just_for_validation:8080/geoserver/schemas/wms/1.1.1/WMS_exception_1_1_1.dtd"> ServiceExceptionReport version="1.1.1" > ServiceException> /ServiceException>/ServiceExceptionReport>

(I had to remove all < characters.)

enter image description here

I am not sure if I took the right approach. To sum up: I have PostgreSQL table with several fields, one of them is coords(point). I want to display icon/dot/image/something on map with OpenLayers through GeoServer WMS. I don't know how to create layer which draw dot on lat,lon based on coords field in PostgreSQL.

3

Your problem is that you have added your data using Postgres data type Point, http://www.postgresql.org/docs/9.3/static/datatype-geometric.html

Rather than the PostGIS geometry, or Geography type http://postgis.net/docs/reference.html#PostGIS_Types

GeoServer has a PostGIS Data Store connector, rather than a Postgres Data Store connector.

Good Luck

1

As noted by Stoner, you are using Postgres point data type instead of PostGIS geometry(point) and geoserver doesn't work with Postgres point data type.

If you are using PostGIS 2.1, a new feature introduced is casting from Postgres Type to PostGIS type.

You should be able to do this with the command

ALTER TABLE realestate 
  ALTER COLUMN coords TYPE geometry(point,4326) USING ST_SetSRID(geometry(coords),4326);

Here I am assuming your data is in long lat. You may need to flip it if the coordinates are wrong order. with ST_FlipCoordinates(ST_SetSRID(geometry(coords), 4326) ) .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.