getting data into geoserver from two columns to form a point

Question

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?

Answer 1

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.

Answer 2

The points are "binary", which makes sense because that is what you asked for (Extended Well Known Binary) so you won't be able to view them directly.

However you can turn this back into text by something like:

SELECT *, ST_AsEWKT(geom) FROM mytable;

See http://postgis.net/docs/manual-2.0/ST_AsEWKT.html for more details