The postgis table I have has the following columns,

id | name | style_id | unique_id | time_stamp | the_geom(polygon)

and logically the unique constraint (not currently imposed in the db) lies with unique_id+time_stamp.

Sample data:

1 |  comp one   | 1 | comp1_u | 2012-11-02 11:00 | <comp1_geom>
2 |  comp two   | 2 | comp2_u | 2012-12-01 12:00 | <comp2_geom>
3 |  comp one 2 | 1 | comp1_u | 2012-12-01 12:12 | <comp1_geom>
4 |  comp three | 3 | comp3_u | 2012-12-01 12:00 | <comp3_geom>
5 |  comp two 2 | 2 | comp2_u | 2012-03-12 01:34 | <comp2_geom>
6 |  comp four  | 4 | comp4_u | 2012-12-01 12:00 | <comp4_geom>
7 |  comp five  | 5 | comp5_u | 2012-12-01 12:00 | <comp5_geom>

My objective is to create a GeoServer SQL View with only distinct elements based on unique_id from the table order by time_stamp. ie., I want my Geoserver layer to show only the latest distinct elements.

I tried this:

SELECT DISTINCT on (unique_id) * from my_table order by unique_id,time_stamp desc

Though this command runs fine in the psql client, it doesn't seem to go well with the style specified for the WMS layer.

The style basically colors each element based on the style_id.

When the layer is displayed, neither comp1_u and comp2_u elements are styled properly nor can I view their info on click in the layer preview window.

Am I using the right query for the view or is there any workaround to achieve the above?


PS. Setup is: Postgres 9.1 with PostGIS 1.5 and GeoServer 2.2

closed as too localized by Ian Turton, whuber Feb 5 '13 at 13:10

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center. If this question can be reworded to fit the rules in the help center, please edit the question.

  • are there any errors in the log file? what is displayed? is anything displayed? How did you set up the view? – Ian Turton Feb 5 '13 at 8:44
  • @iant I just found out the source of the problem. It was a mismatched sql statement in my code that nullified the geom column for those entries whenever a certain function was executed. I suspected this since there wasn't anything wrong in the logs and also only those two elements disappeared every time. – Siva Feb 5 '13 at 11:36