[postgis-users] DISTINCT geometry to a code

[Nicolas Ribot]

Hi,

You could use a windows function to generate ids for the points:

SELECT  geom, row_number() over () as code
FROM juli.savannaswdsp
group by geom;

Nicolas

On 15 March 2017 at 15:49, juli g. pausas <juli.g.pausas at uv.es> wrote:

> Hi
> I have a table with 945361 rows but the geometry (points) are distinct for
> 201606 locations (i.e., some rows refer to the same point in the space).
>
> SELECT count(*) FROM juli.savannaswdsp;                           -- 945361
> SELECT  count(DISTINCT geom) FROM juli.savannaswdsp;   -- 201606
>
> Would it be possible to generate a column with codes for the points? that
> is, like an ID for each point instead for each row (e.g., from 1 to
> 201606), so rows with the same geom have the same code.
>
> [ In R language this would be as follows: as.numeric(as.factor(geom))  ]
>
> Thank for any suggestion
>
> Juli
> --
> *CIDE, CSIC*  |  www.uv.es/jgpausas  |  blog
> <http://jgpausas.blogs.uv.es/>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170315/bc758888/attachment.html>