<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"><!-- P {margin-top:0;margin-bottom:0;} --></style>
</head>
<body dir="ltr">
<div id="divtagdefaultwrapper" style="background-color:rgb(255,255,255)">
<p style="color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt">
Hi</p>
<p style="color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt">
<br>
</p>
<p style="color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt">
You can do something like this, but this uses a extra help table so it's not the best solution.</p>
<p style="color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt">
<br>
</p>
<p style="color:rgb(0,0,0); font-family:Menlo; font-size:15px; margin-right:0px; margin-left:0px; line-height:normal">
<span style="">create table </span>geo_key_map as ( select distinct md5(ST_asBinary(geo)) from test_table);</p>
<p style="color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt">
</p>
<p style="color:rgb(0,0,0); font-family:Menlo; font-size:15px; margin-right:0px; margin-left:0px; line-height:normal">
<span style="">SELECT 8144241</span></p>
<p style="color:rgb(0,0,0); font-family:Menlo; font-size:15px; margin-right:0px; margin-left:0px; line-height:normal">
<span style=""><br>
</span></p>
<p style="margin-right:0px; margin-left:0px; line-height:normal"><font size="3"><font face="Calibri, Arial, Helvetica, sans-serif" size="3">Then you get a table where you add serial column and get counter from 1 to 8144241. </font></font></p>
<p style="margin-right:0px; margin-left:0px; line-height:normal"><font size="3"><font face="Calibri, Arial, Helvetica, sans-serif" size="3"><br>
</font></font></p>
<p style="margin-right:0px; margin-left:0px; line-height:normal"><font size="3"><font face="Calibri, Arial, Helvetica, sans-serif" size="3">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.</font></font><br>
</p>
<p style="color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; margin-right:0px; margin-left:0px; line-height:normal">
<font size="3"><br>
</font></p>
<p style="color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; margin-right:0px; margin-left:0px; line-height:normal">
<font size="3">The original table in this case contained 8144267 rows.</font></p>
<p style="color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; margin-right:0px; margin-left:0px; line-height:normal">
<font size="3"><br>
</font></p>
<p style="color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; margin-right:0px; margin-left:0px; line-height:normal">
<font size="3">Lars</font></p>
<div style="color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt">
<br>
<div style="color:rgb(0,0,0)">
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" color="#000000" style="font-size:11pt"><b>Fra:</b> postgis-users <postgis-users-bounces@lists.osgeo.org> på vegne av juli g. pausas <juli.g.pausas@uv.es><br>
<b>Sendt:</b> 15. mars 2017 15:49<br>
<b>Til:</b> PostGIS Users Discussion<br>
<b>Emne:</b> [postgis-users] DISTINCT geometry to a code</font>
<div> </div>
</div>
<div>
<div dir="ltr">
<div>Hi<br>
</div>
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).<br>
<div><br>
SELECT count(*) FROM juli.savannaswdsp; -- 945361<br>
SELECT count(DISTINCT geom) FROM juli.savannaswdsp; -- 201606<br>
<br>
<div>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.
<br>
<br>
</div>
<div>[ In R language this would be as follows: as.numeric(as.factor(geom)) ]<br>
</div>
<div><br>
</div>
<div>Thank for any suggestion<br>
</div>
<div><br clear="all">
<div>
<div class="gmail_signature">
<div dir="ltr">
<div>
<div dir="ltr">
<div>
<div dir="ltr">Juli
<div><span style="color:rgb(51,51,153)">--</span><br>
<font color="#333399"><b>CIDE, CSIC</b> | <a href="http://www.uv.es/jgpausas" target="_blank">
www.uv.es/jgpausas</a> | <a href="http://jgpausas.blogs.uv.es/" target="_blank">
blog</a><br>
<br>
</font></div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>