[GIS] getting data into geoserver from two columns to form a point

geoserveropenlayers-2postgresql

I have a problem on how I am able to retrieve a geometry data from postgres using geoserver. I am new to this. The latitude and longitude are on separate columns. So I'm trying to figure this out by concatenating this two columns at the same time converting their format with this SQL view like this.

SELECT *, ST_AsEWKB(ST_GeomFromText('POINT ('||latitude||' '||longitude||')',4326)) as geom FROM mytable

where latitude and longitude are columns in my database.

Now my the problem is that I can't see the points. Is there another way to make this solve?

Best Answer

I'm assuming that you have a table defined something like this:

CREATE TABLE points
(
  gid numeric,
  latitude numeric,
  longitude numeric,
  name character varying
)

What I would do, is to use the following query, for the GeoServer's Create new SQL View functionailty.

select gid, name,  
ST_SETSRID(ST_POINT(longitude, latitude), 4326) as the_geom 
from points;

Using this, you can then publish the table as a layer, and then view the WMS in your preferd client.

Related Question