5:20 a.m.
Karel, etal
We have discussed spatial-specific extensions to HQL for quite some time.
But those discussions have always been kind of esoteric ("boy wouldn't it
be nice to have some spatial support in HQL").
As we are working on redesigning the parsing and interpretation of HQL
queries and since spatial has been integrated upstream, it seems like a
great time to discuss specifics of what this might mean.
I have never used spatial data, let alone crafted queries using spatial
data. So I am not the best driver here.
What kinds of things make sense to add to HQL for supporting spatial
queries?
7:29 p.m.
Hi Steve,
Have a look at this table:
http://www.hibernatespatial.org/documentation/03-dialects/01-overview/
It provides an overview of the HQL functions that are supported in Hibernate Spatial. They
are currently registered into each concrete SpatialDialect. I think it might be a good
start to start having these functions integrated into HQL.
These functions are commonly found in spatial databases because they have been defined in
the Simple Feature Specification (SFS). The reference given after each function refers to
this spec. This spec was originally intended to be used only for planar 2D geometries, but
has been extended by most databases to apply also to 3D/4D and non-planer, i.e. geographic
coordinates.
There is also the 'SQL/MM, Part 3' - Spatial specification. This spec is, as I
understand it, derived from the Simple Feature Specification but adds support for more
complicated geometries and for 3D/4D geometries. Not all databases have support for
everything specified in there, and no open-source Java geometry library has either.
As for implementation, there are some weird cases. For example, if you specify in MySQL
(native SQL) "select * from t where st_contains(geom1, geom2) = 1” the spatial query
is not used, but it is used when the statement reads "select * from t where
st_contains(geom1, geom2)”. Both should be equivalent.
Another example. In Oracle10g the HQL spatial functions are translated to something like
this: "SELECT A.Feature_ID FROM TARGET A WHERE sdo_filter(A.shape,
:theWindow,'querytype=window') = 'TRUE’;”
The whole and only the whole expression ‘sdo_filter(...) = ‘TRUE’’ evaluates to TRUE. So
leaving the “... = ‘TRUE’” part off or putting something else in there, fails to
evaluate.
Hope this helps kick off the discussion.
Regards,
Karel
On 26 Aug 2015, at 05:20, Steve Ebersole <steve(a)hibernate.org>
wrote:
Karel, etal
We have discussed spatial-specific extensions to HQL for quite some time.
But those discussions have always been kind of esoteric ("boy wouldn't it
be nice to have some spatial support in HQL").
As we are working on redesigning the parsing and interpretation of HQL
queries and since spatial has been integrated upstream, it seems like a
great time to discuss specifics of what this might mean.
I have never used spatial data, let alone crafted queries using spatial
data. So I am not the best driver here.
What kinds of things make sense to add to HQL for supporting spatial
queries?
_______________________________________________
hibernate-dev mailing list
hibernate-dev(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
10:29 p.m.
What do you mean "have these functions integrated into HQL"? What you
describe is exactly how sql functions are supported as well. Perhaps you
mean defining standard support for spatial functions. As a corollary, take
something like the ANSI SQL TRIM function. Not all databases support TRIM
as defined by ANSI SQL so ultimately we leverage Dialect registered support
for what the database does support. However, we encode "TRIM support" into
the HQL grammar (in the rewrite) to make sure that the arguments are
handled consistently. We do still expect each that the Dialect as
registered an appropriate SQLFunction representation of its TRIM function
support. We could do something like that here as well. The main
difference is that not all applications are using spatial.
So more I am asking about syntax changes that might help support spatial
queries, specific operators, etc. How about declaring "spatial literals"?
Does that make sense for spatial queries?
On Wed, Sep 2, 2015 at 12:29 PM Karel Maesen <karel(a)geovise.com> wrote:
Hi Steve,
Have a look at this table:
http://www.hibernatespatial.org/documentation/03-dialects/01-overview/
It provides an overview of the HQL functions that are supported in
Hibernate Spatial. They are currently registered into each concrete
SpatialDialect. I think it might be a good start to start having these
functions integrated into HQL.
These functions are commonly found in spatial databases because they have
been defined in the Simple Feature Specification (SFS). The reference given
after each function refers to this spec. This spec was originally intended
to be used only for planar 2D geometries, but has been extended by most
databases to apply also to 3D/4D and non-planer, i.e. geographic
coordinates.
There is also the 'SQL/MM, Part 3' - Spatial specification. This spec is,
as I understand it, derived from the Simple Feature Specification but adds
support for more complicated geometries and for 3D/4D geometries. Not all
databases have support for everything specified in there, and no
open-source Java geometry library has either.
As for implementation, there are some weird cases. For example, if you
specify in MySQL (native SQL) "select * from t where st_contains(geom1,
geom2) = 1” the spatial query is not used, but it is used when the
statement reads "select * from t where st_contains(geom1, geom2)”. Both
should be equivalent.
Another example. In Oracle10g the HQL spatial functions are translated to
something like this: "SELECT A.Feature_ID FROM TARGET A WHERE
sdo_filter(A.shape, :theWindow,'querytype=window') = 'TRUE’;”
The whole and only the whole expression ‘sdo_filter(...) = ‘TRUE’’
evaluates to TRUE. So leaving the “... = ‘TRUE’” part off or putting
something else in there, fails to evaluate.
Hope this helps kick off the discussion.
Regards,
Karel
> On 26 Aug 2015, at 05:20, Steve Ebersole <steve(a)hibernate.org> wrote:
>
> Karel, etal
>
> We have discussed spatial-specific extensions to HQL for quite some time.
> But those discussions have always been kind of esoteric ("boy wouldn't it
> be nice to have some spatial support in HQL").
>
> As we are working on redesigning the parsing and interpretation of HQL
> queries and since spatial has been integrated upstream, it seems like a
> great time to discuss specifics of what this might mean.
>
> I have never used spatial data, let alone crafted queries using spatial
> data. So I am not the best driver here.
>
> What kinds of things make sense to add to HQL for supporting spatial
> queries?
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev(a)lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
12:07 a.m.
Hi Steve,
On 08 Sep 2015, at 22:29, Steve Ebersole <steve(a)hibernate.org>
wrote:
What do you mean "have these functions integrated into HQL"? What you describe
is exactly how sql functions are supported as well. Perhaps you mean defining standard
support for spatial functions.
Yes, precisely that
As a corollary, take something like the ANSI SQL TRIM function. Not
all databases support TRIM as defined by ANSI SQL so ultimately we leverage Dialect
registered support for what the database does support. However, we encode "TRIM
support" into the HQL grammar (in the rewrite) to make sure that the arguments are
handled consistently. We do still expect each that the Dialect as registered an
appropriate SQLFunction representation of its TRIM function support. We could do
something like that here as well. The main difference is that not all applications are
using spatial.
It is true that not all applications use spatial, or even that DB’s support (all) spatial
functions. But it would help to ensure consistency in the available dialects. Now this
consistency is tested for in the integration tests, so not having this at the grammar
isn’t a big problem.
So more I am asking about syntax changes that might help support spatial queries,
specific operators, etc. How about declaring "spatial literals"? Does that
make sense for spatial queries?
W.r.t operators: don’t the same concern about not all applications using spatial apply
here as well? In any case, I don’t think operators are going to be popular. Just about
everyone is happy to use the relevant spatial relationship functions. At least their names
and semantics are pretty standard across implementations and technologies. There is to my
knowledge no comparable standard for spatial symbolic operators.
Support for spatial literals would be a great plus. In my opinion, the best candidate for
HQL spatial literals is the Postgis EWKT format
(http://postgis.net/docs/manual-2.1/using_postgis_dbmanagement.html#EWKB_EWKT), which is a
sane, and popular extension of the purely 2D WKT standard. It is supported in the
geolatte-geom library and many others. I can help by writing a grammar for this.
There is also at least one HQL syntax change that would help implement spatial functions:
function invocations that evaluate to boolean should be regarded as valid boolean
expressions. The HQL currently doesn’t seem to allow this, so I needed to wrap them in
equality expressions. But then MySQL wouldn’t use its spatial index (as I wrote before,
although I mistakenly wrote “spatial query” in my original email).
Regards,
Karel
2:12 p.m.
It is true that not all applications use spatial, or even that DB’s
support (all) spatial functions. But it would help to ensure consistency in
the available dialects. Now this consistency is tested for in the
integration tests, so not having this at the grammar isn’t a big problem.
Its up to you. We can add it to the grammar and simply have it throw
exceptions if those functions are not understood.
Support for spatial literals would be a great plus. In my opinion, the best
candidate for HQL spatial literals is the Postgis EWKT format (
http://postgis.net/docs/manual-2.1/using_postgis_dbmanagement.html#EWKB_EWKT),
which is a sane, and popular extension of the purely 2D WKT standard. It
is supported in the geolatte-geom library and many others. I can help by
writing a grammar for this.
What I have done thus far in SQM grammars is to borrow from the idea of
"JDBC escape syntax". So for example, to define a timestamp literal one
would say `{ts '2015-10-10 ...'}`
There is also at least one HQL syntax change that would help implement
spatial functions: function invocations that evaluate to boolean
should be
regarded as valid boolean expressions. The HQL currently doesn’t seem to
allow this, so I needed to wrap them in equality expressions. But then
MySQL wouldn’t use its spatial index (as I wrote before, although I
mistakenly wrote “spatial query” in my original email).
This would require some knowledge of the "metadata" of a function, which is
not there in SQM at all so far. Here, I'd have to know the return type of
the referenced function. We can contemplate that.
https://hibernate.atlassian.net/browse/SQM-15
4:16 p.m.
Karel,
Any further thoughts?
I looked over the link you gave in regards to literals, but did not fully
understand. I'd prefer this follow some "escape syntax" form, beyond that
maybe you could just enumerate the literal types you'd want to support and
their formats.
ALso, I have not seen any discussion around "boolean-function-expressions
as predicates" on SQM-15...
On Thu, Sep 10, 2015 at 7:12 AM Steve Ebersole <steve(a)hibernate.org> wrote:
It is true that not all applications use spatial, or even that DB’s
> support (all) spatial functions. But it would help to ensure consistency in
> the available dialects. Now this consistency is tested for in the
> integration tests, so not having this at the grammar isn’t a big problem.
>
Its up to you. We can add it to the grammar and simply have it throw
exceptions if those functions are not understood.
Support for spatial literals would be a great plus. In my opinion, the
> best candidate for HQL spatial literals is the Postgis EWKT format (
> http://postgis.net/docs/manual-2.1/using_postgis_dbmanagement.html#EWKB_EWKT),
> which is a sane, and popular extension of the purely 2D WKT standard. It
> is supported in the geolatte-geom library and many others. I can help by
> writing a grammar for this.
>
What I have done thus far in SQM grammars is to borrow from the idea of
"JDBC escape syntax". So for example, to define a timestamp literal one
would say `{ts '2015-10-10 ...'}`
There is also at least one HQL syntax change that would help implement
> spatial functions: function invocations that evaluate to boolean should be
> regarded as valid boolean expressions. The HQL currently doesn’t seem to
> allow this, so I needed to wrap them in equality expressions. But then
> MySQL wouldn’t use its spatial index (as I wrote before, although I
> mistakenly wrote “spatial query” in my original email).
>
This would require some knowledge of the "metadata" of a function, which
is not there in SQM at all so far. Here, I'd have to know the return type
of the referenced function. We can contemplate that.
https://hibernate.atlassian.net/browse/SQM-15
9:52 p.m.
Hi Steve,
On 02 Nov 2015, at 16:16, Steve Ebersole <steve(a)hibernate.org>
wrote:
Any further thoughts?
I looked over the link you gave in regards to literals, but did not fully understand.
I'd prefer this follow some "escape syntax" form, beyond that maybe you
could just enumerate the literal types you'd want to support and their formats.
Spatial literals would be really nice to have, but then it is best to have also spatial
functions in the grammar. So let’s try to add them. The escape syntax form is OK for me.
We can have something like ‘{gm ‘SRID=4326;POINT(4.6 52.1)’}’ for a geometry literal.
How do we proceed? Do I write something up informally? Or is there a formalism you prefer?
ALso, I have not seen any discussion around "boolean-function-expressions as
predicates" on SQM-15…
I’ll add some comments on that issue.
Regards,
Karel
10:32 p.m.
On Mon, Nov 9, 2015 at 2:52 PM Karel Maesen <karel(a)geovise.com> wrote:
Hi Steve,
> On 02 Nov 2015, at 16:16, Steve Ebersole <steve(a)hibernate.org> wrote:
>
> Any further thoughts?
>
> I looked over the link you gave in regards to literals, but did not
fully understand. I'd prefer this follow some "escape syntax" form,
beyond
that maybe you could just enumerate the literal types you'd want to support
and their formats.
Spatial literals would be really nice to have, but then it is best to have
also spatial functions in the grammar. So let’s try to add them. The escape
syntax form is OK for me. We can have something like ‘{gm
‘SRID=4326;POINT(4.6 52.1)’}’ for a geometry literal.
I think it's best to not have the functions defined in the grammar.
Accounting for literals and accounting for functions are really 2 very
different things.
As for what I need... Well I need to understand what spatial types you'd
like to see definable as literals and what forms those literal definitions
might take. Like looking at your "example" one, I have zero clue what that
is supposed to mean.
How do we proceed? Do I write something up informally? Or is there a
formalism you prefer?
For now, I think conversation (here, irc, whatever) is better. Let's see
where that leads.
ALso, I have not seen any discussion around
"boolean-function-expressions
as predicates" on SQM-15…
I’ll add some comments on that issue.
Great, thanks...
3090
days inactive
3165
days old
7 comments
2 participants
participants (2)
-
Karel Maesen -
Steve Ebersole