Behavior of WITHIN Queries with Excluded Polygon Rings

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Behavior of WITHIN Queries with Excluded Polygon Rings

Boni Gopalan (BioImagene)

I am finding some difference in behavior when I am trying to find elements that fall within a polygon boundary that has some holes.  I would expect elements that are failing inside these holes to be omitted from query results.  The code works as I expect when using Oracle.  However the exclusion holes are not considered when the database is mysql.

 

A tertiary check on tag 1.0 source shows no special code for considering inner rings as far as mysql provider is concerned.  This is not the case with Oracle provider.  Here there are a much more informed coding to construct SDO_GEOMETRY considering inner holes.

 

Any thoughts?

 

boni



__________ Information from ESET NOD32 Antivirus, version of virus signature database 5950 (20110313) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
Reply | Threaded
Open this post in threaded view
|

Re: Behavior of WITHIN Queries with ExcludedPolygon Rings

Boni Gopalan (BioImagene)

I am doubting the problem lies more with MySQL than with hibernate-spatial.  Hibernate spatial is passing the WKB form of geometry into the WITHIN where clause and it is MySQL that is doing the whole execution.  I wrote SQL Queries and these are the results.

 

The dataset contains 1000 random rectangles within X à [0,10000], Y à [0,10000].

 

SELECT COUNT(*) FROM tile WHERE

  within(tile.LOCATION,

  GeomFromText(

    'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1))'

  )

) =  1

 

Result: 1000 [As expected]

 

SELECT COUNT(*) FROM tile WHERE

within(tile.LOCATION,

GeomFromText(

'POLYGON ((10 10, 5010 10, 5010 5010, 10 5010, 10 10))'

)

) =  1

 

Result: 248 [As expected]

 

 

SELECT COUNT(*) FROM tile WHERE

within(tile.LOCATION,

GeomFromText(

'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1),(10 10, 5010 10, 5010 5010, 10 5010, 10 10))'

)

) =  1

 

Result: 1000 [Expected : 752]

 

 

If I run

 

SELECT COUNT(*) FROM tile WHERE

within(tile.LOCATION,

GeomFromText(

'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1),(10 10, 5010 10, 5010 5010, 10 5010, 10 10))'

)

) =  1

AND

within(tile.LOCATION,

GeomFromText(

'POLYGON ((10 10, 5010 10, 5010 5010, 10 5010, 10 10))'

)

) =  0

 

Then result is 0

 

Where as

 

SELECT COUNT(*) FROM tile WHERE

within(tile.LOCATION,

GeomFromText(

'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1),(10 10, 5010 10, 5010 5010, 10 5010, 10 10))'

)

) =  1

AND

within(tile.LOCATION,

GeomFromText(

'POLYGON ((10 10, 5010 10, 5010 5010, 10 5010, 10 10))'

)

) =  0

 

Result is 752, exactly what I’d expect!!

 

Can anyone help me understand this behavior?

 

Thanks

Boni

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Boni Gopalan (BioImagene)
Sent: Monday, March 14, 2011 12:30 PM
To: Hibernate Spatial Users Discussion
Subject: [hibernatespatial-users] Behavior of WITHIN Queries with ExcludedPolygon Rings

 

I am finding some difference in behavior when I am trying to find elements that fall within a polygon boundary that has some holes.  I would expect elements that are failing inside these holes to be omitted from query results.  The code works as I expect when using Oracle.  However the exclusion holes are not considered when the database is mysql.

 

A tertiary check on tag 1.0 source shows no special code for considering inner rings as far as mysql provider is concerned.  This is not the case with Oracle provider.  Here there are a much more informed coding to construct SDO_GEOMETRY considering inner holes.

 

Any thoughts?

 

boni



__________ Information from ESET NOD32 Antivirus, version of virus signature database 5950 (20110313) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com


__________ Information from ESET NOD32 Antivirus, version of virus signature database 5950 (20110313) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com



__________ Information from ESET NOD32 Antivirus, version of virus signature database 5950 (20110313) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
Reply | Threaded
Open this post in threaded view
|

Re: Behavior of WITHIN Queries with ExcludedPolygon Rings

Karel Maesen
Administrator
Hi Boni,

Check the MySQL documentation on the OGC functions. You'll find this note on the chapter on spatial analysis functions: "Currently, MySQL does not implement these functions according to the specification. Those that are implemented return the same result as the corresponding MBR-based functions" (MBR - Minimum Bounding Rectange) . So the function looks like an OGC function, but current implementations on MySQL have very different semantics than the one specified.

It explains the behavior you have observed since the MBR of a polygon with holes equals the MBR of this polygon without the holes.


Regards,

Karel


On 14 Mar 2011, at 10:23, Boni Gopalan (BioImagene) wrote:

> I am doubting the problem lies more with MySQL than with hibernate-spatial.  Hibernate spatial is passing the WKB form of geometry into the WITHIN where clause and it is MySQL that is doing the whole execution.  I wrote SQL Queries and these are the results.
>  
> The dataset contains 1000 random rectangles within X à [0,10000], Y à [0,10000].
>  
> SELECT COUNT(*) FROM tile WHERE
>   within(tile.LOCATION,
>   GeomFromText(
>     'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1))'
>   )
> ) =  1
>  
> Result: 1000 [As expected]
>  
> SELECT COUNT(*) FROM tile WHERE
> within(tile.LOCATION,
> GeomFromText(
> 'POLYGON ((10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
> )
> ) =  1
>  
> Result: 248 [As expected]
>  
>  
> SELECT COUNT(*) FROM tile WHERE
> within(tile.LOCATION,
> GeomFromText(
> 'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1),(10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
> )
> ) =  1
>  
> Result: 1000 [Expected : 752]
>  
>  
> If I run
>  
> SELECT COUNT(*) FROM tile WHERE
> within(tile.LOCATION,
> GeomFromText(
> 'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1),(10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
> )
> ) =  1
> AND
> within(tile.LOCATION,
> GeomFromText(
> 'POLYGON ((10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
> )
> ) =  0
>  
> Then result is 0
>  
> Where as
>  
> SELECT COUNT(*) FROM tile WHERE
> within(tile.LOCATION,
> GeomFromText(
> 'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1),(10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
> )
> ) =  1
> AND
> within(tile.LOCATION,
> GeomFromText(
> 'POLYGON ((10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
> )
> ) =  0
>  
> Result is 752, exactly what I’d expect!!
>  
> Can anyone help me understand this behavior?
>  
> Thanks
> Boni
>  
> From: [hidden email] [mailto:[hidden email]] On Behalf Of Boni Gopalan (BioImagene)
> Sent: Monday, March 14, 2011 12:30 PM
> To: Hibernate Spatial Users Discussion
> Subject: [hibernatespatial-users] Behavior of WITHIN Queries with ExcludedPolygon Rings
>  
> I am finding some difference in behavior when I am trying to find elements that fall within a polygon boundary that has some holes.  I would expect elements that are failing inside these holes to be omitted from query results.  The code works as I expect when using Oracle.  However the exclusion holes are not considered when the database is mysql.
>  
> A tertiary check on tag 1.0 source shows no special code for considering inner rings as far as mysql provider is concerned.  This is not the case with Oracle provider.  Here there are a much more informed coding to construct SDO_GEOMETRY considering inner holes.
>  
> Any thoughts?
>  
> boni
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus signature database 5950 (20110313) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus signature database 5950 (20110313) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus signature database 5950 (20110313) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
Reply | Threaded
Open this post in threaded view
|

Re: Behavior of WITHIN Queries withExcludedPolygon Rings

Boni Gopalan (BioImagene)
Thanks.  I think it is time I accept the reality that writing portable performing spatial code across Oracle and MySQL cannot be a goal for 2011 product releases :(.

I was able to get highly performing oracle spatial code with these hole punched outer polygons. I wish it had worked for MySQL!

-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Karel Maesen
Sent: Monday, March 14, 2011 4:10 PM
To: Hibernate Spatial Users Discussion
Subject: Re: [hibernatespatial-users] Behavior of WITHIN Queries withExcludedPolygon Rings

Hi Boni,

Check the MySQL documentation on the OGC functions. You'll find this note on the chapter on spatial analysis functions: "Currently, MySQL does not implement these functions according to the specification. Those that are implemented return the same result as the corresponding MBR-based functions" (MBR - Minimum Bounding Rectange) . So the function looks like an OGC function, but current implementations on MySQL have very different semantics than the one specified.

It explains the behavior you have observed since the MBR of a polygon with holes equals the MBR of this polygon without the holes.


Regards,

Karel


On 14 Mar 2011, at 10:23, Boni Gopalan (BioImagene) wrote:

> I am doubting the problem lies more with MySQL than with hibernate-spatial.  Hibernate spatial is passing the WKB form of geometry into the WITHIN where clause and it is MySQL that is doing the whole execution.  I wrote SQL Queries and these are the results.
>  
> The dataset contains 1000 random rectangles within X à [0,10000], Y à [0,10000].
>  
> SELECT COUNT(*) FROM tile WHERE
>   within(tile.LOCATION,
>   GeomFromText(
>     'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1))'
>   )
> ) =  1
>  
> Result: 1000 [As expected]
>  
> SELECT COUNT(*) FROM tile WHERE
> within(tile.LOCATION,
> GeomFromText(
> 'POLYGON ((10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
> )
> ) =  1
>  
> Result: 248 [As expected]
>  
>  
> SELECT COUNT(*) FROM tile WHERE
> within(tile.LOCATION,
> GeomFromText(
> 'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1),(10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
> )
> ) =  1
>  
> Result: 1000 [Expected : 752]
>  
>  
> If I run
>  
> SELECT COUNT(*) FROM tile WHERE
> within(tile.LOCATION,
> GeomFromText(
> 'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1),(10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
> )
> ) =  1
> AND
> within(tile.LOCATION,
> GeomFromText(
> 'POLYGON ((10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
> )
> ) =  0
>  
> Then result is 0
>  
> Where as
>  
> SELECT COUNT(*) FROM tile WHERE
> within(tile.LOCATION,
> GeomFromText(
> 'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1),(10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
> )
> ) =  1
> AND
> within(tile.LOCATION,
> GeomFromText(
> 'POLYGON ((10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
> )
> ) =  0
>  
> Result is 752, exactly what I'd expect!!
>  
> Can anyone help me understand this behavior?
>  
> Thanks
> Boni
>  
> From: [hidden email] [mailto:[hidden email]] On Behalf Of Boni Gopalan (BioImagene)
> Sent: Monday, March 14, 2011 12:30 PM
> To: Hibernate Spatial Users Discussion
> Subject: [hibernatespatial-users] Behavior of WITHIN Queries with ExcludedPolygon Rings
>  
> I am finding some difference in behavior when I am trying to find elements that fall within a polygon boundary that has some holes.  I would expect elements that are failing inside these holes to be omitted from query results.  The code works as I expect when using Oracle.  However the exclusion holes are not considered when the database is mysql.
>  
> A tertiary check on tag 1.0 source shows no special code for considering inner rings as far as mysql provider is concerned.  This is not the case with Oracle provider.  Here there are a much more informed coding to construct SDO_GEOMETRY considering inner holes.
>  
> Any thoughts?
>  
> boni
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus signature database 5950 (20110313) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus signature database 5950 (20110313) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus signature database 5950 (20110313) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
 

__________ Information from ESET NOD32 Antivirus, version of virus signature database 5951 (20110314) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
 
 

__________ Information from ESET NOD32 Antivirus, version of virus signature database 5951 (20110314) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com
 
_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
Reply | Threaded
Open this post in threaded view
|

Re: Behavior of WITHIN Queries withExcludedPolygon Rings

Karel Maesen
Administrator
Hi Boni,

That's the major reason I don't use MySQL when building systems that need geospatial capabilities. Postgresql, Oracle and Sql Server all have good spatial implementations. The one in MySQL is only usable for storing some geometries, but is next to useless for spatial querying (other than MBR-queries).

Regards,

Karel

On 14 Mar 2011, at 11:44, Boni Gopalan (BioImagene) wrote:

> Thanks.  I think it is time I accept the reality that writing portable performing spatial code across Oracle and MySQL cannot be a goal for 2011 product releases :(.
>
> I was able to get highly performing oracle spatial code with these hole punched outer polygons. I wish it had worked for MySQL!
>
> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]] On Behalf Of Karel Maesen
> Sent: Monday, March 14, 2011 4:10 PM
> To: Hibernate Spatial Users Discussion
> Subject: Re: [hibernatespatial-users] Behavior of WITHIN Queries withExcludedPolygon Rings
>
> Hi Boni,
>
> Check the MySQL documentation on the OGC functions. You'll find this note on the chapter on spatial analysis functions: "Currently, MySQL does not implement these functions according to the specification. Those that are implemented return the same result as the corresponding MBR-based functions" (MBR - Minimum Bounding Rectange) . So the function looks like an OGC function, but current implementations on MySQL have very different semantics than the one specified.
>
> It explains the behavior you have observed since the MBR of a polygon with holes equals the MBR of this polygon without the holes.
>
>
> Regards,
>
> Karel
>
>
> On 14 Mar 2011, at 10:23, Boni Gopalan (BioImagene) wrote:
>
>> I am doubting the problem lies more with MySQL than with hibernate-spatial.  Hibernate spatial is passing the WKB form of geometry into the WITHIN where clause and it is MySQL that is doing the whole execution.  I wrote SQL Queries and these are the results.
>>
>> The dataset contains 1000 random rectangles within X à [0,10000], Y à [0,10000].
>>
>> SELECT COUNT(*) FROM tile WHERE
>>  within(tile.LOCATION,
>>  GeomFromText(
>>    'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1))'
>>  )
>> ) =  1
>>
>> Result: 1000 [As expected]
>>
>> SELECT COUNT(*) FROM tile WHERE
>> within(tile.LOCATION,
>> GeomFromText(
>> 'POLYGON ((10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
>> )
>> ) =  1
>>
>> Result: 248 [As expected]
>>
>>
>> SELECT COUNT(*) FROM tile WHERE
>> within(tile.LOCATION,
>> GeomFromText(
>> 'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1),(10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
>> )
>> ) =  1
>>
>> Result: 1000 [Expected : 752]
>>
>>
>> If I run
>>
>> SELECT COUNT(*) FROM tile WHERE
>> within(tile.LOCATION,
>> GeomFromText(
>> 'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1),(10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
>> )
>> ) =  1
>> AND
>> within(tile.LOCATION,
>> GeomFromText(
>> 'POLYGON ((10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
>> )
>> ) =  0
>>
>> Then result is 0
>>
>> Where as
>>
>> SELECT COUNT(*) FROM tile WHERE
>> within(tile.LOCATION,
>> GeomFromText(
>> 'POLYGON ((-1 -1, 10000 -1, 10000 10000, -1 10000, -1 -1),(10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
>> )
>> ) =  1
>> AND
>> within(tile.LOCATION,
>> GeomFromText(
>> 'POLYGON ((10 10, 5010 10, 5010 5010, 10 5010, 10 10))'
>> )
>> ) =  0
>>
>> Result is 752, exactly what I'd expect!!
>>
>> Can anyone help me understand this behavior?
>>
>> Thanks
>> Boni
>>
>> From: [hidden email] [mailto:[hidden email]] On Behalf Of Boni Gopalan (BioImagene)
>> Sent: Monday, March 14, 2011 12:30 PM
>> To: Hibernate Spatial Users Discussion
>> Subject: [hibernatespatial-users] Behavior of WITHIN Queries with ExcludedPolygon Rings
>>
>> I am finding some difference in behavior when I am trying to find elements that fall within a polygon boundary that has some holes.  I would expect elements that are failing inside these holes to be omitted from query results.  The code works as I expect when using Oracle.  However the exclusion holes are not considered when the database is mysql.
>>
>> A tertiary check on tag 1.0 source shows no special code for considering inner rings as far as mysql provider is concerned.  This is not the case with Oracle provider.  Here there are a much more informed coding to construct SDO_GEOMETRY considering inner holes.
>>
>> Any thoughts?
>>
>> boni
>>
>>
>> __________ Information from ESET NOD32 Antivirus, version of virus signature database 5950 (20110313) __________
>>
>> The message was checked by ESET NOD32 Antivirus.
>>
>> http://www.eset.com
>>
>>
>> __________ Information from ESET NOD32 Antivirus, version of virus signature database 5950 (20110313) __________
>>
>> The message was checked by ESET NOD32 Antivirus.
>>
>> http://www.eset.com
>>
>>
>> __________ Information from ESET NOD32 Antivirus, version of virus signature database 5950 (20110313) __________
>>
>> The message was checked by ESET NOD32 Antivirus.
>>
>> http://www.eset.com
>> _______________________________________________
>> hibernatespatial-users mailing list
>> [hidden email]
>> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
>
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus signature database 5951 (20110314) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>
> __________ Information from ESET NOD32 Antivirus, version of virus signature database 5951 (20110314) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users