<p dir="ltr">Y</p>
<div class="gmail_quote">On 16/09/2016 10:01 PM, <<a href="mailto:postgis-users-request@lists.osgeo.org">postgis-users-request@lists.osgeo.org</a>> wrote:<br type="attribution"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Send postgis-users mailing list submissions to<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<br>
To subscribe or unsubscribe via the World Wide Web, visit<br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><br>
or, via email, send a message with subject or body 'help' to<br>
<a href="mailto:postgis-users-request@lists.osgeo.org">postgis-users-request@lists.<wbr>osgeo.org</a><br>
<br>
You can reach the person managing the list at<br>
<a href="mailto:postgis-users-owner@lists.osgeo.org">postgis-users-owner@lists.<wbr>osgeo.org</a><br>
<br>
When replying, please edit your Subject line so it is more specific<br>
than "Re: Contents of postgis-users digest..."<br>
<br>
<br>
Today's Topics:<br>
<br>
1. what does it really mean for one geometry to be equal to<br>
another (Rhys A.D. Stewart)<br>
2. Re: what does it really mean for one geometry to be equal to<br>
another (Sandro Santilli)<br>
3. Re: what does it really mean for one geometry to be equal to<br>
another (Paul Norman)<br>
4. Re: what does it really mean for one geometry to be equal to<br>
another (Paul Norman)<br>
5. Re: what does it really mean for one geometry to be equal to<br>
another (Paul Norman)<br>
6. Re: what does it really mean for one geometry to be equal to<br>
another (Sandro Santilli)<br>
7. pl/pgsql function to write table in parallel mode (Nicolas Ribot)<br>
8. Re: fuzzy tolerance (Willy-Bas Loos)<br>
<br>
<br>
------------------------------<wbr>------------------------------<wbr>----------<br>
<br>
Message: 1<br>
Date: Thu, 15 Sep 2016 20:53:25 -0500<br>
From: "Rhys A.D. Stewart" <<a href="mailto:rhys.stewart@gmail.com">rhys.stewart@gmail.com</a>><br>
To: PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><wbr>><br>
Subject: [postgis-users] what does it really mean for one geometry to<br>
be equal to another<br>
Message-ID:<br>
<CACg0vTkjgR1ZhuDsHK1dZOKi=K=<wbr>RBXbxa+qmq6E46Q0ZurK2=<a href="mailto:w@mail.gmail.com">w@mail.<wbr>gmail.com</a>><br>
Content-Type: text/plain; charset="utf-8"<br>
<br>
Greetings all,<br>
<br>
I maintain a medium size table of customer locations, which, for business<br>
purposes now needs to not have any coincident points. Table definition<br>
follows:<br>
==============================<wbr>==============================<wbr>=========<br>
service.location<br>
(<br>
premises text NOT NULL,<br>
matchtype text,<br>
matchdate date,<br>
connectedtransformer text,<br>
g geometry(Point,3448),<br>
CONSTRAINT servicelocation_pkey PRIMARY KEY (premises),<br>
CONSTRAINT servicelocation_<wbr>premisesnumber_check CHECK<br>
(char_length(premises) = 6 OR char_length(premises) = 7),<br>
CONSTRAINT servicelocation_<wbr>premisesnumber_is_a_number_<wbr>check CHECK<br>
(premises !~* '[A-z]+'::text)<br>
)<br>
==============================<wbr>==============================<wbr>=========<br>
<br>
There are approximately 866k rows, and a gist index on g. I update the<br>
table so that no geometries are coincident ( see<br>
<a href="https://gist.github.com/rhysallister/bcb4bb07a99d69938fff88f150883bee" rel="noreferrer" target="_blank">https://gist.github.com/<wbr>rhysallister/<wbr>bcb4bb07a99d69938fff88f150883b<wbr>ee</a> for<br>
the sql to remove the coincident geoms) I ran the sql in the gist until it<br>
said 0 rows affected.<br>
<br>
To prevent one from inserting or updating a coincident geometry I try to<br>
create a unique index on g. Since gist doesn't support unique indices I do:<br>
<br>
==============================<wbr>==============================<wbr>=========<br>
CREATE unique INDEX unique_g ON service.location (st_astext(g) );<br>
------------------------------<wbr>------------------------------<wbr>---------<br>
ERROR: could not create unique index "unique_g"<br>
DETAIL: Key (st_astext(g))=(POINT(727895.4 663599.3)) is duplicated.<br>
==============================<wbr>==============================<wbr>=========<br>
This makes me slightly flummoxed. I'm pretty sure the query in the gist<br>
returned 0 affected rows. But, maybe I missed a step. I try to find the<br>
offending rows with:<br>
<br>
==============================<wbr>==============================<wbr>=========<br>
select premises, st_astext(g), g from service.location<br>
where st_equals(g, 'SRID=3448;POINT(727895.4 663599.3)'::geometry)<br>
------------------------------<wbr>------------------------------<wbr>---------<br>
premises st_astext g<br>
267077 POINT(727895.4 663599.3)<br>
0101000020780D0000CDCCCCCCAE36<wbr>26419A9999995E402441<br>
==============================<wbr>==============================<wbr>=========<br>
Strange. I now move to being slightly perturbed. I'm very sure the previous<br>
error message made mention of duplicity. I then run<br>
<br>
==============================<wbr>==============================<wbr>=========<br>
select premises, st_astext(g), g from service.location<br>
where st_astext(g) = 'POINT(727895.4 663599.3)'<br>
------------------------------<wbr>------------------------------<wbr>---------<br>
premises st_astext g<br>
267077 POINT(727895.4 663599.3)<br>
0101000020780D0000CDCCCCCCAE36<wbr>26419A9999995E402441<br>
267053 POINT(727895.4 663599.3)<br>
0101000020780D0000CDCCCCCCAE36<wbr>2641999999995E402441<br>
==============================<wbr>==============================<wbr>=========<br>
<br>
Now I'm just confused, the 2 premises have the same st_astext, but<br>
different wkb representations and as such are not being caught in the<br>
st_equals call.<br>
<br>
<br>
Is there some gotcha that I don't know about, maybe something in the docs<br>
that I missed or is this not supposed to happen?<br>
<br>
Rhys<br>
Peace & Love|Live Long & Prosper<br>
-------------- next part --------------<br>
An HTML attachment was scrubbed...<br>
URL: <<a href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20160915/313afacc/attachment-0001.html" rel="noreferrer" target="_blank">http://lists.osgeo.org/<wbr>pipermail/postgis-users/<wbr>attachments/20160915/313afacc/<wbr>attachment-0001.html</a>><br>
<br>
------------------------------<br>
<br>
Message: 2<br>
Date: Fri, 16 Sep 2016 07:11:55 +0200<br>
From: Sandro Santilli <<a href="mailto:strk@kbt.io">strk@kbt.io</a>><br>
To: PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><wbr>><br>
Subject: Re: [postgis-users] what does it really mean for one geometry<br>
to be equal to another<br>
Message-ID: <20160916051155.GB4828@<wbr>localhost><br>
Content-Type: text/plain; charset=us-ascii<br>
<br>
On Thu, Sep 15, 2016 at 08:53:25PM -0500, Rhys A.D. Stewart wrote:<br>
<br>
> Now I'm just confused, the 2 premises have the same st_astext, but<br>
> different wkb representations and as such are not being caught in the<br>
> st_equals call.<br>
><br>
> Is there some gotcha that I don't know about, maybe something in the docs<br>
> that I missed or is this not supposed to happen?<br>
<br>
What you describe is expected:<br>
the text representation is approximated/truncated.<br>
<br>
You could create your index on ST_AsBinary(geometry) if you wanted<br>
to check binary-level equality, but as your subject asks: what's<br>
your concept of equality ? Do you really want those practically<br>
identical points in your db ? Or you could create an index on<br>
ST_SnapToGrid(geom, <tolerance>), for points to be no closer<br>
than <tolerance>. Or (for lines) you could use ST_HausdorffDistance.<br>
<br>
Many ways to interpret equality, which is why the equality operator<br>
is currently just checking for approximated minimum bounding box equality<br>
(probably still equal for your two almost-identical points).<br>
<br>
--strk;<br>
<br>
() Free GIS & Flash consultant/developer<br>
/\ <a href="https://strk.kbt.io/services.html" rel="noreferrer" target="_blank">https://strk.kbt.io/services.<wbr>html</a><br>
<br>
<br>
------------------------------<br>
<br>
Message: 3<br>
Date: Thu, 15 Sep 2016 23:19:49 -0700<br>
From: Paul Norman <<a href="mailto:penorman@mac.com">penorman@mac.com</a>><br>
To: <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
Subject: Re: [postgis-users] what does it really mean for one geometry<br>
to be equal to another<br>
Message-ID: <<a href="mailto:702ce217-2a87-be77-13f1-63bd1a34c5f9@mac.com">702ce217-2a87-be77-13f1-<wbr>63bd1a34c5f9@mac.com</a>><br>
Content-Type: text/plain; charset="utf-8"; Format="flowed"<br>
<br>
On 9/15/2016 6:53 PM, Rhys A.D. Stewart wrote:<br>
> ==============================<wbr>==============================<wbr>=========<br>
> select premises, st_astext(g), g from service.location<br>
> where st_astext(g) = 'POINT(727895.4 663599.3)'<br>
> ------------------------------<wbr>------------------------------<wbr>---------<br>
> premisesst_astextg<br>
> 267077POINT(727895.4<br>
> 663599.3)<wbr>0101000020780D0000CDCCCCCCAE36<wbr>26419A9999995E402441<br>
> 267053POINT(727895.4<br>
> 663599.3)<wbr>0101000020780D0000CDCCCCCCAE36<wbr>2641999999995E402441<br>
> ==============================<wbr>==============================<wbr>=========<br>
><br>
> Now I'm just confused, the 2 premises have the same st_astext, but<br>
> different wkb representations and as such are not being caught in the<br>
> st_equals call.<br>
><br>
><br>
> Is there some gotcha that I don't know about, maybe something in the<br>
> docs that I missed or is this not supposed to happen?<br>
<br>
A safe bet is that you're hitting floating point issues. EWKB is the<br>
canonical format for geometries, but the conversion to a text<br>
representation could lose some precision. If you did want to require<br>
unique geometries, you could do it with a btree index on the geometry,<br>
not st_astext of the geometry.<br>
<br>
As a general rule, comparing two floating point numbers for equality is<br>
tricky. What you probably want is an exclusion constraint which prevents<br>
two points from being within a small distance of each other.<br>
<br>
I don't know of a great way to do this, but a bad way that might work is<br>
EXCLUDE USING GIST (ST_Buffer(geom, 0.1) WITH &&). See<br>
<a href="https://www.postgresql.org/docs/current/static/rangetypes.html#RANGETYPES-CONSTRAINT" rel="noreferrer" target="_blank">https://www.postgresql.org/<wbr>docs/current/static/<wbr>rangetypes.html#RANGETYPES-<wbr>CONSTRAINT</a><br>
and the links from there. I haven't tested this.<br>
-------------- next part --------------<br>
An HTML attachment was scrubbed...<br>
URL: <<a href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20160915/eabff54e/attachment-0001.html" rel="noreferrer" target="_blank">http://lists.osgeo.org/<wbr>pipermail/postgis-users/<wbr>attachments/20160915/eabff54e/<wbr>attachment-0001.html</a>><br>
<br>
------------------------------<br>
<br>
Message: 4<br>
Date: Thu, 15 Sep 2016 23:37:38 -0700<br>
From: Paul Norman <<a href="mailto:penorman@mac.com">penorman@mac.com</a>><br>
To: <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
Subject: Re: [postgis-users] what does it really mean for one geometry<br>
to be equal to another<br>
Message-ID: <<a href="mailto:9221777f-f4c2-ce2b-e19a-680ce943c9b3@mac.com">9221777f-f4c2-ce2b-e19a-<wbr>680ce943c9b3@mac.com</a>><br>
Content-Type: text/plain; charset=utf-8; format=flowed<br>
<br>
On 9/15/2016 11:19 PM, Paul Norman wrote:<br>
> I don't know of a great way to do this, but a bad way that might work<br>
> is EXCLUDE USING GIST (ST_Buffer(geom, 0.1) WITH &&). See<br>
> <a href="https://www.postgresql.org/docs/current/static/rangetypes.html#RANGETYPES-CONSTRAINT" rel="noreferrer" target="_blank">https://www.postgresql.org/<wbr>docs/current/static/<wbr>rangetypes.html#RANGETYPES-<wbr>CONSTRAINT</a><br>
> and the links from there. I haven't tested this.<br>
<br>
After further though, this is doing a bounding box comparison so it's<br>
not quite 0.1 projected units and you can use ST_Expand instead of<br>
ST_Buffer.<br>
<br>
<br>
------------------------------<br>
<br>
Message: 5<br>
Date: Thu, 15 Sep 2016 23:58:28 -0700<br>
From: Paul Norman <<a href="mailto:penorman@mac.com">penorman@mac.com</a>><br>
To: <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
Subject: Re: [postgis-users] what does it really mean for one geometry<br>
to be equal to another<br>
Message-ID: <<a href="mailto:4ac8f86f-fae3-b5f5-6880-88fe0e5c0ec0@mac.com">4ac8f86f-fae3-b5f5-6880-<wbr>88fe0e5c0ec0@mac.com</a>><br>
Content-Type: text/plain; charset=utf-8; format=flowed<br>
<br>
On 9/15/2016 10:11 PM, Sandro Santilli wrote:<br>
> you could create an index on<br>
> ST_SnapToGrid(geom, <tolerance>), for points to be no closer<br>
> than <tolerance>.<br>
<br>
This won't check that points are no closer than <tolerance>, nor will it<br>
solve floating point issues. It's the same as rounding. If two points<br>
are on either side of the line where they round to different grid<br>
points, even if they are arbitrarily close, up to the limit of precision.<br>
<br>
It does mean that you will see fewer values where you get floating point<br>
equality issues, but the difference between the rounded floating points<br>
will increase.<br>
<br>
<br>
<br>
------------------------------<br>
<br>
Message: 6<br>
Date: Fri, 16 Sep 2016 09:11:50 +0200<br>
From: Sandro Santilli <<a href="mailto:strk@kbt.io">strk@kbt.io</a>><br>
To: PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><wbr>><br>
Subject: Re: [postgis-users] what does it really mean for one geometry<br>
to be equal to another<br>
Message-ID: <20160916071150.GA9706@<wbr>localhost><br>
Content-Type: text/plain; charset=us-ascii<br>
<br>
On Thu, Sep 15, 2016 at 11:19:49PM -0700, Paul Norman wrote:<br>
<br>
> If you did want to require<br>
> unique geometries, you could do it with a btree index on the<br>
> geometry, not st_astext of the geometry.<br>
<br>
The btree opclass for geometry only checks bounding box equality,<br>
not geometry equality (see lwgeom_eq, aka OPERATOR=).<br>
<br>
--strk;<br>
<br>
() Free GIS & Flash consultant/developer<br>
/\ <a href="https://strk.kbt.io/services.html" rel="noreferrer" target="_blank">https://strk.kbt.io/services.<wbr>html</a><br>
<br>
<br>
------------------------------<br>
<br>
Message: 7<br>
Date: Fri, 16 Sep 2016 12:32:43 +0200<br>
From: Nicolas Ribot <<a href="mailto:nicolas.ribot@gmail.com">nicolas.ribot@gmail.com</a>><br>
To: PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><wbr>><br>
Subject: [postgis-users] pl/pgsql function to write table in parallel<br>
mode<br>
Message-ID:<br>
<CAGAwT=<a href="mailto:2AM14QetUCihrKAr%2B1%2BaQ%2BbdebkYiAYhp9WUtj4i0zpQ@mail.gmail.com">2AM14QetUCihrKAr+1+aQ+<wbr>bdebkYiAYhp9WUtj4i0zpQ@mail.<wbr>gmail.com</a>><br>
Content-Type: text/plain; charset="utf-8"<br>
<br>
Hi,<br>
<br>
Playing with new PG9.6rc1 / Pgis 2.3beta1, I found parallel query mode to<br>
be really efficient to process big tables.<br>
Unfortunately, it is not possible to directly create tables with parallel<br>
plan (create table as select...) (see:<br>
<a href="https://wiki.postgresql.org/wiki/Parallel_Query" rel="noreferrer" target="_blank">https://wiki.postgresql.org/<wbr>wiki/Parallel_Query</a>).<br>
It is possible, though, to use copy mode with psql feed to create a table<br>
with parallel plan enabled.<br>
<br>
To allow creating tables directly in pure SQL script, I developed a small<br>
hack function that takes a SQL query and creates a table from it, using<br>
COPY command with psql PROGRAM executing the query.<br>
<br>
Usage:<br>
<br>
select * from create_table_parallel(<br>
'table_name',<br>
'select <a href="http://p.id" rel="noreferrer" target="_blank">p.id</a> as idparc, c.gid as idcarreau<br>
st_intersection(p.geom, c.geom) as geom<br>
from parcelle_sample2 p<br>
join carreau_sample2 c on st_intersects(p.geom, c.geom)',<br>
'/usr/local/pgsql-9.6/bin/psql -A -t -p 5439 -d nicolas -c',<br>
8, -- workers<br>
true);<br>
<br>
Limitations:<br>
<br>
• delimiter used for copy operation defaults to '|'<br>
• a 'LIMIT 0' clause is inserted at the end of the passed query to create<br>
table structure: query to run cannot contain a LIMIT clause.<br>
• The function is not safe, as it injects user parameters to build psql<br>
command, and it's not extensively tested.<br>
<br>
Perfomance expected:<br>
<br>
Depends on the number of workers configured and planned:<br>
On a small dataset (~15 000 pg intersected with 360 000 pg), with 8 workers<br>
configured and 3 choosen by the planner, table creation took *24s vs 1m25s*<br>
with a traditionnal create table as select...<br>
<br>
Nicolas<br>
-------------- next part --------------<br>
An HTML attachment was scrubbed...<br>
URL: <<a href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20160916/b051b25f/attachment-0001.html" rel="noreferrer" target="_blank">http://lists.osgeo.org/<wbr>pipermail/postgis-users/<wbr>attachments/20160916/b051b25f/<wbr>attachment-0001.html</a>><br>
-------------- next part --------------<br>
A non-text attachment was scrubbed...<br>
Name: fn_create_table_parallel.sql<br>
Type: application/octet-stream<br>
Size: 2522 bytes<br>
Desc: not available<br>
URL: <<a href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20160916/b051b25f/attachment-0001.obj" rel="noreferrer" target="_blank">http://lists.osgeo.org/<wbr>pipermail/postgis-users/<wbr>attachments/20160916/b051b25f/<wbr>attachment-0001.obj</a>><br>
<br>
------------------------------<br>
<br>
Message: 8<br>
Date: Fri, 16 Sep 2016 17:20:05 +0200<br>
From: Willy-Bas Loos <<a href="mailto:willybas@gmail.com">willybas@gmail.com</a>><br>
To: PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><wbr>>, Daniel<br>
Baston <<a href="mailto:dbaston@gmail.com">dbaston@gmail.com</a>>, Lars Aksel Opsahl <<a href="mailto:Lars.Opsahl@nibio.no">Lars.Opsahl@nibio.no</a>><br>
Subject: Re: [postgis-users] fuzzy tolerance<br>
Message-ID:<br>
<<a href="mailto:CAHnozTi76La74wU0TXE68YLn9TQPHJUbDMYcyR0hCMruROmkRg@mail.gmail.com">CAHnozTi76La74wU0TXE68YLn9TQP<wbr>HJUbDMYcyR0hCMruROmkRg@mail.<wbr>gmail.com</a>><br>
Content-Type: text/plain; charset="utf-8"<br>
<br>
On Thu, Sep 15, 2016 at 1:58 PM, Daniel Baston <<a href="mailto:dbaston@gmail.com">dbaston@gmail.com</a>> wrote:<br>
<br>
> > Any words of warning about using a trigger and storing the data on a 10<br>
> cm<br>
> > grid like i suggest?<br>
><br>
<br>
Wow, thanks for the great responses. Lars Opsahl, nice to see you in the<br>
mailing list :D<br>
So what i gather from this is that it is not ideal to use st_snaptogrid. It<br>
solves some problems, but it creates some new ones too.<br>
Maybe a second geometry column would be a better idea, so that the original<br>
is still there (and will consume your server's memory &hdd space :/ )<br>
Anyway, there is no automatic way to solve the problem right now.<br>
<br>
So how big are the problems that arise from this?<br>
For me i have to say that we often have problems with errors in overlays,<br>
and we have to keep using st_makevalid after every step of a process.<br>
Decreasing the supersmall artifacts in the geometries would probably help<br>
with that.<br>
@Lars Opsahl, you describe a lot of problems or very reasonable wishes in<br>
your presentation (link to abstract in previous mail). Do you think those<br>
could be solved with a concept similar to fuzzy tolerance?<br>
@Daniel Baston could you describe some of the problems that the<br>
hyperprecise coordinates cause for you?<br>
<br>
Cheers,<br>
<br>
--<br>
Willy-Bas Loos<br>
-------------- next part --------------<br>
An HTML attachment was scrubbed...<br>
URL: <<a href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20160916/5dd0f77f/attachment-0001.html" rel="noreferrer" target="_blank">http://lists.osgeo.org/<wbr>pipermail/postgis-users/<wbr>attachments/20160916/5dd0f77f/<wbr>attachment-0001.html</a>><br>
<br>
------------------------------<br>
<br>
Subject: Digest Footer<br>
<br>
______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><br>
<br>
------------------------------<br>
<br>
End of postgis-users Digest, Vol 175, Issue 9<br>
******************************<wbr>***************<br>
</blockquote></div>