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.
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