[postgis-users] spatial query (ST_Intersects)

[Birgit Laggner]

Hi Shreyes,

to select only the first row of a group of rows ordered by the values of 
a certain column, I would use DISTINCT ON. For your example table this 
would look like this:

select distinct on (plot_id) plot_id, area from intersects_result order 
by plot_id, area desc;

Hope that helps,

Birgit.



Am 03.05.2011 14:11, schrieb shreyes shiv:
> Dear all,
>
> I have loaded two shape files into postgis database.
> I am using the ST_Intersects(geom of shape file1, geom of shape file2) 
> command to see the features that interesects.
> Now there are redundant entries created in the table.
> I want to pick the unique tuple where the attribute "area" has the 
> maximum value.
> for ex. the following is a outcome of doing the intersects operation 
> on the two tables.
>
> plot_id              area
> --------------------------------
> S01BAT009      23
> S01BAT009      20
> S01BAT009      19
> S01BAA001      10
> S01BAA001      12
> S01BAA001      30
>
> I want to select
>
> plot_id               area
> -------------------------------
> S01BAT009        23
> S01BAA001       30
>
> from the above table.
>
> How do i write the query please help.
>
> thank you
>
> -- 
> shreyes shiv
> email: shivshreyes at gmail.com <mailto:shivshreyes at gmail.com>
> phone: 9557975780
> IIRS(Indian Institute of Remote Sensing)
> No. 4, Kalidas Road, Dehradun-248001, Uttarakhand, India
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110503/9336612f/attachment.html>