[Geoserver-users] MySQL problem

GeoServer GeoServer User
1

Good evening,
I write because I have a problem on entering points (multipoint) through a
wfs-t with MySQL.
I believe there is an error in the input of MySQL insert statment. In fact,
mysql log shows the following query:

INSERT INTO t_coordinate_ritrovamento_punti_generale (the_geom, species)
VALUES (GeomFromText ('MULTIPOINT ((13.134224764997278 44.92654650996391))',
900913), 'Nutria Myocastor coypus')

The problem is connected to the double parentheses, and the result is that
the "the_geom" field remains empty.

The server configuration is:
Debian 8, MySQL 5.5, Tomcat 8.5.6 , Java 1.8.0_111, Geoserver 2.9.2,
Openlayer2

ps. I tried various versions of geoserver (from 2.2), but the result does
not change; I tried with ubuntu 16.04 LTS and mysql 5.7, and the problem
does not occur.
Unfortunately mysql 5.7 has problems on geometry rendering and therefore can
not be used.

Any suggestion is welcome because I do not know how to search for a
solution.

Thanks a lot,
Paolo

-----
Paolo Tomè
--
View this message in context: http://osgeo-org.1560.x6.nabble.com/MySQL-problem-tp5294671.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

2

Hi,

I am not sure, but I feel that is there is an issue with double parenthesis then it is rather in MySQL than in Geoserver. See http://gis.stackexchange.com/questions/200126/are-nested-parenthesis-allowed-in-multipoint-wkt/200134#200134.

-Jukka Rahkonen-

________________________________________
snuffer892 wrote:

Good evening,
I write because I have a problem on entering points (multipoint) through a
wfs-t with MySQL.
I believe there is an error in the input of MySQL insert statment. In fact,
mysql log shows the following query:

INSERT INTO t_coordinate_ritrovamento_punti_generale (the_geom, species)
VALUES (GeomFromText ('MULTIPOINT ((13.134224764997278 44.92654650996391))',
900913), 'Nutria Myocastor coypus')

The problem is connected to the double parentheses, and the result is that
the "the_geom" field remains empty.

The server configuration is:
Debian 8, MySQL 5.5, Tomcat 8.5.6 , Java 1.8.0_111, Geoserver 2.9.2,
Openlayer2

ps. I tried various versions of geoserver (from 2.2), but the result does
not change; I tried with ubuntu 16.04 LTS and mysql 5.7, and the problem
does not occur.
Unfortunately mysql 5.7 has problems on geometry rendering and therefore can
not be used.

Any suggestion is welcome because I do not know how to search for a
solution.

Thanks a lot,
Paolo

-----
Paolo Tomè
--
View this message in context: http://osgeo-org.1560.x6.nabble.com/MySQL-problem-tp5294671.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

------------------------------------------------------------------------------
Developer Access Program for Intel Xeon Phi Processors
Access to Intel Xeon Phi processor-based developer platforms.
With one year of Intel Parallel Studio XE.
Training and support from Colfax.
Order your platform today. http://sdm.link/xeonphi
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

3

Thanks for the quick response.
Who generates the mysql insert statment? perhaps the mysql extension of
geoserver?
There is a way to modify the insert statment?
Thanks a lot

ps. from
https://dev.mysql.com/doc/refman/5.7/en/gis-wkt-functions.html#function_geomfromtext

For example, both of the following function calls are valid, whereas before
MySQL 5.7.9 the second one produces an error:
ST_MPointFromText('MULTIPOINT (1 1, 2 2, 3 3)')
ST_MPointFromText('MULTIPOINT ((1 1), (2 2), (3 3))')

-----
Paolo Tomè
--
View this message in context: http://osgeo-org.1560.x6.nabble.com/MySQL-problem-tp5294671p5294678.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

4

that insert statement is generated in MySQLDialectBasic.java encodeGeometryValue (https://github.com/geotools/geotools/blob/master/modules/plugin/jdbc/jdbc-mysql/src/main/java/org/geotools/data/mysql/MySQLDialectBasic.java#L169) - it uses the standard JTS WKTWriter so you would need to either write a specific string for multipoints or edit it’s output - based on the version of the database, see https://stackoverflow.com/questions/34241534/get-mysql-version-with-java for clues on how to find that out.

Ian

···

On 7 November 2016 at 17:52, snuffer892 <paolo_tome@anonymised.com> wrote:

Thanks for the quick response.
Who generates the mysql insert statment? perhaps the mysql extension of
geoserver?
There is a way to modify the insert statment?
Thanks a lot

ps. from
https://dev.mysql.com/doc/refman/5.7/en/gis-wkt-functions.html#function_geomfromtext

For example, both of the following function calls are valid, whereas before
MySQL 5.7.9 the second one produces an error:
ST_MPointFromText(‘MULTIPOINT (1 1, 2 2, 3 3)’)
ST_MPointFromText(‘MULTIPOINT ((1 1), (2 2), (3 3))’)

Paolo Tomè

View this message in context: http://osgeo-org.1560.x6.nabble.com/MySQL-problem-tp5294671p5294678.html

Sent from the GeoServer - User mailing list archive at Nabble.com.

Developer Access Program for Intel Xeon Phi Processors
Access to Intel Xeon Phi processor-based developer platforms.
With one year of Intel Parallel Studio XE.
Training and support from Colfax.
Order your platform today. http://sdm.link/xeonphi

Geoserver-users mailing list
Geoserver-users@anonymised.com.382…sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

–

Ian Turton

5

Mysql version:
/innodb_version 5.5.52
protocol_version 10
version 5.5.52-0+deb8u1-log
version_comment (Debian)
version_compile_machine | x86_64
version_compile_os | debian-linux-gnu /

I have some difficulty locating the file to modify.
Could you give me more detailed explanation?

Thanks a lot
paolo

-----
Paolo Tomè
--
View this message in context: http://osgeo-org.1560.x6.nabble.com/MySQL-problem-tp5294671p5294783.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

6

I solved the problem by replacing *jts-1.13.jar* with* jts-1.10.jar* in
"/opt/apache-tomcat-8.5.6/webapps/geoserver/WEB-INF/lib"

Thanks a lot, best regards
Paolo

-----
Paolo Tomè
--
View this message in context: http://osgeo-org.1560.x6.nabble.com/MySQL-problem-tp5294671p5296582.html
Sent from the GeoServer - User mailing list archive at Nabble.com.