[postgis-users] DISTINCT geometry to a code

[Lars Aksel Opsahl]

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 <postgis-users-bounces at lists.osgeo.org> på vegne av juli g. pausas <juli.g.pausas at uv.es>
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<http://www.uv.es/jgpausas>  |  blog<http://jgpausas.blogs.uv.es/>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170315/2e5618d0/attachment.html>