Hi Mark,
Actually it seems that there are a number of functions that do not support
geography in 8.0 (more precisely this happened from version 8.0.11 and
above). In 5.7 there was no issue because independently of the SRID you
provided the actual computations were in cartesian space (no geography
issue) . These are:
- ST_Area
- ST_Buffer
- ST_Centroid
- ST_ConvexHull
- ST_Difference
- ST_Distance (geography support limited to points and multipoints)
- ST_Envelope
- ST_Intersection
- ST_IsClosed
- ST_MakeEnvelope
- ST_Simplify
- ST_SymDifference
- ST_Union
For these functions the passed argument must be a cartesian geometry object
(corresponds to SRID=0)
That mean the expression you mention above:
* SELECT ST_Envelope(ST_GeomFromText('P*
*OLYGON ((6 12, 8 14, 6 16, 4 16, 4 14, 6 12))',4326)) *
should be performed with explicitly setting srid to 0 or just omitting it
as follows:
*SELECT ST_Envelope(ST_GeomFromText('P*
*OLYGON ((6 12, 8 14, 6 16, 4 16, 4 14, 6 12))',0)) *
Now of course there are also situations where you may call ST_Envelope
passing an already existing geography object. In that case I can see 2
options:
- There is a new function available since v8.0.13 ST_transform() which
can be used to transform between srids. I suppose therefore that the issue
you mention can also be alleviated by wrapping geometry with an extra call
to it, before using any of the functions from the list I mentioned above.
These will provide a transparent solution to the problem of handling
geography data.
In that case probably something like the following will work:
*SELECT ST_Envelope(ST_Transform(ST_GeomFromText('P*
*OLYGON ((6 12, 8 14, 6 16, 4 16, 4 14, 6 12))',4326), 0))*
and then transform back*.*
What complicates things though in implementing such a solution is the fact
that this cannot apply but in versions of mysql higher or equal to v8.0.13.
What about earlier versions?
- Wrapping each geometry object passed to the prior mentioned spatial
function list to a call of ST_srid() function in order to cast to cartesian
srid =0 and then back again. This implementation is much simpler and should
alway work for any mysql 8.0 version
*SELECT ST_srid(ST_Envelope(ST_srid(ST_GeomFromText('P*
*OLYGON ((6 12, 8 14, 6 16, 4 16, 4 14, 6 12))',4326), 0)),4326)*
Of course the correctness of the 2nd solution is questionable since as
mysql spatial reference mentions:
-
ST_SRID()
<https://dev.mysql.com/doc/refman/8.0/en/gis-general-property-functions.html#function_st-srid>
changes the geometry SRID value without transforming its coordinates.
-
ST_Transform()
<https://dev.mysql.com/doc/refman/8.0/en/spatial-operator-functions.html#function_st-transform>
transforms the geometry coordinates in addition to changing its SRID value.
However I assume that adopting option 2 will just maintain the situation
that applies as of today in mysql-jdbc module
kind regards
Nikos
Στις Τρί, 8 Σεπ 2020 στις 5:54 μ.μ., ο/η Mark Prins <mc.pr...@gmail.com>
έγραψε:
> O Joy, it gets better.
>
> In MySQL 8 if you store epsg 4326 data you must be wanting to store
> geography and not geometry so let's demand that you flip the axis:
> https://dba.stackexchange.com/a/242004
>
> and then ofcourse things like this fail (on MySQL 8.0.21)...
>
> SELECT ST_Envelope(ST_GeomFromText('POLYGON ((6 12, 8 14, 6 16, 4 16, 4
> 14, 6 12))',4326))
>
> with "Nope can't do this"
>
> Seems that in 8 Geography is a new feature and it needs to be shown off...
>
> Mark
>
> On 04-09-2020 15:23, Mark Prins wrote:
> > Ok, thanks.
> > I'll get to work on it
> >
> > Op wo 2 sep. 2020 om 13:57 schreef Nikolaos Pringouris
> > <nprig...@gmail.com <mailto:nprig...@gmail.com>>:
> >
> > Hmm probably I have missed/overlooked these 2 functions when adding
> > support for ST_... functions in geotools 19.x.
> > In any case as you already mentioned the flag is direct available
> > there so you easily add support for the corresponding ST_ functions
> > at that point.
> >
> > Concerning Mysql v5.5 I think you are right. IMHO there is no need
> > to further support them in newer versions of geotools.
> >
> >
> > Στις Τετ, 2 Σεπ 2020 στις 2:36 μ.μ., ο/η mark <mc.pr...@gmail.com
> > <mailto:mc.pr...@gmail.com>> έγραψε:
> >
> > On 2020-09-01 19:24, Nikolaos Pringouris wrote:
> > > Hi All,
> > >
> > > A couple of years ago I worked on jdbc-mysql module and
> > provided support
> > > for the newly introduced ST_ functions in Mysql5.6 & Mysql
> > 5.7. I think
> > > these functions are also supported in Mysql8.0 with the same
> > signature
> > > so I do not think there is an issue of incompatibility.
> >
> > the problem is the code uses old/removed functions in several
> places
> >
> > If you check the
> > > Data Store factory for Mysql (MySQLDataStoreFactory) you will
> > see that
> > > there is a static function isMySqlVersion56(...) which is
> > called during
> > > creation of the dataStore and if figures out that mysql 5.6
> > or above is
> > > used in enables enhancedSpatialSupport flag automatically and
> > the use of
> > > the corresponding ST_ functions (check also
> > > visitBinarySpatialOperatorEnhanced(...) function in
> > MySQLFilterToSQL
> > > class).
> >
> > this would/my have worked for 5.7, but not 8 as the old
> > functions have
> > been removed, this code:
> >
> >
> https://github.com/geotools/geotools/blob/3496184670f6bb5a7b3af877fed3f312ce86f9a2/modules/plugin/jdbc/jdbc-mysql/src/main/java/org/geotools/data/mysql/MySQLDialect.java#L225-L238
> >
> > simply fails with a database error that function asWKB does not
> > exist
> >
> > same for
> >
> >
> https://github.com/geotools/geotools/blob/3496184670f6bb5a7b3af877fed3f312ce86f9a2/modules/plugin/jdbc/jdbc-mysql/src/main/java/org/geotools/data/mysql/MySQLDialectPrepared.java#L194-L205
> >
> > I can add the enhanced flag around those as a quick scan of the
> > code
> > seems that it's limited to these instances. maybe this was an
> > oversight
> > when a different dev worked on the code and didn't know about
> > that flag,
> > (suggesting the design could be improved)
> >
> > > Of course mysql 8.0 may have additional spatial features (to
> > be honest I
> > > am still using version 5.7.x in my geotools related projects)
> > and
> > > probably the mysql module can still be improved but I am
> > pretty sure
> > > that the module will still be functional when the underline
> > Mysql DB is
> > > v8.0 (in addition to 5.5, 5.6, 5.7).
> > >
> >
> > I don't see any point in supporting 5.5 (EOL December 2018) as
> > it is no
> > longer part of the support matrix, if you want to use an
> > unsupported
> > database software you can use an old version of
> > geotools/geoserver as
> > well IMO.
> > 5.6 will be EOL in a few months (February, 2021)
> >
> >
> >
> > _______________________________________________
> > GeoTools-Devel mailing list
> > GeoTools-Devel@lists.sourceforge.net
> > <mailto:GeoTools-Devel@lists.sourceforge.net>
> > https://lists.sourceforge.net/lists/listinfo/geotools-devel
> >
> >
> >
> > --
> > Disclaimer;
> > This message is just a reflection of what I thought at the time of
> > sending. The message may contain information that is not intended for
> > you or that you don't understand.
>
>
>
> _______________________________________________
> GeoTools-Devel mailing list
> GeoTools-Devel@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/geotools-devel
>
_______________________________________________
GeoTools-Devel mailing list
GeoTools-Devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geotools-devel
