Postgis - How to work with data type 'geography' via JDBC

Question

Working on a web project, which uses postgresql 9.3 postgis 2.1.

There is a column of type geography in the table, it just store a point.

Now I need to insert / select the type via JDBC with Java object.

After reading postgis manual, didn't find much information relevant.

The questions are:

1. In java pojo model class, what Java data type should be used for the column?
2. How to write the insert sql?
3. How to write the select sql to retrieve the value and put into Java object?
4. If mybatis is used, then does it effect answers to above questions?

Answer 1

You don't need special types for POINT geometries. Just use primitive data types like double for the coordinates.

E.g., to insert a new geography type via lon and lat parameters, use geometry constructor functions:

INSERT INTO my_table (geog)
VALUES (ST_SetSRID(ST_MakePoint(:lon, :lat), 4326)::geography);

Or get them back as floating point numbers, use geometry accessor functions:

SELECT ST_Y(geog::geometry) AS lat, ST_X(geog::geometry) AS lon FROM my_table;

There are other input/output formats, like GeoJSON, WKT, etc.