[postgis-users] Re: Comparing Geometries with Different SRIDs

[Brent Wood]

After a belated case of RTFM...

It seems UNION does an implict distinct on all rows, unless UNION ALL is applied (How many Regina's do you want :-)

I guess it makes sense to drop duplicates unless otherwise specified.


stations=# select 1 union select 1;
 ?column?
----------
        1
(1 row)

stations=# select 1 union all select 1;
 ?column?
----------
        1
        1
(2 rows)

stations=# select 'Regina' union select 'REGINA';
 ?column?
----------
 Regina
 REGINA
(2 rows)

stations=# select 'Regina' union select 'REGINA' union all select 'Regina';
 ?column?
----------
 Regina
 REGINA
 Regina
(3 rows)


Cheers

   Brent Wood




--- On Mon, 9/29/08, Paragon Corporation <lr at pcorp.us> wrote:

> From: Paragon Corporation <lr at pcorp.us>
> Subject: RE: [postgis-users] Re: Comparing Geometries with Different SRIDs
> To: "'PostGIS Users Discussion'" <postgis-users at postgis.refractions.net>
> Date: Monday, September 29, 2008, 11:13 AM
> Brent,
> Ah never mind.  For some reason I was under the false
> assumption that in SQL
> Server with dictionary sort order that even though
> 
> REGINA = Regina
> 
> That I would get 2 Reginas when I union them.  That is not
> the case.  I just
> get one.  
> 
> I'm not sure why I thought that or maybe it was like
> that a long time ago.
> 
> Anyrate - so I guess I am back to square one. 
> 
> By the way -  I think we should be talking about
> ST_OrderingEquals not
> ST_Equals.
> 
> I'm not sure which if any of those PostGIS is using to
> do the check, but it
> should be ST_OrderingEquals  since using ST_Equals
> introduces a whole lot
> more philosophical can of worms. Is space empty or is Empty
> space.
> 
> Thanks,
> Regina
> 
> 
>  
> 
> -----Original Message-----
> From: Brent Wood [mailto:pcreso at yahoo.com] 
> Sent: Sunday, September 28, 2008 3:19 PM
> To: lr at pcorp.us
> Subject: RE: [postgis-users] Re: Comparing Geometries with
> Different SRIDs
> 
> Hi Regina,
> 
> I think that is more an encoding/language issue than case
> in such an
> analogy.
> 
> To wax metaphysical, does a muslim worship a different god
> because they
> spell it allah? (Similarly Jews with Jehovah & French
> with dieu...)
> 
> So is st_equals() a cross-language thesaurus, or is it
> restricted to the
> specified SRID? :-)
> 
> 
> 
> 
> Spotcha,
> 
>    Brent Wood
> 
> 
> --- On Mon, 9/29/08, Paragon Corporation
> <lr at pcorp.us> wrote:
> 
> > From: Paragon Corporation <lr at pcorp.us>
> > Subject: RE: [postgis-users] Re: Comparing Geometries
> with Different 
> > SRIDs
> > To: "'PostGIS Users Discussion'" 
> > <postgis-users at postgis.refractions.net>
> > Date: Monday, September 29, 2008, 8:04 AM Charlie,
> > 
> > On second thought not sure how DB II deals with
> relational Union of 
> > geometries.  Does anybody have that by chance to test?
> > 
> > I suppose we could say this is the same deal as
> difference in casing.
> > 
> > SELECT 'Regina'
> > UNION
> > SELECT 'REGINA'
> > UNION
> > SELECT 'Regina'
> > 
> > Even if both geometries are the same which SRID would
> you choose?  So 
> > we should not be doing a geometry compare, but a
> binary compare.
> > 
> > SELECT ST_AsEWKB('SRID=4269;POINT(1 3)') =
> > ST_AsEWKB('SRID=4326;POINT(1 3)')
> > 
> > 
> > Thanks,
> > Regina
> > 
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net]
> On Behalf Of 
> > Paragon Corporation
> > Sent: Sunday, September 28, 2008 2:48 PM
> > To: 'PostGIS Users Discussion'
> > Subject: RE: [postgis-users] Re: Comparing Geometries
> with Different 
> > SRIDs
> > 
> > I feel your pain Charlie, but I'm on Reid's
> side.  
> > Given that some spatial
> > databases do support cross compare between geometries
> of different 
> > SRID (e.g. DB II), not sure about Oracle or SQL Server
> 2008, I would 
> > prefer an answer that is in compliance or throws an
> exception when it 
> > can't be sure.
> > Its annoying from a debugging perspective  to deal
> with things that 
> > fail silently.  Returning false is a fail silently in
> my book.
> > 
> > If we deal with this as a special case - why
> wouldn't we deal with 
> > everything else the same with every other relational
> compare we do?  
> > Its just wrong, but granted doing the wrong thing
> would be 
> > conveniently useful in many cases.
> > 
> > Thanks,
> > Regina
> > 
> >  
> > 
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net]
> On Behalf Of 
> > Charlie Savage
> > Sent: Sunday, September 28, 2008 2:10 PM
> > To: PostGIS Users Discussion
> > Subject: Re: [postgis-users] Re: Comparing Geometries
> with Different 
> > SRIDs
> > 
> > 
> > 
> > Reid Priedhorsky wrote:
> > > Charlie Savage wrote:
> > >>> The 2 point could be spatially equal
> given
> > different SRIDS and
> > >>> coordinates if they were projected to a
> common
> > SRID. So should
> > >>> geometry operators silently Call
> st_transform
> > to make the righthand
> > >>> match the lefthand before comparing? This
> > would be quite the
> > >>> expensive operation.
> > >>
> > >> No. You can't automatically transform
> between
> > different SRID values
> > >> Think of the case of one geometry with an
> SRID
> > value of 4326 and one
> > >> with an SRID value of -1 (no coordinate
> system).
> > > 
> > > Exactly.
> > > 
> > >> So different SRID values, then the geometries
> are
> > not equal.
> > > 
> > > No -- as Stanley said, the geometries could be in
> fact
> > equal, but
> > > expressed in different SRS. So if ST_Equals()
> returned
> > False, it would
> > > be wrong.
> > 
> > Maybe.  But returning "Operation permitted"
> is even worse because it 
> > means you can't do natural things like this
> (without extra annoying 
> > SRID checking
> > code) in plpgsql:
> > 
> > IF (geom1 == geom2) ... END IF;
> > 
> > Or the example with the union earlier posted
> > 
> > 
> > >> It is up to the user to transform geometries
> to
> > the same SRID before
> > >> calling ST_EQUALS.
> > > 
> > > Exactly. ;)
> > 
> > So, I still vote that st_equals will not blow up when
> comparing two 
> > geometries with different SRID values, it just will
> return false.
> > 
> > Charlie
> > 
> > 
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> >
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> > 
> > 
> > 
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> >
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users