First understand a few basic concepts that help to understand the application scenario of this article

  • Geographic Information System GIS

GIS (Geographic Information System or Geo-Information system, GIS) is sometimes referred to as a “geographic information system”. It is a specific and very important spatial information system. It is in the [computer] hardware and software system support, the whole or part of the [Earth] surface (including the atmosphere) space in the relevant [geographic] distribution [data], [storage], [management], [computing], [analysis], [show]and [description] of the technical system

  • ArcGIS Platform

The ArcGIS product line provides users with a scalable, comprehensive [GIS]platform. ArcObjects contains Many programmable components, ranging from fine-grained objects (such as individual geometry objects) to coarse-grained objects (such as map objects that interact with existing ArcMap documents), which integrate comprehensive GIS functionality for developers.

  • Geometry data type

Geometry is a spatial geometry data type that is commonly used to describe spatial geometric information, such as coordinate points, lines, surfaces, and 3D information. That is, GIS generally uses the Geometry data type to store and display geographic information. The common databases that support Geometry are Oracle, SqlServer, Mysql, PostgreSQL Geometry supported in Sql-Server [document] and the geographic data types available in Sql-Server as shown

  • Application Scenario Example

map.png

As shown in the figure, the application mainly uses ArcGIS to do GIS, using SqlServer as the spatial database, using Java to build backend services to process the data, using JS Vue combined with ArcGIS API to do front-end rendering, successfully highlighting geographic information on the map

This article focuses on: using java to process Geometry data, with some simple instructions for the use of arcgis system A simple description of the processing process of a gis application system

With these services provided by ArcGIS, we can query and display spatial geographic information in a visual way. We’ll explain more about this later

1. Spatial data and WKT familiar text

When we use a database that supports spatial data, how do we now add spatial data?

Let’s start with WKT to understand what the spatial data Geometry looks like

[WKT], is a text markup language for representing vector geometry objects, spatial reference systems, and transformations between spatial reference systems. Its binary representation, WKB (well known binary), is superior for transferring and storing the same information in a database. This format was developed by the Open Geospatial Consortium (OGC). WKT can represent geometric objects including: points, lines, polygons, TINs Irregular Triangulated Network and polyhedra. Geometric objects can be represented in different dimensions by means of geometric sets. The coordinates of a geometric object can be 2D(x,y), 3D(x,y,z), 4D(x,y,z,m), plus a value of m that belongs to a linear reference system. The following is a sample geometric WKT string. POINT(6 10) LINESTRING(3 4,10 50,20 25) POLYGON((1 1,5 1,5 5,1 5,1 1),(2 2,2 3,3 3 3,3 2,2 2)) MULTIPOINT(3.5 5.6, 4.8 10.5) MULTILINESTRING((3 4,10 50,20 25),(-5 -8,-10 -8,-15 -4)) MULTIPOLYGON(((1 1,5 1,5 5,1 5,1 1 1),(2 2,2 3,3 3 3,3 2,2 2)),((6 3,9 2,9 4,6 3))) GEOMETRYCOLLECTION(POINT(4 6),LINESTRING(4 6,7 10)) POINT ZM (1 1 5 60) POINT M (1 1 80) POINT EMPTY MULTIPOLYGON EMPTY

Here we are clear, the so-called spatial data is a coordinate or a group of coordinates, this coordinate or group of coordinates have type (POINT point LINESTRING line POLYGON surface), through these coordinates, the GIS system can be a complete positioning query to plot these coordinate information

3. insert data into the spatial database

That database how to add these spatial data? First, let’s understand what the SQLSERVER spatial data insertion statement looks like

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- GEOM is a field of type Geometry --
--we add a 3D polygon data to this field--
--geometry :: STGeomFromText () is a function provided by SQLSERVER that converts WKT text to database geometry type data --
INSERT INTO [dbo]. [TEST_GEO_TABLE] ( [GEOM] )
VALUES
    ( geometry :: STGeomFromText ( 
    'POLYGON ((
        113.507259000000005 22.24814946 8, 
        113.507188600000006 22.248088559999999 9, 
        113.507117399999998 22.24802743 10, 
        113.507046099999997 22.24796624 11, 
        113.507017300000001 22.247888209999999 12
        ))',4326 )
    );

That is, by converting the coordinates into WKT text, we can insert the spatial data. The next thing we have to consider is how to generate the WKT text

4. Creating Geometry objects with Java

4.1 Java API for Common Geometry

The wkt text is just a string. Isn’t it enough to directly stitch the coordinate points into a string that conforms to the WKT format? The reason is the same, but it is difficult to do well.

  • The splicing workload is huge
  • The stitching process is error-prone
  • The result of stitching is not always legal and usable We need a set of JAVA API for data processing, which can easily create Geometry objects for geographic information drawing, creation, validation, etc.

The common GeometryApi on the market are

[Esri]

[locationtech]

Esri is the official javaSDK provided by Arcgis, but unfortunately it doesn’t have many features and can’t even provide basic spatial calculation functions. jts is more fully functional and relatively rich in information.

4.2 Some API usage of JTS

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

    @Test
    public void geoTest() throws ParseException {
        /**
         * GeometryFactory factory, parameter one: data precision parameter two spatial reference system SRID
         */
        GeometryFactory geometryFactory = new GeometryFactory(new PrecisionModel(PrecisionModel.FLOATING), 4326);

        /**
         * Familiar with text WKT reader to convert WKT text to Geometry object
         */
        WKTReader wktReader = new WKTReader(geometryFactory);

        /**
         * Geometry objects, including subclasses such as Point, LineString, Polygon
         */
        Geometry geometry = wktReader.read("POINT (113.53896635 22.36429837)");

        /**
         * Read the Geometry object as a binary stream
         */
        WKBReader wkbReader = new WKBReader(geometryFactory);

        /**
         * A single coordinate point, a single point can create Point, multiple points can create LineString, Polygon, etc.
         */
        Coordinate coordinate = new Coordinate(1.00, 2.00);
        Point point = geometryFactory.createPoint(coordinate);

        Polygon polygon = geometryFactory.createPolygon(new Coordinate[]{
                new Coordinate(1, 2),
                new Coordinate(1, 2). new Coordinate(1, 2),
                new Coordinate(1, 2). new Coordinate(1, 2),
                new Coordinate(1, 2),
                new Coordinate(1, 2),
        });
        Geometry geometry1 = point;
        Geometry geometry2 = polygon;

        /**
         * WKT exporter, write out Geometry object as WKT text
         */
        WKTWriter wktWriter = new WKTWriter();
        String write = wktWriter.write(point);
    }

4.3 Subclasses of the Geometry data type in JTS

jts.png

image.png

According to the documentation in github, we can already introduce the relevant coordinates into the project and create the Geometry object to construct the WKT text

5. Adding data to the spatial database using JAVA

Based on the use of Api in the test class above, let’s summarize a few key points

  • The factory class object should be initialized only once and should be placed in the configuration class injected into the Spring container
  • import relevant coordinate data from front-end or Excel to generate Geometry object
  • Persistent Geometry object to SqlServer

Two ways are recommended for persisting Geometry objects in this example.

  1. get the WKT text of the Geometry object, and then use the geometry :: STGeomFromText () function provided by SqlServer to store the WKT text as the database Geometry type
  2. convert the Geometry object in the jts package to the Geometry object in the SqlServer JDBC package, and persist the Geometry object in binary form to the database

Environment. This example code is based on JTS, SpringBoot, Mybatis-Plus, mssql-jdbc environment.

6. Insert Geometry data using TypeHandler mapping custom object fields

6.1 Custom TypeHandler

When we use the Mybatis framework, Mybatis provides a custom type converter TypeHandler to implement the mapping relationship between special objects and Sql fields

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.io.WKTReader;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
SQLException; import java.sql;


@Slf4j
@MappedTypes(value = {Geometry.class})
public class GeometryTypeHandler extends BaseTypeHandler<Geometry> {

    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Geometry geometry, JdbcType jdbcType) throws SQLException {
        /**
         * Get the wkt text of the jts package object, and then convert it to the geometry object of sqlserver
         * call ps's setBytes() method to persist the geometry object in binary
         */
        com.microsoft.sqlserver.jdbc.Geometry geo = com.microsoft.sqlserver.jdbc.Geometry.STGeomFromText(geometry.toText(), geometry.getSRID() );
        preparedStatement.setBytes(i, geo.STAsBinary());
    }

    @Override
    public Geometry getNullableResult(ResultSet resultSet, String s) {
        try {
            /**
             * Read the binary conversion from the ResultSet into a SqlServer Geometry object
             * Use jts's WKTReader to convert wkt text to jts's Geometryd object
             */
            com.microsoft.sqlserver.jdbc.Geometry geometry1 = com.microsoft.sqlserver.jdbc.Geometry.STGeomFromWKB(resultSet.getBytes(s));
            String s1 = geometry1.toString();
            WKTReader wktReader = SpringContextUtil.getBean(WKTReader.class);
            Geometry read = wktReader.read(s1);
            return read;
        } catch (Exception e) {
            log.error(e.getMessage());
            throw new ServiceException(e.getMessage());
        }
    }

    @Override
    public Geometry getNullableResult(ResultSet resultSet, int i) throws SQLException {
        return null;
    }

    @Override
    public Geometry getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        return null;
    }
}

6.2 Entity objects

The entity object is as follows.

  • objectid is an Integer type, non-self-incrementing (this field is maintained by Arcgis and cannot be modified) @TableId is a mybatis-plus plugin annotation that tells the plugin that the field is a primary key field, the field name is OBJECT, and the primary key policy is user input
  • shape is the Geometry object of jts (the object JSON serialization result is very scary, so use @JsonIgnore to modify it)
  • @KeySequence is also a plugin for mybatis-plus, and serves to identify the primary key sequence name that the object needs to use. Here I implemented an IKeyGenerator that works similar to querying Oracle for the sequence name to populate the primary key before inserting the data.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

@Data
@TableName("LINE_WELL")
@KeySequence(value = "LINE_WELL",clazz = Integer.class)
public class Well extends MyGeometry implements Serializable {

    @TableId(value = "OBJECTID", type = IdType.INPUT)
    private Integer objectid;

    @JsonIgnore
    protected Geometry shape;
}

6.3 Custom primary key generation strategy

In arcgis, the primary key field in the spatial table is int and non-self-incrementing and cannot be modified. When modified as self-incrementing arcgis will show some errors. Therefore, the java backend inserting spatial data needs to complete the primary key query generation by itself. IKeyGenerator is an interface provided by Mybatis-Plus. The role of this implementation is that when this primary key generation strategy is specified, the mp framework will call this implementation before adding data to assign the result to the ID of the object (similar to Oracle’s sequence) Note that this class needs to be injected into the Spring container

1
2
3
4
5
6
7
8

import com.baomidou.mybatisplus.core.incrementer.IKeyGenerator;

public class SqlServerKeyGenerator implements IKeyGenerator {
    @Override
    public String executeSql(String incrementerName) {
        return "select max(OBJECTID)+1 from " + incrementerName;
    }
}

6.4 Geometry Object Persistence

When we call the methods provided by mybatis-plus to persist the object

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

 String str = "POLYGON ((113.52048666400003 22.248443089000034, 113.5206744190001 22.24822462700007, 113.52082998700007 22.248343788000057, 113.52060468200011 22.248547355000028, 113.52048666400003 22.248443089000034))";
        Geometry read = null;
        try {
            /**
             * Here the wkt text is used to generate a Geometry object under the jts package
             */
            read = SpringContextUtil.getBean(WKTReader.class).read(str);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        Well well = new Well();
        well.setShape(read);
        // Here is the save interface provided by Mybatis-Plus, call its internal implementation to store the object directly
        wellService.save(well);
        System.out.println("Persistence successful");

The execution log is as follows. The data is inserted before the execution of the sql in SqlServerKeyGenerator to get the primary key Insert code in the field shape for the Geometry object binary

1
2
3
4

2022-08-30 15:54:23.541 INFO 8484 --- [nio-8905-exec-1] jdbc.sqltiming : SELECT max(OBJECTID) + 1 FROM LINE_WELL 
 {executed in 4 msec}
2022-08-30 15:54:23.631 INFO 8484 --- [nio-8905-exec-1] jdbc.sqltiming : INSERT INTO LINE_WELL (OBJECTID, shape) VALUES (3, '<byte[]>') 
 {executed in 17 msec}

7. Handwritten xml to insert Geometry data

Use the function geometry :: STGeomFromText( #{wktText},4326) provided by SqlServer to convert geometry to WKT text and then insert it

1
2
3
4
5
6
7
8
9

    <insert id="insertCorridorBySql" parameterType="com.zh.xxx.entity.xxx" useGeneratedKeys="true"
            keyProperty="objectid">
        INSERT INTO [LINE_CORRIDOR] (
         shape
        )
        values (
        geometry :: STGeomFromText( #{wktText},4326)
        )
    </insert>

Note that wktText is a temporary field that is not a table field, I have defined a parent class here that all spatial table entities that contain Geometry inherit from this class to handle wkt text

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.io.WKTWriter;

import java.io.Serializable;

/**
 * for Geometry to get Wkt text field to do processing of Geometry parent class, getWktText instead of getText, output three-dimensional wkt text
 * For sql_server can not recognize POLYGON Z syntax, the wkt text to replace
 */
@Data
public class MyGeometry implements Serializable {

    /**
     * 3D wkt output, default is 2D without Z
     */
    @TableField(exists = false)
    @JsonIgnore
    private WKTWriter wktWriter = new WKTWriter(3);

    /**
     * sql_server and jts wkt incompatibility issue
     */
    @TableField(exists = false)
    @JsonIgnore
    private static final String THREE_D_PRIFIX = "POLYGON Z";
    @TableField(exists = false)
    @JsonIgnore
    private static final String TWO_D_PRIFIX = "POLYGON";

    @JsonIgnore
    protected Geometry shape;


    @TableField(exists = false)
    @JsonIgnore
    private String wktText;

    public String getWktText() {
        if (StrUtil.isBlank(wktText)){
            if (getShape() ! = null) {
                String wkt = wktWriter.write(shape);
                if (wkt.startsWith(THREE_D_PRIFIX)) {
                    wktText = StrUtil.replace(wkt, THREE_D_PRIFIX, TWO_D_PRIFIX);
                } else {
                    wktText = wkt;
                }
            }
        }
        return wktText;
    }
}

8 Pit picking records

8.1 jts is not compatible with sqlserver recognized wkt

1
2
3
4
5
6
7
8

[2022-07-01 16:40:20,637] [ERROR] [http-nio-8905-exec-5] jdbc.audit 111 7. PreparedStatement.execute() INSERT INTO [zhundergroundcableline].[dbo].[LINE_CORRIDOR] ( [Shape] ) values ( geometry :: STGeomFromText( 'POLYGON Z((113.5079365 22.24850034 
0, 113.5078521 22.24845659 0, 113.5077674 22.24841271 0, 113.5076826 22.24836872 0, 113.5075978 22.24832498 0))',4326) ) 

SQLServerException: NET Framework error during execution of user-defined routine or aggregate 'geometry': 
System.FormatException: 24142: The value "(" should be at position 8, but the input is actually "Z".
System.FormatException: 
   WellKnownTextReader.RecognizeToken(Char token) at Microsoft.SqlServer.
   SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid)