4

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?

Thanks.

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

2
  • are there any errors in the log file? what is displayed? is anything displayed? How did you set up the view?
    – Ian Turton
    Commented Feb 5, 2013 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
    Commented Feb 5, 2013 at 11:36

0

Browse other questions tagged or ask your own question.