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?


2 Answers 2


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

  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.

  • That's great sir @Devdatta Tengshe, thanks to you. but there's one last thing I forgot. I'm using the ODK Aggregate in gathering my data. The column names in my database are all in uppercase and that's what really the aggregate generate as default. Now, everytime I query my data in postgresql, it won't work. I changed the letters in lowercase and it works. but if there's a new data that i'm going to upload, it automatically creates another table. The newly uploaded data generates the table and set their names in uppercase including the column...
    – dionde
    Nov 13, 2013 at 7:35
  • If the columns are in caps, use double inverted Commas around the names. For example use: "Longitude" in the SQL statement. Always check the SQL in pgAdmin before using it in GeoServer. Nov 13, 2013 at 9:20
  • I am trying to do exactly the same using an oracle database but I fail to create the layer and display it in layer preview of geoserver.
    – user1919
    Dec 16, 2015 at 19:24
  • @dkar: Do the OGC functions work in your oracle? It might be best to post your issue as a new question, with all relevant details. Dec 17, 2015 at 7:25
  • @Devdatta Tengshe Yes. They work. I have posted my issue here: gis.stackexchange.com/questions/174178/…
    – user1919
    Dec 17, 2015 at 8:33

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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