DISTINCT geometry to a code

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

DISTINCT geometry to a code

juli g. pausas
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


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: DISTINCT geometry to a code

Lars Aksel Opsahl-2

Hi


You can do something like this, but this uses a extra help table so it's not the best solution.


create table geo_key_map as ( select distinct md5(ST_asBinary(geo)) from test_table);

SELECT 8144241


Then you get a table where you add serial column and get counter from 1 to 8144241. 


If you want you can wrap the code in to a function returning id based on md5 or insert a new md5 if it's missing. Using a own function will slow down response, but the md5 function is very fast.


The original table in this case contained 8144267 rows.


Lars



Fra: postgis-users <[hidden email]> på vegne av juli g. pausas <[hidden email]>
Sendt: 15. mars 2017 15:49
Til: PostGIS Users Discussion
Emne: [postgis-users] DISTINCT geometry to a code
 
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


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: DISTINCT geometry to a code

Nicolas Ribot-2
In reply to this post by juli g. pausas
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 <[hidden email]> 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


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: DISTINCT geometry to a code

juli g. pausas
Thanks Lars and Nicolas,
The windows option is simple and do exactly what I wanted. thanks.

Juli
--
CIDE, CSIC  |  www.uv.es/jgpausas  |  blog


On Wed, Mar 15, 2017 at 5:42 PM, Nicolas Ribot <[hidden email]> wrote:
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 <[hidden email]> 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


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Loading...